5 Replies Latest reply on Nov 3, 2011 10:37 AM by philmodjunk

    Show UN-related records in a portal?


      Show UN-related records in a portal?


      Hey all; I am having an issue with trying to get some records to display in a table.  Here's the brief description:

      3 tables of interest here:  USERS, SEARCHES, SEARCH_CRITERIA.  Many more exist, but I THINK that these are the only three needed.  :)  ERD included below.  The line disappearing down the left goes to the USERS table; SEARCHES has a FK from the USERS::USERID field.

      (Curious...I attached the image before checking if I could just paste it.  Then I couldn't go back and REMOVE the attached image.  Clicking in the field just brings up a File Picker screen, which won't go away unless you pick a valid file.  Ahhh...but then upon Previewing the post, the pasted clip isn't shown, but the attached one is.  Well, not sure which one will actually show up, so I will leave the attached one - even though I see an option to remove it now.  I suppose there is some instructional 'Read this before you post' thread around somewhere.  :) )

      SEARCHES:  fields SAVE and SHARE (both boolean flags) are of primary interest here.  SEARCHID and USERID used for relations.

      Layout: based on a USERS table

      Portal 1:  views SEARCHES, filtered to show current user's SAVED searches.

      Portal 2:  views SEARCHES, filtered to show ALL SHARED searches

      Portal 3:  views SEARCHES, filtered to show UNSAVED searches.

      I have two other portals:  one shows the currently selected search, the other shows CRITERIA line items of the search.


      Most things appear to be working correctly.  I was having some problems until I set up a table occurrence of SEARCHES, relating it to itself through the SLCTR_SEARCH field (a global for tracking which row/search is selected from the other portals).  (I.e SLCTR_SEARCH = SEARCHID).


      I am, however, having troubles getting the SHARED search portal to display what I like.  It will show searches with the SHARED flag for that current user, but I want it to display ALL shared searches, regardless of who the current user.  Is there a way to unwind or reinclude NON-related records?  It is my understanding that the portal is being filtered by related records of the current USER because the layout is based on that table.


      Thanks for any suggestions.

      -- J


        • 1. Re: Show UN-related records in a portal?

          What records appear in a portal are controlled by several factors. The primary factor is the relationship you define in Manage | Database | relationships. In this case the relationship for your portal is based on UserID_ID, so if you removed the portal filter expression, you'd see all related records with a User_ID matching that of the current user record on your layout. The filter then omits any of these related recorsd for which the filter expression does not evaluate as "True".

          If you don't want the User_ID to be a factor, you need to create a new relationship.

          You can double click a relationship line to open up a dialog where you can select different operators from the default = operator.

          If you set up a relationship like this:

          Users::anyfield X Searches::AnyField

          You will get all records in searches appearing in your portal. You can then add a filter to limit the displayed records from this basis instead of only those records with a matching User_ID.

          • 2. Re: Show UN-related records in a portal?

            Phil (may I call you Phil?  :)),

               That seems to have done the trick.  I can now see 'Share' flagged records in the portal, even from other users.  I changed the relationship from "USERS::User_ID = SEARCHES::User_ID" to "USERS::User_ID X SEARCHES::User_ID".  And it doesn't seem to have adversely affected my other portals either.  Nice.


            But I am not sure what other ramifications might be present by doing it that way that might affect other things that I am not aware.  I am not feeling like I grasp exactly how FM uses these relations to filter/relate things.  I get that I changed it to a cartesian product of the tables.  But will that change the foreign key aspect of SEARCHES::User_ID in some way?



            • 3. Re: Show UN-related records in a portal?

              If you try to use a portal based on that relationship to create new related records, you'll find that it does not work. Since you already know how to create additional table occurrences to produce more than one relationship between the same tables, this shouldn't be an issue.

              Cartesian Join relationships are a bit unusual. You can even define a temporary field in table 1, a temporary field in table 2, link them in a cartesian join and then go back and delete the definitions for these two temporary fields and the relationship will still work. That's because this relationship totally ignores the two fields specified to create the relationship.

              • 4. Re: Show UN-related records in a portal?

                Hmmm...I do have a script that creates a new Search and relies on the UserID coming from the layout (which based on the USERS table).  Can I have both relationships active at the same time and then expect it to function as it was when it was just an "=" relation?  And wasn't part of the point of a portal so that you didn't have to create new table occurrences all over the place?  (The table occurrence thing is still a bit confusing to me.  The one time I did use it it was mostly a guess.)

                I can kind of understand why the cartesian still works after deleting the fields, because it sets up a copy of each entire record related to each other entire record from the other table.  So it isn't relying on just that one field.  If I remember my edu-ma-cation correctly.




                • 5. Re: Show UN-related records in a portal?

                  Can I have both relationships active at the same time and then expect it to function as it was when it was just an "=" relation?

                  Each relationship has it's own table occurrence with a unique name. In scripts and calculations, you can refer to the table occurrence linked with = when you need that relationship, but your portal can only refer to one of the two table occurrence names in the "show related records from" drop down located inside portal setup. The one you choose specifies the relationship and thus it's behavior. If necessary, you can either have two portals, one to each occurrence if you need both functions, or you can create an "add" button that runs a script that creates a new record in the portal's table, but with the necessary key and filter fields initialized so that it appears in your portal. Then you can fill in the rest of the data directly in this new portal record.

                  And wasn't part of the point of a portal so that you didn't have to create new table occurrences all over the place?

                  Nope. Portals are used to make it easier to view and edit related data in a one to many relationship. Portal filtering, often reduces the need for extra occurrences, but not in this case unless you implement the scripted approach I've already described here.

                  The table occurrence thing is still a bit confusing to me.

                  If you haven't done so already, you might want to do the tutorial spelled out in this thread: Tutorial: What are Table Occurrences?

                  It should help you better understand what are table occurrences and how to use them--and this can be a key 'watershed moment' for you that opens whole new methods for getting FileMaker to do what you want it to.