You could make a concatenated field of :
"Project # & Action # "
And require that field to be unique.....
So you need a text field that combines these values, including the project number with a unique values validation. How is that not working for you?
Sometimes it helps to put a separator character such as the pipe character between each field's value.
The problem of unicity is born when you create a new record.
So how do you create a new record ?
I presume that the database user selects Discipline and project by setting 2 globals then hits a create new button, and it's your job to set the item number.
Once they select dis and proj and hit new, you can do a 1 + ExecuteSQL("SELECT Max(Item#) FROM yourtable WHERE Discipline = ? AND Project# = ?"; gDis; gProj) and create a record with gDis, gProj and having the SQL's result set into the Item#.
Thank you to all
I created a text field as suggested.....and it will now live on my layout somewhere out of the way. So the user hits the "new" button and a new record with their project number is created... then each field across the list view is populated and the item number is entered and then the discipline is selected and tabbed out of which then sets a trigger to commit the record so the validation can take place....Maybe the way I have it set up is convoluted but it is working and giving the user a message that the ProjAction# is not unique. With each new record the project number is populated based on the project number in the found set....then the user enters the item number and discipline (Architectural, Mechanical, Electrical, etc.) One the discipline is selected it sets of the trigger.
All of these answer are correct. Thank you
FWIW, you can customize the message to something like
"That Action # already exists in this specific project. Please update the existing record instead of making a new one"
...might make it easier for your users to understand what the error is.
Yes, good idea, I tried to customize the message but got distracted. What does FWIW mean please.
For What it's worth, FWIW means "For What It's Worth".
You also do not need to have the auto-enter field used for unique checks actually present on the layout for this to work.