Set Field Calculation - Possible Or Not?

[panchristo]

Does anyone know of a way to set a field in a related table via a calcuation?

[ryangrg]

Yes and No. What are you trying to do?

[panchristo]

Yes and No. What are you trying to do?

I;m trying to implement an audit log based on Ray Cologon's demo (UltraLog), but want it based on schema transactions rather than scripts, in order to be able to function without user intervention and work under IWP as well.

So basically, I'm looking for a way to capture changed data and write them to a log table but using calculations only.

Any ideas?

[ryangrg]

Using a calculation field you can perhaps expand on something like this:

If ( Field ≠ Self ; Self & " " & Field ; Self )

[Rick Whitelaw]

If(Field<> Self) will always evaluate to 0 and that's if FM will even allow Self in this context.

RW

[ryangrg]

This got me going since I need a audit log for fields sooner or later. I was poking around with something like this, and it doesn't seem to evaluate zero:

AuditTest.fp7.zip

Something seems kind of funny though. Now of course you probably want a list of all the fields for the record, I've been playing around but introducing more fields using List() brings in some kinks but I think something can be worked out. I'll post when I have a chance to play with this some more.

[panchristo]

I'm afraid you didn't get the point.

The problem is how do I create a new record (with certain values) in Table B, every time a timestamp field changes in Table A. That is the basis of the audit log. Every time a record gets modified, a new record is created (in another table) recording this modification. Up to a certain point, this issue has been resolved by many, apart from commercial products. What I am considered with however, is to make it fail-safe and working under Web publishing, without using script triggers (they don't work in IWP or CWP), or modifying my business logic. Unfortunately, the Eventscript plugin which calls scripts from calculations doesn't work there either.

Modifying my business logic and using a script when a record is commited to transfer the log to another table is what I am trying to avoid.

[bruceR]

Your request is impossible. It is not going to happen the way you imagine it.

[panchristo]

Your request is impossible. It is not going to happen the way you imagine it.

Hmm. I guess I have to adjust Ray's solution then to run every time a record is commited, huh?

Or -if you got my point, do you think there's another way?

P.S. If I'm not wrong, databases like MySQL and such support triggers on the database schema. Would it be impossible for FMI to incorporate such ann ability in FM?

It would provide more security (than triggers anchored to layouts) wouldn't it?

[ryangrg]

PHP time.

http://www.filemaker.com/downloads/pdf/article1_php.pdf

http://www.filemaker.com/downloads/pdf/article2_php.pdf

[panchristo]

PHP time.

http://www.filemaker.com/downloads/pdf/article1_php.pdf

http://www.filemaker.com/downloads/pdf/article2_php.pdf

Nice and very revealing at first for an amateur like me. I was afraid though that the solution to my issue would involve PHP.

I guess I'll have to dive into the PHP world as well, but that'll have to wait for the time being.

Thanks anyway for your contribution.

[bruceR]

Note that PHP actually has nothing to do with the solution. Any scripted process could do the same thing. You change the fundamental process: you control edits and submits and then programatically take the actions you have chosen to take when you submit the record.

[ryangrg]

I was under the impression that you could create a script trigger using PHP that would fire on any layout change. Honestly I shouldn't have made this comment because I don't know enough about PHP. So would the solution be to create a button that has a script that commits and updates any changes to the related record. Under IWP don't you all ways need to hit a button to commit records anyhow?

[panchristo]

Note that PHP actually has nothing to do with the solution. Any scripted process could do the same thing. You change the fundamental process: you control edits and submits and then programatically take the actions you have chosen to take when you submit the record.

I was under the impression that you could create a script trigger using PHP that would fire on any layout change. Honestly I shouldn't have made this comment because I don't know enough about PHP. So would the solution be to create a button that has a script that commits and updates any changes to the related record. Under IWP don't you all ways need to hit a button to commit records anyhow?

It seems to me both you are right. Indeed, instead of messing with php I'd already begun thinking of modifying my business logic (edit, commit) in such a way that audit logging will work, instead of trying to solve the problem via PHP, although at first I wanted to avoid it.

Thank you for your comments.