Prevent Duplicate Records before Saving Form

[e4omir]

Hello,

I want to prevent duplicate records when the form is filled, but the tricky part is that the entry should be compared with the part of the existing one.

Example: if the value is already part of the table the following formula works perfectly: Not(In([_THIS],
SELECT(METERING_POINTS[metering_point_id], [metering_point_id]=[_ThisRow].[metering_point_id])))

The problem is that I want to compare only the right 7 digits from the existing one and if the new value is equal to them, then it shouldn't allow input (to be considered as duplicate). There are two ways of those values: first is with 30 digits, and the second is with 7 (the last 7 from the long one). So I want to compare only the RIGHT 7 digits. 

Example: Value "GA5512345678901234567890121122334" is part of the table and I want to enter "1122334". This should be considered as a duplicate due to the RIGHT 7 digits.

I tried with no success: Not(In([_THIS],
SELECT(METERING_POINTS[metering_point_id], right([metering_point_id],7)=[_ThisRow].[metering_point_id])))  

Any help will be highly appreciated.

[Suvrutt_Gurjar]

If understanding of your requirement is correct, please crate a VC called say [Short_Metering_Point_Id] with an expression something like 

RIGHT([metering_point_id],7)

Then you valid_if expression to avoid duplicates in the [Short_Metering_Point_Id]  column can be

ISBLANK(

  FILTER(

  "METERING_POINTS",

    ([_THIS] = [Short_Metering_Point_Id])

  )

  - LIST([_THISROW])

)

The general expression to avoid duplicates is given in the article below at the end

List expressions - AppSheet Help

Edit: If you want to use this expression in the [metering_point_id] column itself, then the expression can be 

ISBLANK(

  FILTER(

  "METERING_POINTS",

    ([_THISROW].[metering_point_id] = [metering_point_id])

  )

  - LIST([_THISROW])

)

[e4omir]

Thanks for the quick response.

With a new VC I was able to cover 2 scenarios:

1. When I try to save a new [metering_point_id] which is already into the table - ID:GA0890990001234567 is there, and I cannot enter the same [metering_point_id] - works perfectly

2. When I try to save a new [metering_point_id] which is the last 7 digits from the existing. Example: [metering_point_id] GA0890990001234567 is there, and I cannot enter 1234567, because the new VC is helping to check that there is the same one (based on the Right([metering_point_id])) - works perfectly

3. Now I am struggling to check - Example: there is an existing [metering_point_id] with number 7654321, and I am trying to enter GA0550550007654321. How to check if the last 7 digits from the NEW ID (still not into the table) are equals to the existing [metering_point_id]?

I hope I was able to explain it.

BR

[Suvrutt_Gurjar]

Thank you. Sorry, I missed the difference between case 2 and 3. They appear same to me. Could you elaborate?

[e4omir]

Sorry 🙂

Case 2 is: If there is an existing [metering_point_id]  = GA0890990001234567 and when I try to enter short version [metering_point_id] = 1234567 it will be compared with the VC named [short_metering_point_id] and will not be able to be entered. Because the VC [short_metering_point_id] = Right ( [metering_point_id] , 7) = 1234567. And here it works, because the [short_metering_point_id] = 1234567 is the same as the ID I am trying to enter

Case 3 is almost the same, but a short version of [metering_point_id] is added vs long to be checked before adding it: The Example is, if there is an existing [metering_point_id] = 1234567 and when I try to enter long [metering_point_id] = GA0890990001234567, I want this new long ID GA0890990001234567 to be considered for duplicate value, because the last 7 digits are the same as the existing [metering_point_id] (1234567).

Hope I explain it now. Thanks in advance

[Suvrutt_Gurjar]

Got it. Thank you. Could you mention  your existing expression and which column's valid_if you are using it?

[e4omir]

I am using the  [metering_point_id] valid_if. The new VC [short_metering_point_id] is hidden and it is only for the comparison of case 2. The whole formula which works is below: (the first condition from this AND is for case 1, the second is for case 2)

AND(
Not(In([_THIS],
SELECT(METERING_POINTS[metering_point_id], [metering_point_id]=[_ThisRow].[metering_point_id]))),
Not(In([_THIS],
SELECT(METERING_POINTS[short_metering_point_id], [short_metering_point_id]=[_ThisRow].[metering_point_id]))))

[Suvrutt_Gurjar · Accepted solution]

Your ANDING() of two formulas looks good for me, except that it may not work for the existing rows. 

Please try an expression something like below

AND(

ISBLANK(

  FILTER(

  "METERING_POINTS",

    ([_THIS] = [metering_point_id])

  )

  - LIST([_THISROW])

),

ISBLANK(

  FILTER(

  "METERING_POINTS",

    ([_THISROW].[Short_Metering_Point_Id] = [Short_Metering_Point_Id])

  )

  - LIST([_THISROW])

)

)

[e4omir]

I am speechless :), it works perfectly. I nested the last formula you wrote with an IF expression along with the couple of other conditions and everything works exactly as it should.

@Suvrutt_Gurjar  thank you very much for your time and help. Much appreciated. 

[Suvrutt_Gurjar]

You are welcome and thank you for the update.