Challenging String/Date Formula question 3

[Squeaks]

I've been trying to come up with a solution to this for sometime but with no result.

I have a report in crystal 7 which returns timetable information. It compares a parameter date range (startday and endday) to each timetabled activity startdate and enddate, and shows the data if the activities have started and not finished within that week range...so far so good, this works fine.

Each of these activites however also have what is referred to as a 'week pattern' - a text string of 1s and spaces to represent whether they take place in any given week. So the activity dates could encompass a whole year but have a couple of spaces in the week pattern to represent it not taking place in a couple of weeks of that year.

For example, Activity1. startdate = 28 April, enddate = 30th May therefore the activity takes place one day each week for 5 weeks. The week pattern for Activity1 would read '11111'. If the week pattern read '11 11' then that activity would not take place in the week beginning 12th May. got it?

Of course there are hundreds of activities each with different dates and week patterns!! And I'd like for my parameter dates to be able to not only see that the start and end dates are valid but ALSO whether that activity is TRUE for that week.

I'm at a loss with this, well beyond my skills. I guess it could be done simply by a programmer (which im not) but I'd really like to see it done as a Crystal routine or formula.

Please help..again
Les

 

[Reebo99]

Something to get you started at least:

DateVar ParamDate := Date("01/02/2003"
DateVar StartDate := Date("01/01/2003"

NumberVar WeekNumber := DateDiff("w",StartDate,ParamDate);

If Mid("1111 1111 1",Weeknumber,1) = "1" then "Yes" else "No"

The items in italics are for my testing only and can be removed. If you replace items in bold with your fields it should work.

Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

You would create a loop to go through the weeks and check the 1111's pattern for the timeframe to determine that each is in the period specified, however you gave no indication of what you want the results to look like.

This isn't very complicated, but please provide what you want output, and if you supply table and field names, you should get a very accurate response, posing theory questions will likely net theoretical answers as a result.

-k

 

[Squeaks]

Hi Synapse, I'll do my best but as a beginner I'm not sure of my explanations or which information aids you or not.

The core date is 1 table called ACTIVITY. Activity has the date fields 'Startdate' and 'Enddate', and the text fields 'Name', 'Room', 'Staff', 'day, 'weekpattern' and 'block'. There are no null fields.

The report resembles a cross tab, finding whats in each Room is the purpose of the report so room are the rows and the column headings are fixed text headings to show Day and within each day the block something like this

---MonA--MonB--MonC--TueA--TueB etc ..Fri
rm1
rm2
rm3

at runtime I ask for two date parameters, 'Startd' (the Monday), and 'Endd' (the following Sunday).

The Grouping is by Room
the Details section is suppressed but within it are a host of calculated fields like the following:

if DateTimeToDate ({activity.Startdate}) <= {?Endd}
and DateTimeToDate ({activity.Enddate}) >= {?Startd}
and {activity.day}=&quot;Mon&quot; and ({activity.block}=&quot;A&quot; then mid ({activity.name}, 10, 3) else &quot;x&quot;

there is a calculated field for each change of Day and Block required but the date params stay the same.

I actually then show in the Room Group Footer a 'MAX of the Calc field' manually placed under the relevant column ... this method was copied from the Crystal help specifically when you want the column headings fixed in place irrespective of matching data (necessary for a real cross-tab).

The resulting grid is very compact and does appear to work nicely

---MonA--MonB--MonC--TueA--TueB etc ..Fri
rm1 Ac1 Ac4 x Ac3
rm2 x Ac9 x x Ac5
rm3 Ac8 x Ac6 Ac7


...except of course now I want the activity name to only show in the calc field if there is also a 1 in the week pattern for that corresponding parameter dates week.

Does this make more sense? I'll keep trying until it does I'm desperate to get this working.
Many thanks for your responses

 

[synapsevampire]

I don't see how the resulting data is categorized/grouped by week? What's MonA, MonB... representing? Why wold you have more than one Monday, etc.?

Here's how you might determine if a x corresponds to a week, and get the starting date of that week in an array, but you still haven't shared what the data looks like, or described the output well enough for this befuddled olde mind:

whileprintingrecords;
numbervar X;
numbervar Y := datediff(&quot;w&quot;,{Table.Start Date},{Table.End Date});
datevar array Datearray [y];

For X := 1 to Y do(
if mid({table.pattern,x,1} = &quot;1&quot; then
Datearray[X] := {Table.Start Date}+((X-1)*7)
);

Now you can check the variable Datearray[X] in a subsequent formula to determine if a start week matches the pattern.

-k

 

[Ngolem]

SV- he is working in CR 7....For-Next loops are not possible in CR7



Jim Broadbent

 

[synapsevampire]

ACK!, thanks Jim.

In lieu of a loop, you'll have to set up numerous formulas to set each element of the array.

Upgrade if you can...

-k

 

[Ngolem]

I'd like to ask a few questions....your layout of the report is different.

You want all Mondays lumped together, all Tuesdays...etc as well.

You must have a maxium number of columns that you can handle. At 7 days /week would you do this for 4 weeks at a time??...Do you limit your start/end Dates???

This &quot;activity field&quot;...does it have a full year of activity contained in it? And does each activity take place only one day/week?

for example...for a given activity the week activity might look like the following

&quot;11111 111 1 1 1111 11111 1111 -......- 1111 111 111&quot;
Where there are say 52 entries of &quot;1&quot; or &quot; &quot; for a monday

So If I was looking for activity from say April 1 to May 1 I would have to set a start point within that string of the Position of April 1 and lift the appropriate section of the activity weeks to May 1....is that what you are looking for??

Jim Broadbent

 

[Squeaks]

I warned you my explanations would be poor

I'll try and explain, firstly this is for a college timetable .. each of the rows in the database table represents a timetabled class (an activity) which takes place ONE time a week, and thats every week between the start and end dates (anything from 1 to 52 weeks range) where the weekpattern equals a &quot;1&quot;, so in fact most do look like Jim's example of a long line of 1s and spaces for the whole year.

so each row has an activity name, a start and end date, the week pattern, day of week it takes place and block, as well as the room it is in and the staff member responsible. the blocks are actually just a way of splitting each day into regular chunks.

The report is supposed to be a quick view of all the rooms in the college (shown in rows on the left)
what subject being taught in each of these rooms (the activity name in the grid)
in each of the blocks within each of the days (the columns) ..each of the five days (Mon-Fri) has no more than 5 blocks so a fixed maximum of 25 columns is shown, and yes the days and blocks are shown consecutively.
The calc fields are a way to make each of the activities which meet the date criteria slot into the right place on this grid/page. It processes the calc fields in the details section but shows the 'yes it takes place' in the group footer for Room (so room group has taken all matching room rows and spread them across into the right days and blocks columns)

When I run this it asks for the parameter dates..which should always just cover 1 week, but any week in the year could be chosen, the only activities to show up in the grid would have to take place during that week, that bit does work, well it does make sure the activity has not finished or indeed has just not started yet! But at this point is not clever enough to determine if that activity then has a 1 during that same week.

dont know if ive answered anything..so im befuddling myself now LOL.. to answer Jim..yes the days are together..5 days a week (5 blocks per day) but only ever 1 week to view (25 cols). yes each activity is only 1 day a week, the weekpattern string would look like your example..but not fixed to 52 of course, but however many weeks are between the start and end dates of the activity. and i only want to see 1 week(chosen parameters) at any time. The report takes these hundreds of activities and slots them into the grid just like a large cross-tab.

I must apologise for the confusion I'm causing, I'm not technically proficient enough to articulate what I need..but if you guys are prepared to give up your time and work on this then please accept my thanks and I'll endeavour to make some sense of it for you

Les

 

[Squeaks]

I've CR Dev 8.5 on order...so any solution using that would be fantastic! I'd not be able to test it for a while though.

 

[Squeaks]

Actually ignoring all my formatting rubbish for a second the synapse formula does make some sense to me.......

 

[Squeaks]

8.5 as at last arrived, I'll try the suggestions already given on Monday or Tuesday.

as a recap,

each row of data (referring to an ACTIVITY) has a start date field, an end date field, and a week pattern field (a text field of 1s and spaces) where 1s represent a single date within the start and end date which takes place (true) ..the spaces a single date within the range where nothing happens.

I enter a parameter start and end date on running the report, the max range for these parameters is 1 week (Monday to Sunday).

I need to check if the ACTIVITY is TRUE within the parameter weeks.