14 Replies Latest reply on Dec 18, 2014 8:19 PM by JamesPeragine

    Filter Portal with relationships

    JamesPeragine

      Title

      Filter Portal with relationships

      Post

      I am trying to filter my portal with a relationship instead of actually filtering my portal.  

      Important to not that I have my layout setup so that it has a self-join portal using a cartesean join so that I can click a portal record (using goto related record script)  and have that records content load on the layout outside of the portal.

      I want to filter the portal with a calculation.  I understand that you can set up a calculation field on the parent table occurrence (that has a boolean result) and join it with a global field(that has a value of 1) in the child table occurrence to accomplish a calculated filter.  I have tried this but can't get it to work, it shows all records.

      Any idea how to get this to work?

        • 1. Re: Filter Portal with relationships
          Sorbsbuster

          You need to have an indexable field on the 'right hand side' of a relationship.  Using a global as the secondary key doesn't mean anything - by definition every record will match (or none, I suppose).

          • 2. Re: Filter Portal with relationships
            JamesPeragine

            Yes of coarse, thanks.

            OK I've changed my child field to a non-Global field and entered a 1 into every field.  The portal is still showing all of the records though.

            So I have my relationship set up as

            _pk:Leads X _pkLeads

            AND

            PortalFilterCalculation (this is my calculation field in my Parent TO) = PortalFilterConstant (This is the field I have entered 1 into)

            When I cycle through my leads the calculation is working properly.  It's showing a 0 for the Leads that don't past the calculation text and a 1 for those that are.  My portal should also be showing the leads where PortalFilterCalculation = PortalFilterConstant (Where 1=1) but it's showing all of them.  

            What am I doing wrong?

            • 3. Re: Filter Portal with relationships
              JamesPeragine

              Actually to be more exact precise, what is happening is that as I cycle through the leads on layout the portal shows all of the leads if the PortalFilterCalculation = 1 or none of them if it = 0

              • 4. Re: Filter Portal with relationships
                philmodjunk

                Giving every record the value "1" is functionally the same as using a global field. Your relationship is matching records exactly as you designed it to. I don't know your calculation, but suspect that you need to swap the fields and use the calc field on the portal's side of the relationship and the "1" field on the layout's side of the relationship. This, BTW, would then allow you to use a global field for the "1" field though I normally just use a calculation field for this purpose.

                • 5. Re: Filter Portal with relationships
                  JamesPeragine

                  Ohhhhhhh I was under the impression that you can only use unstored calculations on the parent side of a relationship?

                  • 6. Re: Filter Portal with relationships
                    philmodjunk

                    That is correct. Keep in mind that you have not described your calculation. I didn't even know that it was unstored until this last post of yours. I suggest posting it so we can suggest an alternative method that will produce the results that you need.

                    Are you using FileMaker 12 or 13? That will make the ExecuteSQL function an option to consider for your relationship level filter.

                    • 7. Re: Filter Portal with relationships
                      JamesPeragine

                      I am using filemaker 12 advanced 

                      I am basically trying to speed up my database.  I got everything working using portal filters, however they were slowing my database down A LOT.   I believe that it's because I had to use summary fields to count the records in my portals.  I can use Count () when they are filtered by a relationship and that works a lot quicker.  

                      Here's the calculations that I am trying to filter through a relationship

                       

                       


                      GetAsBoolean (

                      Case (
                      LeadsPortal::AreaCodeFilter = "SHOW ALL"; 1;
                      LeadsPortal::AreaCodeFilter ="Eastern"; LeadsPortal::Timezone = "Eastern" or LeadsPortal::Timezone = "Eastern/Central";
                      LeadsPortal::AreaCodeFilter ="Central"; LeadsPortal::Timezone = "Central" or LeadsPortal::Timezone = "Central/Mountain";
                      LeadsPortal::AreaCodeFilter ="Mountain"; LeadsPortal::Timezone = "Mountain" or LeadsPortal::Timezone = "Mountain/Pacific";
                      LeadsPortal::AreaCodeFilter ="Pacific"; LeadsPortal::Timezone = "Pacific" or LeadsPortal::Timezone = "Mountain/Pacific";
                      )

                      and

                       


                      Case (
                      Leads::LeadDocStatus ≠ "Dead Deal" and
                      Leads::LeadDocStatus ≠ "Docs In" and
                      Leads::LeadDocStatus ≠ "Deal Funded" ; 1;0
                      )



                      ((LeadsPortal::CallBackDateMoneyReady      ≤      Get(CurrentDate) and LeadsPortal::CallBackTime ≤ Get(CurrentTime)  and
                      LeadsPortal::CallBackDate     ≤    Get(CurrentDate)) or
                      IsEmpty ( LeadsPortal::CallBackDate ) and IsEmpty ( LeadsPortal::CallBackDateMoneyReady )  )

                      and


                      If (
                      LeadsPortal::SalesPersonSort=16; LeadsPortal::__fkSalesAssociate;
                      LeadsPortal::__fkSalesAssociate=LeadsPortal::SalesPersonSort
                      )

                      )

                       

                      • 8. Re: Filter Portal with relationships
                        JamesPeragine

                        FYI I got it working with an unstored Calculation field, however, I will have to set up all types of Script Triggers to keep the data current ya?

                         

                        I'd much rather implement an ExecuteSQL method but don't know much about how to do that.

                        • 9. Re: Filter Portal with relationships
                          JamesPeragine

                          Actually an unstored Calculation seems to work just fine, every time I modify any of the fields in it's calculation it updates the portal.

                          What then is the TRUE difference between a stored and an unstored calculation?

                          Also

                          I would love to implement an ExecutiveSQL method here instead if that will make my database run even faster?

                          • 10. Re: Filter Portal with relationships
                            philmodjunk

                            First, define the following stored calculation field in your table: Name the Field cAreaKey.

                            Case (
                            PatternCount ( LeadsPortal::Timezone ; "Eastern" ) ; List ("Eastern" ; "Eastern/Central" ; "Show All" ) ;
                            PatternCount ( LeadsPortal::Timezone ; "Central" ) ; List ( "Central" ; "Central/Mountain" ; "Show All" ) ;
                            PatternCount ( LeadsPortal::Timezone ;"Mountain" ); List ( "Mountain" ; "Mountain/Pacific" ; "Show All" ) ;
                            PatternCount ( LeadsPortal::Timezone ;"Pacific" ) ; List ( "Pacific" ; "Mountain/Pacific" ; ; "Show All" )
                            )

                            Next, define cAssociateKey as

                            List ( 16 ; __fkSalesAssociate )

                            For both of these calculations, select Text as the result type

                            Define cCallBackTS as:

                            If ( IsEmpty ( CallBackDate ) and IsEmpty ( CallBackDateMoneyReady ) ; TimeStamp ( 1 ; 0 ) ;
                                      TimeStamp ( CallBackDate ; CallBackTime ) )

                            Select TimeStamp as the result type.

                            Define cMoneyReadyKey as
                            If (IsEmpty ( MoneyReady ) ; GetAsDate ( 1 ) ; MoneyReady )

                            Select Date as the result type

                            Define cTodayTS as an unstored calculation: Get ( CurrentTimeStamp )
                            Define cTodayDate as an unstored calculation: Get ( CurrentDate )

                            Now you can construct a multiple field self join relationship:

                            LayoutTO::AreaCodeFilter = PortalTO::cAreaKey AND
                            LayoutTO::cTodayTS > PortalTO::cCallBackTS AND
                            LayoutTO::cDeadDocStatus ≠ PortalTO::LeadDocStatus AND
                            LayoutTO::cTodayDate > PortalTO::cMoneyReadyKey AND
                            LayoutTO::__fkSalesAssociate = PortalTO::cAssociateKey

                            Now that's Really complex. I can't be sure that I nailed down every possible nuance of your original calculation.

                            • 11. Re: Filter Portal with relationships
                              philmodjunk

                              Unstored calculation fields cannot be used on the portal (child or "many") side of the relationship. They can be used on the Layout (Parent or "one" side of the relationship)

                              • 12. Re: Filter Portal with relationships
                                JamesPeragine

                                Thank you SO MUCH for taking the time to put this together.  I am going to go through it and see if I can get it to work.  I actually got some of what you listed here done so far so I must be figuring this stuff out a bit:)

                                 

                                Do you know a good resource for me to learn more about incorporating ExecuteSQL into my filemaker databases?

                                • 13. Re: Filter Portal with relationships
                                  JamesPeragine

                                  So far I got a few of these working,  however I don't understand exactly how they are working.

                                   

                                  with regards to setting cAssociateKey = List ( 16 ; __fkSalesAssociate )

                                  (I related it to my global sort field SalesPersonSort instead and it work, I think that's what you meant to put there)

                                  But I don't understand how listing 16 AND _fkSalesAssociate is making this relationship work.  I know that the list basically creates an array but I thought that each item in the ARRAY created  AND not OR .  Is it basically saying  SalesPersonSort=16 OR SalesPersonSort=_fkSalesAssociate ?

                                  • 14. Re: Filter Portal with relationships
                                    JamesPeragine

                                    Okay got them all working and selecting each portal row, that has a GoToRelated Record Script assigned to it, is noticably faster!

                                    However

                                    If I change any of the dates in my main layout I get "Find in Progress - Processing Query"  Which takes a good 5-10 seconds

                                    So I'm back at square one with speeding this thing up.  I thought that If I filtered this portal with relationships only that it would be zippy again... grrrrrrrr