unique validation on a calculated field

i have 2 relational databases:

client (main database): fields: client #,
matter:                       fields: client #, matter #, calc
the matter database has a calculation field (calc) which is  (client # + matter #)

the client number is unique in the client database
example: client#: 1                      client#: 2
           
the matter# is unique in the matter database only in relation to the  client#. there can be many of the same matter number in the matter database so i can't specify the field as unique, but each client in the matter database can only have one matter number.
example: client#: 1              client#: 1             client#: 2               client#: 2
              matter#: .000        matter#: .001      matter#: .000        matter#: .001
              calc: 1.000            calc: 1.001            calc: 2.000            calc: 2.001

i have two problems:

1st: is there a way to test the calc field for being unique in the matter database? i would like to be warned at the time of entering the matter# if there is an existing record with the same client# & matter# combination (calc field).

2nd: is there a way when adding a new matter# record in the matter database, which is to enter a client# in the client# field and a matter# in the matter# field, to verify that the client# is a  valid record in the client database?
kauakeaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

billmercerCommented:
1st part... Change your concatenated field from a calculation field to a regular text field. Use the auto-enter tab to automatically enter the same calculation you'd use in the calculated field. Also, UNcheck the box labeled "Do not replace existing value for field (if any)" This will allow the auto-entry value to automatically update when the other fields change.
Next, change the field definition validation to require unique values for this field.
Now if you enter values that create a duplicate concatenated field, you'll get an error.

2nd part... The simplest way to do this is to create a value list based on the values of client IDs from the clients table, and define the client ID field in the matters table to be required to be found in that list.

Another easy way to do this is to enter the matter records via a portal from the clients layout. That way, the current client number is automatically entered in the matter record. That will ensure that each matter record has a valid client ID.

If you need more control, you could use calculated fields or script steps to test for the existence of a related field, and display an error message or alert if there's no related record.
 
What version of Filemaker are you using? That will make some difference.

0
lesouefCommented:
A bit more about the portal method on top of what Bill wrote:
You can also work out the next matter # for a given client through the relationship to matters.
If you create a new matter with a script, the matter number will be max(matters::matter#) + 1
if you need to keep leading 0 (nought), use:
right (("000" & max(matters::matter#) + 1) ; 3)
You can also do it this way if you create matters without a script, ie with autoentry in matters table. In this case, create a self relationship using matters::client#. In matter# auto definition use a similar formula:
max(mattersclient:matter#) + 1.
let me know if unclear, methods slightty differs upon filemaker version.

0
kauakeaAuthor Commented:
bill,
1st part: works. however is there a way to test the validation in my save script.
after my commit record step where the validation is taking place, i have it emailing the record information to the person responsible for the file. then i have it opening a new record request.
if the validation fails i would like to be able to end the script.

2nd part: i inserted a test for existing clients from the client database before committing the record in my save script.
this seems to work fine.
the matter numbers not consistent so it is hard to control. example: it could be .000 .001 .002 for billable or it could be .N01 .N02 .N03 for non-billable.
0
billmercerCommented:
To have your script react to a validation error, you have to take control of Filemaker's error handling. Use the Set Error Capture script step to trap error messages. This will disable the normal error dialogs, and you'll have to use your script to handle any errors that arise. Use the IF script step in combination with the Get (Last Error) function to decide what your script should do based on the error. Error # 0 indicates no error, the commit succeeded. Error # 504 indicates the value failed the field validation for uniqueness, so you need to take appropriate action.

There's a complete list of error numbers listed in Filemaker help.

Remember to turn the error handling back on with the Set Error Capture script step after you've tested the value.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.