Auto adjust quantity if similar item added

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
User avatar
zibrahim
Veteran Member
Posts: 137
Joined: 2020-01-28 18:30
Location: Malaysia

Auto adjust quantity if similar item added

Post by zibrahim » 2020-07-05 12:34

Hi there,
I am referring to invoicing or ordering system where you select some items (in child table) to create your invoice or order contents.
Let say user select Product A with Quantity X and save it. Again, if user select the Same Product A, it will create another record in the child table.
Is there any way that we can make the application smart enough to just adjust the Quantity without creating a new record for the same Product.

I hope someone can help me with this.
Thanking you guys in advance for having your attention and sharing your wisdom.
Stay safe always.

Zala.
Zala.
Appgini 24.10.1579, MacOS 14.3.1 Windows 11 on Parallels.

pfrumkin
Veteran Member
Posts: 157
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico USA

Re: Auto adjust quantity if similar item added

Post by pfrumkin » 2020-07-05 18:36

Hi Zala,

I assume you are presenting a list of products from which to select, to add to the order. I am thinking you might want to step back and make the order a list of line items, one product per line item . When updating the order to add or modify a line item (product), you select from the list of existing line items. If the product is already in the order, meaning that there is a line item for this order, then you adjust the quantity. If there is not a line item for this new product in the order, then you would create a new line item. For the line item dropdown (existing line items in the order), make it a lookup. AG presents options to Edit or Add for such a lookup.

So this is predicated on a table of orders, and a table of line items, and a table of products. Each line item links to a unique order, but an order can contain many line items. Each line items links to a product, but a product could link to many line items.

I hope that helps, sorry if I was way off or just confusing. Good luck.

~Paul

User avatar
zibrahim
Veteran Member
Posts: 137
Joined: 2020-01-28 18:30
Location: Malaysia

Re: Auto adjust quantity if similar item added

Post by zibrahim » 2020-07-06 01:27

Hi Paul,
Thanks for the reply. I understand your explanation and that is what I have done so far.
The problem that I am facing now is when I have a lot of records in line item table (which are linked to the parent's table), say 50 records, it is difficult to search which items I have added or not yet. Rather than spending time searching for the line item and updating the quantity, I believe that it is faster to just enter the product and the system will automatically search and add as new if it has not been added and add quantity if the product is already exist.
What do you think....

Zala.
Zala.
Appgini 24.10.1579, MacOS 14.3.1 Windows 11 on Parallels.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Auto adjust quantity if similar item added

Post by jsetzer » 2020-07-06 06:14

chrome_rqg1iyuGtf.png
chrome_rqg1iyuGtf.png (4.04 KiB) Viewed 2129 times

If I got you right, you'd like to put the sum of all quantities of OrderItems, having the same product_id, into one OrderItem.

I am afraid you cannot force users to edit an existing OrderItem instead of inserting a new OrderItem with identical product_id. But after insert you can merge similar OrderItems (by product_id) into one by code.

There are different approaches, I am descibing one easy-to-understand (hopefully) approach here.

DRAFT
  1. create a function orderitems_merge($orderitem_id) { /* ... */ } and in hook orderitems_after_insert and orderitems_after_update call orderitems_merge($selectedID).
  2. In that functon
    1. get $orderitem from database by $orderitem_id
    2. $order_id = $orderitem["order_id"]
    3. select all OrderItems by $order_id ordered by id (or creation_datetime, if there is such a column)
    4. foreach $orderitem
      1. check, if an OrderItem having $orderitem["id"] still exists in database. If not, skip this one.
      2. get sum(quantity) where product_id = $orderitem["product_id"] and order_id = $orderitem["order_id"]
      3. update quantity of $orderitem in database
      4. after update, delete other orderitems
        (having the same product_id and order_id but different id)
Notes
  • after deleting (see 2.4.4) those deleted OrderItems are still in the array you have fetched in line 2.3. If the array-item $orderitem does not exist in DB any longer: skip it. That's the reason I have inserted 2.4.1.
  • In case you have a position field (like orderitems.position) you will have to re-index the positions after update, insert or delete for keeping the sequence.
  • for database integrity, it would be best to wrap those database commands into a database transaction
Last edited by jsetzer on 2020-07-06 06:23, edited 3 times in total.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Auto adjust quantity if similar item added

Post by jsetzer » 2020-07-06 06:17

Maybe this one is even easier.

(2) different approach
  1. Select different product_id from OrderItems of the same order.
  2. foreach product_id
    1. update the quantity of the first orderitem for that product and order
    2. then delete all other orderitems for that product and order
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
zibrahim
Veteran Member
Posts: 137
Joined: 2020-01-28 18:30
Location: Malaysia

Re: Auto adjust quantity if similar item added

Post by zibrahim » 2020-07-06 14:54

Hi Jan,
Thank you for sharing the ideas on how to do this. I will try those suggestions in the future as I just started learning coding recently and have no ability yet now.
Again, many thanks to you and Paul as well.

Zala.
Zala.
Appgini 24.10.1579, MacOS 14.3.1 Windows 11 on Parallels.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Auto adjust quantity if similar item added

Post by pbottcher » 2020-07-06 18:37

Hi,

just to add my 5c.
Why dont you just check in the before_insert function if you have already a record with the corresponding line-item.
If one is found, you add the exising value to the newly added one, remove the first record and let handle the insert the rest. If no record exists, just let it insert it.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
zibrahim
Veteran Member
Posts: 137
Joined: 2020-01-28 18:30
Location: Malaysia

Re: Auto adjust quantity if similar item added

Post by zibrahim » 2020-07-08 04:12

Thanks pböttcher for the 5c. Good idea as well.
Zala.
Appgini 24.10.1579, MacOS 14.3.1 Windows 11 on Parallels.

Post Reply