6 Replies Latest reply on Nov 30, 2011 6:48 PM by alas

    Filtering Portal to Reduce for Selected Items


      I am building a database where people have to select certain attributes for an item. For example, in the attached image, the user has to select geographic regions from the right and have them put into the portal on the left.


      Screen Shot 2011-11-27 at 10.21.16 AM.png

      They can click the arrow on the left of the right portal and it gets added to the empty line of the portal.


      I would like for a way to identify for a user if the item in the right portal has already been assigned to the left portal. One idea I had (preference) would be for the portal on the right side to reduce when an item is selected so it no longer appears on the right portal (unless the person subsequently clicks the X to delete the portal row on the left portal, thus removing that item from the selected list).


      Is there a way to do this?


      If not, is there a way, perhaps, to add an icon or X or something to the right portal rows when an item is selected so it can be identified as already included. I want to accomplish 2 things (1) avoid duplicates in the left portal and (2) help the user make sure they don't miss one without having to manually look at both lists and compare them).


      Thoughts greatly appreciated.




      Message was edited by: alas forgot to add the screen shot. here it is.

        • 1. Re: Filtering Portal to Reduce for Selected Items

          Any reason you're going right to left instead of left to right? That confused me.


          You don't say anything about which table occurences and relationships are involved, or how an item gets "moved", but you can use portal filtering to limit what shows in the righthand portal. A calculation like:


          isEmpty ( FilterValues ( List ( leftPortalTable::Field ) ; rightPortalTable::Field )


          should do it.

          • 2. Re: Filtering Portal to Reduce for Selected Items

            LOL.  Maybe it's my old Hebrew school lessons....   Actually, it's a long story but it's a design evolution that just worked out that way.


            As for the table occurence and relationships, I am using this approach for numerous different tables/occurence and relationships.  But with the example image I included above, this is what the TOs and relationships look like:


            Screen Shot 2011-11-27 at 10.23.26 PM.png

            The Geography for Portal is the list of geographies that appear in the portal (on the right).  On the GeographyNames layout, I have the portal on the left which shows the different region items that can apply to the particular name (from the GeographyRegions table).  I have a script that is running on the arrow button (on the right portal) which copies the ID for the geography item in the "Geography for Portal" portal row and copies it to the new row on the left portal and pastes it into the field of the GeographyRegions table. 


            So I am looking for a way to filter the "Geography for Portal" portal to remove the items that have already been copied to records in the GeographyRegions table (which are then showing in the portal on the left). 


            Does that make sense/help?

            • 3. Re: Filtering Portal to Reduce for Selected Items

              Hi Andrew,


              If you first make a key to all the records in the portal, and then extract the record from the key to the portallist

              it will remove that portal record from that portal.


              (in your case you want to do this in your script)


              Best regards,


              Ruben van den Boogaard

              Infomatics Software


              1 of 1 people found this helpful
              • 4. Re: Filtering Portal to Reduce for Selected Items

                You should be able to do it with the FilterValues calc I wrote about above.

                • 5. Re: Filtering Portal to Reduce for Selected Items

                  Hi Andrew,


                  There are a number of different approaches you could use to accomplish what you've described.


                  If you want a method that will work in all versions of FileMaker (since v7), you'll find a free downloadable demo of one of the options at the following link:




                  The example at the above link manages item assignations across multiple records, which if I understand correctly, goes beyond your current requirements - but it could easily be adapted to your needs.


                  However, if your solution is FIleMaker 11 only (ie will never be accessed by anyone using an earlier version), you could use the new Portal Filtering feature introduced in 11, as David has suggested, to remove items from your portal on the right. To do that, you should invoke the Portal Setup dialog for the portal on the right, and enable the checkbox option labelled "Filter portal records" and then enter a filter formula into the "Specify Calculation" dialog box that appears.


                  FWIW, if you are using the latter method, and assuming that the Idx field is your ID field in both tables, an alternative formula you could use for a v11 portal filter calculation would be:


                  Position(List(Geography for Portal::Idx); List_SMD_GeographyRegions::Idx; 1; 1) = 0





                  R J Cologon, Ph.D.

                  FileMaker Certified Developer

                  Author, FileMaker Pro 10 Bible

                  NightWing Enterprises, Melbourne, Australia



                  1 of 1 people found this helpful
                  • 6. Re: Filtering Portal to Reduce for Selected Items

                    Thanks to everyone.  Wanted to just give an update.  Took me a while to figure out how to make this work, but it seems I have solved my issue with all of your help. First, let me update what the TOs became. 


                    Screen Shot 2011-11-30 at 9.16.18 PM.png

                    Of course not elegant, but the TOs on the left reflect my "right portal" and the ones on the right reflect the two tables that I am relating to each other.  I have also moved to linking on ID fields instead of the actual names of the geographic regions. 


                    So here are the Portals...


                    Screen Shot 2011-11-30 at 9.12.04 PM.png


                    On the right, is the portal of all of the geographic regions.  On the left is the portal that shows, for this record, all of the applicable regions (the joining table).  When you click the arrow button, it calls a script  and  copies the region's ID to the box on the far left and the applicable name is displayed in the adjacent field, being pulled from the regions table. 


                    In the spotlight search box above the right portal, I had used a filtering technique so you could narrow down the list of regions being displayed.  I had difficulty, at first, in getting this to work with the suggestions above, so I started with just the suggestions, got that to finally work and then figuered out how to layer back in the spotlight searching. 


                    So the formula above, isEmpty ( FilterValues ( List ( leftPortalTable::Field ) ; rightPortalTable::Field ), ultimately worked. 


                    Screen Shot 2011-11-30 at 9.11.46 PM.png


                    I had an issue where the portal was not "updating" to reflect the removal of the "used" item.  I found, however, that if I clicked the "show all" button, it worked.  The Show All button caused my portal to reapply the filter.  So I added to my selection script a line that called the "refresh of portal filter" script, and that seemed to work.  I do notice a slight delay, but so far it's not a problem.


                    So I layered the two filter formulas into the portal filter, with an "and" command.  Had issues until I got the parenthesis right, but it looks like this:


                    Screen Shot 2011-11-30 at 9.11.27 PM.png

                    For some reason, it would not work if I reversed the two fornulas (did the removal then the spotlight).  I guess I don't fully understand the formulas, but when I got them in this order with the right groupings through parentheticals, it worked. 


                    I also had to update my "delete" script so that if I removed an item from the portal on the left, that it replaced it back into the portal on the right.  I had to use the same approach of having the script call the other script that refreshes the portal filter. 


                    If someone has a better solution for the "auto update" feature, I'd love to hear it.


                    As for some specific suggestions above, Ray, I love your suggestions.  I have been reviewing all of them, and have previously bought your v10 book.  Thanks for all of your help.  Your demo707 was the first thing I looked at on this problem.  I just could not figure out how to apply it when items from the right portal would be used on more than one record/portal on the right.


                    I had trouble with your Position formula.  Could you help explain what it does versus the one that Jondreau provided?


                    Ruben, I was having trouble figuring out how to adapt yours to copying the item from the right portal to a joining table/portal on the left, as opposed to a single field. 


                    So I hope this recap helps others, and if you gurus have any other suggestions of how to improve on what I ended up doing, I appreciate in advance your wise advice...