Use lookup tables in validation conditions

Users
1

Hi,

I was wondering if anyone can describe the steps of using a lookup table in validating name. For example, I want to set up a registration form and in the registration form, the enumerators with enter the first and last name of participants. However, there is a blacklist of people that I don't want the enumerators to register. So what I want to do is that I want to add all those names in a lookup table and upload it to the form and it any of those names were entered I want a msg to appear for the enumerators to notify them that you can't register this person. Can anyone help please.

Thanks in advance

1 Like
2

Hi Farah

These are the steps that you would take:

  1. Create a Lookup Table with fields "firstname", "lastname". Let's assume you name it "block_registration"

  1. In your form, let's assume your questions IDs called "firstname" and "lastname", you could add a label question type after those two questions. The text of the label would be the message you want to display to the user in the case that you want to block this registration.

  2. For the label question you would add the following expression to both the Validation Condition:

    count(instance('block_registration')/block_registration_list/block_registration[firstname = lower-case(/data/firstname)][lastname = lower-case(/data/lastname)]) = 0

    This expression will search the lookup table for entries with matching first and last name. If any are found the 'count' will be > 0 and so the validation will fail.

  3. In order to only show the label question when the validation fails you can add the reverse expression to the Display Condition of the question so that the question is only displayed when there are no matches:

    count(instance('block_registration')/block_registration_list/block_registration[firstname = lower-case(/data/firstname)][lastname = lower-case(/data/lastname)]) > 0

Update: The data in the lookup table should be stored in lower case and use the lower-case function on the user's input to do case insensitive matches.

1 Like