Auto-calculating number of weeks from X

[Carmelo]

Good morning.

First off I would like to tell everyone how much I appreciate this forum, this is my first post although I have been reading almost everything I could for a good while.

I just started using Filemaker about 3 months ago or so, basically trying to satisfy the boss.

I work for a construction company, trying to keep track of our projects in-house.

I was wondering how I could make a field auto calculate the number of weeks from the project start date, we bill weekly.

For example, if we start the project on this Thursday, 1/19...any work we do from now until next Wed, 1/25, to put an automatic "1". Any work from 1/26 to 2/1, put an automatic "2" and so forth.

Any help would be appreciated, thank you.

Carmelo

[mr_vodka]

If you have a relationship setup so that the jobs are related to the Projects via a Project Number, you can have something like this in a calc in the Child table (Jobs):

Ceiling ( Evaluate (WorkDate - Projects::StartDate) / 7 )

or this should work too

Ceiling (  (WorkDate - Projects::StartDate) / 7 )

[comment]

Try:

Ceiling ( ( WorkDate - Projects::StartDate [color:red]+ 1 ) / 7 )

[Carmelo]

Thank you for the quick replies.

I tried both way, with and without the +1.

I get a big number, 104619.

I have this in the "invoicenumber" auto-calculated field:

Ceiling ( (WorkDate - Work Orders::ProjectStartDate +1)/7 )

I was looking for a single digit number, am I doing this right?

Thanks,

Carmelo

[Carmelo]

UPDATE:

I did not have the Project Start Date correctly in, for the records already created, it was blank, had to go back and enter a value and all new records will automatically have the correct project start date in it.

It works now.

Thanks for the help.

Carmelo

[comment]

Well, 104619 is the number of weeks since January 1, 0001. So it looks like your expression << Work Orders::ProjectStartDate >> is returning a zero. Make sure your relationship is set up correctly and that ProjectStartDate field is not empty.

-- EDIT --

Ah - I see you've fixed it.

Edited January 18, 2006 by Guest

[mr_vodka]

Oops. I left it so that it counted the 7th day.

Thanks for the correction Comment.