10 Replies Latest reply on Dec 14, 2016 5:39 PM by DavidMenlow

    Selecting a specific portal row number and displaying data from a specific field

    DavidMenlow

      I have portal data that has many hundreds of rows of information.  I would like to be able to enter a portal row number in a new field and then have the information of an existing data field from that portal display in another existing field.  It's a simple display, which I will then apply some math functions as summary fields.  Just not sure where to go with this.

       

      Portal Row Number (manually input)
      Portal Data
      47.3%
      78.9%
      115.1%
      126.8%
        • 1. Re: Selecting a specific portal row number and displaying data from a specific field
          philmodjunk

          Not a simple thing to do, but possible. What you seem to show here is a second portal listing information from specific records from the first portal.

           

          Entering a row number is possible, but seems less than ideal. You'd have to determine which number corresponds to a specific row and then you'd need to enter it. Putting a button in the portal row that selects it for your display might be quicker and is easier to implement.

           

          With either approach (button or manually entered row number), you might set up this relationship:

           

          LayoutTable::GlobalTextField = PortalTable 2::PrimaryKey

           

          And base your second portal on this relationship.

           

          Scripts can add/remove the primary key of a selected portal record from the return separated list of values to make it appear in/disappear from the newly added portal.

           

          Script to add/remove a record's ID from the list (run from button in the portal row):

          IF [ IsEmpty ( FilterValues ( PortalTable::PrimaryKey ; LayoutTable::GlobalTextField ) ) // not selected ]

              Set Field [LayoutTable::GlobalTextField ; List ( LayoutTable::GlobalTextField ; PortalTable::primaryKey ) ]
          Else

              #Selected

              Set Field [ LayoutTable::GlobalTextField ;

                      Let ( [ TheList = ¶ & GlobalTextField & ¶ ;

                                 TheList = Substitute ( TheList ; ¶ & PortalTable::PrimaryKey & ¶ ; ¶ ) ] ;

                                 Middle ( TheList ; 2 ; Length ( TheList ) -2 )

                             ) ]

          End IF

           

          If you prefer to manually enter a row number, you'd need to modify the above to use Go to Object and Go to Portal row to put the focus on the correct portal row before performing the above steps.

          • 2. Re: Selecting a specific portal row number and displaying data from a specific field
            DavidMenlow

            Phil,

            You make it very obvious that I need to spend a lot of time getting tutored on scripting.

            I have enclosed a screenshot of what I'm looking to accomplish.Screen Shot 2016-12-13 at 3.00.57 PM.jpg

            I use 5 portals for these different headings.  What I would like to do is to widen the layout you see above and insert a series of 5 number fields for each of this 5 headings.  Obviously, this is for a stock program I have created. So in a vertical series of separate number fields, I will visually pick the portal rows I need to pull that information.  I already have the portal rows numbered, so counting won't be a problem.  If I want the 2 data fields from the Underwriter portal, I might select rows, 1, 3, 4, 7, 10.  I will manually enter those numbers.  There are many data fields in each portal that I have done conditional formatting with.  This file covers 4 24-inch screens so the coloring creates visual pockets that help me identify which portal rows I wish to use.

             

            Going back to the 5 numbers I selected for this example.  I want to then have the results of those row selections to give me the results in the percentage field for the Underwriter portal and of course the results in the dollar field.  I will most likely use a different set of portal row numbers for each of the other 4 segments.  The box at the bottom will be a calculation field to give me the average of the percentage numbers in each segment.

             

            I don't know if this helps, but what you wrote for me is above my skill set.  I was hoping you could dumb it down for me, recognizing that I have to manually fill in the individual portal row numbers for each set.

             

            Thanks so much for your continued dedication to helping us with this amazing application.

             

            Best,

            David

            • 3. Re: Selecting a specific portal row number and displaying data from a specific field
              philmodjunk

              But wouldn't it be simpler to click a portal row to select it than to manually enter a number?

               

              Do your "portal row numbers" uniquely identify the portal record or do they simply identify the record's current position in the portal?

              • 4. Re: Selecting a specific portal row number and displaying data from a specific field
                DavidMenlow

                Phil,

                 

                Clicking a portal row is obviously faster and more reliable.  And you are right, the numbering is just a current position using @@.  I understand that as new data gets added, all the data will shift, since the sort in all portals is date descending.  But when I need to make a decision, I am assured that the data will remain static for the balance of the day.  This is time-sensitive data, so everything happens within an hour each night to make a financial decision.  I already use a button to go to the related value in the related file. That's the double arrow button on the right of the portal number.  The portals generally contain many hundreds of rows.  I know that is very simplistic, but that is used just do a deep dive in the file, when necessary.

                 

                Here is a screenshot of a small portion the portal from one window.

                Screen Shot 2016-12-13 at 8.13.49 PM.jpg

                So if I want row 4 from the top portal, I would want the 2 far right information sets.  The first would be the Underwriter::Opening Percentage and the other would be Underwriter::Opening Points.  Obviously I would want to repeat this with other portals, using 5 different rows in each portal.

                 

                Hope this makes it clearer.

                • 5. Re: Selecting a specific portal row number and displaying data from a specific field
                  philmodjunk

                  Your answer confirms that it would work to add another button to your portal row for making these selections.

                  I am assured that the data will remain static for the balance of the day.

                  That makes no difference. The problem is that a script can't always use the row number to directly access the data from a portal if the portal is filtered. Is the portal filtered? If so, as I posted earlier, a script would need to use go to object to select the correct portal and then go to portal row to get to the portal row. But if the portal is not filtered, you could enter a row number and the GetNthRecord function could be used to get to the primary key of that record.

                   

                  But it seems to me that clicking a button in the portal row makes for a simpler method that also is faster and less prone to error. But it's your solution to use, not mine. A button in the portal row could run the script that I posted earlier. Please note that the button could change appearance when you click it to add it to the portal and a second click would return the button's appearance to it's original look and remove the row's data from the portal.

                   

                  I started to do a simple demo of the file and then recalled that you used @@ to get the record number. This is not the layout symbol used in recent versions of FileMaker. What version are you using?

                  • 6. Re: Selecting a specific portal row number and displaying data from a specific field
                    DavidMenlow

                    My apologies, I must have had a trip down memory lane with what I used to use for adding numbers for non-portal items.  I am using {{Record Number}} in the portal.  Plus I am using 15.0.2.220.  And finally, none of the portals are filtered.

                    • 7. Re: Selecting a specific portal row number and displaying data from a specific field
                      philmodjunk

                      Here is the demo file. Note that you can click a button or enter a row number into a text field. The OnObjectSave script trigger will take the row number and do what is needed to make the "selected" portal update to add or remove that item from its list. You can also click a button in the portal row to do the same.

                       

                      With either method, a png image of a tick mark, located on top of the portal row button will appear or disappear to confirm that the portal row record is or isn't selected in the selected items portal. Hide Object When controls whether or not it is visible in a given portal row.

                      • 8. Re: Selecting a specific portal row number and displaying data from a specific field
                        DavidMenlow

                        Phil,

                        There was no file attached to this email, but additionally, I am unable to access the website to see if the file may be available there.  I’m getting a NET::ERR_CERT_AUTHORITY_INVALID notice which is preventing all my browsers from going any further.  I am located in Central NJ, if that helps.

                        David

                        • 9. Re: Selecting a specific portal row number and displaying data from a specific field
                          philmodjunk

                          File Attachments are only visible and accessible if you open this thread in a web browser. You won't see them in your email.

                           

                          Use Safari, FireFox or Chrome to access the forum and see if you can then find and download the file. Once you get the file downloaded, you are welcome to ask questions about any part of the demo, but I do ask that you look up any unfamiliar functions in FileMaker Help first.

                          • 10. Re: Selecting a specific portal row number and displaying data from a specific field
                            DavidMenlow

                            I’m unable to get access with either of those 3 browsers, plus Opera.  I’ll have to wait it out until the issue is resolved some how.