1 2 Previous Next 16 Replies Latest reply on Jul 5, 2012 2:17 PM by gregcaine

    Layout based on LAST record based on Field in table

    gregcaine

      Title

      Layout based on LAST record based on Field in table

      Post

      I can't quite figure out how to get ONLY the last record from a quotes table. I only want to see the most recent record for each unique email address. For example, a quote can be generated 7 times by an individual - I ONLY want to view the most recent quote (i.e., max system generated numeric auto id). Additionally, I filter the table for the entire day so there can be dozens of unique email addresses in the results view.

      I am used to using grouping functions dynamically when using an ACCESS form. How do I efficiently use a relationship (or calculated field) for my Layout source? 

        • 1. Re: Layout based on LAST record based on Field in table
          philmodjunk

          What tables and relationships have you created for this purpose?

          I'm guessing that you have something like this:

          Customers------<Quotes

          And that you want to see a list of customers but with data from only the most recent quote record listed with each customer.

          If so,

          Option 1:

          Open Manage | Database | Relationships and double click the relationship line between these two table occurrences.

          Use the dialog that opens to specify a sort order for Quotes that sorts by your QuoteID serial number in descending order. When you place fields from Quotes on your Customers based layout, this sort order will make the most recent quote record for each customer the "first" record and those fields will then display data from this most recent record.

          Option 2:

          On your customers layout, add a portal to quotes. Specify the same sort order described in Option 1, but do it in portal setup for just this one portal on just this one layout. (Specifying the sort order at the relationship level affects the order of related records anywhere you reference this relationship throughout the database file.)

          • 2. Re: Layout based on LAST record based on Field in table
            gregcaine

            No other table. Just quotes. Already tried your suggestion with a self join but no luck. I ONLY want to see the last record and NOT have to scroll through the older records. 

            • 3. Re: Layout based on LAST record based on Field in table
              philmodjunk

              I suggest you create that related customers table with one record per customer. That enables you to avoid entering the same data on each customer over and over again as well as making it easier to set up your desired report. It also gives you the option of a customers layout with a portal that lists all quotes made to that cusotmer--and the most recent can be the first quote listed.

              Sticking with just one table, the self join approach can work, but only if you have a unique identifier for each customer--which also argues for using a related customers table as a safe/reliable way to generate those IDs.

              Say you define this self Join:

              Quotes::CustomerID = MostRecentQuotes::CustomerID

              and specify a sort order as previously described.

              Set up a list view layout and remove the body layout part. Use Part set up to add a sub summary part "when sorted by" CustomerID.

              Put your quote data from MostRecentQuotes in this subsummary part and make sure that you keep your records sorted by CustomerID, If you want the list alphabetical by customer name, sort by name, and also by customerID.

              This will produce one row for each customer with data from the most recent quote record for each.

              • 4. Re: Layout based on LAST record based on Field in table
                gregcaine

                The data is not concerning an overlaying object such as a customer, only a quote from a website. The email is the unique identifier. I tried your approach above and it did not group by email and show only the most recent record. The quotes tables has an auto generated number. Any other suggestions?

                • 5. Re: Layout based on LAST record based on Field in table

                  If you have portals understood and how to sort by date in the order you want, just reduce the portal to one line and only the first or last record in that portal will be visible, depending on your sort preference.

                  This is infinitely easier than any method I've seen discussed...  :)

                  Now, consider that portal row. Think outside the box and notice that it can be size adjusted and that you can make it dozens of lines tall and wide as the grand canyon (or a bit less than your computer monitor).

                  So that portal row becomes quite useful info wise.

                  Also, you can duplicate the portal row and change the sort order and show it above or below the other one or use a tab with two tabs, one up and one down....etc.

                  • 6. Re: Layout based on LAST record based on Field in table
                    gregcaine

                    I have been using the portal to accomplish the identification process (successfully) but not filtering efficiently. I will keep experimenting.

                    I really wish I could build an SQL Execute as part of a script on Layout Load that would filter to show ONLY the records I want to see without using a portal. The reason I say this? After the quote is built, I then join the most recent quote with other tables using portals. You can see how portals on portals on portals gets a little busy as well as the relationship building can be exhausting when you all you really want is a record source build with a grouping function. As always, any last thought is appreciated.

                    • 7. Re: Layout based on LAST record based on Field in table
                      philmodjunk

                      this is infinitely easier than any method I've seen discussed...  :)

                      Actually that WAS one of the methods being discussed.

                      The method I've described with the sub summary layout parts will work, it's a standard trick to get one row per group of data with a common value, you just need to set it up to use the email address as the unique identifier.

                      Here's a demo file of the concept: https://dl.dropbox.com/u/78737945/SummaryGroupingDemo.fp7

                      And a table of emails, one for each email can be very easy to setup from your table of quotes should you choose to use that option.

                      After the quote is built, I then join the most recent quote with other tables using portals.

                      Feel free to describe that process in more detail. It's possible that Jack, Myself or another forum participant can suggest ways to make that easier to do from the data you have to work with here.

                      • 8. Re: Layout based on LAST record based on Field in table
                        gregcaine

                        I am trying to emulate the summarygroupdemo.fp7 and am close; It appears that I can not figure out how to get a list to appear as a record in a list format  I am sorting in the table2 by unique id desc (email is = in both tables). How do I get the "record" navigation to show in the list format? Maybe this is the issue at this point

                        • 9. Re: Layout based on LAST record based on Field in table
                          philmodjunk

                          Every layout has three "view as"options you can select for a given layout. View as List, View as Table, View as Form.

                          In the demo file, the data layout has "view as table" selected as a simple way to show all the data. List view is specified for the report layout as it is needed in order to use the sub summary layout part that makes the one row/Email address format possible. You can manually select the view option by clicking one of the "view as:" buttons in the status area or you can use a script to specify the view option. You can also open up layout setup and restrict the view as options for a given layout.

                          Script triggers, BTW, have been placed on both layouts to sort the data to an order appropriate for that layout so the data on the data layout unsorts when you first access the layout and data on the report layout sorts by Email address when you first access that layout.

                          • 10. Re: Layout based on LAST record based on Field in table
                            gregcaine

                            resolved...In addition to all the help from the Comments here, I was reading the FileMaker Pro Bible and was reminded that a NEW LAYOUT has a List View choice. By starting a fresh layout in a List View (versus a Blank Layout), the sub section grouped properly. I now have a functional layout but have one last question. Is there a script function that returns you to the same record in the list after you open and close another window in FileMaker? My focus returns to the top of the List in the layout after the email script opens and closes a new FIleMaker window. Much appreciate any last assistance.

                            • 11. Re: Layout based on LAST record based on Field in table
                              philmodjunk

                              It's actually quite easy to set up a blank layout with sub summary parts and to specify the required list view. Once you know how, it's often many times quicker than using the new layout wizard that will take you through a lot of steps that may not apply to your specific layout needs.

                              Opening and closing a FileMaker window (nor a dialog) should not in itself change the current record in another layout. Can you describe what you are talking bout in more detail?

                              • 12. Re: Layout based on LAST record based on Field in table
                                gregcaine

                                I could not get the SUB SUMMARY, Required List View, to group as much as I tried. Maybe a good nights sleep was all I needed. With regard to the windows and losing my record..let's say I on record 35 out of 50. WHen I click the button to open another window, send an email and close the window...the current layout scrolls back to the top. I even tried to remark out the script on form enter to eliminate resorting. No luck.

                                • 13. Re: Layout based on LAST record based on Field in table
                                  philmodjunk

                                  I could not get the SUB SUMMARY, Required List View, to group as much as I tried.

                                  Did you sort the records to group them?

                                  Sorting the records does not change your current record. It can, however scroll the window, but it should leave the current record visible on the layout.

                                  When you "open a window to send an email", are you selecting send mail from the file menu or doing something else?

                                  Do you have FileMaker Advanced? If so, try enabling the script debugger just before opening the additional window. Then watch to see when/how the underlying window changes.

                                  • 14. Re: Layout based on LAST record based on Field in table
                                    gregcaine

                                    After opening a NEW window and closing...the list of email addreses goes from the record I click all the way back to the first record. Is it a script that requeries? If someone can help, that would be great.

                                    1 2 Previous Next