8 Replies Latest reply on Sep 6, 2013 8:14 PM by LisaB

    how do I determine latest record and then get a total ?

    LisaB

      Title

      how do I determine latest record and then get a total ?

      Post

           I'm pretty new to FIlemaker. I have made an inventory database. Many of the inventory items have been appraised and have been assigned values. Some items have been appraised once, some have been appraised twice, some have not been appraised at all. Each Inventory ite has a unique ID and each apparaisal  for each Inventory item has been recorded on a separate record with a unique ID number and date.  Appraisal Records are sorted in descending order.

           example:

           Appraisal Date           Appraisal Record ID          Inventory item ID             Appraisal  Value

           1999                           A0001-1                              0001                                $ 1,000           (Inv item 0001has only 1 appraisal)

           1999                           A0002-1                              0002                                $ 2,000

           2013                           A0002-2                              0002                                $ 4,000           (Inv item 0002 has 2 appraisals)   

           1999                           A0003-1                              0003                                $ 3,000

            2004                         A0003-2                               0003                                $ 6,000

            2012                         A0003-3                               0003                                $ 9,000          (Inv item 0003 has 3 appraisals) 

                                                                                          0004                                                      (Inv item 0004 has NO appraisals)
                               

           I want to be able to add up the values of only the MOST RECENT appraisal record amount for each inventory item.

           in other words I want to add up ONLY  Appraisal Record  A0001-1               $  1,000

                                                                         Appraisal Record  20001-2               $  4,000

                                                                         Appraisal Record  A0003-3               $  9,000

                                                                          For  a Total of                                   $14,000

           I've been able to figure out how to display the most only  recent records through a one line portal sorted in descending order, but I cannot figure out how to make a summary field for them.

           Please help.

        • 1. Re: how do I determine latest record and then get a total ?
          philmodjunk

               The best ID values to use in FileMaker relationships are simple auto-entered serial numbers. If your users need more complex identifiers that include leading zeroes or some text, do that in a separate field and don't use it in your main "backbone" relationships to link tables.

               Assuming that you have this relationship:

               InventoryItems----<Appraisals

               where you match a given InventoryItem to multiple Appraisals by an InventoryItemID, Then you can double click the relationship line and specify a sort order that sorts your Apprisals records by Date in descending order. THis requires a field that is truly of type date or a "year" field of type number. A text field won't work for this sort order.

               With that relationship, you can simply add the fields from Apprisals to your InventoryItems layout and you will see data from the most recent appraisal. If you don't want to change this sort order in the relationship, you can use a one row portal and specify the same order as a sort order for the portal.

          • 2. Re: how do I determine latest record and then get a total ?
            LisaB

                 Thanks for your response. I already sorted the relationship by date (the year really is a year, not text) and made a one line table . So I'm glad you confirmed that method.

                 What I want to do now is to add together ONLY the most recent appraisals to get a total. 

                 I copied the the one line portal, put it in a grand summary part and added summary field to the portal in but it didn't work.

                 I think I need to have some sort of field (a Calculation field?) that is called Most Recent Value (or something like that) which calls the most recent value and then can be used to generate a total of most recent values.
                 Any suggestions?

                  

            • 3. Re: how do I determine latest record and then get a total ?
              philmodjunk

                   You'll need either a sorted relationship (not a sorted portal) or ExecuteSQL so that a calculation field can extract that most recent value.

                   IF you specify a sorted relationship, the calculation field simply contains the name of the field from Appraisals and now you can set up a summary field to compute  summary value in inventory.

              • 4. Re: how do I determine latest record and then get a total ?
                LisaB

                     I have a sorted relationship but the summary field I set up added ALL the appraisals, not just the most recent.


                     Since the appraisal records are sorted in descending order, for the calculation field, would GetNthRecord work? 
                     Something like InventoryItem ID=InventoryItem ID AND GetNthRecord(AppraisalYear ; 1)

                • 5. Re: how do I determine latest record and then get a total ?
                  philmodjunk

                       Sounds lik you've defined the summary field in appraisals instead of inventory:

                       Step 1: define a calculation field in Inventory that returns the value of the most recent related record that you want to include in your total.

                       Step 2: Define  a summary field in Inventory that summarizes this new calculation field.

                  • 6. Re: how do I determine latest record and then get a total ?
                    LisaB
                              you are right about that (summary field in the wrong table) !
                         The next part is where I'm a little lost.
                         Can you suggest a calculation for returning the most recent related record?

                         Thanks for your patience on what is probably an elementary operation.

                    • 7. Re: how do I determine latest record and then get a total ?
                      philmodjunk

                           It's simpler than you think. wink

                           If you want a calculation field from Apprasials that returns the latest value and the relationship is sorted in descending order by date, then to you just put the name of the field from Appraisals in the calculation dialog, no operators or anything.

                           So if you want a calculation for the latest value of Appraials::Appraised Value, your calculaiton field defined in Inventory would be:

                           Appraisals::Appraised Value

                      • 8. Re: how do I determine latest record and then get a total ?
                        LisaB

                             Well, how about that. Worked perfectly and ultra simple. Thanks a million.