### Title

Calculation from 3+ Tables?

### Post

Hello,

I have the following tables/relationships:

Jobs <<==_kf_jobs_id==>>Lines<<==_kf_product_id==>>Products

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.

Thanks!

GetNthRecord sounds like a function I'd use here, but from a calculation, not a script.

I assume that if you were open 365 days a year, Date_due from jobs + Turn Around from Products would compute the date due. It's the need to add an additional day to that computed date for each day you are closed in that interval that complicates this.

Lines can add an unstored calculation field that copies the DateOrdered or it can be an auto-entered value from Orders if you take the necessary steps to keep the values in synch if you change the DateOrdered date after adding a related records in Lines.

This makes the following relationship possible:

Lines::DateOrdered

>Calendar::DateMake this a sorted relationship where Calendar records are sorted by Date in ascending order.

Then a calculation field (or date field with an auto enter calculation) can use GetNthRecord and the Turnaround figure to compute the due date.