| Author |
Message |
bowja
Newbie
Joined: 07 Dec 2009
Location: Australia
Online Status: Offline
Posts: 31
|
  
Topic: Check if value exists in a column
Posted: 06 Feb 2014 at 2:51pm |
|
Hi Everyone,
This has been frustrating me for some time... I will try to describe what I want rather than what I think is the solution.
I need to check if a date value in my report exists in a public_holiday table's date column. I wish to build conditions around whether the date is a public holiday or not.
I think a solution to the above will give me what I need as the actual report is a little to convoluted to easily explain. Essentially it is a roster made up of 98 subreports and I wish to grey out some of the subreports based on if they refer to a public holiday.
Any assistance with this would be greatly appreciated.
|
|
If you think you can or think you can't you are right - Paraphrased quote Henry Ford
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 07 Feb 2014 at 5:20am |
|
do an outer join to the public holiday table. Then add logic where the date is not null(the only time it is a holiday).
I would use a shared variable as a flag, this way you check the date only once and all the subreports can access the flag, so they don't need to link to the holiday table.
As to the outer join, on the link between the holiday table and the date field, right click the link and select one of the outer joins...if the data doesn't display as you think it should, select the other outer value...
Left or right makes a difference, but I don't know how CR decides which table is left and which is right.
Hopefully, this is not complete gibberish...
|
| IP Logged |
|
bowja
Newbie
Joined: 07 Dec 2009
Location: Australia
Online Status: Offline
Posts: 31
|
Posted: 07 Feb 2014 at 1:05pm |
|
Hi lockwelle,
Thank you for your reply. I have previously tried linking by the roster_shift.date to the public_holiday.date with a left outer. Unfortunately this did not seem to work. The way the shift table is set up is that it doesn't have an entry unless the shift is filled. There are a vast number of individual shifts and each subreport shows only one. This is done via selection formula which selects for day, split. I had attempted to get a boolean from a formula with the link but where the day/split doesn't exist in the table I cant check if it is a public holiday. Is there a loop or vlookup formula that I can use to look through an unlinked table?
Eventually I want to grey ou some shifts (sub reports) Monday through Friday unless they are public holidays regardless of where the shift is vacant (report is balnk). The idea being that vacant shifts that need to be filled can easily be distinguished from vacant shifts that don't. This depends only on the shift type/day and if it is a public holiday.
I hope the background made this clearer rather than confusing things.
Thanks in advance.
P.S. I just had a look and discovered that the reason the joins aren't working is because the date are actually datetime fields and the times are 1200 PM on the roster_shifts table and 1000 AM on the Public Holiday table.
Is there a way to join the tables by date rater than datetime if that is the data type?
Edited by bowja - 09 Feb 2014 at 12:12pm
|
|
If you think you can or think you can't you are right - Paraphrased quote Henry Ford
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 10 Feb 2014 at 5:02am |
|
I don't think so, not in Crystal. In SQL, definitely...a Command might work, since you could select the field and move the time back to midnight, then you could outer join on that...but the date not being in the shift table....hmm...the solution would be a 'dates' table, one that just lists all the dates, then you could outer join the shift calendar to the dates table and the holiday table as well...then you would have all dates available for the report.
HTH
|
| IP Logged |
|
|
|
