9 Replies Latest reply on Feb 2, 2012 11:50 AM by sixpointsteve

    Working with Portals and Self-joins

    sixpointsteve

      Haven't worked in Filemaker for several years (Version 7.0) Things have changed...

      I have a Sales Opportunty table through an ODBC connection to SQL server. I believe this creates a "Shadow Copy" if the SQL view in FMP. I have a view active opportunities which shows details of the opportunity in a form view. I have two tabs below with self-joins based on Company and Salesperson. The intent is you can see other opportunties related to the Company or Salesperson you are currently looking at. My goal is to be able to click on an Opportunity in either portal and have the active opportunities view "Go to the Related record". In reading the documentation that "go to related record' script step does not work in "portals". Years ago I would have written a script, get the primary key for the opportunity selected in the portal, put it in a global field, then perform a search for the opportunity number by adding the global value into the find script step. I see there are variables now, I tried using them but I am only able to get the value of the current record in the Active Opportunities view, not the selected record in the portal. Hope this makes sense to someone...Any assistance would be appreciated. Seems my Filemaker skills need an upgrade too.

        • 1. Re: Working with Portals and Self-joins
          Mike_Mitchell

          Steve -

           

          I use a similar technique all the time, and it works. I suspect something is amiss in your script. What exactly is your method for grabbing the key of the child record? Are you sure you're pointing it to the related field?

           

          Mike

          • 2. Re: Working with Portals and Self-joins
            sixpointsteve

            Thanks for responding.  My script steps are Set Selection (oppnumberjoin:Opportunity number) then I set the variable to oppnumjoin:OpportunityNumber, then goto layout Active opportunities. The set selection is my downfall.  My assumption is that would set the opp number to the opp number of the related record.   I check my variable by doing a show message step.  Should I use a different script step?

            • 3. Re: Working with Portals and Self-joins
              Mike_Mitchell

              Hey, Steve.

               

              I've never used Set Selection for this purpose. It may be causing the problem. Set Selection is generally used (at least in my experience) for selecting a block of text inside a text field. What I normally do in this situation (where I want to grab the key field of a related record) is simply use Set Field (for a global field) or Set Variable (for, well, a variable) equal to the related key field:

               

              Set Field [ gRelatedKey ; relatedTable::keyField ]

               

              or

               

              Set Variable [ $relatedKey ; relatedTable::keyField ]

               

              Since the cursor is sitting on the related record when this step is executed, I've found it reliably grabs the right related record. No frills necessary.    

               

              HTH

               

              Mike

              • 4. Re: Working with Portals and Self-joins
                sixpointsteve

                I looked at the Set Selection script step and it is not the correct step.  That is what I get working late at night or simply getting frustrated.  The issue I have with set field is I have a global G_OppNO field in my OPPs.fp7 file, however I cannot choose it to set it.  No need reply.  You have me started down the correct road.  Simply my error and getting back into the FMP swing...

                • 5. Re: Working with Portals and Self-joins
                  gcatnine

                  I do not use any script. Do that:

                   

                  The layout displays “Opportunity” table

                  The portal is related to “Opportunity” table 2 self-join with “Opportunity” table

                  Then define one of the field into the portal as button with “Go to related record” and  parameters:

                  • Go to related record from ““Opportunity” table 2
                  • Show record using <current layout>
                  • Show only related record > Match current record only

                   

                  The same for salesrep

                   

                  Gianandrea

                  • 6. Re: Working with Portals and Self-joins
                    sixpointsteve

                    Thanks.  Maybe the documentation is incorrect, but it states the Get Related Record script step does not work with portals.

                    • 7. Re: Working with Portals and Self-joins
                      gcatnine

                      filemaker documentation is quite poor. I always make a try

                      be sure to use table 2 in the go to related record parameter

                      • 8. Re: Working with Portals and Self-joins
                        sixpointsteve

                        Thanks!  Works like a champ. Beter than scripting.

                        • 9. Re: Working with Portals and Self-joins
                          sixpointsteve

                          One of the items I discovered, and believe was the real problem when using the Go To Related Record script step is that I had the button defined on the portal to the script to go to the opportunity from the Company tab.  I then copied the script, renamed and modified accordingly to work from the SalesPerson tab.  This fouled up the Company script and prevented it from working.  So I deleted the copied Salesperson script and created a new one from scratch to work on the Salesperson tab.  Voila.