Self-hosted
I have a 16 character primary data field like AA-BB-123-CC-12- etc and I need to validate that BB, 123 and CC are in fact valid codes I have set up in a reference table. Am I able to use a series of lookups in a formula to validate against the reference table codes where each code (like BB) has it’s own View. Thanks
Hey @mikemitch, just to check, do you already have Link to table fields connecting your records to the reference table? lookup() only works with linked records.
yes I do…the BB for example currently has a link as it is a separate datafield…but I want to remove the extra datafields and do all the parsing and validation in the one and only primary field as a formula.
I check about 5 separate datafields against the reference codes…so you fill them all in (and validate) and the primary datafield is a formula assembling the 5 datafields with hyphens…want to stop that and just fill in the primary and check within it so I can delete the 5 other datafields…AI said I could use link or lookup in a formula…can I ? Mike
@frederikdc could you please help with this request? 
I don’t think it is feasible to do all the validation in a single field since this would result in a huge formula that is not easy to maintain.
You can only work with a lookup field if every record in your table is linked to every record of the tables that need to check the values. For example: if the table that holds the possible values for BB contains 20 records, all of those records need to be linked to each record.
A possible workaround is - if the number of potential values are limited - creating a new Single Line Text field and set all the potential values separated with a space as the default value. In this scenario, you can create a formula field that checks if the value for BB appears in that field. The field you use to check BB will have the same value for all records
Be aware that the above is just a workaround. The best solution would be setting up an automation that is triggered on every new record. This automation can get all the values from the corresponding tables and check if the value in the primary field is correct or not.