13 Replies Latest reply on Aug 29, 2014 6:12 PM by RichNasser

    Cartesian Join Portal Not Filtering Correctly

    RichNasser

      Title

      Cartesian Join Portal Not Filtering Correctly

      Post

           Hi:

           I have a cartesian join to TASKS and EMPLOYEES - in my TASK layout I want a simple portal that shows all the employees that are not assigned that task.  The program knows which employee is currently assigned the task via a pop-up that is connected to a global field (and I know which empID it is) - so when I try to filter saying don't show in the portal the person with empID x it still shows in the portal see  screenshot.

            

           I've checked to make sure my stuff is comparing numbers to numbers etc.  I'm thinking that because it's a Cartesian join, it doesn't like something

            

           In the screenshot the pop-up with the name Peter on the left is that the one I am trying to filter.  The numbers on the left side of the portal are the empID numbers and the number on the right is the global value of empID (so Peter is 9)

            

           the filter calc is EMPL 2::__pkEmplID≠EMPL 2::g_EmployeeSelection1

            

           EMPL2 is [X] to TASK and other than that there is no other relation

            

           Thanks all

            

           Last thing: when I have a different filter i.e. EMPL2::Status <> "Inactive" the filter works properly

      Screen_Shot_2014-08-28_at_13.24.56.png

        • 1. Re: Cartesian Join Portal Not Filtering Correctly
          RichNasser

               OK - I seemed to have gotten closer to a result.  In the OnSave script trigger from the pop-up box I put a script step to refresh windows and flush join cache results as checked and it works - however, when I try to filter out the inactive employees with an or statement in the filter calc, it doesn't work...

                

          • 2. Re: Cartesian Join Portal Not Filtering Correctly
            philmodjunk

                 Can you post the "or" statement that didn't work?

                 And here's how to get rid of the need for "Refresh-Flush":

                 Define the global field in the layout's table.

                 Use it as the match field in the relationship with that cartesian join operator.

                 This should enable things to update without needing to script a refresh.

            • 3. Re: Cartesian Join Portal Not Filtering Correctly
              RichNasser

                   OK - the first part about defining in the L/O table and not needing the script worked - thanks!

                    

                   here is the calc in the filter that doesn't quite work:

                    

                   TASK::g_EmployeeSelection1  ≠ EMPL 2::__pkEmplID or EMPL 2::Status  ≠ "Inactive"

              • 4. Re: Cartesian Join Portal Not Filtering Correctly
                philmodjunk

                     Check the field definition for Status in Manage | Database | Fields and see if Status is defined as "number" or "text". It should be defined as text.

                • 5. Re: Cartesian Join Portal Not Filtering Correctly
                  RichNasser

                       yes it is Text - the quirky thing is that each of filter calcs work on their own but when combined with the OR it doesn't work...

                  • 6. Re: Cartesian Join Portal Not Filtering Correctly
                    RichNasser

                         Here is a knowledge base article on this, but I couldn't get their example to work - 

                         http://help.filemaker.com/app/answers/detail/a_id/12123/kw/portal%20filtering

                    • 7. Re: Cartesian Join Portal Not Filtering Correctly
                      RichNasser

                           And finally this KB article probably describes this as a known bog in FM12 - but I'm running 13 -

                            

                           http://help.filemaker.com/app/answers/detail/a_id/11120/kw/portal%20filtering/related/1

                           perhaps not resolved.  Now I need to test on the file as non-hosted...

                      • 8. Re: Cartesian Join Portal Not Filtering Correctly
                        RichNasser

                             So here are the results for the testing I did on FM13 hosted v local files for the portal filtering with global field.  It seems that FM have the bug known as per the KB article above.  UNLIKE the KB article above, the bug DOES occur in my local file as well:

                             Note - the global field is TASK::g_EmployeeSelection1 and it is Cartesian Joined with  EMPL 2::__pkEmplID

                             I DON'T use a cached flush or refresh script at all with these results (didn't seem to make a difference in the results below)

                             1) Hosted:

                        TASK::g_EmployeeSelection1  =  EMPL 2::__pkEmplID or EMPL 2::Status  =  "Active"  works properly

                        TASK::g_EmployeeSelection1  !=  EMPL 2::__pkEmplID or EMPL 2::Status  =  "Active" doesn't work properly

                        TASK::g_EmployeeSelection1  =  EMPL 2::__pkEmplID or EMPL 2::Status  !=  "Active" works properly

                        TASK::g_EmployeeSelection1  !=  EMPL 2::__pkEmplID or EMPL 2::Status  !=  "Active" doesn't work properly

                        EMPL 2::Status  !=  "Active" works properly

                        EMPL 2::Status  =  "Active" works properly

                             TASK::g_EmployeeSelection1    ≠    EMPL 2::__pkEmplID works properly
                              
                             TASK::g_EmployeeSelection1    =    EMPL 2::__pkEmplID  works properly

                              

                        2) Local:

                        TASK::g_EmployeeSelection1   ≠  EMPL 2::__pkEmplID or EMPL 2::Status  =  "Active"  doesn't work properly

                             TASK::g_EmployeeSelection1   =  EMPL 2::__pkEmplID or EMPL 2::Status  =  "Active" works properly

                             TASK::g_EmployeeSelection1   =  EMPL 2::__pkEmplID or EMPL 2::Status   ≠   "Active"  works properly

                              

                             TASK::g_EmployeeSelection1    ≠   EMPL 2::__pkEmplID or EMPL 2::Status   ≠   "Active"  doesn't work properly

                             EMPL 2::Status   ≠   "Active" works properly

                             EMPL 2::Status   =   "Active" works properly

                             TASK::g_EmployeeSelection1    =    EMPL 2::__pkEmplID works properly

                             TASK::g_EmployeeSelection1     ≠     EMPL 2::__pkEmplID works properly

                              

                        • 9. Re: Cartesian Join Portal Not Filtering Correctly
                          TSGal

                               Rich Nasser:

                               Thank you for your posts.

                               Perhaps you could provide some additional information for the filter that doesn't work locally.  For instance, in the local file, what records appear for:

                               TASK::g_EmploymentSelection1 ≠ EMPL 2::__pkEmpID

                               What records appear for the second half of the filter:

                               EMPL 2::Status = "Active"

                               Finally, what records appear with the combined OR filter?

                               TSGal
                               FileMaker, Inc.

                          • 10. Re: Cartesian Join Portal Not Filtering Correctly
                            RichNasser

                                 Hi:

                                 I think I have four screen shots as you can see two with the OR and two alone - the first two with the ORs don't work properly and the individual ones work fine:

                                  

                            • 11. Re: Cartesian Join Portal Not Filtering Correctly
                              RichNasser

                                   one more note for your debuggers to ponder - I tried a workaround something like:

                                    

                                   if(xxxx = xxxx;false;true) or xxxx= "Active" in the hopes of seeing if it was a != issue and that did not work either...

                                    

                                   really would like for this to work or find another workaround....

                                    

                                   Thanks

                              • 12. Re: Cartesian Join Portal Not Filtering Correctly
                                TSGal

                                     Rich Nasser:

                                     Screen #1 - TASK::g_EmployeeSelection1 ≠ EMPL 2::__pkEmpID  or  EMPL 2::Status = "Active"
                                     Six of the seven records don't equal "Bill" to satisfy the left condition, and five records (including "Bill") are "Active" to satisfy the right hand.  This results in all seven records.  This is working correctly.

                                     Screen #2 - TASK::g_EmployeeSelection1 ≠ EMPL 2::__pkEmpID  or  EMPL 2::Status ≠ "Active"
                                     Since the global field is blank, all seven records satisfy the left condition.  The right condition consists of two records, but it is moot since all seven records are included.  This is working correctly.

                                     Screen #3 - TASK:g_EmployeeSelect1 ≠ EMPL 2::__pkEmpID
                                     Since the global field is "Pepito Smith", six of the seven records are included, as the record for "Pepito Smith" is omitted.  This is working correctly.

                                     Screen #4 - EMPL 2::Status = "Active"
                                     As mentioned in Screen #1 description, the five Active records are displayed.  This is working correctly.

                                     Can you post an example where this is not working properly?

                                     TSGal
                                     FileMaker, Inc.

                                • 13. Re: Cartesian Join Portal Not Filtering Correctly
                                  RichNasser

                                       TSGal

                                        

                                       It seems I made a mistake.  I don't know how, but after testing everything hosted and local, the results of the portal work as you describe.  Somewhere along the long line, I missed it and it snowballed.  I do apologize for the confusion and appreciate the effort on your part to see this through.  I am OK if you feel it would be a good idea to delete the entire or part of the post as you see fit so as not to confuse anyone else that might be reading this... again, sorry and thanks.

                                        

                                       Rich