11 Replies Latest reply on Aug 14, 2014 10:01 AM by SteveFransen_1

    Portal displays found set from the same table

    djtechwise

      Title

      Portal displays found set from the same table

      Post

      Hello Phil,

      I have read a few of your answer posts on the subject of getting a portal to display a found set. I must be really thick because I don't quite get it. Can you explain this process again please. I would like to display only found records from the current table (self-join) in a portal list regardless of what the find criteria is. Using version 11. Is this possible? Thanks a bunch -DJ

      Here is one of your previous answer posts;

      Use a script that captures the Primary keys of the current found set in a list and use the list in the portal's relationship. (Could also use a filter expression, but suspect that the relationship will update faster here.)

      Here's how the script might capture the primary key values:

      Freeze Window
      Go to Layout [//go to layout based on same table occurrence, but only place the primary key field in this layout]
      Copy All Records //copies primary keys to clip board
      Go To Layout [//Go to a layout where you've placed the global field: gPrimaryKeys]
      Paste [select ; YourTable::gPrimaryKeys]
      Go To Layout [original Layout]

      Now, if you use this relationship for your portal:

      YourTable::gPrimaryKeys = YourTable2::PrimaryKeyField

      Your portal will display the same records as are currently present in your found set.


      Are the primary key values specific to the particular find criteria? If not, I sounds like this may work. I just need help getting my head around it. What say you?

        • 1. Re: Portal displays found set from the same table
          philmodjunk

          Primary keys are fields where the value in the field uniquely identifies each record. 99.999% of the time in FileMaker, this is a field with an auto-entered serial number. Thus, a list of these values taken from your current found set will uniquely identify all the records in your found set and have nothing to do with the specific find criteria used to produce your found set.

          • 2. Re: Portal displays found set from the same table
            djtechwise

            Right, I guess I never thought of adding a serial number to each table without a specific reason. (or I never knew this as a reason) You probably add a serial number field to every table every time. I may adopt this mode. OK, so I need 3 layouts; 1 that has only the PK, one that has the gPrimaryKeys and the original layout the has the portal on it. All 3 layouts on the same table, right?

            thanks for your help Phil-DJ

            • 3. Re: Portal displays found set from the same table
              philmodjunk

              I do add a PK field, defined as a auto-etnered serial number to the core tables of my solution from the start. Many developers add them to each every table from the very beginning and this is not a bad way to go.

              I tend to add them on an "as needed" basis to the other tables that make up my solution. I can get away with that because adding a serial number field to a table in filemaker is a pretty simple thing to do unlike some other database systems.

              Add the field to your table and specify the serial number options.

              Add the field to a layout based on this table

              Select Show All Records

              Put the cursor in this field and use Replace Field Contents to add serial numbers, selecting the "update serial number in Entry Options" check box.

              Done.

              • 4. Re: Portal displays found set from the same table
                philmodjunk

                Sorry, somehow I missed the rest of your question: 

                OK, so I need 3 layouts; 1 that has only the PK, one that has the gPrimaryKeys and the original layout the has the portal on it. All 3 layouts on the same table, right?

                That will work, though the layout with the global field, gPrimaryKeys, can actually be based on any table in your system and it will still work due to the field being global.

                • 5. Re: Portal displays found set from the same table
                  djtechwise

                  That works great except...

                  The data that I need to show in this found set portal does not exist in the local table. A lot of the data shown on these records is displayed from other tables based on a different relationship but some is local. Does all the data need to be local in order for it to work with this PK found set technique? If so, it kinda defeats the whole benefit of relational data. I guess I could do a bunch of lookups to get the data on this table. Is there a better way? Maybe you have experienced what I am describing. -DJ

                  • 6. Re: Portal displays found set from the same table
                    philmodjunk

                    I can't go beyond generalities unless you spell out the details on what you are trying to show in your portal.

                    A portal can include fields from occurrences other than the one specified in portal setup | Show Records From. What data they display will be determined by the relationships you have defined in your database. If you add a field from an unrelated occurrence, you'll see an error indication when you return to browse mode.

                    • 7. Re: Portal displays found set from the same table
                      djtechwise

                      Specifically,

                      I'll describe two tables;

                      LeadTable (LT)

                      SoldJobTable (SJT)

                      My process creates a new record in SJT when the lead is elevated to a sale. Instead of using a look up to copy the customer info (name, address, ect), I display that data on the SJT layout from LT based on a relationship primary key called LeadNumber. Now if I do a find on the SJT layout, I would like to display that found set in a portal on the same SJT layout that displays the customer info along with other data the existing only in the SJT. Your prescribed technique works to show the found set in the portal but the data that does not exist native to SJT does display correctly. It shows the same iteration of whatever record I'm on in every portal row. Now the data that IS native to SJT, displays correctly (different values on each portal row). This leads me to believe that the data must exist in the SJT in order for this technique to work. Am I missing something here? -DJ

                      • 8. Re: Portal displays found set from the same table
                        philmodjunk

                        Begin off topic comment:

                        My process creates a new record in SJT when the lead is elevated to a sale.

                        Have you considered changing the status of the record from "lead" to "sold" or some such rather than moving the data to a different table? That can be a much simpler way to manage this in many cases.

                        End off topic comment

                        You appear to have this relationship:

                        LT::LeadNumber = SJT::LeadNumber

                        and presumably a relationship like this for your portal:

                        SJT::gLDNumbList = SJTfoundset::LeadNumber

                        Where SJTfoundset is an additional occurrence of the SJT table. If you add an occurence of LT, LTfoundset, you can set up this relationship:

                        SJTfoundset::LeadNumber = LTfoundset::LeadNumber

                        Now you can add fields from LTfoundset to your portal and they will correctly display the matching date from the LT table.

                        Can you give a more complete example of what you are trying to set up here and why you want the portal?

                        I keep coming back to the fact that you can display your found set in a list view layout and not have to add any extra scripting or relationships to show your found set and the related data....

                        • 9. Re: Portal displays found set from the same table
                          djtechwise

                          Oh, I figured it out,

                          Using a calc text field, I can put together the data there and use that on the portal. Since it is native to the table, it works fine in found set portal technique you showed me. Thanks for your help Phil. Your'e the best. -DJ

                          • 10. Re: Portal displays found set from the same table
                            tono

                                 I know I'm a little late, but for anyone reading this, I will note that the proposed script leaves all the primaryKeys in the clipboard. That is a bad practice (changing what the user had on the copy/paste clipboard) 

                                 What I did to fix this is to create a "tempClipboard" global field, in a unrelated 'systemTable'. In the script I added a 'paste' to this tempClipboard at the very begining, and a copy from the same field at the end. This way you preserve the clipboard the same after the script is done, and the user doesn't see a weird text with our primaryKeys that he has no idea why is the system pasting that.

                                  

                                 the script will end up something like this:

                            Freeze Window

                            //paste current clipboard

                            Go to Layout [//go to layout where I have 'tempClipboard' global field ]

                            Paste [ Select; 'tempClipboard']

                            //continue script 

                            Go to Layout [//go to layout based on same table occurrence, but only place the primary key field in this layout]
                            Copy All Records //copies primary keys to clip board
                            Go To Layout [//Go to a layout where you've placed the global field: gPrimaryKeys]
                            Paste [select ; YourTable::gPrimaryKeys]
                                  

                            //copy tempClipboard back to clipboard

                            Go to Layout [//go to layout where I have 'tempClipboard' global field (same as before) ]

                            Copy [Select; 'tempClipboard']

                            //return to original layout

                            Go To Layout [original Layout]

                            • 11. Re: Portal displays found set from the same table
                              SteveFransen_1

                                   This thread has been very helpful. Now I need a portal to display a found set from a different, related, table. I'd appreciate any suggestions if a portal isn't the best solution.

                                   I’m working on a medical record solution. Patients have Evaluations that consist of Elements. One Evaluation record is related to many Element records of different types. For example one Evaluation record may relate to an Element record of type “ChiefComplaint” and a second Element record of type “HistoryPresentIllness” and so on.

                                   Patient----<Evaluation----<Element

                                   Over time, the Element table will have many records since Patients have multiple Evaluations that consist of multiple Elements. For a given Evaluation, I need to rapidly sort and display a found set of Element records.

                                   The first find criteria will always be the Evaluation record to which the Element record is related. A second find within that found set may include criteria like Element type, who gathered the data contained in the Element record, and when they gathered the data. Multiple sort criteria are needed, similar to the find criteria. These find and sort criteria will change based on user input and scripts.

                                   Using Phil’s general script above, all of this appears to work as I step through the script with the debugger…

                                   Freeze Window

                                   Go to Layout [“ElementPrimaryKeys” (Element)]

                                   Copy All Records/Requests

                                   Go to Layout [“gPrimaryKeys” (SystemTable)]

                                   Paste [Select; SystemTable::gPrimaryKeys]

                                   However, when this step executes…

                                   Go to Layout [“Edit History” (Evaluation)]

                                   The portal contains all of the Element records even after doing a find on Element type that limits the found set of Element records to one type (e.g. “ChiefComplaint” or “HistoryPresentIllness”).

                                   I suspect my problem is here…

                                   

                                        Now, if you use this relationship for your portal:

                                   

                                        YourTable::gPrimaryKeys = YourTable2::PrimaryKeyField

                                   

                                        Your portal will display the same records as are currently present in your found set.

                                   My portal is set to display related records from the Element table on a layout based on the Evaluation table. The Edit Relationship dialog window for that relationship is set to…

                                   Table: Evaluation                                           Table: Element

                                   _pkEvaluation             =                _fkEvaluation

                                   Do I need to establish a relationship with SystemTable somewhere?

                                   I’ll take care of the clipboard issue noted above once I get the main issues resolved.

                                   Thanks!