Calculation from 3+ Tables?
I have the following tables/relationships:
There's also a 4th table called Calendar, which has a record for each day we're open.
The products table has a field called turn_around that has the turn around time for the product. The lines table has a field called date_due, which is the date the product is due to be ready. The jobs table has a field called date_ordered (the date the order was placed). The due date is calculated based on the days we're open. Currently to figure this out, I have a script that runs each time a line is added to the job, that finds record in the calendar table that matched the date ordered, and then uses getnthrecord to figure out the date due, based on the turn around time. Overall this solution works okay, but I was thinking there might be a more elegant solution. I was trying to figure out a way to find the date due using a calculation instead of a script, but I'm having trouble figuring out how to pull together so many related fields into one calculation. I'd love to hear some thoughts/suggestions.