Please advise on how to prevent duplicate records (validatio

Please explain what do you mean.

I have 2 Objects:

1. Attendance

2. Schedule

Relationship: One Schedule to Many Attendance.



For Attendance Object:

~ If I create "New Attendance", the lookup field for the Schedule must be unique.

If there is a schedule that is already exists from the Attendance records, it should not allow to save.



How will I do this for validation? Will I use selectQuery? please guide me.

The system will not allow attaching more than one Schedule to Attendance.

what your saying is the relationship, yes it does not allow. What i'm saying is if I click the Save button, it does allow to save the record.

For example,

1. Created "New Attendance", select the Schedule (Lookup Field). Click Save.

Attendance Record:

AttendanceId Schedule

001 May 17, 2013

2. Created again "New Attendance", select the same Schedule (Lookup Field). Click Save. (This time, it should not allow to save if the schedule is already existed.) But it saves.

AttendanceId Schedule

001 May 17, 2013

002 May 17, 2013



You will noticed that there are two records with the same Schedule record. I would want to prevent duplicate schedule like this. It should not be save right? My question is how will I create a validation to prevent this to save if you select same schedule? There's no option in the Lookup Field where in you can check the checkbox for "Do not allow duplicate records for this field".

So instead of attaching new Attendance record to an existing Schedule record the system creates a new Schedule record?



If you're using hosted rollbase.com platform - please submit support request and specify application name, I'll take a look.

Hi Gener,



You may try to use the Field-level Validation for this on the Attendance object's Schedule Field (lookup).



Your code would look something like:





if (parseInt("{!R111.R222#id}") > 0) return "Error message";

//Where R111 is the Related Schedule's Integration Code

//and R222 is the Related Schedule's Attendance's Integration Code



//The script checks if the Related Schedule's Attendance field has been populated





Additionally, you may want to convert your relationship to 1 Attendance to 1 Schedule as it seems this is more fit to your requirement instead of having 1 Attendance to Many Schedules.



Hope this helps,

Piscoso, Martin

Rollbase

Hi Martin,

Your code is nearly what I wanted to do. Yet it always return the "Error message" even if the schedule id was not existing after clicking the save button. Perhaps, we just need to COUNT the records of Schedule id, if Schedule id is greater than 0, then it will return "Schedule already exists".



What code would I use to count the record of the selected Schedule id?

Hi Gener,



What I gave above was just an example.

Note >> "{!R111.R222#id}" is the same as counting, but usually acts faster compared to a query



I just noticed a flaw in the logic and I think I already got the reason why it isn't working as expected.

Here is my take/assumption on it below (kindly validate/invalidate them):



1. The validation rule revolves only around two objects, hence, when we try to set your Schedule from the Attendance record and the validation rule runs, it will always return a value.



--- Why? Because we just attached the current record

-- I had forgotten to exclude the current record from the rule.

- Fix: Include handling for the current record.



It slipped my mind that they would essentially be using only One relationship code, since we are only on two objects.



I think you can try something like:



if (parseInt("{!R111.R111#id}") > 0 && "{!R111.R111#id}" != "{!id}) return "Error message :: {!R111.R111#id} + {!id}";





I think that is how the tok

Hi Martin,



I really appreciate your help and support. Because of your ideas, I had found my answer. Still your code is likely near but again it always return the same "Error Message".



Here is my fix code:



//where R123850791 is the Schedule ID from Schedule Object.

var query = rbv_api.selectQuery("SELECT R123850791 FROM Attendance", 1000);

rbv_api.printArr(query) //tried to show the array of records



//this code will count the selected ID from the array of records.

var count = rbv_api.selectNumber("SELECT COUNT(1) FROM Attendance WHERE R123850791 = ?", {!R123850791#id});



if (count > 0){

return "Schedule Already Exists. Please choose another schedule";

}





Hope this will also help to others.



Thank you very much.