Join or formula issue?

[AcctSolver]

I have a report for sales orders and am trying to add a price component from a new table.

OrderTable:
OrderNo OrdLine Units PricePerUnit
1234 1 15 20.00
1234 2 12 22.00
1237 1 11 21.00

PricingTable:
OrderNo OrdLine PriceType PriceAmt
1234 1 A 2.00
1234 1 B 12.00
1234 1 C 6.00
1234 2 A 10.00
1234 2 B 12.00
1237 1 A 15.00
1237 1 B 3.00
1237 1 C 3.00

I only want to add the value of PriceType C to my report. If there is no value there, I want a 0 to appear.
I joined the tables by OrderNo and OrdLine. I created a formula saying if PricingTable.PriceType = "C" then PricingTable.PriceAmt, else 0. That created a line for each price type. Sure, if they were not PriceType C, it was 0, but that was not the desired result. I could not put the PriceType C in my selection criteria, because I need all the orders without PriceType C as well.

I'm sure there has to be a way to do it. Suggestions are very welcome.

 

[lbass]

You could try removing the Pricing Table from your report and then creating a SQL expression {%PriceC} like this:

(
select max(`PriceAmt`)
from PricingTable A
where A.`OrderNo` = Order.`OrderNo` and
A.`OrdLine` = Order.`OrderLine` and
A.`PriceType` = 'C'
)

Then create a formula:

if isnull({%PriceC}) then
0 else
{%PriceC}

This assumes there is only one value per OrderNo/OrderLine/PriceType combo.

-LB