Please help with conditional formulas!

[jstoehner]

Ok, I'm not sure where to start. Relatively new to CR and programming so please be gentle.

I am designing a report that details tee time reservations. Data is pulled from multiple tables with the main database being Oracle (ODBC). I'm trying to retrieve a price from one of 2 Access tables based on conditions.

Here's what the records look like:

Res_Data (Oracle)
Customer_Name | Customer_Number | Date | Day | Course_Name | Course_Number |

ie. SMITH | S100 | 01/15/07 | WED | TROON | 1010 |

Annual_Rates (Access)
Date | Course_Name | Course_Number | SUN | MON | TUE | WED | THU | FRI | SAT |

ie. 01/15/07 | TROON | 1010 | $50 | $50 | $65 | $75 | $95 | $100 | $100 |

So, the report pulls the reservation data from oracle and then finds the green fee corresponding to that course number and day of the week to yield the following on the report.

SMITH | S100 | 01/15/07 | WED | TROON | 1010 | $65

Some of the courses we book at have rates that change seasonally instead of the same Mon-Sun rate all year. So I created a second Access table with a rate for each day of the year. If looks like this...

Seasonal_Rates (Access)
Date | Course_Name | Course_Number | Rate |
01/01/07 | PEBBLE BEACH | 1025 | $200 |
01/02/07 | PEBBLE BEACH | 1025 | $225 |
(and so on for each date of the year)

So in CR I have the following formula to pull the reservations at clubs with the ANNUAL rate.

if ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
[DayOfWeek({Res_Data.Date})] = "Sun" then {Annual_Rates.Sun}

else if ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
[DayOfWeek({Res_Data.Date})] = "Mon" then {Annual_Rates.Mon}

else if ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
[DayOfWeek({Res_Data.Date})] = "Tue" then {Annual_Rates.Tue}

else if ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
[DayOfWeek({Res_Data.Date})] = "Wed" then {Annual_Rates.Wed}

else if ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
[DayOfWeek({Res_Data.Date})] = "THU" then {Annual_Rates.Thu}

else if ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
[DayOfWeek({Res_Data.Date})] = "FRI" then {Annual_Rates.Fri}

else if ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
[DayOfWeek({Res_Data.Date})] = "SAT" then {Annual_Rates.Sat}

This does the trick for all clubs with annual rates. I get all the reservation data and it pulls the right rate for the corresponding day of the week for that club.

Here's where I am stuck. I added the following code to the end of the above formula hoping that it would pull the rate for the seasonal clubs from the other table. Unfortunately when I retrieve data it returns no records at all.

else IF {Res_Data.Course_Number} = ["1025"] then {Seasonal_Rates.Rate}

So how can I combine these formulas or otherwise structure the report to pull the right rate from the right table? I hope I gave a clear picture of what I am trying to accomplish and that someone will take a stab at a fix.

Thanks!

 

[MrBillSC]

It may be a linking problem. Linking to the different rates tables should be left outer joins. Your rates formula will need to check for "IsNull({Annual_Rates.Course_Number})" first and then "Else If IsNull({Seasonal_Rates.Course_Number})".

 

[jstoehner]

Thank you very much for responding. I changed the links to left outer joins but not sure how to implement your advice on the rates formula. Where do I insert your suggested additions to my existing code? Any chance you could provide a little more help? Thanks!!

//retrieving rates for clubs with annual rates
if [DayOfWeek({Res_Data.Date})] = 1 then {Annual_Rates.Sun}
else if [DayOfWeek({Res_Data.Date})] = 2 then {Annual_Rates.Mon}
else if [DayOfWeek({Res_Data.Date})] = 3 then {Annual_Rates.Tue}
else if [DayOfWeek({Res_Data.Date})] = 4 then {Annual_Rates.Wed}
else if [DayOfWeek({Res_Data.Date})] = 5 then {Annual_Rates.Thu}
else if [DayOfWeek({Res_Data.Date})] = 6 then {Annual_Rates.Fri}
else if [DayOfWeek({Res_Data.Date})] = 7 then {Annual_Rates.Sat}
//retrieving rates for clubs with seasonal rates
else IF {Res_Data.Course_Number} = ["1025"] then {Seasonal_Rates.Rate}

 

[MrBillSC]

Try:

// check for no record in annual and seasonal table.
If IsNull({Annual_Rates.Course_Number}) and
IsNull({Seasonal_Rates.Course_Number}) then 0 else

// if seasonal rate is null use annual rate
If Isnull({Seasonal_Rates.Course_Number}) then
if [DayOfWeek({Res_Data.Date})] = 1 then {Annual_Rates.Sun}
else if [DayOfWeek({Res_Data.Date})] = 2 then {Annual_Rates.Mon}
else if [DayOfWeek({Res_Data.Date})] = 3 then {Annual_Rates.Tue}
else if [DayOfWeek({Res_Data.Date})] = 4 then {Annual_Rates.Wed}
else if [DayOfWeek({Res_Data.Date})] = 5 then {Annual_Rates.Thu}
else if [DayOfWeek({Res_Data.Date})] = 6 then {Annual_Rates.Fri}
else if [DayOfWeek({Res_Data.Date})] = 7 then {Annual_Rates.Sat} else

// use seasonal rate
{Seasonal_Rates.Rate}

MrBill