Remove order detail quatity from product inventory

Phil_Waite · 01-30-2020 10:11 AM

Hi,

I have a product table, an order table and an order detail table.

When an order has been delivered the user manually “archives” the order.

The order may have more than one product in it and so more than one quantity to remove from more than one product inventory.

I have an action button to set the [Order Status] of the order to “Archived”. I intend to set up another action to subract the correct quantity from correct porduct.

Execute and action on a set of rows:

For a record of table : Orders

Referenced Table: Products

Referenced Rows: This is where I need the expression to give the row in the Products table that matches the [Unique Product Id] in the Order Details table in relation to the Order Id this row selected

Thanks

Phil

Phil_Waite

For example. Here are the three parts. The Order, the child Order Details and the Product. The Action is applied to a view of the orders table and the Quantity fro the related order detail is removed from the related product.

Order Id	Contact Status	Order Status	Order Status Calc	Run 2	Running Sheet Calc	Price List	Name & Town	Outlet no	Order Date	Delivery Date	Complete Order Total Inc Vat	Order Taken By	Date Taken	Time Taken	Standing Delivery/Order Details	Payment Type	Cash Status	Invoice Number	Route	User Stop
3068	FALSE	Archived	Closed			Band 2	Cults Hotel Cults	36	22/11/2019	11/12/2019	88.8	phil@listersbrewery.com	22/11/2019	15:36:10		Paid In Cash	TRUE	3169

Order Date	Delivery date	OrderDetail Id	Order Status	Name & Town	Outlet No	Order Id	Product Name	Unique Product Id	Product Id	Total Duty Payable	Duty Payable	Quantity	Price	Total	Total Inc VAT	Standing Delivery/Order Details	Intended Payment Type	Additional Notes	Payment Type (Driver Use)	Gyle Number 1 (Driver Use)	Gyle Number 2 (Driver Use)	User	Invoice Number	Route
22/11/2019	11/12/2019	a98a9aa3	Archived	Cults Hotel Cults	36	3068	Lister’s Best Bitter 9G 3.9%	1	BB 9g	£14.88	£14.88	1	74	74	88.8		0	0	0	533	0	phil@listersbrewery.com	FALSE

Unique Product Id	Product Id	Product Name	Product Description	ABV	Litres	Duty Paid On (Litres)	Duty/Hectolitre	Duty Paid on Container	Band 1	Band 2	Band 3	Band 4	Custom	Add To Inventory/Reset Total	Starting Inventory	Total Inventory	Total Requested	Working	Total Available	Reset Date
1	BB 9g	Lister’s Best Bitter 9G 3.9%	BB 9g	3.90%	41.00	40.00	£9.54	£14.88	£70.00	£74.00	£79.00	£84.00	0	0	0	78	12	0	66	27/1/2020

Steve

What have you tried?

Phil_Waite

Hi Steve

SELECT(Products[Unique Product Id], ([Order ID] = [_THISROW].[Order ID]))

Not doing very well with this one which is why I didn’t post my attempt.

I’m not sure why the expression above doesn’t give a negative result in the desription below the expression box as the Orders Table has no Unique Product Id and the Product table has no Order Id in it.

We are selecting the rows to be acted on. Once this has been done another action has to be created: Set the values of some columns in this row - where the individual quantities for the relevent products are subtracted from the total inventory.

Thanks

Phil

Phil_Waite

This seems to look right

SELECT(Order Details[Unique Product Id], ([Order id] = [_THISROW].[Order id]))

Need to set the value of the correct inventory total based on unique id in the second part of the action. This seems tricker as the above expression has given two Unique Product Id and each one must have the corect and corresponding quantity deducted from it from the order details.

[Total Inventory] = [Total Inventory] minus the quantity in order details where the unique prodcut id = the unique product id

[Total Inventory]-(SELECT(Order Details[Quantity],([Unique Product Id] = [_THISROW].[Unique Product Id])))

I can’t get this to work though.

Thanks

Phil

Steve

Thanks for the details!

Try:

FILTER(
  "Products",
  IN(
    [Unique Product Id],
    SELECT(
      Order Details[Unique Product Id],
      ([_THISROW].[Order Id] = [Order Id])
    )
  )
)

Phil_Waite

Thanks Steve,

And can can the referenced action be just

[Total Inventory]-[Quantity]…?

Will the correct quantites be deducted from the correct products with this?

thanks

Phil

Steve

Nope. The referenced action needs to be an action, not an expression.

Getting what you want isn’t going to be trivial, I think, and I’m afraid I don’t have any ready suggestions for you. I imagine someone else will have to chime in.

Phil_Waite

Hi Steve,

I mean an action that is: Set the values of some columns in this row

Set these columns: [Total Inventory] = Then mey expression which starts [Total Inventory]-…?

Alternatively, when the archive button is hit the Order Details for the order are sent to another table in the googlesheet. A spreadsheet formula is the used to deduct the correct quatities from product table.

I have a reset inventory total button that resets the Total Inventory when a stock check is done. When the order details are sent to the new table there could be another column with “Include” and " Do Not Include" for example. When the order detail is sent to the table it is set to “Include” (in the deduction calculation) and when the inventory is reset all previous ones are set to “Do Not Include”.

Thanks

Phil

Phil_Waite

Originally as shown in the old post I had the current inventory level in the google sheet and I was trying to add or subtract from that through the app

I added a new column [Stock Status] to the order details table that is either “yes” when that item is to be included in the stock and set to “no”

I then have a virtual column that takes the initial stock level (that is held in the spreadsheet) and looks up every item in the order details table that matches that product and subtracts the “Quantity” column IF the [Stock Status] is equal to “No”.

I have an action button where I can reset the total inventory of any product to a figure (after my brewer does a keg count in the cold room).

If you set orders to “Archived” or “Open” in your app then there is another dimension to this that involves another column “total requested” as part of the calculation but I think the answer you are looking for is to use a “Stock Status” column in the order details table and a Virtual Column to calculate whether that row should be deducted or not.