Hi, I have a text field in note form, and other lookup view which contains about 100 entries with the first column contains data required in this field. I need to validate the data user entered in this field and make sure the data entered in this field is from one of that first column of lookup view. Can anybody show me how to do it? thanks a lot,
Subject: How to validate a text data user entered is from a column of a view?
Hi, Peter. The most ideal situation is the text field would be a Dialog list field and the formula for the choices for that field are based on an @DbColumn to the first column of the view with the 100 entries. If the field already exists as free form text and you don’t want to change it, you can put an input translation that does a lookup to the view to check that the value is valid. So your input validation might look like the following assuming that the entry view column of choices is the first sorted column in the view and the entry view is in the same database.
Temp:=@Dblookup(“Notes”:“Nocache”;“”;;;1);
@If(@IsError(Temp);@Failure(“not a valid value”);@Success)
You’ll notice the lookup will fail if the text field value is not in the 100 entry view. Just one last note, you could add @IsDocBeingSaved to the @If statement so the validation is only done when the document is saved. If you don’t want to add this validation to the field but just mark documents if they don’t have a valid field value, just create an agent to run on all selected docs with a similar formula above except remove the @Failure and @Success statements and set some field value on the document if the @DbLookup fails in the agent. You could then display that value in a view to determine what documents don’t have a valid field value.