I’m currently creating a generic inquiry to create a CSV upload report to a third party portal. CHEP (specially treated pallets that are ‘rented’ by our customers). Every week we upload a report to the third party with a count of these pallets with pertinent info - such as which customer and their CHEP account. This also includes quantity.
The conundrum I have is that there are 8 item codes. One item code is for a single pallet - so this quantity is 1 to 1. The other 7 item codes are 1 pallet per unit - the unit count is different on them.
Example: Customer orders 1 unit of CHEP - qty is 40. They also order RPCs (reusable plastic containers) that are stacked onto 1 CHEP pallet. The customer orders 10 units of the RPCs, so the qty of CHEP pallets is 10. Their total quantity is 50.
Where I’m having the issue is coming up with the formula in the results grid to report the quantity of the CHEP for the invoice. I am using ARTran.Qty. I need to translate the unit counts of the 7 item codes to the related CHEP pallet count.
This is my current output - the quantity for the first invoice number is showing 275. The invoice has PALLC - Qty 80 and I6419NC - Qty 195. What it should show is 81. The I6419NC is 195 per unit, and 1 unit has one CHEP pallet.
This is my current results grid set up.
I am at a loss on how to write this formula. Hoping there might be some others who have to also report CHEP or just a big formula brain! Thank you in advance!
Edit: We’re on 2024 R1
