I pull in data via CSV files (28 of them) every quarter. The “FilingID” field is always a unique number. The “DateSubmitted” field corresponds to the FilingID as the higher the number the more recent for a given Company (1B1) field. and the “IE!” field is always the same for a given company. What I am looking for is a formula or script that when a record with a higher numbered FilingID or DateSubmitted (whichever is easier) is added any record with the highest FilingID for a specific company (1B1) or (1E1) is updated to a “Current” status and any existing (older) record is changed to a “past” status.
I think the right approach is an automated action that senses the addition of new records and uses a script to apply the logic and update the fields. This is likely a 1.5hr development task which I might have time to do this afternoon, but you’d probably be better off not paying my rates for this type of work.
Sunday’s are pretty quiet but eventually, someone will stroll by to help you.
If you are manually importing the CSV files, I do not recommend an automation script. An automation script would cost you an automation run for every record/row in each CSV file. If you are manually importing the CSV file, you could have the script do the import as well as all of the other processing. The script would use input.fileAsync() to let the user pick the CSV file from the local file system.
Another option is to convert the company to a linked field and then use a system of roll ups to calculate which is the current fillingID. With this system, no script would be needed.
If you are interested in hiring me, you can book a meeting to go over you import process in more detail and make more specific recommendations.