Allowing Free Text in Ref Column using ValidIf

[goodflow]

Dear Experts,

I am having a State/City/Area setup in Address table which I am referencing in another table using ValidIf and it works fine. User selects State from the dropdown and then the City field shows up, selects City and the Area field shows up. All of the fields provide a dropdown for the user with the values from Address table.

My requirement is rather than showing the values in dropdown in the State field, user is required to enter the state name and with that the City dropdown comes and then the Area dropdown.

Is there a way, free text or numeric id can be allowed in a Ref field (Enum) using ValidIf with the referenced values from State column in this case (from Address table) being not visible to user?

Thanks!

[Marc_Dillon]

I think change [State] to a Text-type column. If you’re utilizing Appsheet’s implicit dependent dropdown feature, then you’ll just have to change valid_if for City a little bit:

valid_If =

SELECT( Address[City] , [State] = [_THISROW].[State] )

[Marc_Dillon · Accepted solution]

The difficulty comes if you want to keep the data validation (force user to type a state name that actually exists in the table). I just tested this as I was curious. I figured specifying a more complex expression for valid_if that does about the same thing, but in a different way, would make the dropdown disappear, but still give validation. I was correct, but I’m not sure where exactly the line is drawn.

This still shows a drop-down of values

IN( [_THISROW].[state] , address[state] )

This does not, but does provide validation

COUNT( SELECT( address[state] , [state] = [_THISROW].[state] ) ) = 1

[goodflow]

Thanks @Marc_Dillon for your prompt suggestions. Let me run them through my scenarios and I will report back the results.

Thanks!

[goodflow]

Thanks @Marc_Dillon. The suggestions are working great. I did only one change for valdation, rather than ‘=1’, I made it ‘>=1’ as there are many entries of state name in the table, >=1 correctly validates the true/false condition but I could only do that because of the direction you set me in. Absolutely grateful.

This is the updated condition I used in ValidIf for State: COUNT( SELECT( Address[State] , [State] = [_THISROW].[State] ) ) >= 1