7 Replies Latest reply on Mar 12, 2015 4:07 PM by philmodjunk

    portal which different values only

    Gilo

      Title

      portal which different values only

      Post

      Hi

       

      Is it possible to have a portal that only shows the different values without repeating them multiple times

      ex: I have a Field with these values:
      a
      a
      b
      a
      b
      c
      c
      d

      I need a portal which shows me:

      a
      b
      c
      d

      Can this be done? 

      p.s: I'm on a MacBook using Safari, and if I hit enter in my post, the cursor jumps to the first line. Is this normal?

        • 1. Re: portal which different values only
          philmodjunk

          Yes, it is possible. And yes there's a bug in Safari that will drive you crazy. For the Safari issue--which affects me when I use my iPhone to answer questions here, enter a space or other character and then back the cursor up to the left of it and now your returns will put the cursor where you would expect it.

          The following suggested solution assumes that you have a field with an auto-entered serial number or Get ( UUID ) function to assign unique identifiers to every record in the portal table. We normally call this field the table's "PrimaryKey".

          For a normal portal, you'd have this relationship:

          LayoutTable------<PortalTable    (-----< means "one to many")

          For your "unique values only" portal, we'll add another Tutorial: What are Table Occurrences? of the PortalTable named PortalTable|SameValue and link it to your original occurrence of PortalTable like this:

          PortalTable----<PortalTable|SameValue

          PortalTable::ValueField = PortalTable|SameValue::ValueField

          Where "valueField" is the field you show with the values a, b, c ...

          You still set up a portal to PortalTable, but now add a portal filter:

          PortalTable::__pkPrimaryKey = PortalTable|SameValue::__pkPrimaryKey

          For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: portal which different values only
            Gilo

            sorry i don't get it.

            I have a table documents, where i have doc_pai_my. This field has (a, b, c)

            The portal should appear in a table called "menu". 

            So i have a relation menu:key is not document:ID (should work as key and ID are not empty but are never equal)
            Then I make documents >------< documentsIselectionportal 
            where documents:doc_pai_my = documentsIselectionportal:doc_pai_my

            I get no result.... 

            • 3. Re: portal which different values only
              philmodjunk

              I left out a key detail. If your original relationship is this:

              LayoutTable::PrimaryKey = PortalTable::ForeignKey

              The relationship to the second occurrence of PortTable usually should include this field as a second pair of match fields:

              PortalTable::ForeignKey = PortalTable|SameValue::ForeignKey AND
              PortalTable::ValueField = PortalTable|SameValue::ValueField

              See this demo File to see this design in action. You'll see two portals side by side and the only difference in their design is the portal filter specified for the second portal: https://dl.dropboxusercontent.com/u/78737945/UniqueOnlyPortalDemo.fmp12

              • 4. Re: portal which different values only
                Gilo

                Thank i got it to work, but - there is always a but ;-) - as I have > 3000 records, which have to be filtered, it always takes a min to filter the portal.
                is there a solution for this?

                • 5. Re: portal which different values only
                  philmodjunk

                  Yes, don't use a filtered portal. Sounds like you need a different data model that will work the way you need without duplicates in the portal's table. I don't know what you have, how you have designed the overall system or how you need it to work for you so any suggestions, including the previous once may or may not work for you in the context of that larger picture.

                  Here's another approach that would require you to "retrofit" your existing data, but might work for you by not using a portal filter:
                  Add a field, FirstInstance, to your portal table that auto-enters the primary key field from the second occurrence of this same table. When you add a new, unique record, this field will be empty. When you add a record that is a duplicate, it will auto-enter the original field's primary key.

                  Now add a stored calculation field, cFirstInstanceFlag, with this expression: IsEmpty ( FirstInstance ) or ( FirstInstance = Primarykey ). Select number as the result type. This field will return a 1 if the field is empty or its primary key matches to the value in FirstInstance. To "retrofit" your existing data, set up a layout based on the portal table, put FirstInstance on it and use Replace Field Contents with the calculation option to copy over the value of the second occurrence's primary key. (That's why there's two terms in the cFirstInstanceFlag calculation.)

                  Now define this field in your parent table: constOne, a calculation with this expression: 1 so that it holds a 1 in every record in the table.

                  Now create this relationship from parent to a new, third occurrence of child:

                  Parent::PrimaryKey = Child 3::PrimaryKey AND
                  Parent::constOne = Child 3::cFirstInstanceFlag

                  The main draw back to this approach is that if you delete a record from the portal's table or edit the field that makes it a duplicate of others so that it is no longer part of that group of duplicate records, you have to find and update the value of FirstInstance for all affected records. This is something that can be scripted, but you have to make sure that you've handled every possible type of data change that keeps this from working.

                  And if there is any chance that you can set up and work from a table that simply doesn't have these duplicate, you would avoid all of these complications.

                  • 6. Re: portal which different values only
                    Gilo

                    Ohhh this is way above my capabilities... well then I have to use normal ValueList...

                    Thanx again, like many times already, for your great help and patience

                     

                    • 7. Re: portal which different values only
                      philmodjunk

                      First mention that you've made of a value list--which can automatically omit duplicate values when it's a "use values from field" type value list.

                      You might also consider Using ExecuteSQL() with the DISTINCT keyword as a way to list unique values if you don't intend to edit the data listed.