Prevent duplicate line item. Increment Qty instead

[Greg_L]

Here’s my scenario:

I have three tables: Products, Orders, and Order Details

Now consider this. I’m building out an order, adding line items (order details) to an order. For each line item, I select a Product and quantity and when I click Save the line item gets added to the order.

What I want is to prevent the addition of new order details records where that product was already added. So, if I already added an order detail with product “Apples”, then the next time I try to add another order detail record with the same product I’ve already added (Apples), I want the quantity adjusted/incremented on that record rather than adding the new order detail record.

I would love help with this. I have driven myself mad messing with different combinations of actions and workflows. Thank you!!

[LeventK]

@Greg_L
Are you using the reference between Orders and Order Details with isPartOf property set to ON?

[Greg_L]

Yes.

[LeventK]

@Greg_L
Gotcha. As the child records are not saved to the sheet yet till you save the parent record, I believe there are not too many options for what you are trying to accomplish. Haven’t tested though, provided you are OK for one more additional step for saving the child record, then may be it’s possible.

[Greg_L]

Thank you. If you’ve got an idea in mind, I’m definitely interested to hear it. Even if it’s an extra step.

[Bahbus]

I would imagine you could use a workflow after everything is saved to check for duplicates and basically merge them. Otherwise, you should be able to use Valid_Ifs to prevent duplicates in the first place.

[Steve]

If the user chooses the item (e.g., apple) before arriving at the form (e.g., from a deck, gallery, or table), you can accomplish your goal with an action pretty easily, I’d think. The row-selected action would determine whether the item already occurs in the order. If so, the user is directed to the existing row; otherwise, to a new row.

If the user is to choose the item from within the form, and the user adds an item that already occurs in the order, you could use a row-saved action for the row to merge the new row with the existing one then delete the new row. Also pretty easy, I’d think.

[LeventK]

@Steve
I have tried various ways but with the use of isPartOf option of the ref column, the FormSaved action of the child form does not respond to any action as the parent form is not saved yet. Could you please elaborate the method you have proposed to “… merge the new row with the existing one then delete the new row…”?

[Steve]

Ah, yes. If using IsPartOf, my suggestion wouldn’t work for exactly the reason you mention. I was imagining adding line items outside of the parent form. Using IsPartOf from within the parent form, the best I can imagine is a row-saved action that tries to roll-up duplicates.That wouldn’t be as simple.

[LeventK]

@Steve I have tried various methods but it’s not working as the data is on the cache mem and not yet saved to the gSheet. Rather than using the isPartOf, using the action button to call the OrderDetails_Form and then using some tricky Form Saved actions with this form might help I believe…But no way with the isPartOf as far as I have tested

[Steve]

I have to defer to your experience here, @LeventK. My experience with IsPartOf is minimal.