6 Replies Latest reply on Sep 20, 2016 3:06 PM by philmodjunk

    2 level-Self-join Portal Filter


      Hi all,


      I need to filter a portal using information from a table then filter that information again with a child table. ie. One of the portal (Parts) needs to show expired shelf life parts that are not quarantined. Shelf life is in one table and the location is in a child table. There are more than one portal on the layout and the layout is not linked to the Parts table.


      The structure looks like :

      Dashboard -> Parts -> Part_Transaction

      Dashboard -> ... -> ....


      I have tried all kind of things, but the second filter will not work.

      Thank you for your help!

        • 1. Re: 2 level-Self-join Portal Filter
          David Moyer


          edit:  I had to erase my reply - it was misguided.

          • 2. Re: 2 level-Self-join Portal Filter
            Johan Hedman

            If you are using a Dashboard I would use a Virtual Table and store my information there. Have FileMaker Server run a Scheduled Script every 5 minutes that delete all records and then create records.


            Here is a good example of how you can handle a Virtual Table

            Using the Virtual List Technique - Part 1 - Soliant Consulting

            • 3. Re: 2 level-Self-join Portal Filter

              Shelf life is in one table and the location is in a child table.


              Does that mean that shelf life is specified the parts table and location in the parts transaction table. That does not seem to make sense, but it's what seems to fit the details of your post. Perhaps you could name each table and show the relationship (including match fields) between them?


              And what is the relationship between Dashboard and Parts?

              • 4. Re: 2 level-Self-join Portal Filter

                The reason information specific to the all parts (Shelf Life) and their location being in separate tables sounds strange. I did not have a choice to make a table for the location and other details because of traceability requirements.

                Consumable parts of the same batch and same shelf life count as one Part ID, but they could be in different location : some in quarantine, some on a work site (that might come back). I need to know where and how many.

                As for parts with serial numbers they will be on the aircraft, in quarantine, overhauled, back in the store waiting to be installed back on the aircraft. This is mostly used by the person responsible of maintenance.


                Here are the table occurrences of the 3 tables used (Parts, CoPart, PartTransaction). No problem with grey.

                Table grah.png

                Portals are on DASH_Part.

                The problem I encounter is with filtering Quarantine. Also, because of traceability there are records in PartTransaction with quantity = 0. I have to filter out stock = 0 also.


                I hope this is clearer, I am very new to FileMaker.

                • 5. Re: 2 level-Self-join Portal Filter

                  Thank you, I had a look, and it sounds like a very good solution for reporting. I will need more time to figure everything out.

                  • 6. Re: 2 level-Self-join Portal Filter

                    I assume then that ShelfLife is a date and if that date is on or before today it is expired.

                    I will guess that IsQuarantine will have the value of True in PartTransaction if the item is in Quarantine. You'll need to adjust the following expression if this is not the case.


                    Make a relationship from Dashboard to partTransaction and then link an occurrence of PartTransaction to an occurrence of Part


                    You can then show all PartTransactions, but filter them down to just those that meet your criteria.