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::Date
Make 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.
Thanks, I tried doing that (and had tried other things along those lines before posting) but I keep getting a ? for the value.
This is what I did:
Calculation field in lines called date_due_calc: GetNthRecord ( calendar::date ; (get(recordnumber) + lines::turn_around) )
lines::turn_around is an unstirred calculation that equals products::turn_around
For the relationship lines::date_ordered > calendar::date
All the components seem to be working correctly, but the date_due_calc returns a ?. Can you give me some insight into what I'm doing wrong?
Remove the Get ( RecordNumber ) term as it makes no sense here. And you don't need the Lines::turn_around calculation field as you can refer to the value in the related table that it is copying directly.
GetNthRecord ( calendar::date ; Products::turn_around) )
Ah okay, it seems to be working now. Part of the problem was that the turn around time starts the day after the product was ordered. So I changed the calculation to this: GetNthRecord ( calendar::date ; products::turn_around + 1).
I also changed the relationship to lines::date_ordered < calendar::date instead of lines::date_ordered > calendar::date, because the original way was producing a completely wrong date for calendar::date.
Thanks as always for you help!!