9 Replies Latest reply on May 30, 2015 2:48 PM by erolst

    populating a field from a related record, Part Two

    rbogdanoff

      I have a Customers table and a related Purchases table, with the purchase date on each purchase record. I need a searchable field on the Customer layout that shows the productDescription from the Purchases table for the very first product purchased by the customer. How the heck do you do that?

        • 1. Re: populating a field from a related record, Part Two
          erolst

          Since you insist …

           

          • create a TO of Purchases, say, Purchases_byDateDescending

          • relate it to Customers by id_customer and sort the relationship by Purchases_byDateDescending::datePurchase

          • create a calc field (same data type as id_purchase): cIDOfDateFirstPurchase = Purchases_byDateDescending::id_purchase

          • create a TO of Purchases, say, Purchases_firstByDate

          • relate it to Customers by Customers::cIDOfDateFirstPurchase = Purchases_firstByDate::id_purchase

          • put Purchases_firstByDate::description on the layout

          • search

          • 2. Re: populating a field from a related record, Part Two
            rbogdanoff

            Brilliant, thank you very much. My poor old brain still doesn't wrap around

            the "Filemaker Way of Doing Things" to achieve things like this.

             

            Robert

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

              rbogdanoff wrote:

              the "Filemaker Way of Doing Things" to achieve things like this.

              What I suggested is really the “FileMaker Way” to let you search in an indexed field that comes only from the first (by date) purchase of a customer.

               

              There are (as usual) alternative ways – e.g. use a script to search the related description field from your normal Customer --< Purchase relationship, then discard any hits that don't come from the first purchase (which is easier to establish during runtime, e.g. using ExecuteSQL()).

               

              More code, less structure – there is always a tradeoff …

              • 4. Re: populating a field from a related record, Part Two
                siplus

                In Part One you saw how to copy the Purchase creation date from the Purchases table to the Clients table. It involved having a field in Clients (FirstPurchaseDate) ready to be the home of that date.

                 

                In Part Two you are basically asking the same question, with just another field as source/destination 

                • 5. Re: populating a field from a related record, Part Two
                  erolst

                  siplus wrote:

                  In Part Two you are basically asking the same question

                   

                   

                  He is; but once you need two fields from a certain related record, I think it's better to get a pointer to that record, rather than starting to replicate the attributes of the related table …

                   

                  i.e., give customers an attribute of "ID of earliest purchase", rather than add "date of earliest purchase", "description of earliest purchase" … (and where will it end?)

                   

                  Because if there will be a part three, like "I want to have a searchable list of all vendors that supplied items that a customer bought with their first purchase …" – then what?

                  • 6. Re: populating a field from a related record, Part Two
                    siplus

                    erolst wrote:

                     

                    Because if there will be a part three, like "I want to have a searchable list of all vendors that supplied items that a customer bought with their first purchase …" – then what?

                     

                     

                    details of first purchase: weight, length, price, color.

                     

                    it will never end and you are right about the Zeiger, but I am right about the right of part 2 2 actually exist (rhymes intended).

                    • 7. Re: populating a field from a related record, Part Two
                      erolst

                      siplus wrote:

                      I am right about the right of part 2 2 actually exist (rhymes intended).

                       

                      "What was the part in the middle?"

                      • 8. Re: populating a field from a related record, Part Two
                        rbogdanoff

                        A fly in the ointment! I did as you described above, and when I try and

                        search in the field created in your second to last step of instructions, I

                        get this FM error: "This operation cannot be performed because one or more

                        of the relationships between these tables are invalid." Ack.

                        • 9. Re: populating a field from a related record, Part Two
                          erolst

                          rbogdanoff wrote:

                          I did as you described above

                          Which shows how trusting you are …

                           

                          To be honest, I didn't test this and forgot that, while this setup is OK to display the related data, it does not work for searching, because at that moment the relationship is not valid.

                           

                          Wouldn't it be easier to use two triggers on the Purchase layout for the date and the customerID fields and set the IDOfOldestfOPurchase as a stored value? You don't need any additional TOs, just something along the lines of

                           

                          ExecuteSQL ( "

                            SELECT id

                            FROM Purchase

                            WHERE

                              id_customer = ? AND

                              theDate = ( SELECT Min ( theDate ) FROM Purchase WHERE id_customer = ? )

                            " ; "" ; "" ; Purchase::id_customer ; Purchase::id_customer

                          )