Skip to Content
Former Member
Dec 09, 2014 at 10:50 PM

Need to lookup boolean value in table


I have a situation where I need to determine if the date is a working day or not and if not, find the next earliest working date.

I'm starting with the date from this statement:

//due date - production time

datevar startday: = datevalue(dateadd("d",{-invt.prod_time},{due_date.due_date})

I have a table (calendar) with 2 columns, date and is_working, where the date is a calendar date is_working is "Y" or "N"

the tables calendar and due_date are linked through {due_date.due_date} and {}. There was not other link I could create in the report with the table calendar.

My questions are:

1. Should calendar be linked at all or a stand alone table?

2. How do I lookup {calendar.is_working} using startday?

3. How do I adjust startday to find the next earliest "Y" in {calendar.is_working}? There will not be more than 3 consecutive "N" values.

I was thinking of:

startday = startday-1

then keep testing until I get a "Y" for the {calendar.is_working} test

I appreciate any assistance.