confused about using dates in calculations

[Mariano]

i have read a few different posts about using dates, but I haven't had much success understanding or implementing the best way to do this...

I have a database with different construction jobs, each job has a list of all material needed for the site with the dates needed, and the vendor it is coming from. I have a seperate table with all the vendor info and their respective lead time in weeks. What I would like to do is flag the items that wont make it in on time based on the current date and the vendors lead time, then incorporate it in a script to email the appropriate project manager.(i pull the email addresses from another table with all of the job contact information).

thanks in advance!

Mariano

[comment]

flag the items that wont make it in on time based on the current date and the vendors lead time

There seems to be an element missing: when was an item ordered? The expected date of delivery is:

DateOrdered + 7 * Vendor::Lead

An item won't make it on time if:

ExpectedDate > DateNeeded

The current date doesn't enter the equations.

[Mariano]

Right, but the material may not have been ordered yet.

I have the date the material is needed at the site, and the time it takes the vendor to get us the material after the product is ordered.

I need to find the items that were not ordered and that will not make it to the site on the required date if ordered today. So if today is 3 weeks away from the date we "require" it at the site and the vendors lead time is 6 weeks, and we dont have it on order, the material needs to be flagged. Often orders arent placed in time due to the approval process of drawings, and the dates we originally set for them to be required at the site need to be pushed back.

[comment]

will not make it to the site on the required date if ordered today

Then use Get(CurrentDate) instead of DateOrdered in the same calc as above.

Note that this has to be unstored.

[smsaw]

When you use unstored, can another field refer to this "unstored" field in its validation calculation? I'm running into a validation problem and was wondering if this might be my problem.

[comment]

I don't see why that should be a problem.