Inventory change after sale not working

[Badger]

Hey Everyone,

I have been struggling with this for about a week now, I have three tables "Sales", "Sold Items" and "Inventory". When a sale is made a Item Sold entry is made through the Sales table (The sales table holds a ref row to the items Sold table filtered by the Receipt No), When the sale is completed the Paid yes/no selector is selected to yes and a bot is activated.

This is where I'm having trouble, the items sold have a quantity to how many are sold and is kept in the Sold Items table and a New Amount Available virtual row  calculates the new amount of stock available but I cant seem to change the amount in the Inventor table for this.

The bot calls an action on the sales table to execute an action on a set of rows and the Inventories Row ID is set to the referenced row and the Referenced action calls the action on the inventory that sets a value of some columns in this row, the "Available Stock" is the column selected and I have expressions in there to check the Receipt no and the Item code to try and identify the Item that the Available Stock needs to be modified on but its not working.

I would greatly appreciate any help please.

[lynchk21]

Can you provide a screen shot of your tables so we can see the column structure? This would make it easier to assist. Thanks!

[Badger]

I did post a reply but they are not approving it for some reason sorry.

[Badger]

@lynchk21 Sure, thanks for the quick reply

I hope this is enough info, let me know if there is anything else you need. I did try googling extensively on a solution but nothing comes up. I have it halfway working, but once a second sale is loaded the inventory is no longer updated.

[lynchk21]

Maybe try:

1. Update Sold Items[Receipt No] from text type to reference type with Sales as the reference table
2. Create a slice on the Sold Items table called "Paid Sold Items" with the expression [Receipt No].[Paid]=TRUE
3. Update the Sold Items[Item Code] from text type to a reference type with Inventory as the reference table
   1. This will auto generate a Ref_Rows column on the Inventory column called [Related Sold Items]
   2. Update (or create an additional column) the to REF_ROWS("Paid Sold Items", "Item Code")
4. Update the Delivery[Item Code] from text type to a reference type with Inventory as the reference table
   1. This will auto generate a Ref_Rows column on the Inventory column called [Related Deliveries]
5. Create a virtual column on the Inventory table called [Virtual Stock Available] with the expression SUM([_thisRow].[Related Deliveries].[Quantity])-SUM([_thisRow].[Related Sold Items].[Quantity])

You will now have a real-time virtual column that have the available inventory.