How to use Parent or List total in formula

dedrashores

I have a list called with a Parent named Total Fruit. Above are the quantities needed for each type. I need 8,526 pieces of fruit by formula, but that is misleading because the number of oranges and bananas is negative. I actually need 15,019 pieces of fruit.

If I select oranges I expect my result to be (4,696). However, if Total Fruit is selected I need the result to be 15,019.

Assistance would be greatly appreciated.

swapnil_J

Hi @dedrashores, Although this is not the most suitable solution but you can try something like this, you can display values present in count line item

Here Total fruit is parent of fruit list and not the top level

You can write the logic to select the value of Oranges and banana if needed in the boolean formatted line item

AjayM

HI @dedrashores ,

You won't be able to display your desired total using the same lineitem where it carries negative values. You will need help of few other lineitems (assuming 'data' lineitem has original values):
lineitem1 to provide only negative values;
lineitem2 to give you desired 'total fruit' value using 'data' and 'lineitem1' (formula: data - lineitem1);
lineitem3 to identify appropriate result based on selected list item;

Also, you will need list formatted lineitem (field card) as a selector on the UX, so that you can use this and write the formula in lineitem3. For ex, lineitem3 = if selected list item is not top level, then refer 'data' lineitem else refer lineitem2.

PS: As per your example, Grapes and Kiwis also have negative values, but your desired total outcome ignored those. You can modify lineitem1 formula based on your need using boolean selector as suggested by swapnil_J

Cheers!

sobaid

True Total=Total Fruit- Orange - Banana

8526-(-4696)-(-1797)=15019

edenwheeler

To achieve your desired results, use conditional logic in your formulas. Create a "Quantity Needed" line item and use an IF function to check the selected item. If it's "Oranges," set the quantity to -4696. If it's "Total Fruit," set it to 15019. Otherwise, set it to 0. This will accurately calculate the quantities based on the selected item.

dedrashores

Thank you all for your input. I used @swapnil_J solution with good results.