Conditional Mod Field

[ShawnR]

I have a database solution that is running on FileMaker Server 5. At night, I have it run some scripts to update invoice information. I have Created By and Mod By fields, however I do not want the "Server" to show up as the person that last mod'ed the records. Is there a way to do this? Thanks

Shawn

[Wendy T]

Hi Shawn

If your scripted updates are importing files, there is a checkbox option called "perform auto-enter options...". Running the import with this UNCHECKED will not affected auto-enter fields such as your Mod By field.

Another method (requires manual intervention, unfortunately) is to open "Define Fields" and remove the auto-enter bit from Mod By, run your updates, then put the auto-enter bit back in again.

You've possibly already considered these, but since there were no other replies...

I'm not really familiar with FMServer, so maybe it has other possibilities that someone else can shed light on??

cheers,

Wendy

[ShawnR]

The update does not do any importing so that is not it...

Since the update happens at night while no one is in the office, I don't think the un-check option is an option.

I think the only thing I can due is make a second Mod field (No auto enter) and have the update script COPY the first Mod field (Auto entered) and place it into the second if the name is not the server name... Not pretty, but I think that might be the only way..

Thanks for the input...

Shawn

[-Queue-]

Or make your Mod field a calculation: Case( not Status(CurrentUserName) = 'server' and Mod Time, Status(CurrentUserName), GetField( "thisfield" ) )

This will overwrite the field with itself when the server changes something and only change it when another user makes a modification.

[ShawnR]

When I tried this calculation option, I get a problem with circular definition. I assumed where "thisfield" is, you mean the name of the field the calculation is defined. (ie field:modified by)

[-Queue-]

That's correct. Make sure you're quoting it, too, otherwise it will look for the contents of the field name specified in 'modified by', which, of course, doesn't exist.

[ShawnR]

Okay, almost there... I think it has to do with the "mod time' portion. Here is what I have so far

Modified By = Calculation =

Case(

(PatternCount(Status(CurrentUserName),"Server") = 0) and Modified At, Status(CurrentUserName),

GetField("Modified By")

)

I had to change the way it looks for the server due to the name will change from site to site. But it is still changing. Does it have to do with the way the Mod Time is changed? Could it be looking at the calculation before it changes the Mod Time? (Mod Time = Auto Enter, Mod Time, No Modification)

[-Queue-]

When you say it is still changing, do you mean the circular definition error is still occurring? Did you intend for 'Modified At' to be 'Mod Time'?

[ShawnR]

It will still update the 'Modified By' even if it is the 'server' doing the update.

Yes, 'Modified At' should be 'Modified Time', sorry about that.

[-Queue-]

It should update 'Modified By' when anyone, including the server, changes the record, but it should use the name currently in the field instead of using the server's name. So the name basically overwrites itself in lieu of the server's name.

If this isn't happening, can you attach a copy of the file?

[ShawnR]

I must be on crack or something.... I just tried the same calculation above (that I posted) in a "test" database, and it works fine.... I will have to look in the production database and see if I made a type-o....

Thanks for the help once again....

Shawn

[-Queue-]

No harm, no foul. Glad it worked for ya.

[-Queue-]

Okay, after testing this idea on a db with 85000+ records (which I highly do not recommend ), I've realized there is a definite procedure to go through in order for it to work correctly (and not lose 85000+ records' worth of data!) Ahem I've outlined the procedure so as to help prevent other developers from going postal.

1. Change your FM username in Edit->Preferences->Application to a specific username that your calculation will ignore, e.g. in this case, 'server, in order to prevent all records from showing your username as the last modifier or today as the last modified date.

2. Create one extra temporary field for each 'mod' field that you will be changing. In my case, this is one extra text and one extra date field.

3. Perform a Replace on each of these fields with the data from that of their permanent 'twin'.

4. Go into Define Fields and change each of the permanent fields to a calculation similar to this one: Case( not Status(CurrentUserName) = "server" and Mod Time, Status(Current...), GetField( "tempfield" ) ), where 'tempfield' is the field you've recently replaced with the data for each field.

5. Exit Define Fields and allow the mod fields to update.

6. Return to Define Fields and change "tempfield" to the name of the field currently being defined.

7. Delete the temporary fields and exit Define Fields. The data should now be as desired. Don't forget to revert your username.

The reason for the temporary fields, for the curious, is that changing a text, date, or time field to a calculation with Get Field() specified with the current field causes FileMaker to obliterate any data currently in the field. But if you change it to a calculation with Get Field() specifying another field, allow it to update, and then change the Get Field() to the current field, it keeps the data already present in the field. I'm not exactly sure why this happens. I only know it's nice to have deciphered the way to make the magic work.

Figuring this out was more enjoyable than having our nightly processes crash tonight and going to the office to clean it up, and then having the police show up because some janitor got high and ran around the building, triggering the yard alarm in the process. Anyone want to trade jobs?

[ShawnR]

Okay, got it working in the production database. I did not understand when you change a field name, the 'GetField("fieldname")' did not update on its own, but now it makes sence that it would not.

While thinking about this, I was going to take it a step farther. In some cases, I have a user run the same script (the one that the server runs at night) and was going to have the calculation check to see if the user was running it by checking a global and then update accordingly.

update = Global, Number

Set 'update' to 1 if I want it to update, 0 if not to update.

Now here is the part I do not understand.

When I change the calculation to this:

----------- ONLY TRY IN A TEST DATABASE --------------

Case(

update = 0, GetField("Modified By"),

(PatternCount(Status(CurrentUserName),"Server") = 0) and Modified Time, Status(CurrentUserName),

GetField("Modified By")

)

----------- ONLY TRY IN A TEST DATABASE --------------

Or if I use nested 'IF' statements, FileMaker 5.5v2 will accept the calculation, but when you click on DONE in the define fields dialog box, FileMaker goes to lunch.

I have to force FileMaker to quit, and it damages the database.

I have attached a test database with out the added line in the calculation. Will this just not work?

Test.zip

[-Queue-]

I believe you'll want 'update = 0' (or 'not update') 'AND Modified Time'. Otherwise, you're putting the system into a perpetual spin. You only want it to update when modified, not constantly. I would test this, but I cannot at the moment. I will later, however.

[-Queue-]

This adds another level of difficulty with the Update field in place. It's a global field, so you're going to have to use a lookup-based field instead, because the calculation can't be stored and will constantly update, which is definitely not something you want to do.

I'll dig into this more during this weekend.

[-Queue-]

Okay, I've resolved the problem. The attached file should provide all you need to implement this technique fully.

This consists of looking up using unstored calculations when a record is modified and using the default "Don't copy contents if empty" to prevent the data from 'updating' when the calculation fields are null.

This also includes displaying the created information when the record is first created, as long as creation of new records is scripted and the record is exited before any data entry is performed.

I've used a global to hold what the UserName should contain and a calculation field for a repeated test, for the sake of clarity and ease of understanding. They are not necessary to the technique or possibly even desirable (depending upon your situation).

I think I may clean this up and post it as a sample file, as well. Thanks for the mental calisthenics!

p.s. It appears that the problem with your formula (and a few of mine) is the multiple usage of GetField() for the current field. Since If() and Case() resolve an entire calculation before providing the answer, more than one call for the current field (even in separate Case() portions) causes an infinite loop.

ConditionalMod.zip