The key to this is how do you define that it has been modified? The problem is of course that you cannot control any auto-entered modification dates or times so somehow that process needs to have some manual components to it... especially if those dates are in the tables concerned. It is therefore something I would do from the perspective of another table where you can sit the two tables for comparison side by side. After you have verified the mod date on the local info should be pasted to the imported mod date so they match and disappear from your view.
Hope that makes sense
You lost me Lyndsay. I uploaded a demo of what I am doing to my original post. Not sure if I was to wordy the first time. Basically what I want is if the user looks at the info and all is ok then they can manually check the verified checkbox. Or if something needs to change they can select it from the drop down and once they do it auto checks the checkbox. I thought about using two verified fields stacked and conditionally formatted as the others are but then in list view it would make it difficult to sort by those verified.
One way I know that works is a bit clumsy, the other two ways you should test.
A bit clumsy but working
1. Make an extra number field called something like 'verifiedByHand';
2. Expand your verifying Case statement so that it is true when verifiedByHand is true. This must be the first part of the case-statement: Case( verifiedByHand = 1 ; 1 ; (follows the original calculation))
3. Stack this field underneath your calculated field and make the calculated field explicitly non-enterable.
From your description I do not know at what time the check-box should change - during import, or during day-time, when users modify the database.
Other ways are:
1. Make the verification field a normal number field, and run a script after the import has been finished, that sets the field according to your calculation. Now you can change the field in the normal way by clicking on it. This will work, but it's slower.
2. Change the verification field into a normal number field and put your calculation in its auto-enter calculation. You have to run a test if this calculation is triggered by the import. It probably is. If so, this is the cleanest solution.
3. If users modify the fields during day-time, and you want the check-box to be set whenever that happens, you can add a 'onObjectValidate' script-trigger to the user-modifiable fields. This trigger triggers only when the field has been modified, not when a user enters and exits without doing something. It should trigger a simple script that sets the verification-field.
Hope this helps.
I had a look at your demo file.
You are almost there... All you need do is alter the definition of your field.
Redefine the "Verified" field making it a Text field; then go to Options > Auto-Enter, and tick the box where it says "Calculated value". You can use the calculation you have already defined. It works OK.
The last thing is to have the option: " Do not relace existing value...." NOT ticked.