Expression to avoid repeating/replication of data

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

Try something like as your Valid_IF:

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

Simon@1minManager.com

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.

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?

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.

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

TableName[Serial number/Barcode device 8]))

3 Likes

It worked!! Thank you.

1 Like

You’re welcome

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!! :pray: :blush:

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 :pray: :blush:

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.

1 Like

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!!

1 Like

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 :blush:

Try this instead:

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

This is an absolute Genius!! God Bless You!! :pray: :blush:

Thanks to everyone for your important inputs!!

1 Like