Prevent linked reference field in Form from accepting new values that aren't in the reference table

[Burner]

So I have a table called “US states”, which other tables have linked fields to. One issue I encounter often is that when I have a form in a different table and add a field called “Select state”, which is linked to the “US States” table, I often have people who fill out the form start typing in the state and accidentally insert a new State (sometimes misspelled or sometimes just the abbreviation) thereby creating a new record in the linked “US States” table. Is there a way that a linked field in a form restrict creation of new records and only allow users to select from the list of linked records?

[ScottWorld · Accepted solution]

Forms will not allow creation of new records through a linked record field. Users are required to choose a pre-existing value only.

[Burner]

D-uh, I should have checked. Thanks, Scott. This implies that someone on the team is entering records directly in the table rather than using a form and hence is creating new linked records that way. Thanks a bunch once again.

[ScottWorld]

You’re welcome! You could also change permissions in the table so that nobody can create new records there.

[Burner]

Thanks, Scott. Now that I did not think of. To be honest, I’ve never really explored the “Edit field permissions” options, assuming that’s what you were referring to. I tried looking into this and it seems like it gets me halfway there.

I set all the fields in my “US States” table to allow nobody to edit them. I thought that this will prevent someone entering a new state record through a related table e.g. so no one can enter NYC when selecting the state in a different table when the “US States” table already has a record called NY.

What actually happens though is that when in a different related table, I select the state, and instead of selecting NY from the list of available linked records, decide to type in NYC – a record is still created in the “US States” table. Albeit has its primary field blank (unnamed), it still creates a lookup back to related table from where NYC was entered. It’d be great if a the “US States” table prevented new records from being created altogether, so that no other table that a field linked to US States, could create a new record in the US States table.

[ScottWorld]

I was actually referring to editing table permissions, where you can prevent people from creating new records.

I just tested this, and it works across all scenarios, including preventing the creation of records through linked fields.

[Burner]

Thanks, Scott. That’s another area I hadn’t explored until now. In fact I had to do a quick Google search to see how to set Table permissions and realized that it’s right next to where I constantly enter the table description – but somehow have never noticed this option. This will seem to do the trick and so I’ll enforce permissions to that no new linked records can be created in my States table.

Thanks so much, once again. Really appreciate it.