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

AppSheet AppSheet Q&A
,
1

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

2

Serial Numbers, If You Must Tips & Tricks ?

Danger Ahead! In general, sequential numeric identifiers (i.e., serial numbers) are risky in AppSheet: if two users happen to add rows at the same time, both rows could be assigned the same serial number, which could lead to confusion (at the least) or data loss (at the worst). For this reason, serial numbers are strongly discouraged! Basic Serial Numbers One way to implement serial numbers is with a normal (not virtual) column named (e.g.) Serial of type Number and an Initial value expression …

1 Like
3

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
4

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
5

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

3X_c_b_cb9006e05927186f5f6a49c39f8024e20f477f2d.png
3X_c_b_cb9006e05927186f5f6a49c39f8024e20f477f2d.png776×157 3.13 KB

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
6

After reading this:

[How to save a new entry ? Before the end of the Form](https://community.appsheet.com/t/how-to-save-a-new-entry-before-the-end-of-the-form/24453) Questions

Hi everybody, I am moving little by little, step by step on the App. I was wondering how can I save an entry in a Child table, in both the child Table and the Parent Table. You will see with the screenshots!! Thanks in advance for your help ! [How to save] [Howtosave] [How to save3]

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
7

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
8

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