Prevent duplicate values input based on 1 entity attribute

Hi,

Is there anyway to prevent the input to an Entity from user if there is a duplicate in entry of an attribute of the same Entity?

Lets say

Already in Database

Name: John

IDNumber: 123 (tagged to Local Var of ID_Input)


Then May decides to add in

Input from User

Name: May

IDNumber: 123 (tagged to Local Var of ID_Input)


That the 'SubmitOnClick' will reject?


What i have in mind is something like,

If(ID_Input <> DBbyID.List.Current.DB.IDNumber)

True > Pass

False > IDNumber.Valid = False ; IDNumber.ValidationMessage = "Duplicate Entry!"


But i know this only compares the current row's IDNumber Attribute.

How do i compare it to the entire Entity? 

Hi Xing Yu Choo,

I'd write an Aggregate that fetched all the records of the Entity that had the value I'm trying to guarantee is unique. In a new GetDuplicates aggregate, I'd have this single filter GetDuplicates.List.Current.DB.IDNumber = ID_Input.

Then it's a simple matter of testing the value of GetDuplicates.List.Empty: if it's true, then there are no other entity records with that value, otherwise there are and you react accordingly.

Jorge Martins wrote:

Hi Xing Yu Choo,

I'd write an Aggregate that fetched all the records of the Entity that had the value I'm trying to guarantee is unique. In a new GetDuplicates aggregate, I'd have this single filter GetDuplicates.List.Current.DB.IDNumber = ID_Input.

Then it's a simple matter of testing the value of GetDuplicates.List.Empty: if it's true, then there are no other entity records with that value, otherwise there are and you react accordingly.

Hi Jorge,


I dont think the filter works....

I've created an aggregate GetDBByIDNumber (It's actually just part of the DB)

Added the filter ID_Input = DB.IDNumber

Created an If GetDBByIDNumber.List.Empty

True > Pass

False > Assign [IDNumber.Valid = False ; IDNumber.ValidationMessage = "Duplicate Entry!"]


Duplicate entries based on IDNumber are still being created when input 

Hi Xing Yu Choo,

What are you doing after your IF? Assuming IDNumber is an input field inside a form, you need to have a final IF testing for the Form.Valid property (check the Input Validation lesson for more info): if it is false, end the action and show validation messages, otherwise continue and save.

You can always add this constraint to the database by adding an Index on the DB entity, attribute IDNumber, making sure you set the Unique property to Yes, but you should still perform the validation we discuss above as a best practice.

Jorge Martins wrote:

Hi Xing Yu Choo,

What are you doing after your IF? Assuming IDNumber is an input field inside a form, you need to have a final IF testing for the Form.Valid property (check the Input Validation lesson for more info): if it is false, end the action and show validation messages, otherwise continue and save.

You can always add this constraint to the database by adding an Index on the DB entity, attribute IDNumber, making sure you set the Unique property to Yes, but you should still perform the validation we discuss above as a best practice.


Yes i am aware of the validation method with form.valid if at the end.

The aggregate that is suppose to return duplicate entries based on IDNumber using above mentioned filter but it doesnt work because it only returns the first IDNumber's Input entry (It doesnt compare the whole list)

Xing Yu Choo,

An Aggregate, based on that filter, will return any number of records whose IDNumber attribute has the value currently stored in your variable ID_Input. It abstracts a database query filtered by the IDNumber value.

If no records are found in the database, it means the record you will insert is unique (regarding its IDNumber attribute at least). If the aggregate returns a non-empty list, it means there is at least one record already in the database with that value in its IDNumber attribute.

If you are not getting this behaviour and you can show us what exactly you are doing and what is happening (or a sample OML that reproduces your problem). Have you tried debugging?

Jorge Martins wrote:

Xing Yu Choo,

An Aggregate, based on that filter, will return any number of records whose IDNumber attribute has the value currently stored in your variable ID_Input. It abstracts a database query filtered by the IDNumber value.

If no records are found in the database, it means the record you will insert is unique (regarding its IDNumber attribute at least). If the aggregate returns a non-empty list, it means there is at least one record already in the database with that value in its IDNumber attribute.

If you are not getting this behaviour and you can show us what exactly you are doing and what is happening (or a sample OML that reproduces your problem). Have you tried debugging?

Hi attached is the OML.

It's under PEQuestions's Submitonclick


Thank you. The GetDuplicates we mentioned is 

Xing Yu Choo,

Aggregates you define at the screen level will run (asynchronously) based on the values the variables they use have initially: since NRIC_Input would be empty in the start, the outcome would be an empty list.

