Hi I have a formula which goes i 1

[iamdavidlee]

Hi

I have a formula which goes in a loop to find the number of ppl in a house for a period of time. eg from 1st to 10th.

In the formula field i am going in a loop from start date to end date. For each day i want to check another table to see if customer was in the property on that day (using customer checkindate and checkoutdate)

But i am not sure how to go through another table while running a formula or from a formula filed.

HELP....

Thanks
Davids

 

[synapsevampire]

Join the tables on some key field if possible.

If not, you might pass some values to a subreport to accomplish this.

You'd need to supply some specifics if you need specific help, such as Crystal version, your loop formula, example data and the table structures, etc.

-k

 

[iamdavidlee]

oops forgot to put the subject name...

Also is it possible to use a variable used in forula field in the SQL Expressions field.

Thanks

 

[iamdavidlee]

hi

I have two tables.

House Rent table

HouseID RentID StarDate EndDate Amout
1 1 1/11/03 30/11/03 100.00
1 2 1/12/03 31/11/03 200.00

Basically this table has house rent details for different periods. From the above data there is a house which chrages a rent of 100 for October and 200 for December.

Occupant Table
RentID CusID CheckinDate CheckoutDate
1 5 1/11/03 15/12/03
1 6 1/11/03 30/11/03
1 7 20/12/03 31/12/03

This table has customers checkindate and checkoutdate. RentId is linked.

What I want in the report is to display the following details for each record in the House Rent table.

RentID CusID Amout

The problem is the amout needs to automatically calculated. The amout depends on how many ppl are sharing the house on everyday during period of stay.

Basically in the example data above, for the first month upto 15th two ppl are sharing the house so half the rent ($50) is divided between two ppl ($25 each). Then for the rest of that month there is only one peron so he should be allocated $50.

What I tried to do was for each House Rent record I went in a loop from House Rent Start Date to End Date for each cusomter to see which days he was in the house, this was working, I was also able to calculate amout per day but i am not sure how to check how many ppl occupied the house for a certain day so I can divided the days cost correctly between the occupants. This needs do be done while i am inside the loop check each day.

Would very much appreciate the HELP....

Thanks

David

 

[iamdavidlee]

hope the explanation is understable.... PLEASE HELP....

Thanks

 

[Ngolem]

your calculation is a bit confusing...a little more expanded data example would help (data and final result)

You don't need a looping formula per se

You would just link the two tables by RentID

HouseID RentID StarDate EndDate Amout
1 1 1/11/03 30/11/03 100.00
1 2 1/12/03 31/12/03 200.00

I think you made a typo on the bolded endDate

then Group something like this

Group 1 - House ID
Group 2 - Rent ID
Group 3 - StartDate
Group 4 - CheckinDate
the data would then be sorted by CustID
then you'd do your calculations in the Detail section which would do your looping for you.

But before this formula could be composed you need a whole bunch of rules...two that come to mind immediately are:

1. how is this rent money divided again? (give an example in gory detail...worst case)
2. What if a person stays only 1 week...ie. not a full month

Hope this helps



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!