Is the employee ID, the only thing that needs to be unique or do you also have an event ID and what you need are unique combinations of Event ID and Employee ID's?
If so, there are several different approaches you can take--from a field validation that shows an error if the same employee is selected a second time for the same event to interface designs that simply make it impossible to select the same employee twice.
But before I can discuss such options, I need to know the tables and relationships involved as well as the answer to the question that I asked at the start of this post.
Event ID and Employee ID are unique and i need them that way as the event ID is the same as the contract ID and that is how i for sure know which event is which (We usually work multiple times at the same address). The employee is unique per employee for Payroll purposes.
I would need a validation field, as there are times where we allow them to work two different events the same days, so i would an alert letting me he has been double book, i will then write notes on the event that will let me know why he is there twice on the same date.
Once a contract gets approve on the contracts table it will be then get confirmed and it will create a new record on the Confirm Events table. While on the CONFIRMED EVENTS table I select the employees that request each event (there are many events each day). Once an employee name is selected for an event the event's information (Date, address, start/end time) will appear on the employee payroll information. so that the path would be CONTRACTS - CONFIRMED EVENTS - EMPLOYEE PAYROLL.
Employee and event id must be unique, but only in the tables where these ID's are generated--employees and confirmed events. (And your screen shot had not yet appeared in the forum when I posted my first response.)
What is unique is the combination of event ID and employee ID in each record of your join table.
You can define a text field in the join table with this auto-entered calculation:
_kf_confirmed_event_id & " " & _kf_employee_id
Then you can select a "unique values" validation rule. This is a good "insurance policy", but it allows users to make the mistake of adding the same employee twice for the same event before displaying an error message--not the most user friendly of ways of handling this.
But a dwindling value list of employees can update to not list an employee in your value list once you have selected that employee for the current event. And other options also work to keep the use from making such a duplicate selection in the first place.
See this demo file for an example of this validation field option, a diminishing value list and a "check boxes" selection format as three different ways to prevent this creation of duplicate join table records:
Just use Open from FileMaker's file menu to open and convert this older format file to the newer file format if you are using FileMaker 12 or newer. When you examine this file, just think of "contacts" as your table of employees.
so if i want to do it with the date, do i replace the eventid with event date?
Why would you do that when you already have and event id shown in your screen shot? What problem would that solve for you?
The formula you gave works great when i enter an employe twice on an EVENT. BUT if i enter an employee TWICE on a DIFFERENT EVENT on the SAME DATE it will not alert me. I need to know if enter an employee to work two DIFFERENT events the SAME DAY.
Then yes, you could use the date in place of the event ID--but be careful, this sounds more like a case where you want a warning--not an actual prohibition preventing the entry of the same employee for two events on the same day. (You might have a few exceptions where the same employee for two events of the same day is actually the correct input.) If that's the case, you'll want to be sure to provide uses with the option to override the warning when it appears.
yes that is exactly the case! some times we allow the same employee to work two events in one day.
Do i need to add a date field to the join table?
The auto-enter calculation that I described could auto-enter the date from confirmed events, but if you do, you'll need to be careful if you ever need to update a confirmed event record by changing the date of that event. If you do, the auto-enter calculation will not automatically update to use the new date--you'll need to add a script or manually do the following after such a date change:
- Find all join table records for that event.
- use Replace Field Contents with the calculation option to re-enter the EmployeeID and date.
for some reason if i check "ALLOW USERS TO OVERWRITE DURING DATA ENTREE" it will not give me the alert "EMPLOYEE ALREADY WORKING" but instead it give the attached screen shot.
What happens if you add employeeAlert to the layout or portal?
If that changes the validation error message shown, there are ways to hide this field while leaving it present on the layout.
it worked! thank you!
If this is FileMaker 13, you can select this field while in layout mode and enter the word: True into the Hide Object When box in the Inspector's data tab.
If this is FileMaker 12, you may be able to drag this field out to the right of the right hand edge of the layout to hide it.
If this is an older version, you can size the field very small and see if everything still works if you use the Inspector's behavior settings to deny access to the field while in browse mode. If not, you may want to remove the field from the layout's tab order and hide it behind a tab control or something.