Empty Columns Filled With Last Column with Value

[Gert1]

Good day.

I have a Table with a column BARCODE.
In the Excel sheet only some of the BARCODE rows have values the rest are blank, to be captured in the App.
In the App all the empty BARCODE columns gets filled with the value from the previous non empty BARCODE column.
There are no “Initial Value” Formulas or any other formulas on this column except for a “Valid If” formula to prevent duplicates as follows : Not(In([_THIS], SELECT(PRODUCTS[BARCODE], [BARCODE] <> [_THISROW].[BARCODE]))), but this is not working. If I edit say a picture Column in the record with a empty BARCODE cell in the Sheet, the BARCODE column is filled in the edit form. When I save, the App allows the duplicate to be saved.

Empty cells in spreadsheet:

Filled values in App:

As seen from “Arenel Ginger Nuts” all BARCODE is filled with the same value. Applicable to both questions.
Two questions:
Why are the empty BARCODE columns filled with the previous value?
Why is my formula for duplicate entries not working?

Thanks
Gert

[WillowMobileSys]

Gert:

If I edit say a picture Column in the record with a empty BARCODE cell in the Sheet, the BARCODE column is filled in the edit form.

If understand correctly, you are saying that you Edit a row that initially has an empty Barcode value, and when you change the Image, the Barcode is automatically assigned in the Form (to one that already exists) AND is allowed to be saved. Do I have that correct?

Some expression IS assigning the value. On the Form, this can only be done in the Auto Compute section of the column definition. Double check that you have no expressions in the Initial Value and App Formula. Also, check there is nothing in the “Spreadsheet Formula” field.

[Gert1]

@WillowMobileSystems
No.
When I run the App all the empty columns are already filled.
When I edit the record

I just found the issue when I tried to confirm the edit procedure.
The Barcode Column is required and the initial value had the formula @(_FILL) in it.
I didn’t put it there.
I assume it is put in by AppSheet when the Column is required and there are empty values in the Sheet.
Never seen it before. I removed it and now it is working fine.

The issue with the formula for duplicate entries remains though.
If I add a Product with the same BARCODE it still allows it.

Gert

[Marc_Dillon]

Gert:

Not(In([_THIS], SELECT(PRODUCTS[BARCODE], [BARCODE] <> [_THISROW].[BARCODE])))

This expression isn’t going to prevent duplicates, because it’s removing the current value from the list of values to check against. Your SELECT condition should check against the key value, or the _RowNumber, not the barcode.

https://community.appsheet.com/search?q=prevent%20duplicates

[Gert1]

@Marc_Dillon

I disagree.
You want it to remove the current value from the list of values to check against, if not the check will never allow you to edit any other columns because it will always find the value of the BARCODE column in the list. If you check it against itself it will always be true.
I can’t make it a key because it has to be editable and as far as I know key columns are not editable.\

Gert

[Marc_Dillon · Accepted solution]

Gert:

You want it to remove the current value from the list of values to check against, if not the check will never allow you to edit any other columns because it will always find the value of the BARCODE column in the list.

Your logic is half way there, you’re just missing a small step. You don’t want to remove the current value from the list to check against, you want to remove the current record’s value.

Your [_THIS], and the [_THISROW].[BARCODE] within the SELECT, are the same value. Your expression is essentially asking “is X in this list of values that don’t include X?”

Gert:

I can’t make it a key because it has to be editable and as far as I know key columns are not editable.

You’re misunderstanding. I’m just referring to the condition in your SELECT, not the value that you’re editing.

Try this instead:

NOT( IN( [_THIS] , SELECT( ... , [_RowNumber] <> [_THISROW].[_RowNumber] ) ) )

[Gert1]

@Marc_Dillon

“is X in this list of values that don’t include X?”

I have a list [1,2,3,4,5] and my check value 3 is in the 3rd position, then
"is 3 in the list of values [1,2,3,4,5] is true

but if I exclude it from the list then
"is 3 in the list of values [1,2,4,5] is false.

What am I missing?

[Marc_Dillon]

You start with a list of [1,2,3,4,5].

You are editing the record whose value was previously 3.

Imagine that you attempt to change it to 5.

Your expression would allow that, because it would remove 5 from the list of values to check against.

It would be
“is 5 in the list [1,2,3,4]”

If you tried to change to 4, it’d be
“is 4 in the list [1,2,3,5]”

Because each time your expression is removing the value to check from the list of values to check against.

[WillowMobileSys]

Marc_Dillon:

Good, I’m glad. Phew…I was struggling a bit on that explanation.

I have to admit I wasn’t believing it until this part:

Marc_Dillon:

You start with a list of [1,2,3,4,5].

You are editing the record whose value was previously 3.

Imagine that you attempt to change it to 5.

Your expression would allow that, because it would remove 5 from the list of values to check against.

[Gert1]

Thank you! I understand

I changed
Not(In([_THIS], SELECT(PRODUCTS[BARCODE], [BARCODE] <> [_THISROW].[BARCODE])))
to
Not(In([_THIS], SELECT(PRODUCTS[BARCODE], [_RowNumber] <> [_THISROW].[_RowNumber])))

it now works

Gert

[Marc_Dillon]

Good, I’m glad. Phew…I was struggling a bit on that explanation.

[Gert1]

No, I was slow to comprehend.
My Bad.