7 Replies Latest reply on Feb 21, 2011 12:13 PM by johnhorner

    Showing records that are not related

    yuichim

      Title

      Showing records that are not related

      Post

      Hi,

      I was wondering if there was a way to show all records that are not related in the portal.

      Basically I am creating a company directory from Users table and wanted to show all user records who are in "Active" state.
      Would that be possible?

        • 1. Re: Showing records that are not related
          johnhorner

          in general to show "unrelated" records you actually set up a relationship and select the "x" for the operator (instead of "=" or ">" for example) and for this particular operator i don't think it matters which two fields you use to define the relationship because it will relate all records from the table on the right ("child" or "related") side of the relationship.

          for your particular request, however, you don't actually want all the records but only the records marked as "active".  this can be done using a conventional relationship.  you will need a global field that is set to "active" or "1" or whatever value you are using to designate "active" status in the user records, and then set up a relationship such that the global active field = user active field.

          • 2. Re: Showing records that are not related
            philmodjunk

            Yes and no.

            Yes you can show all "active" records, but no, they won't be unrelated. A portal must be defined with a relationship linking two table occurrences on your graph. The relationship and (in FileMaker 11) any portal filter you define for it will control what records are visible in the portal.

            Here are two approaches that work:

            Option 1: (FileMaker 11 only)

            Define a relationship that matches to all Users records, but filters by the value "active" in a status field in Users:

            MainTable::SomeField X Users::SomeField

            Use this portal relationship:  Users::Status = "active".

            Option 2 (works with earlier versions of FileMaker)

            Define a calculation field, cActiveKey, in your "main" table to simply return the text "Active".
            MainTable::cActiveKey = Users::Status

            With either method, a portal to Users on a layout based on "Main" will list your active users.

            • 3. Re: Showing records that are not related
              johnhorner

              philmodjunk, i had posted an answer to this questino using a global field with a fixed matching value ("active" or "1" etc, depending) as that is how i have always done it.  i know, however, that you are a much more experienced user than i am so i am assuming that your option 2 is preferable to the method i have been using?  is that correct?  is your calculation field a global field?  can you explain the reasoning for that choice as well as the choice for a calculation vs. a fixed value?  what about an auto-enter calculation?  just curious... i am worried i have been doing it wrong and curious to learn the best approach.

              • 4. Re: Showing records that are not related
                philmodjunk

                Johnhorner, though the post times may not look like it, we responded at the same time (I got called away from my desk mid post).

                No major differences here. My example with the calculation (Which does not need to be global in most cases) simply "hard wires" the portal to only and always show the "active" records. With a global field, you can place it on the layout with a value list and let the user select different options for what will appear in the portal.

                • 5. Re: Showing records that are not related
                  johnhorner

                  philmodjunk, thanks!  it seems painfully obvious now in retrospect, but for ages i kept a list of my global fields so that everytime i had to make an empty clone of my database (i share it with a couple other users) i manually had to go back and enter in all the values... of course a calculation field makes sense... thanks again!

                  • 6. Re: Showing records that are not related
                    yuichim

                    Thanks for your help guys.  I am still new to FM and am getting used to creating a field for relationship.   What I ended up doing is that since the Users table contains their phone extensions and stuff, I ended up creating a field called xDirectory and create a self relationship.

                    Users::xDirectory --- Users2::xDirectory  and specified a portal filter calculation  Users::status="Active"

                    This worked :)

                    Thanks again for both of your help!!

                    • 7. Re: Showing records that are not related
                      johnhorner

                      i am glad you got it working.  to use that technique, however, you do not need to create an extra field (assuming that you are setting up the relationship to match all using the "x" operator).  it does not even need to be the same field on both sides of the relationship.  your relationship could be:  Users::PhoneNumber "x" Users2::LastName, for example, and it will still match all the records and filter as desired.  Unless you are using the xDirectory field for some other purpose, you are probably better off without it for simplicity's sake.