10 Replies Latest reply on May 7, 2013 8:15 AM by brianquillin

    Need Help with Portal Filter (Boolean)

    brianquillin

      Title

      Need Help with Portal Filter (Boolean)

      Post

           Concerning these 3 Table and Occurences:

           Contacts - People
           Participation - Collects information based on people's affiliation
           Years - I have a Table collecting Years (ie. 2012, 2013 - each a separate record)

           * I also have a year_status field to indicate either previous, current, or future * - this is my attempt to set the "current" year in one location and not have to update reporting scripts every year (I may be off base in this concept but I'm trying it out) *

           WHAT MY PORTAL IS DOING RIGHT:

           I have a cartisian join between Participation (TO) ---X--- Participation 2 (for viewing a left side record list)

           I also have Particpation2::year_id >----- part_YEAR::year_ID (needed fields are reflecting proper data).

           I have the Participation 2 portal accurately showing the related Contacts and their 'Affiliation' type based on their Participation record.  (I also have a global search field working properly to filter the portal by PatternCount, etc.)

           HERE'S MY PROBLEM:

           I have not figured out the proper filter calculation to view ONLY the CURRENT Participation records.  

           I need a boolean statement that I can add to my existing portal filter to knock out any participation record that is not the current year.  

           My apologies for the confusing (and probably incomplete) description of my design but any help or nudge in the right direction would be greatly appreciated.

           Thanks in advance...

        • 1. Re: Need Help with Portal Filter (Boolean)
          philmodjunk

               is year_status defined in participation?

               So you need to have your partial text match AND the year_Status must have the text: "current"?

               if so:

               PatternCount ( Participation2::TextField ; Searchfield ) AND
               Paticipation2::year_status = "Current"

          • 2. Re: Need Help with Portal Filter (Boolean)
            brianquillin

                 year_status is not currently defined in Participation but I will set that up.

                 QUESTION 1:  
                 What is the correct way (or best way)?  

                 Should I set participation::year_status as a calc field - If (part_YEAR::year_status = "current" ; "" )

                 OR

                 Should I set it up in the relationship between the TOs?
                 Participation2::year_id = part_YEAR::year_ID
                 AND
                 Participation2::year_status = part_YEAR::year_status

                 QUESTION 2:

                 My portal filter says the result must be boolean.  Of the many AND combinations I've tried, most of them haven't worked or hidden ALL the portal records.

                 This is a novice question but I have to ask:  Does participation2::year_status = "current" return a boolean result or a text result?

                 Thanks much...

                  

                  

            • 3. Re: Need Help with Portal Filter (Boolean)
              brianquillin

                   I have tried many more combinations based on participation2::year_status = "current" but the portal still isn't filtering those records.

                   I currently have participation2::year_status setup as Auto Enter Calc where that field equals part_YEAR::year_status.

                   Participation2::year_status is pulling the appropriate text 'status' but the filter isn't removing those records.

                   I think I am missing something very obvious but I'm being blinded by the endless combination of methods I've tried.

                   (this was supposed to be an quick & easy filter but I'm on day 3....)

              • 4. Re: Need Help with Portal Filter (Boolean)
                brianquillin

                     Why does this filter expression cause no records to be visible in the portal?

                     Participation 2::year_status = "current" &  IsEmpty ( Participation::gSearchText) or PatternCount ( part_CONTACT 2::name_alphabetical ; Participation::gSearchText )

                     --------

                     Why does this filter expression not filter out those records whose year_status is any but "current"?

                     IsEmpty ( Participation::gSearchText) or PatternCount ( part_CONTACT 2::name_alphabetical ; Participation::gSearchText ) & Participation 2::year_status = "current"

                • 5. Re: Need Help with Portal Filter (Boolean)
                  philmodjunk

                       You aren't using And in your calculation expression. You are using &--the concatentation operator.

                       Use the word AND to get a boolean And in your expression.

                       You should not need to add the calculation field to participation2 for this to work. Given the relationship, you should be able to refer to the field in the related table in your filter calculation.

                       But It's really not clear to me how you are using that addditional table, it seems an unecessary table from what little I understand about your solution from these posts.

                  • 6. Re: Need Help with Portal Filter (Boolean)
                    brianquillin

                         Thank you for the responses.  I am still at a loss on this.  I changed '&' to 'AND' but no change in the result(s).

                         I'm attaching a few screen shots that will hopefully shed some more insight into my problem.

                          

                    • 7. Re: Need Help with Portal Filter (Boolean)
                      brianquillin

                           another screenshot

                      • 8. Re: Need Help with Portal Filter (Boolean)
                        brianquillin

                             another screenshot

                        • 9. Re: Need Help with Portal Filter (Boolean)
                          brianquillin

                               Just tried negating(bypassing) the entire Year Table altogether.

                               I set a simple text field, Participation::year, to represent a 4 digit year (ie, 2014).

                               Then I filtered portal by:

                               IsEmpty ( Participation::gSearchText) or PatternCount ( part_CONTACT 2::name_alphabetical ; Participation::gSearchText ) and Participation 2::year = "2014"

                               Records with participation2::year set to 2013 are still visible in the portal.

                               I am baffled.

                          • 10. Re: Need Help with Portal Filter (Boolean)
                            brianquillin

                                 I moved participation2::year to before the IsEmpty and its now working properly IF I bypass the Year Table.

                                 Still working but may settle for this...

                                 thanks again...