14 Replies Latest reply on Feb 26, 2016 10:44 AM by BruceRobertson

    Complex join / portal, is this even possible?




      I am fairly new to filemaker, but have lots of development experience otherwise. The first issue I ran into which was frustrating was not being able to create a "Circular" reference. I have the following table layout:




      So the scope of what I need done. You enter a company layout, then choose a building, then choose / create a new inspection. Once on the inspection layout. I want to list all of the dampers that are assigned to the building that the inspection is for. So On my inspections layout I have a portal that links to the dampers. (This is because I do not want the client to have to add the dampers to each new inspection as they are done semi-regularly). Ideally, from I wanted to link inspection_dampers to dampers but this isn't allowed as it creates a loop. Instead I had to create a separate TO for dampers 2. So in my portal when I pull in my dampers it has no reference to the damper_id  inside of inspection_dampers and is only returning the first record for each damper.


      Here is my portal layout:



      The only way I can think of off the top of my head to get around this is to create an inspection_dampers record for each damper when the inspection record is created, but that will mean if they add a damper after the fact I have to add functionality to add that damper to the inspection.


      Any thoughts or advice, do I need to rework my thinking on this project?

        • 1. Re: Complex join / portal, is this even possible?

          Paradigm shift in progress.... welcome to FM!

          You should not think of the relationships graph as an ERD.

          Perhaps a better perspective is to think of it as a graphical query engine where Table occurences are similar to views.

          Don't be afraid of multiple TOS' on the graph for any given table.

          Because almost everything in FileMaker is tied to context this shift in thinking is important.

          Google relationship graph filemaker and ready the pdf by Ray Cologon of Nightwing....

          • 2. Re: Complex join / portal, is this even possible?

            Thanks, for the reply, I will definitely look into that pdf. I have tried working with multiple TO's. It always seems to get me part of the way there.


            I think my problem is the need to show all dampers for the building. It makes it so I end up not having the final piece. If I use dampers 2 as my portal source instead of dampers, I basically get what I want, except that it only lists dampers that have records in the inspection_results, but I need to list all of them for the building. So I'm leaning towards pre-populating that table with data when the inspection is created. Hopefully I'll find something useful in that pdf.

            • 3. Re: Complex join / portal, is this even possible?

              dont just limit yourself to the pdf as there ae some other good sources if you look.

              • 4. Re: Complex join / portal, is this even possible?

                Hi Jason


                What if you select dampers has the table for your portal on the inspection - VIEW layout? If I follow the graph the inspections_dampers and dampers tables are already linked.

                That's why you got the circular reference message.


                On a side note, you might want to try the anchor buoy model of organising your relational graph. It might help you be clearer on exactly which relationships are available on which layout. See this blog post among others: What Is FileMaker Anchor / Buoy Relational Design? — thePRACTICALba


                Hope that helps and please tell me if I'm completely out



                • 5. Re: Complex join / portal, is this even possible?

                  So, these post are helpful for an overall best practices. However, they don't seem to have answers that meet the needs that I'm looking for and I don't think it's an organizational issue, but maybe I'm wrong, and maybe what I'm trying to do isn't possible and I just need to go one more level deeper into a layout. (or generate all the inspection_dampers records when I create the inspection initially)


                  I will probably switch to the anchor buoy model, however. I don't think that's the issue as I've tried small scale tests of that model and I run into the same problem. My main issue is that there doesn't seem to be a way to relate dampers and dampers 2 because my inspection_dampers::damper_id is related to dampers 2::id, not dampers::id so when I pull in my inspection_dampers results, trying to show them on the same portal for my dampers it only shows the first record in the database for that inspection for all of my dampers. Is there a way to pass dampers::id to the dampers 2::id reference? It just doesn't seem like it's possible with the way filemaker looped relationships


                  I've been searching for several hours trying to find a solution, and while I can find information about join tables, I haven't found anything that fits my need of needing to pull in all the dampers for the building, regardless of whether they have been inspected or not and also listing the inspection results in that same portal. I have tried going the other way and building relationships of the dampers TO, but then it doesn't know what the inspections_dampers::inspection_id is set to and pulls the results from the first row in the inspections_dampers table regardless of which inspection you are looking at.

                  • 6. Re: Complex join / portal, is this even possible?

                    What you can do maybe is make id_building appear in inspection_dampers and link inspection_dampers and dampers 2 using id_building.

                    In the inspection_dampers table this new id_building field would be a calculation equals to inspections::id_building.


                    Would this work?

                    • 7. Re: Complex join / portal, is this even possible?

                      Something to consider.


                      Layout based on Inspections2 related to Inspection_dampers2 and Dampers2. Use fields in the portal that are from the same "2" tables as the relationship.

                      • 8. Re: Complex join / portal, is this even possible?

                        To emphasize the thought use a "3" and completely separate those TO relationships from everything else in the graph to test the portal function.

                        • 9. Re: Complex join / portal, is this even possible?

                          Cfons, Not sure what that is going to do, it still doesn't let my inspections_dampers table know what the value of dampers::id is to match it to it's inspection_dampers::damper_id since I can't link them together do to it creating a loop. I'm pretty certain at this point, especially based on these answers, that I'm going to have to make my portal load from Inspection_dampers and write a script to create a record for each damper upon creation of inspection. This is not ideal because it doesn't allow easily adding dampers to the inspection after the fact.




                          I pretty much have a good grasp on how the relationships work in this, and unfortunately I believe my project has a requirement that cannot be met in the way I hoped due to the lack of being able to create  a 3 way relationship because of looping.

                          • 10. Re: Complex join / portal, is this even possible?

                            Portal based on Inspection_dampers but add fields from Dampers2 to the portal.


                            Dampers and Dampers2 have the exact same data.

                            • 11. Re: Complex join / portal, is this even possible?

                              I understand they have the same data, the issue isn't my understanding of the way the table occurrences work. The issue with having my portal based on inspection_dampers is it will only pull in dampers that have already been inspected, and I need it to pull them in even if they haven't been inspected. Which means I have to manually add them to the inspection somehow. These inspections occur every couple of months and I don't want my client to have to add all of the dampers to the inspection each time they start a new one, this is why I was using the dampers table for the portal. However, since there doesn't seem to be a way for me to pass dampers::id to my inspection_dampers::damper_id since they are not related I'll probably have to go with my idea of auto creating the records when a new inspection is started.

                              • 12. Re: Complex join / portal, is this even possible?

                                Not the best idea but a Cartesian join of inspection_dampers and dampers2 with a filtered portal.


                                You need more TOs and maybe one extra field, but you don't seem to want to use them.

                                • 13. Re: Complex join / portal, is this even possible?

                                  IT's not that I don't want to use them. I have tried all of the suggestions that you have been suggested and I still end up with the same problem. I load my portal off my dampers, by the time I get to my inspections dampers relationship there is no relationship to the damper id from my portal so it doesn't load the correct records. I don't see how setting up additional TO's is going to change that, the only way I can see that your solutions would work is if I used the dampers_inspections for my portal, which then I lose any dampers that haven't already been inspected from my portal.

                                  • 14. Re: Complex join / portal, is this even possible?

                                    " I don't see how setting up additional TO's is going to change that"

                                    Nevertheless; they will.

                                    Get some training; or think again about the problem more openly.

                                    Or perhaps hire somebody to do this part of the design for you.

                                    This is an easily solved problem.