0

I'm trying to create the following scenario in Filemaker:

I basically would like to track changes of a value in a table, when used in a layout.

To make it simple, I have a table with two columns, Table 1 with an ID (Primary Key) in the first column and the second column with a value. When I update/add the value in Column 2 for an ID in Table 1, I would like to store the ID, the new value, and the current timestamp in Table 2.

I would like this for both changes and new records in Table 1.

In SQL this is simple, just to create a trigger to insert a new record in Table 2 on Update in Table 1.

But have not yet figured out how to do it in Filemaker. I know that I can use script triggers. My guess is to use the script trigger OnRecordCommit and New Record/Request, and in the script do something like:

OnRecordCommit in layout "table1"

  1. Go to layout "table2"
  2. New Record/Request
  3. Set Field Table2::LogID; Table1::ID
  4. Set Field Table2::LogValue; Table1::Value
  5. Set Field Table2::LogTimestamp; get(Current Timestamp)

When I tried the scenario above, infinite empty records are created in table1

Guidance is truly appriciated! Sincerely, Christoffer

Asken
  • 3
  • 3

1 Answers1

1

You are changing the context and I do not see you are linking to the record you are trying to log. Try to store logID and logValue in variables before you leave table1 layout and set the log record from these variables.

Another option is to look at what others did. Have a look at these links:

http://timdietrich.me/fmeasyaudit/

https://www.excelisys.com/fm-tips/filemaker-pro-13-tip-n-trick-easy-bake-filemaker-pro-13-audit-trail/

http://www.nightwingenterprises.com/demos2014/demo1306.html

Nicolai Kant
  • 1,391
  • 1
  • 9
  • 23
  • Thanks for the links, I will check them out. Are new to Filemaker and to the procedures how to manage to script, but the idea of storing the variables gives me a better understanding. I will try it and get back. – Asken Jun 07 '16 at 13:38
  • first two links are dead now – Tom Mar 08 '21 at 05:36
  • Yes, since the answer was given 5 years ago – Nicolai Kant Mar 11 '21 at 00:24
  • Updates for the two broken links above: 1) https://github.com/chivalry/FMEasyAudit 2) https://excelisys.com/fmp-tip-n-trick-filemaker-pro-13-easy-bake-audit-trail/ – Andreas Kohlbecker Jul 22 '22 at 09:37