Calc with dates

[osiris612mb]

Ok I am trying to design a time and billing database that will calculate (obviously) what to pay lawyers for time worked during a specific time period. We are assuming that doing a search for all time entries from Jan 1 to Jan 15 would be a lengthy process (over 3000-4000 time entries total for that time period, approx.) due to the calculations. We were thinking that at the time of data entry changing that time period of Jan 1 to Jan 15 to a look up number of 1 and from Jan 16 to Jan 31 to 2 and so on... that way we would only look for the number 1 or 2; depending on which pay period we are processing, would be alot quicker. Does this make sense and would it be quicker the second way? Now, if so, HOW do you get that look up number 1-24 into a field through a calculation?:??

I appreciate any help, thanks in advance.

[osiris612mb]

Ok I am trying to design a time and billing database that will calculate (obviously) what to pay lawyers for time worked during a specific time period. We are assuming that doing a search for all time entries from Jan 1 to Jan 15 would be a lengthy process (over 3000-4000 time entries total for that time period, approx.) due to the calculations. We were thinking that at the time of data entry changing that time period of Jan 1 to Jan 15 to a look up number of 1 and from Jan 16 to Jan 31 to 2 and so on... that way we would only look for the number 1 or 2; depending on which pay period we are processing, would be alot quicker. Does this make sense and would it be quicker the second way? Now, if so, HOW do you get that look up number 1-24 into a field through a calculation?:??

I appreciate any help, thanks in advance.

[osiris612mb]

Ok I am trying to design a time and billing database that will calculate (obviously) what to pay lawyers for time worked during a specific time period. We are assuming that doing a search for all time entries from Jan 1 to Jan 15 would be a lengthy process (over 3000-4000 time entries total for that time period, approx.) due to the calculations. We were thinking that at the time of data entry changing that time period of Jan 1 to Jan 15 to a look up number of 1 and from Jan 16 to Jan 31 to 2 and so on... that way we would only look for the number 1 or 2; depending on which pay period we are processing, would be alot quicker. Does this make sense and would it be quicker the second way? Now, if so, HOW do you get that look up number 1-24 into a field through a calculation?:??

I appreciate any help, thanks in advance.

[Ender]

I don't think there would be any noticeable difference in speed; both would be very fast if the field you're searching is indexed.

I don't know if this fits your needs, but you might consider adding a Billing Period table, where a record is one lawyer's semi-monthly time summary. This would then relate to your Lawyer table by Lawyer ID and your Time Worked table by Lawyer ID and date or date range (Billing Period 1 might be for the dates 1/1/04 through 1/15/04, etc.) This table is mostly useful if you want to use it as the place to enter Time Worked data, or you have a need to store sub-totals by Billing Period (rather than recalculating them in a sub-summary report every time.)

[Ender]

I don't think there would be any noticeable difference in speed; both would be very fast if the field you're searching is indexed.

I don't know if this fits your needs, but you might consider adding a Billing Period table, where a record is one lawyer's semi-monthly time summary. This would then relate to your Lawyer table by Lawyer ID and your Time Worked table by Lawyer ID and date or date range (Billing Period 1 might be for the dates 1/1/04 through 1/15/04, etc.) This table is mostly useful if you want to use it as the place to enter Time Worked data, or you have a need to store sub-totals by Billing Period (rather than recalculating them in a sub-summary report every time.)

[Ender]

I don't think there would be any noticeable difference in speed; both would be very fast if the field you're searching is indexed.

I don't know if this fits your needs, but you might consider adding a Billing Period table, where a record is one lawyer's semi-monthly time summary. This would then relate to your Lawyer table by Lawyer ID and your Time Worked table by Lawyer ID and date or date range (Billing Period 1 might be for the dates 1/1/04 through 1/15/04, etc.) This table is mostly useful if you want to use it as the place to enter Time Worked data, or you have a need to store sub-totals by Billing Period (rather than recalculating them in a sub-summary report every time.)

[comment]

This may do what you want:

2 * Month ( Date ) - ( Day ( Date ) < 16 )

I don't know if it will be any faster than a date range find.

Presumably you will be purging your file every year, since "1" will find records from January 1-15 of any year.

P.S. A law firm with over 8000 billing entries per month, and you have to seek free advice in a forum?

[comment]

This may do what you want:

2 * Month ( Date ) - ( Day ( Date ) < 16 )

I don't know if it will be any faster than a date range find.

Presumably you will be purging your file every year, since "1" will find records from January 1-15 of any year.

P.S. A law firm with over 8000 billing entries per month, and you have to seek free advice in a forum?

[comment]

This may do what you want:

2 * Month ( Date ) - ( Day ( Date ) < 16 )

I don't know if it will be any faster than a date range find.

Presumably you will be purging your file every year, since "1" will find records from January 1-15 of any year.

P.S. A law firm with over 8000 billing entries per month, and you have to seek free advice in a forum?

[osiris612mb]

After trying that calc on paper I seem to have a problem.

Jan 15 would be { 2* 1 - ?)

I am a newbie to FMP7 so sorry if this is a common sense solution... and would this be done in a case function. And we will be purging this file to back up every year.

And yes the lawyers might have a lot of time entries but my work is done on my own...

[comment]

Jan 15 would be (2*1 - 1).

Jan 16 would be (2*1 - 0).

The condition (Day ( Date ) < 16) evaluates to 1 when true, 0 when false.

You could use Case() for this, but it is redundant.