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
Solved! Go to Solution.
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?โ
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] ) ) )
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.
@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
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
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
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?โ
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] ) ) )
โ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?
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.
I have to admit I wasnโt believing it until this part:
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
Good, Iโm glad. PhewโฆI was struggling a bit on that explanation.
No, I was slow to comprehend.
My Bad.
User | Count |
---|---|
44 | |
35 | |
25 | |
24 | |
15 |