Is there a way to create a column with unique...

(Diogenes ASBL - VZW) #1

Is there a way to create a column with unique values without being the key column?

And what expression would I use for that?

And without using the initial value Uniqueid () (I want to determine the value of the column myself)

Thx Bram

(Aleksi Alkio) #2

Check the below article and the section “Preventing Duplicate Field Values”. - List Expressions and Aggregates List Expressions and Aggregates

(Fernando López) #3

Not exactly, you can use an expresion to control that there is no repeated values, but it can fail if two or more users simultaneously create a new row. The formula in Valid_if property of your unique_value will be:

COUNT(SELECT(YourTable[ID], AND([Unique_Value] = [_THISROW].[Unique_Value], [ID] <> [_THISROW].[ID]) )) = 0

(Diogenes ASBL - VZW) #4

Thank you very much @Aleksi_Alkio

Is there also a way to make a suggested value unique?

I have a form where a user can enter a file number: this number is automaticly generated in the

suggested_values==LIST(MAX(List Personnes[Dossier ID])

The problem now is that when two users are entering on the same time this form with this automaticly generated number, this number will be the same for both files.

You have any idea?


(Aleksi Alkio) #5

If you try to read the value from the list and as you mentioned, the biggest one, there is no solution how you could avoid that because two users are using the app at the same time.

(Diogenes ASBL - VZW) #6

Is there a way to make an action that saves the form when the user fills in a file number?

(Aleksi Alkio) #7

What do you mean by that? I couldn’t follow, sorry.

(Diogenes ASBL - VZW) #8

No problem … I was not clear.

The values of the form are only saved in the backend googlesheet from the moment you click on the save button at the end of the form, right? So my suggested value =LIST(MAX(List Personnes[Dossier ID]) is going to suggest a new [Dossier ID] number to one user from the moment the other user saves his form. This means that when two users are on the same time completing the form (with multiple other columns to enter), the [Dossier ID] number will be the same.

I thought a solution could be that after entering data in the [Dossier ID] column, I oblige the form to save to the backend googlesheet with an action.

This with the purpose that my suggested value adepts immediately to te Max+1 number from the moment you have set the value in the [Dossier ID] column.

So I wunder if there is a way to oblige the form to save from the moment the value is set in the [Dossier ID] column.

(Aleksi Alkio) #9

I’m afraid that won’t work either… it doesn’t matter what the process is, you can still have duplicate values.

(Diogenes ASBL - VZW) #10

But it would reduce de time where

more than one user sets the value of this column (since my form is quit long) and so it would reduce the chanse on duplicate values

(Aleksi Alkio) #11

Actually it won’t help. Let’s say two users sync the app now, and then they start to fill the form. The other user fills the form 10 minutes and the other one 15 minutes. Because the sync time was the same, they both have the same data in their device. When they saved the record, they both will have the same value. If the third person has synced the app while they were filling the form, that person will have the same value again.