Switch between tables at runtime

[byrne1]

I am working on a report that will display sales data for a specific date. My tables are as follows:

SALESD - holds the sales detail
ITEM - holds information about items that appear in SALESD
ITEMSER - holds information about serial items that appear in SALESD

Field SALESD.ITEMNUM maps to ITEM.ITEMNUM.
Field SALESD.SERNUM maps to ITEMSER.SERNUM.
Field SALESD.ITEMNUM will always be populated regardless of the value in SALES.SERNUM.

If the value in field SALESD.SERIAL is 1, then I need to pull the item information from the ITEMSER table. If, however, the value in field SALESD.SERIAL is 0, then I need to pull the item information from the ITEM table.

How can I format my select statement to check the value in SALESD.SERIAL and then determine where to get the item data?

Thank you very much for any information!

 

[synapsevampire]

Create a formula which has something like:

if {salesd.serial} = 1 then
{itemser.iteminformation}
else
if {salesd.serial} = 0 then
{item.iteminformation}
else
"Bad data"

Use this formula instead of the fields.

-k