Expression to avoid repeating/replication of data

AppSheet AppSheet Q&A
1

Hi

I am. Relating an app for a company. The app can capture up to 8 devices per user. In each device, I have a column called "Serial number/Barcode ", this is the column used to identify that device, and only that device because serial number /Barcode are specific to one device.

when I enter a serial number /Barcode that has already been entered I want the app to reject it, and shows an error message, to try to achieve this, I used the Valid_if, with the expression [_THIS] <>[Serial Number /Barcode device 2], and I repeated this expression for 6 other devices, I added all these expressions in the serial number /Barcode of the first device in the Valid_if,
. But the expre is not working

2

Try something like as your Valid_IF:

ISBLANK(ANY(Select(YourTable[Barcdode],[Barcode]=[_ThisRow].[Barcode])))

Simon@1minManager.com

3

Try this as the Valid If for the first device:

NOT(
  IN(
    [_THIS],
    LIST(
      [Serial number/Barcode device 2],
      [Serial number/Barcode device 3],
      [Serial number/Barcode device 4],
      [Serial number/Barcode device 5],
      [Serial number/Barcode device 6],
      [Serial number/Barcode device 7],
      [Serial number/Barcode device 8],
    )
  )
)

Repeat for each device’s column with corresponding device number adjustments.

4

Hi, I tried this one “NOT(
IN(
[_THIS],
LIST(
[Serial number/Barcode device 2],
[Serial number/Barcode device 3],
…
)”

as instructed but it does not work as expected, it does not search if the serial number/barcode has already been entered in the system before, it only works if i repeat the serial number /barcode on the same form.

I am using the scan function to enter Serial numbers/ Barcodes ,is this maybe contributing to the failure?

5

it does not work as expected, when i enter a repeat serial barcode / barcode it does not search if the serial number/barcode has already been entered in the system before, it only works if i repeat the serial number /barcode on the same form of the same device.

6

Try something like…
NOT(IN([_THIS],
TableName[Serial number/Barcode device 1]+
TableName[Serial number/Barcode device 2]+
…
TableName[Serial number/Barcode device 8]))

7

It worked!! Thank you.

8

You’re welcome

9

I have a Stock Item Table where I enter Product Barcode (Bc) and Product Name. Once I enter a Barcode and Save it then the next time if by mistake I am entering the Same Barcode it should show error “You are making a duplicate entry. This Barcode already exists in the Stock Item Table”.

I tried with this formula in the Valid If of the Barcode (Bc) Column : NOT ( IN ( [_THIS], StockItem [Bc] ) )

It detects the duplicate Barcode and does not let me add a new entry but if I want to edit an existing entry also it does not let me edit saying that “You are making a duplicate entry. This Barcode already exists in the Stock Item Table”.

Please let me know how to do this.

Thank you!! God Bless!!

10

Edit instead of insert for existing key Tips & Tricks ?

Neat idea! Here’s how I imagine it working (untested!): Create a simple table (e.g., Scan) with one column (e.g., Barcode) to receive the scanned bar code. The Barcode column should (necessarily) be both key and label. Create a (or use the AppSheet-provided) form view (e.g., Scan_Form) for the Scan table. Enable Auto save and Auto re-open. Optionally make Scan_Form the app’s starting view by selecting it in UX > Options > Starting view. Create an action (e.g., Edit_Target) to send th…

11

Thanks for the reply!

This seems pretty difficult in my case as I have other Columns also that I want to check for duplicae entry in the same form, is there a simpler way to do this?

May be can you give me the expression that will work in my scenario?

Appreciate your help!

Thanx

12

Total_Solutions:

I tried with this formula in the Valid If of the Barcode (Bc) Column : NOT ( IN ( [_THIS], StockItem [Bc] ) )> > It detects the duplicate Barcode and does not let me add a new entry but if I want to edit an existing entry also it does not let me edit saying that “You are making a duplicate entry

You need to check against all of the records in the Table except the current record. Instead of StockItem[Bc], try using SELECT( StockItem[Bc] , ...) with a condition that will exclude the current record.

13

Marc_Dillon:

Instead of StockItem[Bc] , try using SELECT( StockItem[Bc] , ...) with a condition that will exclude the current record.

Idea Sounds Good!! Logically it makes perfect sense!!

Can you please gudie me what the expression should be and how to exclude the current record in that.

NOT ( IN ( [_THIS], SELECT(StockItem [Bc], ( [Bc] = [_THISROW].[Bc] ) ) ) )

I tried the above but gives error. I can’t figure out how to exclude the current record that we are editing.

Thank You!!

14

3X_c_1_c1c4b64c6c2cc3d00b33c13866ac0100a5acc66c.png
3X_c_1_c1c4b64c6c2cc3d00b33c13866ac0100a5acc66c.png725Ă—150 10 KB

15

I put the formula in Valid If, it allows edit but it also allows duplicate barcodes:
NOT ( IN ( [_THIS], SELECT(StockItem [Bc], ( [Bc] <> [_THISROW].[Bc] ) ) ) )

May be I am not putting the formula in the correct place. Please let me know.

Thanx

16

Try this instead:

NOT ( IN ( [_THIS], SELECT(StockItem [Bc], ( [_ROWNUMBER] <> [_THISROW].[_ROWNUMBER] ) ) ) )
17

This is an absolute Genius!! God Bless You!!

Thanks to everyone for your important inputs!!