8 Replies Latest reply on Sep 14, 2014 12:18 PM by firebase

    Get Data from filtered Portal

    firebase

      Title

      Get Data from filtered Portal

      Post

      I have a portal that I filter, when I do:

      GetNthRecord(tbl::name; 2)

      I get a result that is not in the portal, because its filtered, but in the regular database.

      So actually i should get record row 3 instead of 3.

      Any ideas how to get the filtered result?

       

       

       

        • 1. Re: Get Data from filtered Portal
          philmodjunk

          GetNthRecord, as you have discovered uses the set of related records, not the set of records visible in the portal when it determines what record to access for data.

          Possible options:

          Set up an unfiltered portal that shows the same records as your filtered portal.

          Use ExecuteSQL in a query that reproduces the filter logic

          Use a script that finds this record on a different layout or in a different window by performing a find.

          • 2. Re: Get Data from filtered Portal
            firebase

            Thanx! I used SQL, i just thought therer would be a direct option or so.

            • 3. Re: Get Data from filtered Portal
              philmodjunk

              Come to think of it, there is and I am embarrassed to say that I used in a file that I am currently working on and didn't think of it.

              In a script, you can use go to portal row to put the focus on the Nth portal row and then you can access the values of any field in that portal record.

              Go to Object ["PortalObjectNameGoesHere"]
              Go to Portal Row [select; No Dialog; $N]

              • 4. Re: Get Data from filtered Portal
                firebase

                Great that worked out! After using the script step

                go to field

                its selecting the right field and then i used the function

                Get(ActiveFieldContents)

                to get the data of the field.

                • 5. Re: Get Data from filtered Portal
                  firebase

                  But that dosent work, because I need it in a calculation.

                  My SQL code also did not work, because it was way to complicated, so now i think about to prefilter that into another field that I read into the portal row.

                  • 6. Re: Get Data from filtered Portal
                    firebase

                    I found a faster way:

                    ExecuteSQL ("
                    SELECT  tbl_a.field1 FROM tbl_a
                    INNER JOIN  tbl_b ON tbl_b.field2 = tbl_a.field2
                    WHERE NOT EXISTS
                       (SELECT tbl_c.field3
                            FROM tbl_c
                            WHERE tbl_a.field3 = tbl_c.field3) and  tbl_b.field2 = ?
                    ORDER BY tbl_a.field1
                    FETCH FIRST 1 ROW ONLY
                       " ;"";""; tbl_b::field2  )

                    • 7. Re: Get Data from filtered Portal
                      philmodjunk

                      Go to Object ["PortalObjectNameGoesHere"]
                      Go to Portal Row [select; No Dialog; $N]
                      Set Field [ ParentTable::Field ; PortalTable::PortalField ]

                      Is what I used and it works just like I need. There's no need to put the focus in the field in order to access the value.

                      In my case, I used it to set up a slide control that shows images from the same records listed as those listed in the filtered portal by setting a match field to the portal record's Primary Key. It simulates scrolling sideways through a fully flexible number of images where moving to the next slide control panel brings up the image for the next record in the portal, highlights that portal row to show which record's image is visible and scrolls corresponding out of view portal rows into view if necessary.

                      This can be found in Adventures In FileMaking #2.

                      But not that was not a summary field. I'd have to test that to see if that worked.

                      • 8. Re: Get Data from filtered Portal
                        firebase

                        Btw. thanx for your 2. adventure, this is awsome! :)