AnsweredAssumed Answered

Scripting to Replace Field Contents Across All Records

Question asked by kasta on Mar 16, 2016
Latest reply on Mar 16, 2016 by BruceRobertson

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!




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.