Formula Reference

I have a sublist called Expense and another called Item. Both contain fields called Amount. I’m trying to implement another field and insert a formula referencing Amount from the Expense sublist because when I do {amount} / {quantity} it does not work at all so I believe due to conflict. How do I reference the one from Expense thanks

Beginner Asked on October 17, 2019 in How To's.

What transaction are you working with? It sounds like you are working with a vendor bill and the expense sublist of a vendor bill does not have a quantity column.

on October 17, 2019.

Purchase Order

Formula Reference

on October 21, 2019.

On the purchase order form, there is the Amount in the Item subtab and Expense subtab. I created the Quantity field and PPU field and I want the PPU field to return the price per unit by dividing the {amount} / {quantity}

on October 21, 2019.
Add Comment
2 Answer(s)
In general, the quantity column in not available at expense sub-list.

You should use a formula (numeric) field with this formula – for purchase order transaction.

case when {item} is NULL then {amount} else round((nvl({quantity},0)-nvl({quantitybilled},0))*nvl({rate},{amount}),2) end

This formula is a good reference to start with.

Beginner Answered on October 18, 2019.

On the purchase order form, there is the Amount in the Item subtab and Expense subtab. I created the Quantity field and PPU field and I want the PPU field to return the price per unit by dividing the {amount} / {quantity}

 

RE: Formula Reference

on October 21, 2019.

OK, regard the items subtab it should be fine just make sure to use this formula {amount}/NVL({quantity},1) to avoid dividing with zero. Regards,the expense subtab you don’t have a quantity column to calculate your PPU.  Would you like that the PPU will be calculate only for the items subtab?

on October 22, 2019.

Based on the Expense subtab. Because both of them have the amount field. Thanks

on October 22, 2019.

As I said before your formula will not work properly at the expense subtab because you don’t have a value there for the quantity. Hence the PPU field won’t work there.

on October 22, 2019.
Add Comment

Formula reference didn’t work for me at all so I created a script and it worked

RE: Formula Reference

Beginner Answered on October 28, 2019.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor