Hi,
I have the following use case with three entities as shown below
Country → Cities → Localities
Country is a selection from the list (maintained in excel)
Cities are user-generated meaning the users would add cities
Localities are user-generated meaning the users would add localities.
How do I maintain unique entries for cities/ localities?
Appreciate help!
As long as cities and localities are user generated, it will be difficult to keep them unique. But what you can do as a workaround is to add a parameter tab in your sheet, if not already done, where using a unique formula you will collect all cities typed by your users, they should be unique if they type them correctly, respecting some uppercase / lowercase rules.
Then if the UI where your users have to type the city, you add a choice component pointing on the unique values column of the parameter tab. Ask your users to search for the city in the list, and if it does not exist, allow them to add a new one (you can add a checkbox component "add a new city which, when checked, will make appearing a text entry component).
But be sure that you will have duplicate and you will have to maintain your list manually regularly.
Unless someone else has an idea or solution?
I had a similar situation where I needed to maintain unique locality data created by users. The user-generated entries contained duplicates (e.g. City of Springfield) that were unique values (Springfield, IL and Springfield, MA). However, the inline list of City locality data (via a relation column) would display information from both cities of the same name. This applied to a manager/admin level view which has access to multiple projects (or Cities) that users might call the same name. A non-issue from a user level since access is limited to the row owner.
While not truly unique, I created a template column to (e.g. x=City, y=State) and a Locality relation column to constrain localities to the unique city/state combinations. That way I could prevent user generated data from being related to the wrong City. Is there some combination of columns that could be combined to create a unique helper column (e.g. Locality, City, Country)?
I had a similar issue where I just use the submission column and the user name column and combined them followed by the count of that column. So even if the same user entered springfield twice i had 2 unique springfields.
Example (Column A is username, Column B is City, Column C is State, Column D is =(A2&B2&C2&COUNTIF($C$2:C3) ) you could also use an arrayformula but you would have to modify the count method.