Help with Validation Calculation

[Martin_S]

OK, so I've been asked to resolve an issue whereby a calculation on a field does not appear to be working as expected.

 

We have a field called 'Activity', and based on what item inthe pick list is chosen, the appropriate description field is displayed (it is something different for each activity). The logic is identical for each field, and the logic is:

 

We have three levels of user - user, office admin and system admin.

 

All users (any level) must add a description, and the user should not be able to save the record if the description is empty.

 

If five minutes since the record has been created has passed, a user cannot modify the description.

 

Office and system admins can modify the description irrespective of time passed.

 

So for the description field, I have Validated by calculation ticked, and the calculation (for the Service activity) is as follows:

( ( d_Activity_Type = "Service" and not IsEmpty( Self ) ) or ( d_Activity_Type ≠ "Service" and IsEmpty( Self ) ) )

and  

If ( IsUserUser ; Get ( CurrentTimeStamp ) < ( r_Creation_Timestamp + 18000 ) or IsEmpty( r_Creation_Timestamp ) ; True ) 

//Time is in seconds, 18000 = 5 minutes

For a Mailing, the code is almost identical:

( ( d_Activity_Type = "Mailing" and not IsEmpty( Self ) ) or ( d_Activity_Type ≠ "Mailing" and IsEmpty( Self ) ) )

and  

If ( IsUserUser ; Get ( CurrentTimeStamp ) < ( r_Creation_Timestamp + 18000 ) or IsEmpty( r_Creation_Timestamp ) ; True ) 

The function 'IsUserUser' is a custom function that returns true/false based on whether the user is of the user status or not.

 

What I'm seeing is that a record can be saved without a description for an office admin. I've not tried it for a user yet, but want to get the logic right, as am obviously missing something.

 

Any help/advice (or better way to do this) gratefully received.

 

Many thanks

 

Martin

[comment]

Try (untested!):

not ( d_Activity_Type = "Service" and IsEmpty ( Self ) )
and
not ( IsUserUser and Get (CurrentTimeStamp) > r_Creation_Timestamp + 18000 )

based on what item inthe pick list is chosen, the appropriate description field is displayed (it is something different for each activity).

 

Why is it necessary to have three description fields for one item?

... or IsEmpty( r_Creation_Timestamp ) ...

 

How is it possible for r_Creation_Timestamp to be empty?

[Martin_S]

Try (untested!):

not ( d_Activity_Type = "Service" and IsEmpty ( Self ) )
and
not ( IsUserUser and Get (CurrentTimeStamp) > r_Creation_Timestamp + 18000 )

Why is it necessary to have three description fields for one item?

 

How is it possible for r_Creation_Timestamp to be empty?

 

Thanks for your suggestion, I think I need to keep the or part though as this stops the validation triggering when the control is hidden. Will give it a go and see what happens.

 

To answer your questions

 

I'm supporting the application which was written by a third-party, so having to work around what is there and try and fix/modify as required. So changing this would be a change that, as the application has been released to the business, I wouldn't have the OK to make. Any major changes bar bug fixes would be triaged and unless it wasn't working, left as was.

 

Currently, on the live system it works but with different rules, in that no one can modify after 5 minutes, irrespective of user status.

( ( d_Activity_Type = "Service" and not IsEmpty( Self ) ) or ( d_Activity_Type ≠ "Service" and IsEmpty( Self ) ) )

and ( Get ( CurrentTimeStamp ) < ( r_Creation_Timestamp + 18000 ) or IsEmpty( r_Creation_Timestamp ) )

Anyway, in this case, each option requires a different description field - for the type 'ad-hoc' it is plain text and the others three it is a pick-list (different list/source for each type).

 

And regarding empty creating timestamp, am not sure if this condition is ever true - I'm assuming this was added to work with historic data (before the field was added), but I'm guessing all records now have a creation date so I can take this element of the calculation away.

 

Thanks again

 

Martin

