7 Replies Latest reply on Mar 16, 2016 2:58 PM by BruceRobertson

    Scripting to Replace Field Contents Across All Records

    kasta

      Hello! I'm quite new to FileMaker, and have just begun exploring scripting relatively recently, so apologies if this question seems a bit silly, but I'm getting quite exasperated and think I'm probably missing something quite obvious here.

       

      I am trying to script to replace the contents of a field across all records. The contents of this field then instigate a calculation in another field, the result of which is different depending upon what was inputted in the first field (the one I am scripting to replace). There is also an additional field that sums up the contents of the calculated field.

       

      Example of my goal -- the field I am scripting for is replaced to 2016 across all records. As a result, the calculation field on each record then yield the appropriate result for when 2016 is inputted in the other field (this calculation works fine on a record-by-record basis). Then, on another layout, a sum field sums all the calculated fields that were populated when 2016 was selected.

       

      Here's what I have for the script--simple and to the point--but it doesn't update the field across all records, only some, thus the sum field isn't showing the correct total.

       

                Replace Field Contents [No dialog; FIELD1 ; Current contents]

       

      I tried to fix it by inserting a loop, and also tried to use Set Field as well, but still no luck.

       

      Any ideas? Thanks all!

       

      EDIT (INFO FROM REPLIES BELOW FURTHER EXPLAINING MY ISSUES AND GOAL):

       

      So to be more specific, I have 12 "year" fields and 12 "amount" fields on Layout 1, which are all user inputted. There is also a "select year" field, and the user can input any year here. If the year inputted in "select year" matches any of the 12 "year" fields, then calculation field on the same layout will add up the corresponding "amount" fields. This calculation works fine on each record, e.g. if I input "2016" in the "select year" field, and there are two "year" fields with 2016, then the calculated field will show me the Amount1 + Amount2 total. Same for if I input "2017", and there is one "year" field with 2017, then the calculated field shows me Amount3 which corresponds to 2017. If I input "2018" and there is no "year" that is also 2018, the result is 0.

       

      My goal with the script, located on Layout 2 and with a trigger set for after a "select year 2" field was exited, was to force update the "select year" across all records on Layout 1, which in turn causes the calculated result totals on Layout 1, and then an additional field (on Layout 2) shows the sum of the new contents. Like if I wanted to see ALL amounts for 2016, across all records, ideally the script changes "select year" to 2016, giving me the amounts on each record, and then the sum field gives me the total.

       

      The main issue is that the script appears to work once with whatever value I input first into my "select year 2" field on Layout 2, but when I want to change the field, that is update the current contents, it doesn't appear to do so. The first sum it returned stays in place and the "select year" field on Layout 1 stays at the first value I tried to run, not updating as the current contents in "select year 2" update.

        • 1. Re: Scripting to Replace Field Contents Across All Records
          BruceRobertson

          Something is missing in your description.

          If you start with a found set of all records; then the replace operation DOES replace the contents of the field; across all records in that table.

          Usually it is helpful to be more specific about what your solution does. Table A table B Field1 Field2 is not generally helpful.

          • 2. Re: Scripting to Replace Field Contents Across All Records
            kasta

            Hmm okay. So to be more specific, I have 12 "year" fields and 12 "amount" fields on Layout 1, which are all user inputted. There is also a "select year" field, and the user can input any year here. If the year inputted in "select year" matches any of the 12 "year" fields, then calculation field on the same layout will add up the corresponding "amount" fields. This calculation works fine on each record, e.g. if I input "2016" in the "select year" field, and there are two "year" fields with 2016, then the calculated field will show me the Amount1 + Amount2 total. Same for if I input "2017", and there is one "year" field with 2017, then the calculated field shows me Amount3 which corresponds to 2017. If I input "2018" and there is no "year" that is also 2018, the result is 0.

             

            My goal with the script, located on Layout 2 and with a trigger set for after a "select year 2" field was exited, was to force update the "select year" across all records on Layout 1, which in turn causes the calculated result totals on Layout 1, and then an additional field (on Layout 2) shows the sum of the new contents. Like if I wanted to see ALL amounts for 2016, across all records, ideally the script changes "select year" to 2016, giving me the amounts on each record, and then the sum field gives me the total.

             

            It worked once for me with the above example, but when I update the current contents in "select year 2" to another year, e.g. 2015, the sum box won't update, and when I go back to Layout 1, many of the "select year" fields which had first been set to 2016 are the same or now blank, and have not changed to 2015.

             

            Does that make sense?

            • 3. Re: Scripting to Replace Field Contents Across All Records
              kasta

              I take back the bit about it being blank, the main issue is that the script appears to work once with whatever value I input first into my "select year 2" field on Layout 2, but when I want to change the field, that is update the current contents, it doesn't appear to do so. The first sum it returned stays in place and the "select year" field on Layout 1 stays at the first value I tried to run, not updating as the current contents in "select year 2" update.

              • 4. Re: Scripting to Replace Field Contents Across All Records
                BruceRobertson

                Sounds like a classic case of a data structure problem. Big warning flags when you have Year1, Year2, Amount1, Amount2, etc.

                This generally indicates they should be related records.

                I suggest you forget the replace operation and instead go into a more detailed description of what this solution is all about.

                • 5. Re: Scripting to Replace Field Contents Across All Records
                  kasta

                  Thanks Bruce--I'm sure I probably haven't done precisely what I should in terms of relating them, but I'm pretty sure what I'm trying to script right now should work regardless... Ideally I really need to do a quick fix on this just so it works right now to demonstrate something--I am planning on going back and restructuring everything but this particular issue is really bugging me and I don't think it's necessarily how I have anything else set up.

                   

                  I was looking at the script again performed with dialog this time and it seems that the "current contents" isn't updating as it should when I've inputted new text there. I tried adding a script for commit record and refresh window, triggered upon exit of the updated field, and then making a button to perform the script after this step, but the "current contents" doesn't update until I've clicked around and performed some other stuff like switching between layouts--it's like there is a lag before it's registered and then can be used in the script? Is that normal?

                  • 6. Re: Scripting to Replace Field Contents Across All Records
                    BillisSaved

                    Good afternoon kasta,

                     

                    I hope your day is going well. Since you're looking for a quick fix, could you try something like this:

                     

                    • Create a "Utility" table with 1 global field - this field would be used on your layout for the user to enter the desired date
                    • Use an ExecuteSQL() statement in your Amount_Total field calculation to collect the total for you:
                      • ExecuteSQL (

                    "SELECT Sum \"Amount\"

                    FROM \"Your_Table\"

                    WHERE \"Year_Field\"=?";

                    ""; ""; Utility::GlobalField)

                     

                    This suggestion may not fit your solution, but I thought it may be worth a try. Good luck!

                     

                    God bless,

                     

                     

                    Bill

                    • 7. Re: Scripting to Replace Field Contents Across All Records
                      BruceRobertson

                      Way too vague, way too many ways you could be doing the things you say you're doing. It would be better if you post your file; or a simplified example illustrating what you're doing.