1 2 Previous Next 24 Replies Latest reply on Jun 1, 2015 7:22 AM by disabled_jackrodgers

    populating a field from a related record

    rbogdanoff

      I have a table of customers and a related table of purchases. Each customer has many purchases. I have a portal on the customer record sorted by date showing all their purchases, but I would like to have a field in the customer table I can search by that shows the date of their very first purchase.

       

      The only way I know how to do this is with a script that runs in the customer layout OnRecordLoad, that grabs the date in the first portal row and puts it in the date field on the customer record. But this script only runs and populates the date field on the customer record when each record is opened in the layout. Is there a way to do this as a calculation field? Many thanks.

        • 1. Re: populating a field from a related record
          Ramki

          Hi rbogdanoff,

           

          You can declare a Calculation Field in the customer table (FirstPurchase_Date) and get the minimum purchase date from the purchase table and store it in the customer table.  Declare the calculation field as date field  Hope This helps.  you do it only when the FirstPurchase_Date is blank.  Otherwise it will keep changing whenever there a post dated purchase.  Again this is dependent on your business requirement.

           

          Regards

          Ramakrishnan

          • 2. Re: populating a field from a related record
            siplus

            You sure want to avoid searching on unindexed (and unindexable) fields.

            At a closer look, you don't need calculation fields at all. All you need is a field "FirstPurchaseDate" in your Customer table.

            The purchase-generating procedure will write the purchase date into that field, if the field is blank, otherwise not.


            You can initially populate the field with a "Replace Field Contents" using a formula like Min(customers_Purchases::purchaseDate).

            • 3. Re: populating a field from a related record
              erolst

              siplus wrote:

              The purchase-generating procedure will write the purchase date into that field, if the field is blank, otherwise not.

               

              To cover the scenario of adding an (overlooked) earlier purchase at a later date, I'd suggest

               

              If [ Purchase::theDate < Customer::dateFirstPurchase ]

                Set Field [ Customer::dateFirstPurchase ; Purchase::theDate ]

              • 4. Re: populating a field from a related record
                Mike_Mitchell

                It's worth noting that searching on an indexable related field (such as the date field in the related table) isn't appreciably slower than searching on an indexable field in the current table. So, if you were interested in searching for the earliest created purchase record (rather than a date field you could enter later to cover the scenario erolst correctly points out), you could just perform your search on purchase::creationDate and be done with it. If the relationship is not sorted, then that related field will point to the first field in the creation order - which will be the oldest.

                 

                Also note that, if the relationship (not the portal) is sorted, then this approach will return the first record in the sort order - and may impose a performance hit the first time it's executed as the records are cached. So use with caution (although that's true anytime you use a sorted relationship).

                 

                For future reference (not necessarily for this use case).

                 

                Mike

                • 5. Re: populating a field from a related record
                  siplus

                  I agree - my reply was actually more directed to Ramki's post.

                   

                  Having a one-line portal on clients showing records from the purchases through a non-sorted, plain PK to FK relationship, and only holding the purchase date field could be enough.

                   

                  P.S. I never use relationship sorting.

                  • 6. Re: populating a field from a related record
                    Ramki

                    Hi,

                     

                    I agree with you. Since we are checking empty/blank, it will get executed only once.

                    Thanks

                    Ramakrishnan

                    • 7. Re: populating a field from a related record

                      If you have a TO with a relationship of CustID to CustID and sort on the data field in the realtionship graph then using MIN or MAX will give you the appropriate value in a calc field.

                       

                      Calculated Field: Min ( Invoice date field from sorted TO )

                       

                      Of course you could drag a date field onto your layout pointed at that TO and use the date field if the TO is sorted in the definitions.

                       

                      Either would work on a Form, list, table or report.

                      • 8. Re: populating a field from a related record
                        erolst

                        jackrodgers wrote:

                         

                        If you have a TO with a relationship of CustID to CustID and sort on the data field in the realtionship graph then using MIN or MAX will give you the appropriate value in a calc field.

                         

                        Calculated Field: Min ( Invoice date field from sorted TO )

                        Min() and Max() work as expected without a sorted relationship (or set of values) – that's their point.

                        • 9. Re: populating a field from a related record

                          Min() and Max() work as expected without a sorted relationship (or set of values) – that's their point.


                          Oops. Still working on my second sip of coffee this am...


                          The sort was needed to display the portal and dragging the date field from the invoice table. So both the calc and the showing of the field were done in one TO.


                          So even if the sort is not needed for Min/Max it was needed in the example.

                          • 10. Re: populating a field from a related record
                            DavidJondreau

                            I'm not sure that's the case...Say you have 100 Customers each with 1,000 orders. Searching locally means 100 indexed values. Searching related means running through 100,000 index values. Wouldn't the second be slower?

                             

                            I do think that searching on an indexed related field is the same as searching an unstored calculation locally that references that related field.

                            • 11. Re: populating a field from a related record
                              Mike_Mitchell

                              Not according to Rosemary Tietge.

                              • 12. Re: populating a field from a related record
                                Mike_Mitchell

                                I believed the same thing, until a DevCon some years back. I made the statement in Rosemary's presence that searching on a related field was slow. She corrected me: "Not if the related field is indexed. It's basically just as fast."

                                 

                                Todd Geist confirmed it at the Performance Panel at last year's DevCon. There's a slight performance hit, but not a lot.

                                 

                                As for the number of records, it's irrelevant. A calculated field in the parent table still has to reference the same number of records. It just can't be indexed, so it has to do all the calculations when you perform the Find.

                                • 13. Re: populating a field from a related record
                                  rbogdanoff

                                  Thanks to everyone for all the great info here

                                  • 14. Re: populating a field from a related record
                                    siplus

                                    I've done a test, 5'000 clients with 600'000 orders - my peace of mind needs tests, regardless of what was stated and by whom.


                                    Searching both directly (date field in clients, populated by replace - min (), indexed, and indirectly, i.e. in portal to orders on indexed date field, as of messages above.

                                     

                                    The difference is there (3 ms against 216 ms) but it IS irrelevant.

                                     

                                    We all win.

                                    1 2 Previous Next