Loop to get total of quantity in repeating section

[dfricke10]

This is a list control
I'm trying to get the total of the QTY field with the SKU field.
So, my end result would ideally be:

SKU: 300000, QTY 2
SKU: 300001, QTY 1

And then I'd update the SQL database accordingly.

I haven't mastered the loops. Would this be best in xbasic or javascript?

2024-04-11_10-29-01.png

[madtowng]

are you against getting the total from sql?

[dfricke10]

Nope. Right now I do a SQL UPDATE, but it won't update the field twice if it's in the list twice. It will update the Bag and Snickers once, but I can't get it to decrease the amount for snickers 2.

[dfricke10]

Here's what i have now. I've changed a few names for security.

sql = <<%sql%
UPDATE Inventory
SET quantity = quantity - 1
WHERE itemid IN(SELECT Items.itemid
FROM Sales
LEFT OUTER JOIN (Items
LEFT OUTER JOIN Inventory
ON Items.itemid = Inventory.itemid )
ON Sales.SALEID = Items.SALEID AND Inventory.quantity >= 1
WHERE Sales.SALEID = :SALEID);
%sql%

[dfricke10]

Basically, the initial script takes that list control, writes it to an 'Items' table, then this SQL script compares it to the Inventory table, and updates the Quantity field.

[madtowng]

Right now it's clear as mud to me.
It's obvious your goal is to update the Inventory table.
I'm not sure how you're using the Sales and Items tables.

My guess is that you're putting sales for a specific time period into the sales table, and then you want to reduce the total (item.id) in inventory by the sum of (sales.item.id) for a specific time period.
(looks like my time period is covered by your sales.saleid).

If I got that part right, then I can work on what should be the easy part.
The sql statement doesn't look right to me (could be my ignorance).

One more thing that will help is the flavor of sql you use.

[dfricke10]

Yeah, ADHD brain tracks all over the place.

Point of sale system.

Using just SQL. Not MySQL.

I have a Sales table, Items table and Inventory table.

Sales table is the base sale.
Items is the sale items that are sold from that Sale.
Inventory is the store's inventory table showing product details and quantity in stock.

The list is the list of items being sold. This list is saved to the Items table. Both have a separate field called "SALEID" that links the two together. It's not the primary key for either. It's a random string.

The goal is to update the Inventory table with the quantity of the Inventory item being sold. Right now that script works fine, but only if they buy one of each item. If they buy 2 of one item, it only takes 1 of the Quantity field in the Inventory table.

All this is done right away when the sale is completed. The inventory is updated right away.

[Russell Craycraft]

Is this any better?

sql = <<%sql%
UPDATE Inventory
SET quantity = quantity - 1
WHERE itemid IN (
SELECT i.itemid
FROM Items i
JOIN Sales s ON i.SALEID = s.SALEID
JOIN Inventory inv ON i.itemid = inv.itemid
WHERE s.SALEID = :SALEID
AND inv.quantity >= 1 );
%sql%​

[madtowng]

It looks like the real problem is in the subquery.
I don't have the structure of your tables, so I am making some educated guesses based on the image below.
Sample data and table structure are the difference between guessing and testing my response.
I believe getting the query below to work as expected will make the update query work as expected.

select sum(coalesce( i.qty,0))
from items i
left join sales s on s.saleid = i.saleid
where i.sku = s.sku and s.saleid = :saleid
group by i.saleid,i.sku

image.png

[dfricke10]

Both of these got me in the right direction and solved my problem. Plus, found a solution to another bug while I was there. Thanks much for the time!!!

[madtowng]

details… show the final code, and ideally explain the hidden bug.

[dfricke10]

I realized I don't actually need the Sales table. The Items table had the SALEID In it in order to do the linking. And I know you guys use the table aliases, but I'm not used to those yet. I'll modify this now that I know it works.

sql = <<%sql%
        UPDATE Inventory
        SET quantity = quantity - (
        SELECT sum(coalesce( Quantity,0))
                FROM Items
            WHERE Items.SALEID = :SALEID and Inventory.itemid = Items.itemid
            GROUP BY Items.saleid, Items.itemid)
        WHERE itemid IN (
        SELECT Items.itemid
            FROM Items
                JOIN Inventory ON Items.itemid = Inventory.itemid
        WHERE Items.SALEID = :SALEID
        AND Inventory.quantity >= 1 );
        %sql%​

The hidden bug was unrelated to this. It was when the list of products, which is not saved to anything until the sale is complete, saves to the database table. The problem was very rarely the fields would duplicate but not duplicate in the sale total. The order of things is 1. Enter sale details with sale total, etc. 2. Enter items (save list control as a After Ajax Callback when creating the sale), enters the payments, then sets the inventory levels.

The issue was, if the internet connection was slow, the javascript SaveListEdits would timeout and would start again. At least that's what I think happened. I increased the timeout and the symptom went away.

I was able to replicate it repeatedly in Live Preview so it was nice to see a supposed fix.

[madtowng]

Glad to see ya got it figured out.
If something isn't quite right, check back.
I can work with sql, but I am far more fluent in mysql (ie: I really like to test my sql express code before passing it an answer).
As for the aliases, I use them to make the columns easier to pick out as I'm reading the code.