Remove order detail quatity from product inventory

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

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

What have you tried?

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

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

Thanks for the details!

Try:

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

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

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.

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