I am trying to divide a formula I have used in my Saved search by Quantity Available

Absolutely! In your Saved Search, you can achieve this by dividing the “Quantity Available” field with a formula that checks the transaction type. Here’s how: The formula you provided checks if the transaction type is “Sales Order”.

If it is, it divides the transaction quantity by 8. Otherwise, it returns 0. To divide the “Quantity Available” by this formula, you can use the following expression in a new formula

(numeric) column: {quantityavailable} / (CASE WHEN {transaction.type} = ‘Sales Order’

THEN {transaction.quantity}/8 ELSE 0 END) This formula will first evaluate the inner part (the CASE WHEN statement) to determine the value based on the transaction type. Then, it will divide the “Quantity Available” by that resulting value.

Thank you for the response. However, it does not seem to be working. Please see the formula used below. I also used Formula Numeric row, Sum Summary Type and the function was Absolute Value.

 {quantityavailable} / (CASE WHEN {transaction.type} = ‘Sales Order’ THEN {transaction.quantity}/8 ELSE 0 END)

Any ideas?

I think it is also better to have a context what’s the goal of your formula, and some sample output from sample results. My assumptions are:

• You are doing Item Saved Search
• You are trying to get the sum of SO quantities and divide that sum to 8 first (let’s name this as SOQty)
• Then you will divide item’s current total available quantity by SOQty

From assumptions above, you can create saved search as, under Results subtab:

• Name | Group | Label: ItemName
• Available | Maximum | Label:ItemAvailQty
• Transaction : Quantity | Sum | Label:TranQty
• Formula(Numeric) | Maximum | Formula: MAX({quantityavailable})/ NULLIF((SUM(CASE WHEN {transaction.recordtype} = ‘salesorder’ THEN {transaction.quantity} ELSE 0 END)/ 8),0) | Label: ItemAvailQty/TranQty

Explanation:

• Since you are using division, it is important that you include handling of dividing by zero by using NULLIF()
• I summarized {quantityavailable} to Maximum because this will appear multiple times if an item has multiple transactions
• I first summed up transaction quantities before dividing by 8. You can modify this according to your needs.