Solved: Duplicate entries problem

viv_2 · 11-04-2022 11:27 PM

Hi guys, I'm new in using appsheet.

I will try to explain my situation as best I can…

column 1 [ Name ] column 2 [ system]

what I want to do is I can use the same data of the col name again but not the same data of the system. Example

[Name] [ system ]
marrie iphone

marrie android

I want to use the same name again but I want to avoid duplication of the system. I need help on what formula I need to input in data validity. Sorry for my explanation hoping for you assistance! Thanks

WillowMobileSys

The best way is to NOT ALLOW the duplication rather than checking for it after the fact. To do this, make the [System] an Enum and list ALL of the system choices.

Assuming the name of this table is "User Contact Methods", then in the Valid_If property of the [System] column use an expression like:

{"iPhone", "Android",...<<rest of all possible choices>>} - SELECT(User Contact Methods[System], [Name] = [_THISROW].[Name])

The "{...}" part is shorthand for LIST(...) - it just creates a list of the possible choices. The minus is List Subtraction . The SELECT() is choosing all current used system entries for the Name. The idea is to remove the already used items so they cannot be chosen again. A much better user experience!

View solution in original post

WillowMobileSys

The best way is to NOT ALLOW the duplication rather than checking for it after the fact. To do this, make the [System] an Enum and list ALL of the system choices.

Assuming the name of this table is "User Contact Methods", then in the Valid_If property of the [System] column use an expression like:

{"iPhone", "Android",...<<rest of all possible choices>>} - SELECT(User Contact Methods[System], [Name] = [_THISROW].[Name])

The "{...}" part is shorthand for LIST(...) - it just creates a list of the possible choices. The minus is List Subtraction . The SELECT() is choosing all current used system entries for the Name. The idea is to remove the already used items so they cannot be chosen again. A much better user experience!

viv_2

I'm using a list of values table for the system column how can I use that in your suggested expression I tried to use IN function but it doesn't work. by the way this is my first expression used in the problem before I posted this problem.

NOT(AND(

IN([_THIS],SELECT(system[system],[_THISROW].[system]<>[system])),
IN([_THIS],SELECT(system[name],[_THISROW].[name]<>[name]))

))

Swoopy

Just try checking ✔️ the KEY? option at both columns (name and system) and then save. A new virtual column will be created automatically with KEY? checked.

This allows you to have duplicated names, but not name & system.

WillowMobileSys

Yes, that is an easy way to handle it but it is awful user experience. A user must attempt to enter the combination of values before being told they cannot.

Additionally, I do not recommend using any row data values as keys. These values can and do change. Imagine if a user named "Marrie", used as part of the key, was supposed to be "Marie Elena" and needs to be changed. Updating the rows in THIS table is not a big deal but what about the 10 other tables that referenced THIS table? That becomes a bigger challenge and there would be app issues until all references were fixed.

Sure, there are cases where it doesn't matter and won't be a problem but there are others that it will. It is best to not have to think about it and just build it without any concern - now or in the future.