6 Replies Latest reply on Dec 5, 2011 10:52 AM by BarbaraSevde

    Difficulty populating field from related table

    BarbaraSevde

      Title

      Difficulty populating field from related table

      Post

      I am building a solution for a farrier business and am having problems getting one field to populate properly.  I am a newbie and  I’m obviously missing something critical.

      The field I want to populate is date_last_service - not stored because it will change based on the date of the most recent invoice. 

      The tables are:

      Owners

      Horses

      Invoices

      Lines_Products_Invoices

      Products (also includes services)

      Globals

      The relationships are

        Owners <-> Invoices <->Lines<->Products-> Horses 2

        Owners -> Horses<->Invoices 2  

       

      The Horses relationship to Owners successfully populates a portal showing each owner’s horses.

      Horses 2 relationship with Invoices successfully allows an invoice to show the horse on which each service is performed, and also allows me to show an invoice list based on Lines_Products_Invoices  and sortable by horses and owners, etc

       

      I added Invoices 2 in order to link date_last_serice to date_invoice (unsuccessful so far)

      Without Invoices 2, I have been able show an invoice list showing date,horses, products & services, and totals, so I’m not sure that Invoices 2 is necessary. 

                 

      The field date_last_service appears in the 3 layouts: 

      1)horse form layout based on Horses

      2)in a portal based on Horses in the Owner layout, (It shows all the horses belonging to a particular owner.)

      3)in a Horse List layout based on Horses

       

      Any suggestions would be appreciated. I don’t know if I need Invoices 2 in the layout.  

      I would appreciate any suggestions. Aside from this - my solution works just great.  

       

      In the table occurrence Horses, I added a field “Date_Last_Service_Experiment - the field appears at the bottom of the Horse Form Layout to check the result of my experimentation, so I wouldn’t mess up what I had so far. Here's a link to the file with dummy data which you can access at 4shared.com

       

      http://www.4shared.com/file/1mG1p5yf/Filemaker_11172011_Experiment.html


      Thanks.

       

       

       

        • 1. Re: Difficulty populating field from related table
          philmodjunk

          Horses 2 relationship with Invoices successfully allows an invoice to show the horse on which each service is performed, and also allows me to show an invoice list based on Lines_Products_Invoices  and sortable by horses and owners, etc

          You show this as a link to your products table and that makes no sense here. If each invoice is aloways limited to a single horse, you can define a link from Invoices to horses: Invoices::HorseID = InvHorses::HorseID. If you preform services on more than one horse in a single invoice, you'd link the line items table to horses:

          LineItems::HorseID = LinesHorses::HorseID

          With either relationship, a calculation field in Horses could be set up as Last ( Invoices::InvoiceDate ) to return the date of the last invoice to list that specific horse.

          • 2. Re: Difficulty populating field from related table
            BarbaraSevde

            Thank you for your response.  

            More than one horse is referenced in each invoice.

            Horses 2 is linked to Line_Product_Invoice (the equivalent of LineItems, just my naming convention so I am reminded of the purpose of the Line Table) not to the Products table:

            Line_Product_Invoice::kf_horse_ID=Horses 2::kp_Horse ID

            (You might have to wiggle the tables  to untangle the connections to see this.)

            But in your suggestion, are you saying I should have a LineHorses (which I don't have) and where should it be?  I tried various versions of a Line Horses in the process of tryng to figure this out, but I was not successful.  Sorry if I'm kind of thick about this. The calculation you suggest was the one I tried, but obviously my relationships are not correct.

            Thanks for your help.

            • 3. Re: Difficulty populating field from related table
              philmodjunk

              With these relationships:

              Invoices::InvoiceID = Line_Product_Invoice::InvoiceID
              Line_Product_Invoice::kf_horse_ID=Horses 2::kp_Horse ID

              You can define a calculation field, (select date as the return type), with this expression to compute the date of last service:

              Last ( Invoices::InvoiceDate )

              Make sure to specify Horses 2 as the context table occurrence in the context drop down at the top of the specify field dialog box.

              • 4. Re: Difficulty populating field from related table
                BarbaraSevde

                Thank you so much. I had thought I tried it, but I had made the field type a date and tried using auto-enter calculation which didn't work.  But making the field type a calculation worked perfectly.  I thought the two approaches were interchangeable, but apparently not. I so appreciate your time and effort!  We're ready for our trial run nowSmile

                • 5. Re: Difficulty populating field from related table
                  philmodjunk

                  Auto-enter calculations that refer to fields in another table will not update when data in the other table is modified--as you discovered.

                  • 6. Re: Difficulty populating field from related table
                    BarbaraSevde

                    Lessons learned from mistakes always stick better - thanks again.