4 Replies Latest reply on Nov 5, 2014 12:30 PM by fireballprinting

    Calculation from 3+ Tables?



      Calculation from 3+ Tables?



      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.


        • 1. Re: Calculation from 3+ Tables?

          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.

          • 2. Re: Calculation from 3+ Tables?

            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

            and lines::date_ordered 

            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?


            • 3. Re: Calculation from 3+ Tables?

              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) )

              • 4. Re: Calculation from 3+ Tables?

                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!!