9 Replies Latest reply on Apr 14, 2011 6:00 AM by matthijsbeekman

    Identifying most recent versus less recent related records in Filemaker Pro 9

    keswick

      Title

      Identifying most recent versus less recent related records in Filemaker Pro 9

      Post

      I want to able to go through related records in a table and identify which one is the current version and which ones are older (stale) versions, so they can be marked as active versus archived.  For example, I have a People table with records on individuals, each person with a unique PersonID.  A given person may have multiple records, however, reflecting changes in position, organization etc over time.  I would like to have a script that would go through the file, sort the records by PersonID and date (I know how to do that much) and then mark the most current record for each PersonID as “active” and the other records as “archive”.

        • 1. Re: Identifying most recent versus less recent related records in Filemaker Pro 9
          davidhead
             Are you suggesting that there are multiple records in the People table with the same "unique PersonID"?
          • 2. Re: Identifying most recent versus less recent related records in Filemaker Pro 9
            TSGal

            keswick:

             

            Thank you for your post.

             

            David/uLearnIT brings up a good question.  However, from your post, it appears you do have multiple records with the same PersonID.  Therefore, you may want to create a script that identifies the current record, marks it "active", and mark all multiples as "archive".

             

            For this example, let's assume the following fields.  Change it to fit your needs.

             

            PersonID (Text)

            EntryDate (Date)

            Status (Text)

             

            Create a new script with the following steps:

             

            Go to Layout ["Layout #1"]

            Enter Browse Mode []

            Show All Records

            Sort Records [Restore; no dialog]   (Sort by PersonID Ascending, EntryDate Descending)

            Go to Record/Request/Page [First] 

            Set Variable [$ID; Value: ""]

            Loop

               If [PersonID = $ID]

                  Set Field [Status; "Archive"]

               Else

                  Set Field [Status; "Active"]

                  Set Variable [$ID; Value: PersonID]

               End If

               Go to Record/Request/Page [Next; Exit after last]

            End Loop

             

             

            This script goes to the appropriate layout, enters Browse mode and shows all records.  From there, we sort the records by PersonID and then by reverse EntryDate order, ensuring the first record in each PersonID group is the most current/active record.  Then, we go to the first record in the database.

             

            We will use a variable $ID to keep track of the PersonID, and initialize it to a blank string.

             

            Next, we enter the loop.  Inside the loop, we evaluate the PersonID.  If it equals the previous record ID, then we mark the current record as "Archive".  If it is different, then we know it is the first occurrence of that PersonID and set the Status to "Active".  Then, we assign the variable $ID to the current PersonID.

             

            Then, we go to the next record.  If we reach the end of the file and all records are processed, we exit the loop.

             

            I hope this helps.  If you need clarification for any of the above steps, please let me know.

             

            TSGal

            FileMaker, Inc. 

             

            • 3. Re: Identifying most recent versus less recent related records in Filemaker Pro 9
              HBMarlowe_1
                

              keswick wrote:
              A given person may have multiple records, however, reflecting changes in position, organization etc over time.
              This suggests that your structure is not working well for you. Perhaps you should have a join table between Organizations and People. You might call it Affiliations, and it would contain foreign keys for OganizationID and PeopleID, as well as fields for position, etc.

               


              • 4. Re: Identifying most recent versus less recent related records in Filemaker Pro 9
                keswick
                  

                Thanks for the script suggestion - it works great! 

                 

                There was one interesting minor issue I had with the Set Field step - it appears that there are two ways to specify the target field and value, but only one "worked".  Method (A) uses the two buttons on the script editor window (Specify - to choose the target field; and Specify - to set the value in a calculation window). Method (B) uses the first Specify button to set the target field and then uses the Specify button that comes up on the sub-window where the field is chosen - that also goes to the calculation window.  When I used the (A) method the field was filled out; when I used the (B) method, it wasn't (though I could tell that the records were being identified as "active" or "archive" correctly by putting in a custom dialog). The (B) method put extra square brackets around the value (it looked like: SetField[Table::Field["text"]]) , which tipped me off.

                • 5. Re: Identifying most recent versus less recent related records in Filemaker Pro 9
                  TSGal

                  keswick:

                   

                  I'm a little confused with the two methods you mentioned.

                   

                  I used Method (A).  That is, I clicked the two buttons for "Specify target field" and "Calculated result".

                   

                  If I understand your Method (B) correctly, I clicked on "Specify target field", I still had to select the field.  I don't know how you were able to get the field to automatically display.  Once I selected the first "Specify" button, inside that field, the only other "Specify" button was to determine the repetition (in case the field had repeating values).  Is this what you are talking about?  Instead of looking for a static 3rd or 4th repetition, you can create a calculation to get the desired repetition (last repetition, repetition with a certain date, etc.).  I'm assuming this is what you are encountering.  Does this make sense?

                   

                  TSGal

                  FileMaker, Inc.

                  • 6. Re: Identifying most recent versus less recent related records in Filemaker Pro 9
                    keswick
                       I think that in Method (B) I ended up putting the desired text value in the repititions field without realizing it.  When I clicked the Specify button on the floating window (where I selected the target field) it brought up the Value window (looking just like it does in Method A where it is specifiying the field value), so I overlooked that the button was associated with the Repititions field and that window was for specifying the repitition value.
                    • 7. Re: Identifying most recent versus less recent related records in Filemaker Pro 9
                      Lex262
                        

                      2 Fold additional related question:

                       

                      Can a stale record be "locked" to protect it if it dates old than say 18 months? Still viewable, but data unchangeable/protected. (My example doesn't pertain to duplicate information, just any records older than MM/YYYY

                       

                      If so, can it then be unlocked by password should it require access by management to change any data found to be in error in the future? 

                      • 8. Re: Identifying most recent versus less recent related records in Filemaker Pro 9
                        davidhead
                           Yes, old records can be locked via the security privilege set. Presumably you have a date field that you could refer to in the expression? You need to investigate Privilege sets and look at the options for limiting editing of records.
                        • 9. Re: Identifying most recent versus less recent related records in Filemaker Pro 9
                          matthijsbeekman

                          My problem is related:

                          In my main table i have the following fiels:

                          Date, Item1Price1

                          Now i have a second table which is a price list.

                          It has the following fields: Mutation Date, Itemname, Price1, Price 2, Price 3 (Where the different prices depict different ammounts of the item)

                          My goal is to set the Item1Price1 field in het main table to the current price as set in the pricelist.

                          At first I related the two tables using the Date and Mutation date. This way, the price changes in the future are not used and records from the past are not changed with today's prices. I can't however figure out how to make a subselection of the related tables. At default FMP seems to take the first one. I would like to first cancel out all the other items and then pick the most current record. Setting multiple relationships won't do since i would like to retrieve prices for multiple items.