7 Replies Latest reply on Sep 27, 2011 11:45 AM by philmodjunk

    1 to Many Question - Case Study



      1 to Many Question - Case Study




      Specs: FileMaker Pro 10 - On site Server


      I am designing a tracking table on an ordering database.  My company orders printed materials from a 3rd party vendor.  The materials are shipped using a meeting number, which can be related to multiple order numbers.  The order numbers contain the package information and waybill numbers.


      I am struggling with how to display multiple order numbers from a single meeting number, so that any Project Manager can review all of the related waybill numbers pertinant to a single meeting number.



      •  All data is located on a single database called "Production"
      • The "tracking information table" is a table on the database, with a unique identifier "Meeting Number" for all tables
      • 1 unique Meeting Number per session (courses)
      • 1 unique Order Number per order
      • Multiple Order Numbers per Meeting Number
      • Multiple WayBill Numbers per Order Number
      • Ability to display a list of WayBill Numbers that pertains to a single Meeting Number
      If the case is missing information, I don't mind logical responses that might assist in the development.
      Thank you very much,

        • 1. Re: 1 to Many Question - Case Study

          You should be able to put a portal to waybills on a layout based on Meeting that will list the waybills related to that meeting number.

          • 2. Re: 1 to Many Question - Case Study

            You can also base a list view type layout on Waybills, but include fields from Meeting. This can be more flexible than a portal if you plan to print this out as a report. The invoices starter solution uses this method for printing an invoice if you need to examine an example.

            • 3. Re: 1 to Many Question - Case Study

              Mark and Phil,


              Thank you for your solutions and feedback.  Both cases present the data well, however both are only displaying 1 record as a 1 to 1 relationship.  Below is a bit more information of my problem.


              Table 1: Production

              Fields: Meeting number

              Table 2: Tracking

              Fields: Meeting number, Order number, Waybill number


              Table 2 can have more than one record for a single meeting number, whereas Table 1 is most likely not going to (assume it is unique).  The Production table lists the production requirements (materials, people, etc.) whereas the Tracking table lists all information relevant to tracking (waybill number, number of pieces, etc.).


              I would like help figuring out how to display more than 1 associated record in the Tracking table, for  each record in the Production table.  For example, Meeting number: 12345 has 1 instance in the Production table.  However, the same meeting number may have multiple instances in the Tracking table (more than 1 package ordered for the meeting number).  The multiple instances are differentiated by the unique order number in the tracking database.


              So far, netiher solution will result in multiple records being display.  With the portal, I can only view 1 order number and waybill number, although in the Tracking table there are 3 records with the same meeting number.


              Please assist.


              Thank you,


              • 4. Re: 1 to Many Question - Case Study

                If neither case is working, I would guess that there is something wrong in the relationship.  Double check your relationship graph and make sure that the Production and Tracking tables are related by meeting number  and that the meeting numbers are actually the same where they should be (ie no stray characters crept in).  Ideally the meeting number would be an autoentered, unique serial number in the Production table and would be automatically entered into the Tracking table when a record is created from the Production table..

                • 5. Re: 1 to Many Question - Case Study

                  For the "portal free" method I described make sure that

                  a) Your layout is set to list view


                  b) you correctly pull up all the records you see in the portal in your found set. Either a Go To Related Records or a scripted find can be used to pull up the related records on this report layout.

                  • 6. Re: 1 to Many Question - Case Study


                    Thank you very much for posting the solution.  The portal strategy did list more than 1 matched record, so that I was able to list all of the order numbers that belong to a single meeting number.  I ended up having to edit the Field/Control to be a drop down menu and used the Tracking table as the list values.  



                    I am exploring your method, because printing a report is most likely going to become a new requirement.  However, I am having trouble listing all of the records that match the criteria.  For example, 1 meeting number is found in the Production table once but 3 times in the Tracking table.  The 3 different iterations of the meeting number have unique order numbers associated, with individual waybill numbers.  When I followed your instructions (apart from the Go To Related Records - which I am unsure about) I produced a list of all of the meeting numbers with order numbers and finally waybill numbers.

                    Unfortunately, it only displayed 1 record for each meeting number.  This is a problem because some meeting numbers have multiple order numbers and waybill numbers (in the related table - Tracking).

                    Do you know of an additional step I can take to list more than 1 associated record?


                    Thank you both for your time and help, it is greatly appreciated.




                    • 7. Re: 1 to Many Question - Case Study

                      Go to related records will bring up the same records that you see in your portal so that's an option you should consider here.

                      Performing a find for the line item records works well here as well. I'd need to see the details in how you attempted to do this before I could suggest how it might have gone wrong.

                      Let's say that you have this relationship:

                      Production::Meeting Number = Tracking::Meeting Number

                      Go To Related Record [Show only related records; From table: Tracking; Using layout: "Tracking Report" (Tracking)]

                      Should bring up the portal records in one step.

                      Using a find, you could use the following script:

                      Set Variable [$MeetingNumb ; value: Production::Meeting Number ]
                      go to layout ["Tracking Report" (Tracking) ]
                      Enter Find Mode [] // clear the pause script
                      Set field [Tracking::Meeting Number ; $MeetingNumb ]
                      Set Error capture [on]
                      Perform Find[]

                      The results for the scripted find should be indentical to that used in go to Related Record. If the find is only finding one record when you have more than one in the portal, then you need to make sure that you are in list view mode and then you need to double check your relationship for the portal as it must be based on some other field than Meeting Number.