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.
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:
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?
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)
Ruben van den Boogaard
Region.fp7.zip 6.8 K
You should be able to do it with the FilterValues calc I wrote about above.
1 of 1 people found this helpful
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
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.
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...
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.
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:
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...