function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SFmaverickSFmaverick 

Help with best approach to problem

Background: I have a custom Object called Shift. It has lookup field to Contact. Each 'record' of the object Shift is just what you'd expect, it contains a Date and time that a Contact is scheduled to work.

 

Problem: I need to prevent users from scheduling the same contact for two shifts on the same date.

 

If there is a Shift where:  Contact = John Smith; Date = 9/15/2010, I don't want the user to be able to create another shift with those same values. I don't want to double book anybody.

 

I'm not sure of the simplest way to prevent this; or even a possible way. Thanks for any help / advice!

Best Answer chosen by Admin (Salesforce Developers) 
hisrinuhisrinu

You have to use a workflow to do this not the formula, then you can set this field as unique(even you can hide from pagelayouts)

 

 

hisrinu wrote:

You can try a combination of contactid and date fields(convert data type to text) into another new text field and make that field as unique, so whenever user is trying to create a new shift for this contact automatically uniqueness gives an error to user

 

All Answers

hisrinuhisrinu

You can try a combination of contactid and date fields(convert data type to text) into another new text field and make that field as unique, so whenever user is trying to create a new shift for this contact automatically uniqueness gives an error to user
SFmaverickSFmaverick

I had considered a field that combined the Contacts Name and the Date, which is easy to do in a formula field, but formula fields can't be Unique IDs. I could easily create a trigger that used the formula field to populate a text field, but the formula wouldn't be populated until after the record was created, which would then make it too late to prevent the user from creating the shift.
ScoobieScoobie

The problem you would have here is not someone creating two shifts e.g. both 8-5 but when someone creates two shifts 8-5 and 12 - 9. These would overlap but would be perfectly valid for the same person doing these shifts using your current solution.

 

 

I think you may need to do a bit more design and capture more use cases to establish the solution you want to implement. Once you have these you can begin putting the meat on the bones so to speak.

 

Scheduling in Force is possible as we have successfully implemented an appointments system which dealt with conflicts but this was in no means a simple piece of code.
SFmaverickSFmaverick

I would use the Contact and the Date, not the time. Even if the times don't overlap, a contact will never work two shifts on the same date.
hisrinuhisrinu

You have to use a workflow to do this not the formula, then you can set this field as unique(even you can hide from pagelayouts)

 

 

hisrinu wrote:

You can try a combination of contactid and date fields(convert data type to text) into another new text field and make that field as unique, so whenever user is trying to create a new shift for this contact automatically uniqueness gives an error to user

 

This was selected as the best answer
Always ThinkinAlways Thinkin

Hi,

Since it seems triggers are on the table as an option what if you:

  1. Use a Before Insert/Update trigger to prevent insertion of bad record
  2. Run a query against all Shift records where the Contact is the same and where the start datetime in the new record is greater than another record's start datetime and less than the same record's end datetime OR the end datetime is greater than another record's start datetime and less than the same record's end datetime.
  3. If the query returns any records, throw an error message.
  4. If the query throws no error message, allow the insert/update to continue.

This trigger will quickly hit governor limits because every record in the trigger.new will have to be queried uniquely, so you'll only be able to handle up to 20 records. I don't know the context of your use case, that may or may not be a problem.