1 2 3 Previous Next 30 Replies Latest reply on Apr 16, 2016 10:32 AM by jeffmdm

    How to show all records in table linked to table with fewer records

    jeffmdm

      I have a table with 1800 records (all product skus, archived and current) that I have linked with a relationship (using sku as key) to another table (1300 records of current products only) that doesn't have a record for every sku.  When I use a layout to try to look at data from both tables together I am limited to the number of records (1300) in the second table.  How do I set it up so that I can see all 1800 records, including possibly blank fields from one table? 

        • 1. Re: How to show all records in table linked to table with fewer records
          Mike_Mitchell

          The only way I can think of offhand to do that would be to put a portal on the layout using a Cartesian join.

           

          Is there a reason you can't just use a status flag to indicate which products are current and use the "all records" table by itself?

          1 of 1 people found this helpful
          • 2. Re: How to show all records in table linked to table with fewer records
            BruceRobertson

            "When I use a layout to try to look at data from both tables together I am limited to the number of records (1300) in the second table"

            Not really.

            Be sure to base the layout one the first table (1800 record table).

            Display related data from the second table.

            1 of 1 people found this helpful
            • 3. Re: How to show all records in table linked to table with fewer records
              jeffmdm

              Yes, I forgot about portals, it's been a few years since I used FM, thanks

              • 5. Re: How to show all records in table linked to table with fewer records
                jeffmdm

                I realized after my first response that I didn't answer your question. The data on which products are current comes from an ecommerce marketplace, and the data on all products comes from an inventory system, so they are independent tables from independent sources.  Both tables will be re-uploaded periodically with new data to replace or add to the existing data so it seems to me that they should remain independent tables.

                • 6. Re: How to show all records in table linked to table with fewer records
                  Mike_Mitchell

                  Not so sure about that. If they were truly “independent”, you wouldn’t be asking how to search them simultaneously.  

                   

                  You can hold them in the same table, and process the imports in temporary tables. The ones that come from the marketplace get tagged as “current”, while the ones that come from the inventory don’t.

                   

                  A “product” is a single entity, regardless of where it comes from. Your data model should be based on entities, not necessarily on the workflow (although that can create exceptions to the norm).

                   

                  But nobody’s going to break down your door at 3:00 AM if you keep them separately.  

                  • 7. Re: How to show all records in table linked to table with fewer records
                    jeffmdm

                    So now I've had a chance to try your suggestion and read a bit about cartesian joins.  My helper book says cartesian joins are for when "All records in the table on the left are related to all records in the table on the right, regardless of the value in their key fields."  That's not the case for me, the records in my situation have a unique one to one relationship, it's just that records don't exist in the current table for all values in the "all" table.  I'm trying to end up with a layout where the column for the "current" table is simply blank if the product isn't current. 



                    • 8. Re: How to show all records in table linked to table with fewer records
                      erolst

                      Mike_Mitchell wrote:

                      But nobody’s going to break down your door at 3:00 AM if you keep them separately.  

                       

                       

                      Of course not – 4AM is generally agreed upon as the best time to ensure total shock & awe!

                      • 9. Re: How to show all records in table linked to table with fewer records
                        jeffmdm

                        Bruce, I think your suggestion is what I tried at first, but I'm not sure when you say "Display related data from the second table." if you are referring to using a portal, or if you are referring to the layout setup where you can pick another table and add a field of data.  The second approach is what I tried at first, and was surprised that it was limited to displaying only records in the "current" table, even though the layout was based on the larger "all" table.

                         

                        Could the problem be related to the choice made when linking the tables, there is something about allowing the creation of records in the linked tables?  I think I said no to that choice.  Does FM need to create a record in the smaller table with just the missing unique key column but no other data in order to make this work?  That would be fine with me if that's the case.  Can that be done if the smaller table is already populated before the link is established?  Or do I need to empty the smaller table and re-import the data to get FM to create the new records?

                        • 10. Re: How to show all records in table linked to table with fewer records

                          Check this out...

                           

                          SQL LEFT JOIN Keyword

                          The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

                           

                          SQL LEFT JOIN Keyword

                           

                          (FileMaker supports these.)

                           

                          Hope this helps.

                           

                          - m

                          • 11. Re: How to show all records in table linked to table with fewer records
                            Mike_Mitchell

                            That simply won’t work. If there is no record in the current table for a record in the other table, then there’s nothing to join to. You need to base the table on the larger group, or you need to use a portal. Or, alternatively, you could use an ExecuteSQL call to perform an outer join (which is really what you’re looking for) and display that information via a Virtual List or something like that.

                             

                            The reason a Cartesian join was suggested was because it will reveal records that don’t exist in the current table. It’s not about whether the current table has a match in the other table; it’s about reflecting the records from a table where there is no match.

                            • 12. Re: How to show all records in table linked to table with fewer records
                              jeffmdm

                              Here's why I think they're independent.  The inventory table of all products is just that, it's all the products I've ever sold on any marketplace, using a SKU as the unique key.  New products are added weekly.  The products are sold on different marketplaces, with different marketplaces having different mixes of products.  There is not data in the inventory that says which products are on which marketplaces.

                               

                              Then there is the "current" data I can download from the marketplace which includes the SKU.  The marketplace only contains and downloads the products currently listed.  And, this may be the insight needed to understand the problem better, sometimes the marketplace drops (removes) a product listing for various reasons and doesn't tell you. 

                               

                              So I may think that a product is current because I listed it and never removed it, but that may not be the case if the marketplace removed it.  So they are independent data sets that need to be viewed together so that I can detect when this has happened, and for other reasons not related to this specific problem.

                               

                              I think you are suggesting importing the "current" data into a field in my "all" table.  I didn't use this approach initially for two reasons.  The first is that I thought I didn't need to with a database, and the second is that from using FM years ago I remember making errors when importing data from different sources into one table if I didn't remember to point the incoming fields to the correct fields in the table.  The field titles for the same data use different names in their source systems.  It seemed less likely to create bad data in the table if the tables remained separate and were just linked. 

                               

                              Does any of this change your suggested approach?

                              • 13. Re: How to show all records in table linked to table with fewer records
                                Mike_Mitchell

                                Not really. What I suggested was using intermediate tables for preprocessing before you move the data to the main table. That’s a good practice anyway, in a lot of cases, just because it allows you to catch errors before they contaminate your main table. (You should probably do that even if you do keep them separate.)

                                 

                                If the marketplace drops a record, then part of your processing is to remove the “Current” stamp from the record. That can be done with a simple Find - any record in the table that doesn’t have a match in the marketplace import, but is marked “Current” - gets unflagged.

                                 

                                There doesn’t have to be data in inventory to indicate what’s not in the marketplace. If it’s not present in the marketplace import, then it’s not in the marketplace.  

                                 

                                You can do whatever you want. What we’re suggesting here is that you’re making your life more difficult by keeping two sets of books. What you’re trying to do - an outer join from a FileMaker layout - simply isn’t available. Bruce and I have given you at least a few ways to work around it, but it would just be easier to make each product record a product and be done with it.

                                • 14. Re: How to show all records in table linked to table with fewer records
                                  jeffmdm

                                  "You need to base the table on the larger group"

                                   

                                  I think I have based it on the larger group.  I'm starting with a layout based on the "all" table of 1800 records.  Then when I link it to the smaller table of 1300 records, I can only see 1300 of the 1800 records in the "all" table layout.  Am I missing your point, doing something wrong, or up against something that just doesn't work the way I thought it did?

                                  1 2 3 Previous Next