10 Replies Latest reply on Aug 28, 2013 7:27 AM by jormond

    New Filtered Portal refresh method?

    darrenburgess

      I THINK I have happened on a new way to refresh a filtered portal. I had a script that created a related record and the customer reported that the related record was not showing in the portal. The method for creating the related record was to set the foreign key across the relationship.

       

      This is the portal filter:

      People_EVENTS::description ≠ "HAS NO NEXT ACTION"

       

      This is what DIDNT work:

       

      This worked:

      • Add an additional calc to the filter: People_EVENTS::description ≠ "HAS NO NEXT ACTION" and 1=1
      • Commit the parent record after creation of the related record

       

      QUESTIONS

      • What could cause failure of the typical methods for portal refresh
      • Why does this "new" method work?
        • 1. Re: New Filtered Portal refresh method?
          DavidJondreau

          Are you sure the relationship to Events is valid? Recent versions of FileMaker won't match relationships on null values.

           

          Otherwise, it's likely the Commit[].

           

          I think you need a commit before the Refresh Window[ Flush cache ] and maybe before the Cartesian refresh.

           

          Changing modes causes a commit, but not necessarily a cache flush. I don't think that should have mattered with the way your filter is set up,unless you're not using FMP 12, but 11.

           

          Also, check out this: https://fmdev.filemaker.com/thread/71995. Bruce and I have been playing around with ideas on portal filtering here. It seems like the not equals =/= operator is a little funky with portal filtering, but I'm still looking into that.

          • 2. Re: New Filtered Portal refresh method?

            DavidJondreau wrote:

             

            Recent versions of FileMaker won't match relationships on null values.

            Hi David,

             

            I just wanted to clarify that we can indeed relate to empty values in version 11 and 12 (not sure about 10) if we use non-equijoin and there are three instances it can come in quite handy.  Note that I am using the ParentID simply because it is predictable, will relate properly for this need, and can possibly eliminate the need for an additional field.

             

            To find both empty or 0 boolean

            Parent::ParentID > Child::boolean (must match data type)

             

            Of course you can always create a calc of 0 and thus eliminate the 0 from the boolean as well.

             

            To find empty date

            A parent field with global DATE of 1 (must match data type)

             

            Parent::cGenesisDate > Child::date

             

            To find empty text

            Parent::ParentID > Child::text (you do not need to match data type)

             

            If a number exists in the text field then the record will match so only use it when you KNOW you will never have numbers.  It works well with value lists, such as Gender (M or F).  The first two examples can work in conjunction with the original parent/child relationship in multikey because you are matching data types:

             

            So in first example, it can be:

             

            Parent::ParentID = Child::ParentID

            AND

            Parent::ParentID > Child::boolean

             

            The example above (To find empty text) will fail if you add the numeric primary/foreign keys. Of course the best approach would depend upon fields available and specific need and it wouldn't be widely used but it can sure come in handy at times.  :-)

             

            Corrected:  It works when adding primary keys in third example as well ... not sure what I was thinking.

            • 3. Re: New Filtered Portal refresh method?
              darrenburgess

              David

               

              I have no question that the relationship is valid.  primary and foreign keys are set.  When I get a chance i will see if the problem is the ≠ based filter. 

               

              Darren Burgess

              MightyData

              • 4. Re: New Filtered Portal refresh method?

                darrenburgess wrote:

                 

                This worked:

                • Add an additional calc to the filter: People_EVENTS::description  ≠  "HAS NO NEXT ACTION" and 1=1
                • Commit the parent record after creation of the related record

                 

                QUESTIONS

                • What could cause failure of the typical methods for portal refresh
                • Why does this "new" method work?

                 

                Hi Darren,

                 

                I believe that committing the parent record after creating the child WOULD commit the children as well, accomplishing what David is suggesting ... that a commit is probably necessary on the child side.  But it still may not refresh without help - more so if you are using separation.

                 

                Why adding the 1=1 works?  It is a separate evaluation (because of the AND) and since it does not require the newly created record, it can process thus forcing update?  It is interesting!! 

                • 5. Re: New Filtered Portal refresh method?
                  darrenburgess

                  Well TRUE in place of 1=1 works as well.

                   

                  Note that I am creating the related record from the parent context by setting the Foreign Key across the relationship.  This may be part of the reason that the portal filtering is confounded.

                   

                  I need to experiment some more and see if I can narrow the cause.

                   

                  In addition, I want to look at creating the child record in a new window and seeing if my {portal filter}AND TRUE plus a scripted commit would work to refresh the portal.

                  • 6. Re: New Filtered Portal refresh method?
                    jormond

                    Darren,

                    Can you whip up a quick sample?  I've tried this as you mention in the beginning...but mine shows up as soon as the record is committed. So I must be missing something specific to replicated the refresh problem.

                    • 7. Re: New Filtered Portal refresh method?
                      darrenburgess

                      I have been attempting to do replicate this in a simple file, rather than the customer solution.  I can't replicate either.  Stay tuned….

                      • 8. Re: New Filtered Portal refresh method?
                        darrenburgess

                        Here is the sample file. 

                         

                        I replicated the child record create method I am using in the customer solution here.  Unfortunately, I cannot replicate the issue in the sample file.  A simple commit will refresh the filtered portal in the sample file.

                         

                        In the customer solution, I had to modifiy the portal filter with the "and TRUE" in order for the commit to refresh the portal.

                         

                        Mysterious.

                        • 9. Re: New Filtered Portal refresh method?
                          DanielShanahan

                          darrenburgess wrote:

                           

                          Here is the sample file. 

                           

                          I replicated the child record create method I am using in the customer solution here.  Unfortunately, I cannot replicate the issue in the sample file.  A simple commit will refresh the filtered portal in the sample file.

                           

                          In the customer solution, I had to modifiy the portal filter with the "and TRUE" in order for the commit to refresh the portal.

                           

                          Mysterious.

                          Is the customer on a hosted file?

                          • 10. Re: New Filtered Portal refresh method?
                            jormond

                            That was the same thing I was seeing Darren.  I remember back in the beginning of my FileMaker learning...running into a number of instances where I was having trouble getting calcs/portals to refresh. Having a better understanding of databases in general, I don't run into it often...and thus don't remember what I was doing then that caused the problem. I vaguely think I was running into context issues. Same table, but either the script, portal or actual referenced field was from a different TO.  That seemed to cause a delay in the update to the local cache file.  One that I had to address by causing an update on the foreign TO, and then refreshing the window of the parent record.