[comment]

 I think I need to keep the or part though as this stops the validation triggering when the control is hidden.

 

I am afraid I don't follow. What is the "or part" and what does this have to do with validation triggering?

[Martin_S]

or ( d_Activity_Type ≠ "Mailing" and IsEmpty( Self ) ) )

Sorry - I was referring to this part...but trying your example seems to work without it. I was thinking that this was there to stop it triggering from the hidden fields (for the non-selected types), but it seems that they don't trigger.

 

Thanks

[comment]

Uhm ... this just occurred to me:

 

If the field is set to validate only when modified, then it can be left empty.

 

OTOH, if the field is validating even if not modified, then users will not be able to modify any field after 5 minutes have passed. Perhaps that is the desired effect - but then this should be part of their privilege set, not attached to any single field.

[Martin_S]

It's only this field that the 5 minute setting applies to, not all. Each of the four description fields have the setting unchecked for Validate only if modified. Testing it as it is works exactly as required, so thanks.

 

Martin

[comment]

Testing it as it is works exactly as required

 

Well then either I am mistaken or you are not testing this very well...

[Martin_S]

Well then either I am mistaken or you are not testing this very well...

One minor error, 18000 is not 5 minutes! The value is now 300, and it has been tested under each of the three user roles and signed off as doing what they required.

 

I used your code exactly as is (modified only for the first part setting which activity type):

