Exclude update flag field from updating date modified field

[jn08]

Hi everyone,

I'm trying to create a field which flags whether a record has been modified today, and is modifiable by the user in case they don't want to include it in the next export (and I will set my metadata export script to flag all exported records to "No").

The auto-enter calculation I've added to the field works, but (rather obviously) it updates automatically to "Yes" as soon as the user tries to click "No".

This is the calculation:

If ( PatternCount ( Record Modified ; Get (CurrentDate) ) ; "Yes" ; "No" )

I guess I could try sticking this flag in a related table so it doesn't update when the record upates ... but is that the simplest solution? Or is there a simpler way?

[comment]

In general, users should not flag records by modifying a local field - unless the flag applies to ALL users. To flag a record for themselves only, users should add the record's ID to a list held either in a global field or variable (for use in the current session) or in a field in the user's record in Preferences (for a more permanent use).

[jn08]

The flag does apply to all users. I need to know when a record has been updated by any user in order to know which records to export. Only the modified records need to be exported. I want the ability to flick the flag off manually because sometimes the user (or I) could make a change they know will not affect the export, so it'd be good to be able to ensure it *won't* be included. It'd also be good to switch it off when the records that need to be exported are exported, which could presumably be done with the export script.

Any suggestions on how to do it?

[comment]

You will need to either add a special field that indicates modification only to selected fields (with the flag field being excluded), or place the flag out of bounds of the flagged record - such as by creating a related record in a "flag table" or by adding the ID to a list kept in a field of a one-record System table.

[jn08]

Erk. That sounds complicated. Is there a way in the "flag table" of automatically creating a record so that it creates a flag as soon as there's a record to be linked to?

[comment]

Ahm... could you rephrase your question?

[Vaughan]

Erk. That sounds complicated.

Yes it is, it's database design.

[jn08]

Hah! Sorry guys.

If I create the flag field on a separate table then I'll have to manually create related records in that table if I want the flag field to monitor the product record's date modified field. I think that makes sense.

I think it might just be easier to change the date modified field so that it doesn't monitor my local flag field. I just don't want to have to specify every single field except that one, my product table has 80 fields or so.

[comment]

It's complicated because you are trying to modify a record without the record noticing it's been modified.

If I create the flag field on a separate table then I'll have to manually create related records in that table.

I still don't understand what you mean. You "flag" a record by creating a related record in another table - see here how:

http://fmforums.com/forum/topic/80957-serials-in-multi-user-environment/page__view__findpost__p__376185

You "unflag" the record by deleting the related record. There's nothing manual in either process - you need to script this.

I think it might just be easier to change the date modified field so that it doesn't monitor my local flag field. I just don't want to have to specify every single field except that one, my product table has 80 fields or so.

Perhaps you could use the FieldNames() function.

[jn08]

Ah, yes. I understand now.

I think the FieldNames() function solution might be the way to go. I've tried searching the forums for a local field solution (a record modified field that only watches particular fields - in this case all but one) but I can't seem to find it. Can anyone point me in the right direction?

[comment]

http://fmforums.com/forum/topic/70365-calculation-not-triggering-modification/page__view__findpost__p__333369

http://fmforums.com/forum/topic/58276-evaluate-expression-field1-field2/page__view__findpost__p__275937

[David Jondreau]

Wouldn't it be as simple as having an "Export Modified" auto enter calc field =

If ( Flag ; Self ; Modified )?

Where Modified is the modification timestamp field?

[jn08]

Thanks. Having trouble doing the calculation using FieldNames(). This is what I've got so far, but it doesn't work:

Let (

trigger = FieldNames ( Get (FileName) ; Get (LayoutName) )

;

Get (CurrentTimeStamp)

)

Obviously if it worked this would show a timestamp if *any* field were updated, but I thought it was a start to work it out... Any tips?

[David Jondreau]

FieldNames won't work. FieldNames() returns the names of the field, not references to them. Subtle difference, but you need references to trigger an auto-enter calc. See comments links, they contain a simple answer: Listing all the fields that need to be referenced in the calculation.

Mine might still work, but changing the Flag field to 0 will trigger the modification, we'd need to figure that out

[jn08]

That's a pity. So there's no way to do it but to list every single field? And presumably that means I have to update it every time I add fields to the table down the line. Is there any way to automate this?

I'm not sure I understood your solution.

If ( Flag ; Self ; Modified )

My idea was that the flag should set on automatically based on when the record is modified, and be switched off automatically when there's an export, but it also needs to be manually switched off at times when the record is modified but the user knows it won't affect the export. At the moment if I do that with a normal date modified field it will immediately set the flag back to on (because it's modifying the record).

[jn08]

All right, I've used the method suggested and it works well. The only problem is that the flag doesn't register changes in calculation field results. Is there any way to force it to notice the change in a calc field?

[David Jondreau]

Well, it should update if all the calculation fields are stored. I think a sample file or lots of details about the bigger picture are needed here.

[Vaughan]

All right, I've used the method suggested and it works well. The only problem is that the flag doesn't register changes in calculation field results. Is there any way to force it to notice the change in a calc field?

Huh? Calculation fields change when their source fields change. Igor the calc field and concentrate on the source fields.

[Vyk]

pretty sure he means calc fields that may be referencing records from a related table, in which case, the main record would not be flagged as modified if the related record data gets changed and the calculated value changes. At least i think that's what he means.

[jn08]

Ah, yes. That is what I mean. So there's no way to update on that basis?

[David Jondreau]

Why don't you tell us what your database is trying to accomplish, in the real world, in plain English, and we'll see if we can help you.

[comment]

Note that Filemaker's native auto-enter modification time also doesn't react to changes in unstored calculations (and for a good reason).

[jn08]

I've got it working about as well as I expected it to get. Though it still doesn't watch the calculated fields, none of those are critical.

However, I suspect I've got my database wired up wrong, which is why I can't quite conceptualise how I'd do what I wanted to do properly.

At any rate, I've got a books database with 80+ fields, many of which are automatically stored, some of which are calculated from related tables. Occasionally I need to output metadata for the books (to booksellers etc). Mostly this is new titles, but I'd like to have the database keep an eye on any books that might have been updated with new information since the last update and output them along with new titles. At the end of that export, I'd like to make sure all exported titles don't get exported again (unless they're modified).