1 2 Previous Next 27 Replies Latest reply on Jul 28, 2017 10:38 AM by disabled_brianb

    Find Matching Record and Set Field

    cmj

      I need some guidance on scripting to update some fields in another table, providing one specified field in one table matches a specified field in the other table.

       

      The tables are as follows:

      Connection Table 1 to Table 2 is one to many. Allow creation of records in Table 2 via the relationship.

      Connection Table 1 to Table 3 is a one to many. Allow creation of records in Table 3 via the relationship.

       

      Layout 1 is based on Table 1 and contains a portal based on Table 2.

      Table 3 values reside in layout 3 (in layout view). There can be many records in layout 3 because it's in this layout that all records are created - except for the 1 to a maximum of 4 records that are to be populated from the portal in layout 1.

       

      The goal is to have table 2 populate table 3 (via trigger script on a portal in layout 1). If there is a match in records from a portal row in Layout 1 with any of the records in table 3, I want to set some of the fields in table 3 to match those in table 2.

       

      I'm guessing that:

      First I have to see if any records match a specific field in the portal with a specific field in table 3.

      If there is no exact match, I want to create a new record in table 3 and set various fields in table 3 to match the fields in table 2 (based on the portal row I'm sitting on). I can already do this.

      If there is a match (there would only be a maximum of 1 match), I need to set various fields in table 3 (on the record that matches) to be the same as the various fields in table 2 (in case the values have changed from the time the record was originally created).

       

      I do not have a clue as to what script functions and steps I need to take to make this happen (newby).

       

      Any direction to accomplish this would be greatly appreciated.

      Thanks

        • 1. Re: Find Matching Record and Set Field

          Going to bed  but the idea ia

           

          At start if triggwr set variable to qhatever field your matching and want to copy

           

          Go ro layout

          Show all records

          Error capture on

          Wntwe find mode

          Set search field w variable

          If statemnt if error capture not found

          Creat new record

          Set  fields with vsr

          Show all records

          Go to layout ...

          Exit script

          If found

          Go to record first

          Loop

          Set fields wiith variables

          Go to nwxt record

          End loop if laat record

          End oop

          Show all

          Go to lsyout original

          • 2. Re: Find Matching Record and Set Field
            cmj

            Thanks Brian.

            I appreciate your suggestion but I need a little more detail to grasp it.

            I'll look in the morning....off to sleep as well.

            Thanks

            • 3. Re: Find Matching Record and Set Field

              Sorry about the previous post's spelling/grammar     i was half-asleep and on my phone

               

              this is generic script and example file; you will have to adjust  table and field names  and potentially add more variables

               

              Capture.PNG

              • 4. Re: Find Matching Record and Set Field
                philmodjunk

                What I read is that you have these relationships:

                 

                Table3>----Table1----<Table2

                 

                Yet you want to match a value in Table 2 to a value in Table 3.

                 

                I would create a second occurrence of table 3 in the relationships graph and link it to the occurrence for Table 2

                 

                Table3>----Table1----<Table2-----Table 3 (2)

                 

                If your relationship from 1 to 2 is:

                 

                Table1::__pkTable1ID = Table2::_fkTable1ID

                 

                You might add:

                Table2::_fkTable1ID = Table3 (2)::_fkTable1ID AND

                Table2::YourMatchFieldHere = Table3 (2)::YourMatchFieldHere

                 

                You can allow creation in this relationship and then this script step:

                 

                Set Field [Table3 (2)::Field ; Table2::Field ]

                 

                Will create a new record in Table 3 if no matching record exists. It will update a matching record if at least one matching record does exist. (It will update the first such related record.)

                1 of 1 people found this helpful
                • 5. Re: Find Matching Record and Set Field

                  see another example of a different approach......and much easier, prob much faster

                  • 6. Re: Find Matching Record and Set Field
                    cmj

                    Thank you Brian and Phil.

                    I really am enjoying working with FM so far. However, without the tremendous help provided in this community, I would have hit a brick wall many times.

                    I will apply the solution later this evening.

                    • 7. Re: Find Matching Record and Set Field
                      BruceRobertson

                      You seem to indiscriminately use "show all records", especially before performing a Find operation. That is not necessary and in some cases leads to performance problems. Also, it can be an advantage to switch to Find mode BEFORE making the go to layout statement.

                      • 8. Re: Find Matching Record and Set Field

                        BruceRobertson wrote:

                         

                        You seem to indiscriminately use "show all records", especially before performing a Find operation. That is not necessary and in some cases leads to performance problems. Also, it can be an advantage to switch to Find mode BEFORE making the go to layout statement.

                        -That is a good point and i suppose a bad habit (?)   i started doing that as a habit because my personal stuff involves tons of importing from various .xls, .csv, and .txt files. As for most of our instruments output in these 3 formats.  Ran into past issues with matching fields  so i adopted the show all at the start of every go to layout and at end of script.   didnt realized it could cause performance issues and curious to know in which way? I understand that its an extra an unnecessary step  but assumed it is relatively minor with respect to processing.

                         

                        - Whats the rationale for the entering find mode prior to switching layouts? ( intended to  be a question of understanding)

                        • 9. Re: Find Matching Record and Set Field
                          RickWhitelaw

                          When you enter find the go to the new layout all the records and fields in the layout do not need to load. Much faster.

                          2 of 2 people found this helpful
                          • 11. Re: Find Matching Record and Set Field

                            RickWhitelaw wrote:

                             

                            When you enter find the go to the new layout all the records and fields in the layout do not need to load. Much faster.

                            Interesting....    I never would have thought that would be the case.   I never thought about it, but since it came up i  would have guessed that it would  have cached that table or performed some type of  temp indexing to perform the find. so whether it loads before or during it would balance out.

                             

                            i.e.  Table isnt loaded but still has to load while find processing is occurring vs. loading table first and perform find faster     

                             

                            Haha  I trust everyone'sl opinion's,  just thinking out loud, and learning

                            • 12. Re: Find Matching Record and Set Field
                              BruceRobertson
                              i.e.  Table isnt loaded but still has to load while find processing is occurring vs. loading table first and perform find faster    

                              Let's say you have a summary field on the target layout. Or fields that displays results of a complex unstored calc. You go to this layout. You show all million records.

                              Then sometime ...... after ....... a ....... while...... when the summary finishes.....

                              You proceed to do your other operations; enter find mode, etc.

                               

                              If you begin by entering find mode, then go to the layout; set field and perform your find.

                              And six records are found.

                              Your summarydisplaysfasterandyouaredone.

                              • 13. Re: Find Matching Record and Set Field

                                That completely makes sense from that perspective, buuuuuut what if the scenario is performing a find on said unstored calc field such as summaryfield > ##  (tbh i never tried so idk if you can search an unstored calc or why you would want to lol).

                                 

                                In theory wouldnt the cal fields need to be evaluated before the find process begins and hence an assumed developers position of processing all table records first before performing the operation related to those records?

                                 

                                 

                                BTW  Thanks, and just to be clear i dont intend to be challenging the standards in my comments, just trying to get a better understanding how FMP 'thinks'.  This is unfortunately  how i learn ;-)

                                • 14. Re: Find Matching Record and Set Field
                                  BruceRobertson

                                  So, is your rationale to ALWAYS show them the worst possible performance?

                                  What if most of the time they are not going to bother with a find on the unstored calc field?

                                   

                                  That's why you don't want to perform finds on unstored calcs.

                                  More so, the more complicated and poor performing they are.

                                  No, there would not be an advantage.

                                  No, you cannot perform a find on a summary field.

                                  1 2 Previous Next