Modify Date field in multiple tables?

[Rich S]

Currently, in the "Master" table in a runtime solution I'm attempting to code, I have a date field--named "Modified"--that's defined with Auto-Enter: Modification Date; it dutifully records when any of the fields in Master have been modified and it works fine.

However... (There's always a 'however'...)

The calc only works in the Master table; it

doesn't record any changes made to its seven "satellite" tables that are related to it. How would I code a calc so if any of the fields in all of the tables have been changed, the Modified date field in Master will update itself?

At first, I thought I could have a latched field in each of the tables so that if a change is made it toggles from 0 to 1 (or 1 to 0)--all the tables would then be logically compared to see if there's been a change. That seems...well, inelegant if not very messy logically to code so I'm sure there's a better way. Any ideas?

Cheers, and to our Jewish friends, Happy Chanukah!

Rich

[Ocean West]

FileMaker has no way to update parent record data from changes in children records. That said you should employ some sort of "update" scheme. That runs a script to update the date in the parent file to reflect that changes were made.

Depending on the timeliness or frequency of updates needed you could create a script that runs server side. Go to child table, find all records that were modified today, go to related parent record - all in found set. Loop thru records updating the date field to the "max of the mod date in the child table.

FMPS9 can run scripts server side so an update script (that works with web compatible scripts) will work perfectly.

[Søren Dyhr]

The calc only works in the Master table; it

doesn't record any changes made to its seven "satellite" tables that are related to it.

Steven is right the calc' should reside on the other side of the relation ... but there is more to it your seven satellite tables doesn't seem like a healthy relational structure, well I can be mistaken here, but I need some kind of explanation???

Further more do I think Steven's approach is stumbling near to the notion of sycronization, which by all means should be avoided in a relational approach, and it's very likely to scale badly beyond being a source for inconsistency!

--sd

[comment]

This could be done easily with an unstored calculation field, instead of auto-entered stored data. As has been pointed out, the data already exists in the "satellite" tables, so there is no point in storing it twice.

I believe that with many records the most efficient scheme would be this: in each table, a global field with auto-entered Modification Timestamp, and a calculation field in the master table (result is Timestamp) =

Max ( Table1::gModified ; Table2::gModified ; Table3::gModified ; ... ; Table7::gModified )

This still duplicates data storage to some extent, but it eliminates the need for a cascading calculation over all records in the solution.

Note that deletion of records must be scripted, and the script must update the global field in the relevant table, as this not automatically recorded anywhere.

[Rich S]

Thank you (all) for the great input!

The schema, so far, looks like a hub with Master in the center with each, separate, (child) table related to it...though that leaves quite a few many-to-many relationship links. (I've read and heard that having a many-to-many relationship is a bad thing and that it's proper/better to have one-to-many relationships, instead, but have yet to understand why that is.)

What is intriguing is in the table viewer, if a related table is positioned on one side of Master it shows the connection as a many-to-many, but if I move the related table to the other side of Master, it shows it as one-to-many. Strange!

[Søren Dyhr]

(I've read and heard that having a many-to-many relationship is a bad thing and that it's proper/better to have one-to-many relationships, instead, but have yet to understand why that is

I'm quite astonished as well here, why is it bad? Who says so ...context and purpose is missing, if this is to be believed or elevated into - erhm... rule!

Please enlighten me here!!!

--sd

[comment]

I think it means that you should use a join table (which breaks a many-to-many into 2x one-to-many)?.

[Søren Dyhr]

I sussed it in that direction too, but filemaker have yet another option which I wouldn't shy away from either, when reporting is of less importance.

--sd

[Rich S]

I didn't want to leave you guys hanging since I'm out-of-town, but I'll cite the many-to-many references here when I return next week.

Ciao!

[Rich S]

In case you're still tracking this topic, guys, two references that advocate avoiding many-to-many relationships in favor of one-to-many are:

FileMaker Pro 9--The Missing Manual by Geoff Coffey and Susan Prosser, pg 312: "To fix thins, you need to chop your many-to-many relationships in half, turning each into two separate one-to-many relationships."

FileMaker Pro 9--Beyond the Basics by Chris Ippolite, in the Auditing Your Diagram section of the the video series. "The goal here is to turn all many-to-many relationships into one-to-many relationships."

[Søren Dyhr]

The issue is not avoid them! But to know what to use or what is at hand to make them. The many to many [color:red]isn't a natively found type neither with filemaker nor 4D which I can speak of, and only two ways exists to construct them from ... it is exactly as Comment writes:

I think it means that you should use a join table (which breaks a many-to-many into 2x one-to-many)?.

This needs to be elevated to a higher level, lets take Wikipedia:

it is necessary to implement such relationships physically via a third junction table

http://en.wikipedia.org/wiki/Many-to-many_%28data_model%29

So It seems more or less universal! This means certainly not you should avoid them, but instead you have implement it with what you have at hand, which is two one-to-many's or a pilcrow-delimited field in one of the tables. If you take the later option, is there a price to pay you can't report on the join-table, since it lacks.

On the other hand could they utilize functions (calc'fields) and even custom functions for the linking, which allows dynamic structures utilized without the interaction of a script, which is urgent with the sandwiched table because record creation is required.

This special filemaker feature was used pretty intensively earlier on when scripting was even more crippled that today with IWP'ed solutions, since only one record could be made via the interface back then.

The book advice stands though, it's better to make the join table right away, instead of later discovering that reporting was needed despite the odds ... it's pretty similar to not using the one-line script-steps you have access to when defining a button, you might so swiftly outgrow it and then assign a dedicated script instead to the button.

But a James Joyce quote fits this issue exactly:

Irresponsibility is part of the pleasure of all art; it is the part the schools cannot recognize.

Which could be translated to, you'd better know all the rules before breaking them!

--sd

[Rich S]

Thanks, Søren, for the wise advice.

*blush* I really must learn to look at the big picture instead of focusing on the minutia.