11 Replies Latest reply on Dec 23, 2013 8:40 AM by BruceHerbach

    What is the secret to updating all records but on an individual record:field basis in a table?

    tguise

      "Thanks in Advance" to anyone & all who can provide some insight into what I thought would be a simple exercise. (Needless to say, I am very new to FM Pro 12...and now humbled even more...)

       

      Given: A (test) Table with 3 Fields, 50 Records.

      Research showed I must have Fields 2 and 3 set to Global status, but that by doing so "now" would destroy the field content.

       

      Objective:

      (a) Create 2 new fields in existing table with Global attributes.

      (b) Create a "script" to step through each record and copy the contents from Field 2 to Field 4 AND Field 3 to Field 5.

      (c) Delete Fields 2 and 3.

       

      Proposed Script

      ******************

      Go to Layout [x]

      Set Variable [$TRC; Value:Get (TotalRecordCount)]

      Set Variable [$K; Value:0]

      Go to Record/Request/Page [First]

      Loop

      Set Variable [$K; Value: $K + 1]

      Set Field [Test_Corporations::Test_Corp_Type Copy; Test_Corporations::Test_Corp_Type]

      Set Field [Test_Corporations::Test_Corp_Name Copy; Test_Corporations::Test_Corp_Name]

      Go to Record/Request/Page [$K]

      Exit Loop If [$K > $TRC]

      *****************

      To my surprise, shagrin, frustration, etc.... Set Field, Set Field by Name, Replace Contents..., etc change ALL Contents of Each Field & Record in the found set!

       

      So... What is the strategy whereby one moves to each successive record and update field contents with unique values?

      Again, "Many Thanks" in Advance for some insight into this situation!

      -Tom

        • 1. Re: What is the secret to updating all records but on an individual record:field basis in a table?
          erolst

          If you want to set a field to a value that is unique per some record attributes, you have to use an expression that yields a unique value for that record; a global field is not unique per record, but per table. So if your "copy” field variants are globals, and you use them as result expression, (it should comes as no surprise that …) after running this script all records of the affected found set will have the same value.

           

          btw, you don't need to initialize the counter outside the loop, and your exit condition should be “=”.

          tguise wrote:

          Objective:

               (a) Create 2 new fields in existing table with Global attributes.

               (b) Create a "script" to step through each record and copy the contents from Field 2 to Field 4 AND Field 3 to Field 5.

               (c) Delete Fields 2 and 3.

           

          What's your objective in non-database terms?

          • 2. Re: What is the secret to updating all records but on an individual record:field basis in a table?
            tguise

            Thanks for responding erolst!

            Also, Thanks for the heads up on the counter initializtion.

             

            Somewhere last evening I read that in order to implement a picker window it was recommended that the query fields should be of global nature. So I basically decided to change the table structures. (The task to copy the fields seemed initally to be fairly easy.)

             

            The more I worked with the Set Field and simular functions, seeing that the scope of their operation was the entire table or found list the harder I tried to understand why? Especially since I could see the record pointer moving down the table with the debug/watch windows.

             

            At this point, I am (now) just trying to understand the strategy behind the implied scope of Set Field [by Name], Replace Contents and other such functions... as to why their scope is table or (found set) wide, as opposed to being record level. If I am unable to grasp a critical understanding of the scope issue, then I will gladly settle for understanding how to implement the general task of copying 2 fields within a record to a different set of 2 fields within the same record. Hopefully I will eventually grasp the "why".

             

            It's been about 12-14 years since I last programmed, so I'm trying to understand some of the strategies that are employed by FM. I am seeing that with a background in programming, even as many years ago that it was, it is bringing with it, a set of pre conceived & biased expectations that are not helping with the learning curve.

             

            Again, Thanks for your assistance, as any and all insight is greatly appreciated!

            -Tom

            • 3. Re: What is the secret to updating all records but on an individual record:field basis in a table?
              erolst

              Tom -

               

              Set Field and Set Field by Name apply to the current (related) record, Replace Field Contents applies to the found set (or a related set) – both of which (record/set) can change inside a script, but that doesn't alter the basic way these steps work.

               

              Since you're coming from a more mainstream programming background, you need to translate your concept of scope to the FileMaker idea of ‘context’ – current window/layout/TO/baseTable/record - and to a certain amount you can map the ideas of class/object/variable to table/record/field, but as you said, it's a good idea to try and let loose of preconceptions stemming from other environments.

               

              Don't know where you read the ‘global’ thing (care to provide the link?), but here is a simple picker window. As you can see, it needs nothing more than a cartesian TO plus a global field (or two) for the filtering, and it simply works with the existing data - no need to alter your table structures (that sounds really drastic!), or add data fields. For serious scenarios and/or ones with more fields/records or non-local hosted files, you may want to implement a filter in the relationship itself, due to the (convenient, but inherently inefficient) way a portal filter works.

               

              Be aware that this is but one way to implement a picker (I happen to be portial, so to speak …) – you could e.g. do without a relationship and use a list layout and Quick Find.

              • 4. Re: What is the secret to updating all records but on an individual record:field basis in a table?
                tguise

                Hi erolst;

                 

                Thanks for the commentary on the Strategic Insights in to FMP, in time I hope to get there. And "Thanks" for the sample Picker file. It is nice to get a different view of a project and it will expand one's future considerations. As far as trying to provide you with the link to the item I "read last night" regarding the use Global fields in a picker... well, the fact I can't even find it now. If I do run across it, I'll forward it to you.

                 

                If I may, I'd like to revisit the Set Field [by Name] function. I worked with that a little today, but still had no success. So, I'm still at the confused stage as to what controls the scope which affects either a single record vs a complete found set without the use of any global variables.

                 

                Additionally, speaking of scope, more definitively scope creep, I exported the file with the values I was trying to copy via Set Field into an .xlsx spreadsheet. I then deleted the table and was going to recreate and populate with the Import Command. Well....that didn't even work as advertized. At that point I took a long break.

                 

                So, if you are interested in looking at the files I was working with, I have included them for your review. They consist of 1 file with 2 tables. 1 table has 3 fields, the other 5 fields. The one with 5 fields has a V2 of the Type and Name field just copied.

                 

                At the end of the day, my goal was to Import the .xlsx file into FM table, Test_Corporations. The second task was to then copy the values from Test_Corporation::Test_Corporations Table:Fields into Test_CorporationV2::Test_CorporationsV2 Table:Fields using the Set Field (). The script Copy Fields was source of that effort.

                 

                So, if you have a few minutes to spare, as well as the desire to look at these items I would be most interested in your take on the tasks. Again the whole task is to see how to make the Import function work as well as to see how to make the Set Field function work. If not, I completely understand.

                 

                Thanks for your time and interest!

                -Tom

                • 5. Re: What is the secret to updating all records but on an individual record:field basis in a table?
                  erolst

                  Tom -

                   

                  1. Whenever possible, instead of traversing a found set, use FileMaker's batch operations, like Replace Field Contents and Import Records. The only advantage a loop with Set Field has is that you can check for errors (especially in a multi-user environment) – usually caused by locking or validation issues – log them separately at your discretion and/or implement an error handling routine. While RFC will tell you if errors occurred, it will not tell which records were affected.

                   

                  2. If you traverse a found set, you can use Go to Record/Request [ next ; exit after last ], which in a loop will do exactly that without you having to keep track. The (small) downside is that it causes a (silent) error which appears in the error log; so unless you're a server admin who wants to keep his error log clean, never mind …

                  tguise wrote:

                  At the end of the day, my goal was to Import the .xlsx file into FM table, Test_Corporations. The second task was to then copy the values from Test_Corporation::Test_Corporations Table:Fields into Test_CorporationV2::Test_CorporationsV2 Table:Fields using the Set Field (). The script Copy Fields was source of that effort.

                   

                  3. As noted, use Import Records, and there is no need to take the roundabout way with an Excel file as intermediary; to import between FileMaker tables A and B, find the records in A, switch to B and import from A (where, if you have several TOs, you have to choose the correct one, because each TO has its own found set).

                   

                  Also, you cannot reference or write into a table that's not related (unless the fields in question are globals), which in the calculation editor of a field definition would be glaringly obvious (can't close the dialog), but not so in a script which per force must be context-agnostic. So in your file you could write a script in which B::field is set to the value of A::field, but then you better make sure that (at runtime at least) there is a valid relationship between A and B.

                   

                  4. The scopes of the commands are as I described them; you need to watch out for the secondary scope, so to speak: if you Set Field[] a related field, you should be aware which of potentially many related records you're addressing, which is a function of the current native record and the sort order of the relationship.

                   

                  5. Why duplicate data at all? Relate, display and use, but don't duplicate. In your example file, there should be exactly one place where a company and its name are stored; if you need this company/name for other purposes (invoices, mailings, statistics), use its primary key to establish a relationship, then display the name via the relationship.

                  • 7. Re: What is the secret to updating all records but on an individual record:field basis in a table?
                    tguise

                    No, not that I know of. Although It does share some simularities...hmmm....

                    -Tom

                    • 8. Re: What is the secret to updating all records but on an individual record:field basis in a table?
                      tguise

                      Thanks for replying erolst!

                       

                      After walking away from it for several hours then upon returning and revisiting your commentary, I went back in and all of the tasks performed as expected.

                       

                      So "Thanks for your time, patience and effort!"

                      - Tom

                      • 9. Re: What is the secret to updating all records but on an individual record:field basis in a table?
                        BruceHerbach

                        Tom,

                         

                        This may be a bit late now.. but wouldn't if be easier to just create 2 new Global fields and leave the data in the existing field.  You can rename the fields if that is an issue or change the relationship graph to use the global field in the relationship?

                         

                        Bruce

                        • 10. Re: What is the secret to updating all records but on an individual record:field basis in a table?
                          tguise

                          Bruce H;

                           

                          Thanks for responding. No, it's not too late now, as this is just a very small test whereby I started off testing the scope of affect that Set Field [by Name] has. And what you suggested is what I was trying to do, except I was taking it two more steps by copying the data into the corresponding fields then deleting the non-global fields.

                           

                          At the end of the day with some collaboration with erolst, it finally worked. I can't tell exactly why it didn't work the first few times, but, I suspect I got so focused on it that I got "so far off into the weeds" at which point nothing worked...:) So, after backing off for a while then coming back to it with a fresh look at it, the Set Field [] and Import () appears to work.

                           

                          (I need to start turning on screen capture video, so that when I realize I'm off in the weeds & lost, I can go back and try to figure out what in the world I was thinking about...)

                           

                          Sometime over the next couple of days, I'm going to back to it and I can replicate and repair some of the issues I encountered while "in the weeds".

                           

                          Thanks for your time and for your input, as a new set of eye's are always welcomed.

                           

                          -Tom

                          • 11. Re: What is the secret to updating all records but on an individual record:field basis in a table?
                            BruceHerbach

                            Hi Tom,

                             

                            This maybe a bit obvious, but.

                             

                            A global field has one value for all records in the table.  So it can be

                            very helpful for scripting a QuickSearch or finding specific records in a

                            related table and many more

                             

                            It should never be used to store data for a specific record in the table.

                             

                            Good luck with your project

                             

                            Bruce

                             

                            Sent from my mobile device... Please excuse typos.