JessicaMatenga
5 years agoQrew Trainee
Formula to return correct subtotals
HI
I have two numeric subtotal fields from on table to another. I have tried to create a single formula field using the If and Isnull statement, but it is only returning data from the isnull field.
Result 1 - If(IsNull([Purchase Order Subtotal]),[PO Subtotal.],[Purchase Order Subtotal])
If the [Purchase Order Subtotal] is populated, it would return the Purchase Order Subtotal for jobs that had a value in this field. But for jobs where the [PO Subtotal.] is populated, it returns a zero result.
Result 2 - If(IsNull([PO Subtotal.]),[Purchase Order Subtotal],[PO Subtotal.])
If the PO Subtotal. is populated, it will return the value of the PO Subtotal. value. But for jobs where the [Purchase Order Subtotal] is populated it returns it as the same as the PO Subtotal. value.
Note:
- the [PO Subtotal.] field is also an If and Isnull formula field
- the [Purchase Order Subtotal] field is an * formula field
I think I have figured it out but don't know how to write the formula. Essentially, in either case, the Result 1 returns as Zero because in all these cases, if the Purchase Order Subtotal contains data, then the PO Subtotal. will not be populated anyway. It is either one or the other so the ISNULL is incorrect. I need a formula that returns either one or the other, whichever one is populated.
------------------------------
Jessica Matenga
------------------------------
I have two numeric subtotal fields from on table to another. I have tried to create a single formula field using the If and Isnull statement, but it is only returning data from the isnull field.
Result 1 - If(IsNull([Purchase Order Subtotal]),[PO Subtotal.],[Purchase Order Subtotal])
If the [Purchase Order Subtotal] is populated, it would return the Purchase Order Subtotal for jobs that had a value in this field. But for jobs where the [PO Subtotal.] is populated, it returns a zero result.
Result 2 - If(IsNull([PO Subtotal.]),[Purchase Order Subtotal],[PO Subtotal.])
If the PO Subtotal. is populated, it will return the value of the PO Subtotal. value. But for jobs where the [Purchase Order Subtotal] is populated it returns it as the same as the PO Subtotal. value.
Note:
- the [PO Subtotal.] field is also an If and Isnull formula field
- the [Purchase Order Subtotal] field is an * formula field
I think I have figured it out but don't know how to write the formula. Essentially, in either case, the Result 1 returns as Zero because in all these cases, if the Purchase Order Subtotal contains data, then the PO Subtotal. will not be populated anyway. It is either one or the other so the ISNULL is incorrect. I need a formula that returns either one or the other, whichever one is populated.
------------------------------
Jessica Matenga
------------------------------