Custom table field: validation on drop down list options

Irvin Hercules asked on July 15, 2020 12:54

I have a field in a custom table with a type of drop-down list with several options.

Is there a way for me to check the current table data as follows:

loop through all selected options
if current option was already selected/exists in current dataset
do not display the current option when creating a new entry to the dataset

Thank you

Recent Answers

Dmitry Bastron answered on July 15, 2020 18:12

Hi Irvin,

When you configure a field, you can add validation rules further down the form. If you select "General condition" you can use any macro method or write your custom macro to validate the input. I'm not sure though if I understood your question correctly, but if you can provide a data example of what are you trying to achieve this will definitely be easier to help you.

0 votesVote for this answer Mark as a Correct answer

Irvin Hercules answered on July 15, 2020 18:21

Hi Dmitry

This is for adding a new entry or row to a custom table. The default form for entry displays a list of US states by name... each state name is added to a list of options in the field editor.

So the goal is to exclude a specific state when that state name matches a value in the current custom table "StateName" column.

Thank you

0 votesVote for this answer Mark as a Correct answer

Dmitry Bastron answered on July 15, 2020 19:00

Ok, makes more sense now. Then instead of list of options, you can use macro or SQL Query for it. The Query could be like this (it doesn't have to be a single query, it could be a more complicated SQL script):

declare @states table (stateCode varchar(20), stateName varchar(20))

insert into @states values 
    ('Florida', 'Florida'),
    ('Texas', 'Texas'),
    ('Washington', 'Washington'),
    ('California', 'California')

select stateCode, stateName
from @states
where not exists (select *
                  from customtable_YourCustomTable
                  where customtable_YourCustomTable.States like '%' + stateCode + '%')

0 votesVote for this answer Mark as a Correct answer