Checking whether an existing record exists in database

Hi,

I am making a form where users have to input a topic. However, I want it such that when a user types in a existing topic that is already in the database, the form will be invalid. I am having problems with checking whether the topic already exists in the database.

I searched through the forum and found 2 probable solutions: 

1) https://www.outsystems.com/forums/discussion/29663/how-to-verify-item-present-in-list-or-not/

2) https://www.outsystems.com/forums/discussion/47620/how-to-check-if-an-entity-already-contains-a-record-with-an-attribute-set-to-true/ 

One is the use the ListIndexOf, however i have no idea how to use and implement this action in my module.

Another is fetching data from the database, however i do not know how to filter correctly in the aggregate editor to check whether there is an existing record in the database. 

Can anyone help me to understand both methods? Thanks. 

I tried using the ListIndexOf as shown in the oml attached, but i couldn't even create a new topic. Is there a issue anywhere ? And i am also quite unsure how the condition for the ListIndexOf should be like. 

Solution

Hi, Jolene.


You just need to query with an aggregate, which with the following filters: 

Trim (ToUpper (YourEntitie.Atribute)) = Trim (ToUpper (Form.InputVariable)) 

And

YourEntitie.Id <> InputId 

Then check in an 'If', if the aggregate returns any data. 

Aggregate.Empty = True 

If it is empty it is valid, if not, it is invalid.


If you intend to use a Record List, instead of an aggregate, I suggest you use the ListFilter function. And make the same filter that I showed above, just adjusting the parameters. Then check if the filter returns any items.


The Trim and the ToUpper are because the field appears to be of the text type. This will avoid query errors due to spaces and uppercase or lowercase letters.
Solution

Eduardo is correct but just a note on his post above, as with SQL queries the Aggregate string compares are case insensitive so the ToUpper shouldn't be necessary but the trim won't hurt if you are worried about leading or trailing spaces

So, Jolene. 

The inputId, to resolve to use the same screen to edit too, is not mandatory. 

You can create a Server Action that only allows you to create a new item in your entity if it does not exist in the database. 

You can do this on the same Action server that will create the item. Enter the text variable as an input parameter and a Boolean variable as an output indicating whether the item already exists or not. 

Follow the same steps as I said above, only now, if the result of the if is invalid, set the value of the output variable to false. If it is valid, set to true and already create your item in the database.

In the client action you will check the output of the Server action, and set whether the field is valid or not according to it.

 Good luck.