I'm having a hard time assessing whether the "Replacing field contents" script steps can be made to work with what I am trying to do. Hopefully one of you can provide me with bits of information I'm missing.
This is a fairly complex process I'm trying to set up:
We have a number of records for which we need to compare certain values against a number of conditions. Let's say those records are stored in tableA.
For this I have set up a table of about 100 records where I store the conditions as text, i.e. "(isempty(tableA::fieldA) OR tableA::fieldA=0) AND table::fieldB>0"
I then loop through this table and for each entry I create a text file which lists the records' ids along with the last modification date, the related month and year and the current condition's id. Something like :
I then import this file in tableB where the proper relationship allows a calculated field to evaluate the condition and display a status (0 or 1).
All that is run as a scheduled script and works fine.
But what I need is to keep the initial status as well as the current status (can change when the record in tableA is updated).
The current status is the aforementionned calculated field. I tried several things for the initial status :
- An auto-enter value with the evaluate(condition) set as the default value but that doesn't seem to work when run on the server
- An auto-enter value with the value of the current status, doesn't seem to work either
- Using a replace field contents on a found set matching the current date for creation, which should show only the new records and not the updated ones with either the evaluate(condition) or the current status value set to replace the initial status.
Some if not all of those methods seem to work properly when the script is run manually from FM Advanced but none work as a schedule script, the replace field contents seemingly replacing all the values of initial status with the same value (0 or 1) but not actually checking the condition or mimicking the current status field value.
I hope one of you can make sense of what I'm trying to explain and has some insight about what is wrong with my approach and/or what proper method should be used to achieve the desired result.
Will happily give more details on demand tomorrow (18:00 GMT+1 here).
Thanks in advance for any help you can provide.