Code needed to show availability of resources via reservation database

[Former Member]

Post Author: BeckyS

CA Forum: Formula

I have an Access database where people can check out items to use. The basic table looks like this.

num

Employee

check_out

planned return

laptop

camera

projector

1

RES

7/28/07

7/28/07

TRUE

FALSE

FALSE

2

DJJ

7/21/07

7/24/07

TRUE

FALSE

TRUE

3

KLV

8/1/07

8/3/07

FALSE

FALSE

TRUE

4

CW

7/31/07

8/2/07

FALSE

TRUE

FALSE

In Crystal, I want to look at dates, starting with today' s date that tells me if the item is available or not. Something like this.

08/01/2007

laptop 1

Available

camera

Not Available

projector

Not Available

08/02/2007

laptop 1

Available

camera

Not Available

projector

Not Available

08/03/2007

laptop 1

Available

camera

Available

projector

Not Available

I set up a date table as group 1, then each of the objects as groups underneath that. The code I wrote looks like this, but it isn't working.

If {DateTable.Date}>={Check_Out_Table.Check_Out}and {DateTable.Date}<={Check_Out_Table.Planned_Return}and {Check_Out_Table.Camera}= truethen "Camera is unavailable"else "Camera is available"

HELP!

[Former Member]

Post Author: synapsevampire

CA Forum: Formula

Ahhhh, so the intent is to book in advance.

Then you must be using a future date of some sort to compare to what is in the database, right?

Try creating a parameter to allow the user to enter the date they need it.

Group by the Num.

Suppress the Group Header and the Detail sections.

We'll display at the Group footer.

In the group header place a formula of:

whileprintingrecords;booleanvar HideMe := False;

In the details place a formula of:

whileprintingrecords;booleanvar HideMe;If {?MyParameterDate} in {table.checkout} to {table.plannedreturn} thenHideMe := True

Place the fields to display in the group footer and in the suppression formula for the group footer use:

whileprintingrecords;booleanvar HideMe

Now if the date requested is covered it won't show.

It's probably slightly differtenbt from this, but the theory is sound.

As for having the user enter things multiple times, of course they don't do that, ever...

Entering data is unrelated to a report in most instances.

-k

[Former Member]

Post Author: BeckyS

CA Forum: Formula

Thank you for the answers. Unfortunately whether it's your code or my code the answer is always available. I can not get it to say unavailable. I may have to revamp this whole thing (although I don't really want to).

I did want to address a couple of your comments. First the reason I have this report is so that someone can book an item way ahead of time. They can look at the date they need something and then book it if it is not planned to be in use yet. Whether it actually gets checked in at that time is another issue. I do have a return date field, just didn't include it in my note because it has nothing to do with this particular report.

Also, I know I could write this with each item checked out individually in a table with employee, item number, check out date and planned return date, but as you and anyone who deals with users know they do not want to input their name three times to check out three items. They want to enter it once and check 3 boxes. Much simpler for them (although a pain for me). Is the way I want to do this even possible or am I going have to give up and try your way! : )

[Former Member]

Post Author: synapsevampire

CA Forum: Formula

Your real dilema is with the poorly conceived database.

A planned return date doesn't mean something was returned.

You should have a table of items with a unique ID, and a flag of in/out.

Then another table of employee, item number, date checked out, and if need be, an estimated return date.

But you still need a real field to show that it was returned. Returns may not happen on time, they can be late or early.

You also reference 2 tablss in your formula, so you need to shar5e the real data involved, not just some of it.

Anyway, to use ONLY the table you showed as an example you'd just use:

if currentdate > {Check_Out_Table.Planned_Return}andnot({Check_Out_Table.Camera}) then"Camera available"else"Camera is unavailable"

Not sure why you have to check the dates at all, the camera flag field should suffice.

if not({Check_Out_Table.Camera}) then"Camera available"else"Camera is unavailable"

-k

[Former Member]

Post Author: Bandit07

CA Forum: Formula

Becky,

This is a formula using CR10 developer with currentdate.

If(CurrentDate) In {Check_Out_Table.Check_Out} to {Check_Out_Table.Planned_Return} and {Check_Out_Table.Camera} = True Then "Camera is unavailable"Else "Camera is available"

B