8 Replies Latest reply on Aug 5, 2014 8:26 AM by philmodjunk

    Script to change values in Found Set

    sccardais

      Title

      Script to change values in Found Set

      Post

           My question is related to a join table (Transactions_JOin) that is part of a system to manage various aspects of a neighborhood Homeowner Association.

           This question involves the interaction between three tables. Owners. Transactions_JOin. Lots.

           Transactions_JOin is a join table linking Lots to Owners.  Current Owners are identified in the Transactions_JOin table with a Status Field. The status field for every record in Transactions can be "Current" or "Previous." It must be one or the other.

           The Status field can't be blank. There must be one "Current" for each Lot. e.g. all transactions with the same Lot_ID cannot have a status of "Previous." There must be one (only one) Status = "Current"

           For each group of identical Lot_ID's, the one with the most recent transaction date should be set to "Current."  All other records with the same Lot_ID should be set to "Previous."

           How would I do this via a script?

        • 1. Re: Script to change values in Found Set
          philmodjunk

               I still don't think you need the field.

               I would do the following in order to link directly from the current record of Lots to the current owner:

               Owners|Current-----<Lots----<Transactions>----Owners

               Owners|Current::__pkOwnerID = Lots::_fkcCurrentOwnerID

               Owners|Current is a new Tutorial: What are Table Occurrences? of teh Owners table.

               Define _fkcCurrentOwnerID as a calculation field with this expression:

               Transactions::_fkOwnerID

               As long as the relationship between Lots and Transactions is sorted by Transaction Date in descending order as described in your previous thread, this field will return the ID of the current owner of any given lot.

               It IS an unstored calculation, but as long as you use it from the context of Lots to refer to Owners|Current, it will work.

          • 2. Re: Script to change values in Found Set
            sccardais

                 Phil:

                 This is still not working as a sorted relationship. I *think* I followed your instructions. The attached image shows the steps I took and the result. Basically, the sorted relationship is not always showing the current owner based on the transaction date. The image combines 7 smaller images that I'm sure will be self-explanatory to you.

                 If I wanted to use the Status approach (!) what would the script look like that I asked about in my last post?

            • 3. Re: Script to change values in Found Set
              philmodjunk

                   I ran some tests.

                   It worked for me, but I had to use a script to update the layout with: Refresh Window [Flush cached Join results]

                   to get the layout to update to show the right "current owner" data.

              • 4. Re: Script to change values in Found Set
                sccardais

                     Phil

                     Did not work for me. See attached.

                     https://www.dropbox.com/s/ayh3i3ohugobscb/PhilModJunk%20Test%20Copy.fmp12

                     Added the script you described. Ran it manually and attached to a script trigger when the layout, Current Owner, loads. Lot # 19 in Current Owners should be Leenan - not Cobb. See Transactions layout, lot #19. Leenan transaction is more recent than Cobb.

                     Why do I get the impression you're not keen on the Status = Current approach ? :) I thought that was a GREAT idea!

                     Just kidding of course. I appreciate your help and I know I'm learning something in this process.

                      

                • 5. Re: Script to change values in Found Set
                  philmodjunk

                       It's an extra field that will result in errors if you fail to keep them correctly updated and seems a needless complication.

                       Of course I'm finding what I am recommending as an alternative is not as simple as I'd like either...

                       Your _fkcCurrentOwnerID calculation was not correctly defined to evaluate from the context of ow_Lots_Current and it did not reference the Ow | Current::__pkOwnerID field.

                       Your settings will work, however, but only if you open the Lots to transactions relationship and specify the needed sort order on the transaction date field.

                  • 6. Re: Script to change values in Found Set
                    sccardais

                         Eureka - but not without some drama.

                         I tried making the changes outlined in the first paragraph of your reply above and that "broke" the database -- spinning beachball. FileMaker Not Responding, etc. Fortunately, having learned my lesson yesterday, I had a backup so I tried the suggestion in the 2nd paragraph and it worked.

                         Honestly, I can't say yet that I understand WHY it works. I understood "the Why" of the Status = Current approach but I don't understand your method yet. I'm not asking you to explain it either! For now, I'm just going to accept that it works and hope to understand why later.

                         Thanks very much for your help and patience.

                         Thanks, too to Fenton Jones

                          

                    • 7. Re: Script to change values in Found Set
                      sccardais

                           The breakthrough yesterday created a list of Lots and their current owners but I wasn't able to create a list of Owners and their Lots. I have no idea why. I assumed this would be simple but it wasn't. I tested the idea of identifying the Current Owner for each lot by adding a new field (CurrentOwnerID) to either Lots or Transactions and both approaches seemed to work. The new field is populated with the OwnerID of the current owner. Unfortunately, this is a manual - error prone - process.

                           I outlined a script to automate this but could use help in making it a real script. Here's my outline:

                      SCRIPT TO IDENTITY THE CURRENT OWNER OF EACH LOT

                            

                           # Create a list of each of each LotID based on values from the Lots table.

                            

                           # Go to a layout showing all Transactions

                            

                           Loop through the LotID's starting with the first LotID number

                            

                           Perform a find for each LotID value

                            

                           If the found count is NOT greater than 1 (only1 transaction)

                            

                           # Set field Transactions:Current Owner with the Owner ID of the found record

                           # Go to the next Lot #

                            

                           If the Found Count is more than 1 …

                            

                           # Sort by Transaction Date in Descending Order. (most recent on top)

                           # Get the Owner ID of the first record. (Most recent transaction date)

                           # Set field Transactions:Current Owner with the Owner ID of the first record

                            

                           For the remaining records in this found set, set the Current Owner field to blank

                            

                           # Omit the first record

                           # For the remaining records, Set field Transactions: Current Owner to blank ""

                            

                           Go to the next LotID and repeat the process.

                            

                           # Stop after the last LotID

                      • 8. Re: Script to change values in Found Set
                        philmodjunk

                             The changes I specified in my previous post should not have locked up your file. I think you should run a recover on your current copy just to be on the safe side.

                             the _fkcCurrentOwnereID field is an unstored calculation and has to be due to it's "copying" data from a related record in the transactions table. That works from Lots to Owner, but not from Owner to Lots--which is probably why you are having trouble getting a list of owners and their lots. But keep in mind that each owner can own more than one lot. Such a list would require that you list the same owner more than once--which can't be done from the owner layout anyway. You'd need a portal to transactions that filters out old transaction records for lots that the owner previously but does not currently own. That can be done by adding in more table occurrences or I am beginning to think that a stored, indexed field in Lots that stores the current owner's ID might be the simpler option after all.

                             Scripts linked to triggers and buttons that delete transaction records (such as to correct an error) can be used to update such a field.