11 Replies Latest reply on Apr 26, 2012 5:11 PM by DavidSmith

    Using a Portal to retreive data more than two hops away

    DavidSmith

      Title

      Using a Portal to retreive data more than two hops away

      Post

      We are trying to set up a Filemaker database controlling component usage/purchasing within a small manufacturing company. The “products” we sell are assembled from “components” and we have the following tables:

       

      “Orders” linked using a “many to many” relationship with “products” which in turn is linked with a “many to many” relationship with “components”.

       

      We want to create a portal that will show all the “orders” that require a certain component. We can easily set up a portal within a layout based on the "component" table that looks two hops away to "products" but can’t get the information we require that is more than two hops away.

       

      Is this something Filemaker can’t do or have we got the relationships wrong or something else. Any help would be gratefully received…many thanks in advance.

        • 1. Re: Using a Portal to retreive data more than two hops away
          philmodjunk

          We need to know more. Can you upload a screen shot of manage | database | relationships? (Please zoom and crop to remove unused white space).

          In particular, how did you implement the many to many relationships? This is best done by using "join" tables, but in Filemaker a return separated list of values or a repeating field could also be used--though I would not recommend that you do so in this case.

          • 2. Re: Using a Portal to retreive data more than two hops away
            DavidSmith

            Yes...sorry should have done that in first place...thanks for your help...our relationship graph is pretty complex so I have blanked out all the irelevant links...thanks again

            • 3. Re: Using a Portal to retreive data more than two hops away
              philmodjunk

              The achilles heel of such a "relationship chain" is that you need related records that correctly match values in the id fields at each point. If one record is missing or does not store the correct matching value, the chain is broken and you do not see the expected records in your portal.

              As a test, I defined 5 tables with the names and relationships shown in the attached image.

              The __pk fields are all auto-entered serial numbers. The _fk fields are all number fields.

              I carefully created 4 component records and three product records.

              Using value lists based on the __pk_Comp_ID field, I then build lists of components for each product using a portal to the Product_Component join table.

              I then used a portal to line items to create 3 order records with different products selected from a drop down list of __pk_ProductID values.

              When I place a portal to Orders on my Components layout, I see a list of all orders where at least one product containing that component was ordered in the line items table as expected.

              I used FileMaker 12 for this test, but would expect FileMaker 11 to produce identical results as there have been no changes in this area to the best of my somewhat limited knowledge...

              See any differences between what I did in my test and what you have in your database?

              • 4. Re: Using a Portal to retreive data more than two hops away
                philmodjunk

                Oops! forgot the screen shot.

                • 5. Re: Using a Portal to retreive data more than two hops away
                  DavidSmith

                  Many thanks for the reply...it's getting late here, but looks as though you have done exactly what we have! Possibally your opening para is the clue to what is going wrong our end. I need to check further in the morning...meantime many thanks for your help, it is much appreciated.

                  • 6. Re: Using a Portal to retreive data more than two hops away
                    DavidSmith

                    Thanks again “PhilModJunk” I think we understand now…we are getting what you have reproduced but it’s not what we wanted!

                     

                    Maybe Portals isn’t the way to do it, but what we ultimately want to show is how many of the particular component we are viewing has been used for a range of orders complete with an order by order breakdown of how that figure was arrived at. It’s all in the tables…just can’t get our heads round how to display it. For instance using your test example “line items” would contain the quantity of products for each order and “product_component” would have a “qty of component used per product” field.

                     

                    Any further help would be gratefully received

                    • 7. Re: Using a Portal to retreive data more than two hops away

                      When there is a series of links that don't use the same matching field but have the relation on another field you can use GTTR and list the particular records in question or, I just thought of this, fill in temporary id field so you can use a portal for that distant table. But then you'd have to clear it when you leave the record.

                      go to a layout of the last inline link

                      GTTR the next table

                      GTTR the next table using the found set

                      etc...

                       

                      Or, and this is just an inspiration, go to last inline link and set the field in the next table to be the same id number which now cretes an inline link.

                      • 8. Re: Using a Portal to retreive data more than two hops away
                        philmodjunk

                        This has become exactly like another post here in the thread. In that one their "components" were book titles that could be part of a "boxed set" listed in their line items table.

                        The only method I could think of that works reasonably well is to add one more table that creates a component break down for each line item record

                        LineItems::LIneItemID = LineItem_Component::LineItemID

                        You'd use a script performed by a script trigger each time the qty field in LineItems is saved. It would use the data in the related components and product tables to generate one record in this table for each component that makes up that line item and enters the correct Qty value (which can be a calculation when there is more than one component of the same item in the product.)

                        Then you can set up a summary report based on LineItem_Component that provides an order by order break down of components. You'd need some summary fields in this new table along with some sub summary parts to set it up, but it should work for what you want here.

                        • 9. Re: Using a Portal to retreive data more than two hops away
                          DavidSmith

                          Thanks again “PhilModJunk”...guess we have no choice but to go along the road you suggest. We didn't particularly want to do that for two reasons...firstly the existing tables are already populated with a load of data, so we will have to think of a way to get that into the extra table...and secondly (don't ask why, it's a long story!) the qty field in line items needs to remain editable for a period of time after it is created...so if it gets altered we have to find the existing entry in the new table and change that each time.

                          Have to say we are rather dissapointed that you can't draw all the data along in the manner we require...we thought that was exactly what Filemaker could do.

                          If you or anyone else has any further thoughts we would be gratefull to hear them...thanks again and for Jack Rodgers suggestion which unless we are missing something won't give us what we want either?

                          • 10. Re: Using a Portal to retreive data more than two hops away
                            philmodjunk

                            You understand the issues, I think. ONObjectSave is a trigger you can put on your quantity field to update/create the records needed. And once you have this working from the trigger, you can pull up all your  line items records and use a script some evening late at night to loop through all your existing line items perrforming the same script as a sub script of your looping script.

                            You'll also want to enable a delete option in the relationship so that deleting a line item record deletes all of the related records from this report table.

                            • 11. Re: Using a Portal to retreive data more than two hops away
                              DavidSmith

                              Yes as you say that has to be the way to go...thanks again