Need some guidance

[jweldy]

I everyone, I'm brand new to the FileMaker database but have some FB experience and a newbie here.

I'm well into a heavily modified starter solution for asset management and have been asked if FM will do something!

What I need to do is every time a assets mileage or hours is changed in a shared field of "current hours" have it recorded in a table that can then be reported from with a time stamp for each change to the field and it's value.

Could someone give me a bit of advice?

Thanks always in advance,

Jay

[ejpvi]

There may be better ways to do it, but perhaps you could use an "OnModify" Script Trigger for that field.

Create a new table to house the changes and Timestamp fields.

The script could create a new record and populate the information into the newly created table, and have the Timestamp field auto-populate the current time whenever a record is created.

You could also make a "Submit" button that executes that script, if you don't like using Script Triggers.

I have done something similar for tracking changes to a field. A table with a record for each change and have a timestamp is pretty easy to report on.

Is that the direction you are looking for?

[jweldy]

Hi and thanks for replying, that's "I think" exactly the direction I want to go. A submit button would certainly do the trick for me until I get through my learning curve.

So the next thing to learn is how to take that field and link it to the submit button then send it to a table.

FM looks easy coming from MSSQL and MySQL, but it's not!

So I'll look into learning this next, I appreciate the lead,

have a good day.

Jay

[ejpvi]

Within the Script you write, just make sure it is pulling the data from that field. In case you have never used a script trigger... it is pretty easy. Just right click the field you want to apply it to in Layout Mode.. and choose script triggers. There are several options... I suggested OnModify since it would only trigger if someone changes the data in that field. It is about the same as created a button, except it will execute whenever someone modifies the field.

The script you would need to write would be very basic as well..

Probably something like:

Freeze Window

set variable[$Temp; FieldName]

Goto Layout[ChangesTable]

New Record

Set Field(Changes; $Temp)

Goto Layout[Original]

Where FieldName is the one you are monitoring. ChangesTable is a layout that has your New table that will keep track of the changes. As of yet, you probably just need a changes field and a Timestamp Field. Remember the TimeStamp can be autopopulated as the record is created. When creating the field in the Database Manager, click options.. and choose the necessary auto-populate timestamp on record creation.

I always use a Freeze Window at the beginning so no one will notice that your script is jumping around layouts and creating records and stuff...

Then either use the Script Trigger on that field, or create a button on that layout that points to that Script.

Play around with it.. I just typed that up pretty quick, but hopefully will put you in the right direction. I would suggest using a local copy of your database from a backup for testing, instead of making changes in the live system until you are certain it will work how you want it to.

Thanks,

[jweldy]

Thank you again, I really appreciate you taking your time to help.

I'll work on this tonight and see how it all works out.

Oh yeah, I always test first on a copy out of long learned toying around!

Again,

TY

[jbante]

If you're using FileMaker Pro Advanced, you could install and use the UltraLog solution by Ray Cologon: http://www.nightwing.com.au/FileMaker/demosX/demoX01.html

The basic idea is that you add an audit log field to the table you want to record changes in, and that field is populated by this custom function. Then you can periodically write that data to a separate table. The downside is that it's more complicated, but the upside is that it works at the schema level rather than the interface level — so changes made in a layout that doesn't have that trigger or "submit" button, even changes made through the Web or ODBC, still get logged. This style of audit logging is pretty popular for people who want robust audit logging without a plug-in.

[bcooney]

"assets mileage or hours is changed in a shared field" -- I don't understand what that means. I didn't want to jump into an audit log recommendation, until I understood the question. It's probably an audit log situation, but perhaps it's a data model issue.