You need your Aggregate running inside the action where you are executing the validation logic... since you are not using the Screen aggregate anywhere else, I'd suggest you just delete it and add one right before the IF tool you circled red above, with the same filter: NRIC_Input = PEAnswers.NRIC.

If this screen can also be used to edit an existing PEAnswers record, I'd suggest you add a second filter to that Aggregate, excluding the PEAnswers you are editing from the check for duplicate NRIC: PEAnswers.Id <> PEAnswersId

Jorge Martins wrote:

Xing Yu Choo,

Aggregates you define at the screen level will run (asynchronously) based on the values the variables they use have initially: since NRIC_Input would be empty in the start, the outcome would be an empty list.

You need your Aggregate running inside the action where you are executing the validation logic... since you are not using the Screen aggregate anywhere else, I'd suggest you just delete it and add one right before the IF tool you circled red above, with the same filter: NRIC_Input = PEAnswers.NRIC.

If this screen can also be used to edit an existing PEAnswers record, I'd suggest you add a second filter to that Aggregate, excluding the PEAnswers you are editing from the check for duplicate NRIC: PEAnswers.Id <> PEAnswersId

The aggregate option avail is extracting information from Local Storage... Is there anyway i can direct extract from the database? 


If the Entity was defined in the Database section, you should be able to create a database query by dragging the entity from the Database directly to the action flow (in a Client Action there are also two Tools for LocalStorage - hollow - and Database Aggregates - filled - on the toolbox, if I'm not mistaken). You must take into consideration that by doing this without any other checks you are forcing your application to be online, and this will be slower than typical interaction with a mobile app, as it needs to go to the server for these checks.

If you want to have a server-side check, I suggest you put the Aggregate I described in a Server Action (you can name it CheckForDuplicates), with two Input Parameters (NRIC and PEAnswerId) and a AlreadyExists boolean as Output Parameter. After the Aggregate runs, assign to your AlreadyExists output the value not YourAggregate.List.Empty.

Then you just need to call this CheckForDuplicates Server Action from your Client Actions any time you want to check if the NRIC already exists in another database record.

Jorge Martins wrote:

If the Entity was defined in the Database section, you should be able to create a database query by dragging the entity from the Database directly to the action flow (in a Client Action there are also two Tools for LocalStorage - hollow - and Database Aggregates - filled - on the toolbox, if I'm not mistaken). You must take into consideration that by doing this without any other checks you are forcing your application to be online, and this will be slower than typical interaction with a mobile app, as it needs to go to the server for these checks.

If you want to have a server-side check, I suggest you put the Aggregate I described in a Server Action (you can name it CheckForDuplicates), with two Input Parameters (NRIC and PEAnswerId) and a AlreadyExists boolean as Output Parameter. After the Aggregate runs, assign to your AlreadyExists output the value not YourAggregate.List.Empty.

Then you just need to call this CheckForDuplicates Server Action from your Client Actions any time you want to check if the NRIC already exists in another database record.


I cant drag Entity from DataTab to the main flow... 

I've tried your method but it still does not work (duplicate checker)

Attached is the oml. Did i execute your method correctly?


Hi Xing Yu Choo,

You mostly implemented it correctly, yes. However:

  • On your Screen Action, your If tool (after the calling the Server Action) is not checking for the output of the Server Action, and is still using an Aggregate that shouldn't even be on your Mobile Screen anymore.
  • Additionally, on the Aggregate of your Server Action, you are not excluding an existing PEAnswers record you may be editing (using a second filter with PEAnswers.Id <> PEAnswerId), so in case you are editing an existing record the Aggregate will return it, even though it is the only one on the database and the one you are modifying)

Jorge Martins wrote:

Hi Xing Yu Choo,

You mostly implemented it correctly, yes. However:

  • On your Screen Action, your If tool (after the calling the Server Action) is not checking for the output of the Server Action, and is still using an Aggregate that shouldn't even be on your Mobile Screen anymore.
  • Additionally, on the Aggregate of your Server Action, you are not excluding an existing PEAnswers record you may be editing (using a second filter with PEAnswers.Id <> PEAnswerId), so in case you are editing an existing record the Aggregate will return it, even though it is the only one on the database and the one you are modifying)

Hi Jorge,

I've managed to get what i want via ForEach function, comparing the input to every entry.

Thank you for your help anyways!

Hi Xing Yu Choo,

You can try this one. 

On click of Submit you can make one DB call as "select * from Table Name where IDNumber =  ID_Input " and check if table.list.Empty() if it is empty so there is no record with this ID so you can continue to save or else if it is false then you can give error message as "Duplicate Entry!"