Prevent duplicate in details tables (with "part of" option)

Hello,
I have “Order” and “OrderDetails” tables, link with OrderID fields, and option “Is a part of” selected. “OrderDetails” has a field “Serial”, which should not be duplicated. However, I found out that because of the “Part Of” option, when creating new records for “OrderDetails”, it will not be saved until the “Order” table is saved, and I don’t know how to prevent the duplication in this situation.
Can anybody help showing me what to do to achieve this?
Thanks.
Anh Han

1 Like

Hi,
This is not sequential number, this is product serial number, and it is unique by ProductID. What I’m trying to achieve is to prevent user from wrongly enter serial number twice.
Thanks.
Anh Han

1 Like

Maybe a valid_if false=In([_this], [virtual column in child table listing serials already added from parent row]) or just suggest the values in this column so you would know as you’re typing.

I believe a virtual column would allow you to skip waiting for table to be saved as they change as I enter data in a form, but I’m not sure of this in this particular case.

2 Likes

Have your tried an expression something like below in the Vlaid_if of the [Product Serial Number] column in the Order Details table?

I tried it in the sample app with “Is Part of Setting” between Orders and Order details and duplicates are prevented with the suggested expression.

Please refer the last section of the article below.

5 Likes

After reading this:

I figure your best bet is restructuring away from ispartof. You could probably have the deleting related records functionality when parent is deleted recreated with automation. Unless that feature is crucial I’m not sure you would even need ispartof.

1 Like

I’d expect you can query the other IsAPartOf child rows being added:

ISNOTBLANK(
  SELECT(
    [Related OrderDetails][_ROWNUMBER],
    AND(
      ([Serial] = [_THISROW].[Serial]),
      ([_ROWNUMBER} <> [_THISROW].[_ROWNUMBER])
    )
  )
)
2 Likes

I check the suggested expression, review my app and found out that “IsPartOf” is not the problem, actually you can prevent duplication with ValidIf expression: NOT(IN([_THIS], SELECT(OrderDetails[Serial], [OrderDetailsID] <> [_ThisRow].[OrderDetailsID])))

Thank you all, I really appreciate your advice.
Anh Han

2 Likes