Make one field from Many

[alexlc]

Hi,

Using Cr9, accessing Lotus via ODBC

Form/Table is Care Plan & holds details for recurring shifts for a service user, with start & finish times for each shift. Field names: {CarePlan.BedMondayFrom1}, {CarePlan.BedTuesdayTo1}, {CarePlan.LunchTuesdayFrom2} etc.
There are potentially 56 shifts per Care Plan - I need to find the total hours booked on that care plan.

I was wondering if there was any way to query/merge all 56 start/end times into one field called @Start_Time / @End_Time? rather than have 56 DateDiff formulas to add up.

Thanks in advance. We are waiting for this function to be added to the database, but have no timescale atm.

Alex

 

[Turkbear]

Hi,
Since the data is in Lotus, can you create some totals
there and then just return those cells to CR?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[alexlc]

Hi,

That's what I'm waiting for IT to do for me - I'm not a Lotus programmer, but they have a list of jobs to complete before they can look at this & I have another manager asking for the data.

Unfortunately the system is all designed for the front end user with little thought to data analysis/extraction being incorporated when they do things.

I've started writing formulas now, but hoped there would be a less fiddly way of doing it.

 

[Madawc]

You can save a little time by doing a paste of the formula to a dummy report, changing the name and details and then pasting back. (Crystal 11.5 allows you to duplicate formula fields using the Field Explorer, but you said you were on Cr9.)

Another possibility is to have a single command that selects the relevant start date for a care plan, something like:

if not isnull({CarePlan.BedMondayFrom1})
then {CarePlan.BedMondayFrom1}
else if not isnull({CarePlan.LunchTuesdayFrom2})
then {CarePlan.LunchTuesdayFrom2}
...

Do the same for End Date and then you can have a single formula for the comparison. I assume unused dates are null.




Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[IanWaterman]

Or you can do it in the care plan group footer, simply do a date diff on the Min start date and the max end date.

Ian

 

[alexlc]

Thanks all,

The problem is that all 56 fields could have data in - the care plan holds all shifts for any given week (so the Start & End Time fields are "Time" data, not "DateTime" data), there might be 2 AM calls starting 08:00:00 & ending 08:30:00, then 2 lunch calls at 12:00:00 - 12:30:00 etc, so finding the earliest & the latest start & end times wouldn't calculate the total hours booked.

So I can get Madawc's formula to display the first & last times, but it's giving a zero when you DateDiff it.

Uses Crystal 9 to access Lotus Notes via ODBC.
I extract data, manipulate it & curse a system geared to the front end user.

 

[Madawc]

Are you saying that date and time data can be in 56 different places in a single record, and any of them might be before or after any of the others? And any of them may be blank?

If this were SQL, I would use an SQL Command to create a new table with a separate entry for each date/time, long-winded but possible with 56 separate selects which could be built using cut-and-paste. Then they could be processed easily. Whether something similar is possible with Lotus Notes, I have no idea.

Or you could say that the task is virtually impossible without a redesign of the database. An extra table with just one date-time per record.

Possibly you could do something with tables. I've not really used them with Crystal.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[alexlc]

Hi,

Yep, that's it, I can potentially have 56 start times, or just 1, same for end times. It's a joy isn't it? I wish there was a way I could put a picture in here so you can see the form as the end user sees it.

I'm currently writing Start formulas to make the Time field a DateTime field, before doing end, the DateDiff for them all.

Do you know if there's a limit to formulas you can have on one report?

Uses Crystal 9 to access Lotus Notes via ODBC.
I extract data, manipulate it & curse a system geared to the front end user.

 

[fisheromacse]

i have had reports with over a hundred formulas.
i do not know if there is an upper limit.

 

[Madawc]

When creating multiple formulas, you can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5, you can also duplicate formula fields using the Field Explorer.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP