11 Replies Latest reply on Jul 31, 2013 5:41 AM by IT_User

    Filtered Portal based on Checkboxes

    IT_User

      So I've tried looking around, and I know this is probably an easy fix, but I just can't seem to get it.

       

      I have two tables. One has a layout with a portal to the other table. I would like to filter it by using global fields I put into the first table.

       

      I've tried the relationships of:

      Table1::_kp_tableID = Table2::_kf_tableID

       

      and then tried to use the portal filtering, by using an expression like

      Table1::globalField1 = Table2::field2

       

      Tried to Refresh Window (without flush cache)

       

      Didn't work

       

      So (removed the portal filtering) I tried to use table relationship like:

      Table1::_kp_tableID = Table2::_kf_tableID

      Table1::globalField1 = Table2::field2

       

      Tried some combinations of:

      Refresh Window (without flush cache)

      and setting the ID field to itself on the first table

      Usually set with script triggers of onObjectModify and OnObjectSave

      and possibly Commit Records

       

      Both globalField1 and field2 are checkboxes of 1 or nothing, and are number fields.

       

      Any help is appreciated, thanks!

        • 1. Re: Filtered Portal based on Checkboxes
          erolst

          Your description does not make it clear if the unfiltered relationship works, either in the key-key- or the globalField-indexedField-combination. If not, there's nothing left to filter.

           

          Also, don't forget that when using checkboxes, a common error is to use the wrong value list (I speak from experience), so you think your global has a certain value (because it's checked), when actually you're using the wrong VL.

          Try using an Edit Box while debugging.

          • 2. Re: Filtered Portal based on Checkboxes
            IT_User

            Hi, thanks for your reply.  MY VL on fields seems to be fine (I looked at the Data Viewer just in case).  My unfiltered portal with just the key to key relationship is fine.  It shows only the correct related records.

             

            When adding the portal filter expression, unless I do something like:

            Table2::field2 = 1

            it does not seem to work.

             

            (I should mention I have 2 global fields, and when I hardcode

            Table2::field2 = 1

            or

            Table2::field4 = 1

            It shows records that have atleast field2 or field4 checked with 1)

             

             

            When doing the portal relationships, I found out one more thing:

            Since I have 2 global fields it complicates things.  Also, it might be because I have NULL values instead of a 0.

            So in other words, this works:

            Table1::_kp_tableID = Table2::_kf_tableID

            Table1::globalField1 = Table2::field2

             

            but this does not:

            Table1::_kp_tableID = Table2::_kf_tableID

            Table1::globalField1 = Table2::field2

            Table1::globalField3 = Table2::field4

            • 3. Re: Filtered Portal based on Checkboxes
              erolst

              If the globals to act as portal filters can be potentially empty, then check them inside the portal expression, and if necessary, given them a blanket value, like Let ( [ myGlobal1 = Max ( myGlobalField1 ; 1 ) ; myGlobal2 = Max ( myGlobalField2 ; 1 ) etc, and use these variables in your expression.

              • 4. Re: Filtered Portal based on Checkboxes
                IT_User

                Correction, I did have an "or" instead

                 

                (I should mention I have 2 global fields, and when I hardcode

                Table2::field2 = 1

                and

                Table2::field4 = 1

                 

                So it works hardcoded, but I would rather not hardcode it.

                • 5. Re: Filtered Portal based on Checkboxes
                  IT_User

                  Ok, so i tried it your wa.

                  Since the fields in teh second table can also be blank, I did a blanket value for those, as well, to come out like this:

                   

                  Let ( [

                  myGlobal1 = Max ( Table1::myGlobalField1 ; 1 ) ;

                  myGlobal2 = Max ( Table1::myGlobalField1 ; 1 ) ;

                  field1 = Max ( Table2::field1 ; 1 ) ;

                  field2 = Max  ( Table2::field2 ; 1 )

                  ] ;

                  myGlobal1 = field1

                  and

                  myGlobal2 = field2

                  )

                   

                  After much thought and playing around, I realize what I want is a lot more complicated than I thought in the first place...

                   

                  Anyways, thank you for your help!

                  • 6. Re: Filtered Portal based on Checkboxes
                    erolst

                    Well. it wasn't much of a help - but maybe it's more productive to just say what you're actually trying to accomplish?!

                    • 7. Re: Filtered Portal based on Checkboxes
                      IT_User

                      What I'm trying to do, is kind of a billing system:

                       

                      I have a Clients table, which is connected to a transactions table.  The transactions are billing for what service the client is paying for.

                       

                      On the one Clients table I want to list transactions that are related to the client in a portal.  The two checkboxes I am working with are if the transaction has been paid and/or printed.  By default I want it to list all related records, but if they click the global checkbox for paid, it should show paid (but shows both printed and unprinted).

                       

                      I realized that it might be harder to do with both paid and printed checkboxes, so I think I might settle on something that filters on just the paid checkbox.

                      • 8. Re: Filtered Portal based on Checkboxes
                        erolst

                        Why didn't you day so? Maybe this one helps.

                        • 9. Re: Filtered Portal based on Checkboxes
                          DavidJondreau

                          I dislike creating fields for stuff like this. I prefer not to go into Manage Databases... if I don't have to. You can create a simple conditionally formatted textbox-based button (or pair of buttons) that achieves the same purpose by setting a global variable, say, called $$portal.filter.

                           

                          I strongly recommend using portal filters for this and not relationship filters.

                           

                          As others may have made clear, the problem is that you want all the records to show when the chosen filter is empty, but "empty" doesn't match those items that are "Paid" or "Printed". Your portal filter should be (assuming instead of a global field you are using a global variable):

                           

                          = IsEmpty ( $$portal.filter ) or ( $$portal.filter = childTable::Status )

                           

                          Forget about Max() and extra fields, they're completely unnecessary.

                          • 10. Re: Filtered Portal based on Checkboxes
                            IT_User

                            Sorry, sometimes I don't think clearly, but thank you, I think that will help!

                            • 11. Re: Filtered Portal based on Checkboxes
                              IT_User

                              That sounds like a good plan, as I don't like to crowd the database with fields.