unique validation on a calculated field

Posted on 2005-03-28
Medium Priority
Last Modified: 2012-08-13
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?
Question by:kauakea
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 19

Expert Comment

ID: 13650312
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.

LVL 28

Expert Comment

ID: 13650936
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.


Author Comment

ID: 13656195
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.
LVL 19

Accepted Solution

billmercer earned 2000 total points
ID: 13658273
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.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question