7 Replies Latest reply on Oct 21, 2016 7:03 AM by sisterme

    Help viewing data from related tables in one layout

    sisterme

      Hi everybody!

       

      This is my first time visiting this forum.

       

      I'm using "antiquated" Filemaker 9 basically to generate receipts and some simple reports and scripts.  We import data from an online store.  When I built the database, I didn't know what I was doing and so the structure is not ideal, but it works for our simple needs.  I just lumped everything (contact info and order information) into one big table.  (Someday when I have time, I'm hoping to create a new structure and bring all the data back in, but not yet.) 

       

       

      One table was ok at the beginning because we had one product of one kind.  However, now our product offerings have expanded, so I created a new table for a new kind of product with 40 offerings.  I adapted my customer order layout to include fields from both tables, then realized I couldn't import data into two tables via the one form.  So I duplicated the layout and set the copy to show records from the new table.

       

      In summary, I have:

      Table POs (the original master table)

      Table Greeting Cards (new table)

      Layout POs (original layout showing records from Table POs)

      Layouts POs copy (new layout showing records from Table Greeting Cards)

       

       

      Both tables have a Customer ID field and a unique Order ID field.  The tables are linked via these fields and the layouts have the fields corresponding to the appropriate table. 

       

       

      I successfully imported data into the two Layouts, but I want all that information for the related records to show up in one view/layout. Can you help me? 

       

       

      Sorry for the long message!

       

       

      Pax,

      Sr. Miriam Esther

        • 1. Re: Help viewing data from related tables in one layout
          philmodjunk

          You'll need to explain that relationship in more detail. You shouldn't need to link the records by both fields. What we need to know is how both of those two fields, Customer ID and Order ID get a value.

           

          And after we get a clearer picture, our suggested solution is very likely to tell you that you need to make a separate customers table with only fields that describe that customer with only one record for each customer.

          • 2. Re: Help viewing data from related tables in one layout
            sisterme

            Hi Phil,

             

            Thanks for this input!  Ok, so the terrible thing about the current structure is that there is a new customer record for every order, even if the customer has ordered before.  The Customer ID comes from the online database, which is structured more sensibly.  But I could not use the Customer ID as the primary key in any case, because it comes from the online database (which obviously, is not synced with the local database) and we also receive mail orders( which are not being assigned an id online).

             

            The primary key for the original table is PO#.  A unique one is generated with each customer/order record.

             

            The Order ID is the unique order ID generated by the online database.

             

            When I export the orders from the online database into Excel, each order is on its own row and with different kinds of information in various columns.  So there is a column with the Order ID and a column with the Customer ID.

             

            I'm using the same spreadsheet to import into both layouts.

             

            Hope that makes sense!

             

            SME

            • 3. Re: Help viewing data from related tables in one layout
              philmodjunk
              The primary key for the original table is PO#.  A unique one is generated with each customer/order record.

              Then this is the only value that you should be using to link the two tables.

              • 4. Re: Help viewing data from related tables in one layout
                sisterme

                I did already try using that to link them, but I just realized that it was set as a Text field in one table and as a Number field in the other table.  Now I've fixed that, but the related information still is not showing up in either of my layouts.

                 

                Thank you for your patience!

                • 5. Re: Help viewing data from related tables in one layout
                  philmodjunk

                  Then the next step is to look as how you set up your layouts.

                   

                  On what table occurrence did you base the layout? Does that match to the correct "box" of the same name where you set up your relationship?

                   

                  What fields and from what table occurrences did you put on your layout?

                   

                  Did you try putting a portal to the related table on this layout to see what it would show?

                   

                  Have you checked to be absolutely sure that you have exactly the same number in these two fields so that they really do match?

                  1 of 1 people found this helpful
                  • 6. Re: Help viewing data from related tables in one layout
                    sisterme

                    Have you checked to be absolutely sure that you have exactly the same number in these two fields so that they really do match?

                     

                     

                    I don't understand how the flow of information works, so I'm confused about how we can use the PO# generated by the internal database to sync to imports coming from another database.  Right now, when the I import into the Greeting Card table, those records don't seem to be assigned any PO#.

                     

                    Here was my original thought.  I set up the relationship so that a record created in one table would create a record in the other table.  The PO# field in T_PO automatically assigns this unique serial number to a new record.  So I figured if I imported into the T_PO it would create a corresponding record in the other table which would have both the internal PO# and the unique Order ID via the related table.  It didn't seem to me like the internal PO would connect the records in the 2nd import, since it's not in the spreadsheet.  But I was hoping the unique Order ID would.  So, does it seem to you like I need a relationship based on both those fields?  Or I am totally scrambling the process?   ( :

                     

                    My layouts have contact info on the left and a tab control on the right.  Most of the tabs have fields from the original T_PO, but one tab was to display data from T_Greeting Card.  One Layout is set to show records from T_PO (is this what you mean by table occurrence?) and has fields for the internal PO#, Order ID (from online store) and Customer ID (from online store) fields all connected to the same T_PO.  The other layout is set to show records from T_Greeting Card and has the same fields connected to T_Greeting Card.  All the mapped fields import into the two layouts, but I just want to view all that data from one screen.

                     

                    Thank you for all your help!

                    • 7. Re: Help viewing data from related tables in one layout
                      sisterme

                      Reading through some other old posts and using the above logic, I was able to import the T_PO data into the new layout with the Greeting Card data via the matched field Order ID.

                       

                      I think my problem is resolved.  Thanks for your suggestions!