not ( d_Activity_Type = "Service" and ...
not ( d_Activity_Type = "Mailing" and ...

etc.

So why do you think it may be wrong?

[comment]

5 minutes, 5 hours ... let's not be petty.

 

Anyway, the way I see it is that if the field is to set to validate even if not modified, then it will validate whenever any field has been modified and user attempts to commit the record. Now, if the user is a "user" and the record is more than 5 minutes old, then the validation will fail because this part is true:

IsUserUser and Get (CurrentTimeStamp) > r_Creation_Timestamp + 300

[Martin_S]

 

5 minutes, 5 hours ... let's not be petty.

 

Anyway, the way I see it is that if the field is to set to validate even if not modified, then it will validate whenever any field has been modified and user attempts to commit the record. Now, if the user is a "user" and the record is more than 5 minutes old, then the validation will fail because this part is true:

IsUserUser and Get (CurrentTimeStamp) > r_Creation_Timestamp + 300

Seems I described it wrongly - after 5 minutes, a user cannot modify the entire record, which is what you say should be happening - that's correct. Non-users can modify anytime.

 

Thanks

 

Martin

[comment]

after 5 minutes, a user cannot modify the entire record

 

Ah, good. So the laws of logic operate the same way at your place as they do in the rest of the universe. However, this is the case I referred to in post #6 above:

 

Perhaps that is the desired effect - but then this should be part of their privilege set, not attached to any single field.

 

IIUC, you now have no less than three fields enforcing the same rule. Now suppose you want to extend that limit to 10 minutes - you have to modify the validation formula three times.

Edited August 28, 2014 by comment

[comment]

BTW, Filemaker has a nice built-in feature where if you allow users to create records but not edit them, they can edit newly created records until the end of the day when they log out. This is not 5 minutes but very reasonable, IMHO, and a breeze to implement.

[Martin_S]

Ah, good. So the laws of logic operate the same way at your place as they do in the rest of the universe. However, this is the case I referred to in post #6 above:

 

 

IIUC, you now have no less than three fields enforcing the same rule. Now suppose you want to extend that limit to 10 minutes - you have to modify the validation formula three times.

They do, I mis-interpreted the requirements, but you managed to second guess that anyway

 

Yes, I can see your point (and it's applied to four fields, not three).

 

I'm actually going on a two day training course (as to date have had no training on FM - could you guess? ), so maybe I can bring this up and learn how to apply it in a privilege set, and not as currently.

 

BTW, Filemaker has a nice built-in feature where if you allow users to create records but not edit them, they can edit newly created records until the end of the day when they log out. This is not 5 minutes but very reasonable, IMHO, and a breeze to implement.

OK, thanks - for whatever reason the 5 minutes was specific, but worth knowing - thanks.

Martin

[Lee Smith]

Hi Martin,

 

One of the great things about our Forum is that we can attach copies of our files. I’m wondering why you didn’t utilize this feature as it normally speeds up the whole process of getting the Original Poster (you) a solution to their problem.

 

If you don’t know how to do this, just follow the steps in this article. ATTACH FILE

 

Lee

[Martin_S]

Hi Martin,

 

One of the great things about our Forum is that we can attach copies of our files.

Hi Lee

 

I didn't include the application because firstly it contains confidential data and secondly would take too long to explain how to get to where the issue is in terms of using the tool.

 

I appreciate I could have removed all the data, but still, giving instructions would have taken too long.

 

I appreciate your point raised, I would happily of included the file if it was possible.

 

Martin

[Lee Smith]

 

I appreciate I could have removed all the data, but still, giving instructions would have taken too long.

 

It’s obvious that you didn’t read the link I posted, your concerns are covered.

[Martin_S]

It’s obvious that you didn’t read the link I posted, your concerns are covered.

I did, but felt the time it would take for me to set up sufficient data for it to make sense and to then write sufficient instructions on how to get to the issue, and because of company branding etc, decided against including a copy.

It may be the right thing to do, and i totally appreciate where you are coming from but due to the specific business focus this covers, it is not something that would be easy to document simply!

Martin

[bruceR]

You're asking for free help. it is YOUR job to do the work of making it possible - and easy - for others to help you.

 

OK, you can't supply your actual data file.

 

Nevertheless, you can build an example file that is similar enough to allow others to help.

You might start with a clone of your file. Cloning removes all data from a file. Then you might do other work to incorporate sample data, and remove any other proprietary information that might still exist in the clone.

[LaRetta]

Well Martin is obviously putting in the work, Bruce.

 

Martin, when I am struck with a situation such as this is to create a new simple, sample file with just the few fields/relationships necessary to clearly see the issues.  If you try to work in an existing, complex file then the logic can easily get lost.  A new file also allows you to clearly see what happens with the data when you only have a few records.

 

Then, if still stuck, you can post that file here (zipped first).  Or if you solve the problem yourself using your test file, you can easily transfer the logic into your real solution without error.  The other added benefit of a sample file is that you can present it to your management for review to be sure you have their business rules in line.

[comment]

I don't think providing a file is a requisite. IMHO, this question was presented clearly enough and a file was not necessary.

 

There are some questions where a file may be helpful. Let's be more discriminating. I, for one, have enough files of my own and I am not looking forward to wade through other people's files, if the question can be presented in other ways.

[David Jondreau]

BTW, Filemaker has a nice built-in feature where if you allow users to create records but not edit them, they can edit newly created records until the end of the day when they log out. This is not 5 minutes but very reasonable, IMHO, and a breeze to implement.

 

I did not know about this! Is that what happens when you set Create to "yes" and Edit to "no" for Custom privileges? And that's editing their own session's new records?

[comment]

And that's editing their own session's new records?

 

You know, I never tested if this applies only to records created by the same user, or all records created during the current session, no matter by whom. I rather suspect it's the latter but I don't really know. If you are inclined to check this, please let us all know.

[David Jondreau]

I gave a quick test and it does appear to be session-based.

 

If I log into a file using the same account name on two different copies of FileMaker (actually a 12 and a 13) hitting a hosted file (on 13). The privileges are set to make New Records but not edit.

 

I can create new records and edit those with each account, but they can't edit each others. Re-logging in "resets" the session.

 

Here's a sample: fmp://wingforward12.dyndns.info/Farm.fmp12

full access: admin / 123

New / Not Edit: test / test

[comment]

they can't edit each others.

 

Ah, okay. That's good to know. Thanks for taking the time.