10 Replies Latest reply on Dec 13, 2013 5:29 PM by raphthepenguin

    Problems with Tickboxset

    raphthepenguin

      Hey,

       

      I am trying to set an reminder that is connected to a Tickboxset.

      My set has to values:

      First Contact and Follow Up

      Next to both I have a Datefield.

       

      What I am trying to do is to set a reminder that shows when the First Contact is 3 days ago and the Follow up hasnt been ticked.

       

      Eventually I want a "Home" page that shows records that need to be contacted in a portal. But right now I am even struggeling with getting the right calculation.

       

      I made a field that is called reminder. That if the three days are up it simply says "remind" to start with. But I have problems trying to recognize in a calculation what tickbox is ticked and witch is not. Obviously if the follow up is clicked I dont want the reminder to show up any more!

       

      Thanks!

        • 1. Re: Problems with Tickboxset
          Mike_Mitchell

          What you want here is the FilterValues function. Values in fields formatted as checkbox sets are stored as return delimited lists. The FilterValues function will allow you to determine if the value you want is present in the list.

           

          HTH

           

          Mike

          • 2. Re: Problems with Tickboxset
            raphthepenguin

            How can I integrate that in a If function?

            When I just put the FilterValue it works fine (when pressed the field shows the value).

             

            But whenever I try to integrate it in a Function (since I have 3 conditions) its not showing anything.

             

            My three conditions are

            First Contact clicked

            Follow Up not clicked

            First Contact Date + 3 < CurrentDate

             

            This was my idea (it doesnt work):

             

            If(

            FilterValues ( Tickboxfield ; "First Contact") = 1 and

            FilterValues ( Tickboxfield ; "Follow Up") = 0 and

            (FirstContactDate)+3 < Get(CurrentDate);"Remind";"")

            • 3. Re: Problems with Tickboxset
              erolst

              Your idea was down the right alley, but sadly text fields don't behave like number fields in that you can't use their value as Boolean result; i.e. If ( numberField ; … ) works OK, but If ( textField ; … ) doesn't.

               

              You could count the result of FilterValues() with ValueCount and use that as Boolean, but since you're only interested in presence vs. non-presence, it's easiest to use IsEmpty ():

               

              Case (

              not IsEmpty ( FilterValues ( "First Contact" ; Tickboxfield ) ) and

              IsEmpty ( FilterValues ( "Follow Up" ; Tickboxfield) ) and

              FirstContactDate + 3 < Get ( CurrentDate ) ;

               

              "Remind"

              // if your False or default result of Case() or If() is empty, you don't need to spell it out.

              )

               

              If you have a large and a small list, it can make a dfiference which list is filtered, and which one does the filtering. For two short lists, this is probably in the nanosecond range, but still …

               

              EDIT: Text corrected since I obviously can't remember or ain't able to look up the optimal arrangement …

              • 4. Re: Problems with Tickboxset
                raphthepenguin

                Haha, two nanoseconds...

                But what do you mean by filterring the longer list through the shorter one?

                 

                This works! Thank you!

                • 5. Re: Problems with Tickboxset
                  erolst

                  raphthepenguin wrote:

                  But what do you mean by filterring the longer list through the shorter one?

                  This works! Thank you!

                   

                  You're welcome.

                   

                  If the lists are greatly differing in size, you can optimize your code by arranging the list in a certain order … (see following posts)

                   

                  Be aware that "how many of A are in B" is different from "how many of A are in B"; in that case, you have to choose the order that matches the question. 

                   

                  My nanosecond comment was referring to the fact that for two lists with 1 and 3 values, respectively, the difference should be entirely immaterial (but would be tangible on the abacus version of FileMaker …).

                   

                  EDIT: text edited in light of the following posts.

                  • 6. Re: Problems with Tickboxset
                    BruceRobertson

                    Are you sure about that? I think I've seen many references declaring that it was the other way around. Short list first.

                    • 7. Re: Problems with Tickboxset
                      erolst


                      BruceRobertson wrote:

                       

                      Are you sure about that […]

                       

                      Told you I always mix those up … please provide a link to one of those references so I can make a bookmark and do some quick reading whenever I want to look like I know what I'm talking about.

                      • 8. Re: Problems with Tickboxset
                        raphthepenguin

                        That makes sense...

                         

                        well I am already at the next problem... Same thing, only different Outputs required.

                        This time I have 4 checkboxes and instead of just writing remind I would like to note which of these I want to remind for (this will help me when I will create the portal later, so I can let people know what the reminder is for)!

                         

                        Here is my try: The resultfield is just getting stuck in the first one ("Week 1-2")

                         

                        Let([

                        psp =AppProcess_PostSchool Process;

                        SD = Application Process_SchoolStartDate

                        ];

                         

                        Case (

                        IsEmpty ( FilterValues ("Week 1-2"; psp) ) and

                        SD + 10 ≤ Get ( CurrentDate ) ; "Rmd Week 1-2";

                        not IsEmpty (FilterValues( "Week 1-2"; psp)) and

                        IsEmpty ( FilterValues ( "1 Month Before Outreach" ; psp ) ) and

                        SD + 60 ≤ Get ( CurrentDate ); "Rmd 1 Month Before Outrech";

                        not IsEmpty (FilterValues( "Week 1-2"; psp)) and

                        not IsEmpty ( FilterValues ( "1 Month Before Outreach" ; psp ) ) and

                        IsEmpty ( FilterValues ( "During Outreach" ; psp ) ) and

                        SD + 135 ≤ Get ( CurrentDate ); "Rmd During Outreach";

                        not IsEmpty (FilterValues( "Week 1-2"; psp)) and

                        not IsEmpty ( FilterValues ( "1 Month Before Outreach" ; psp ) ) and

                        not IsEmpty ( FilterValues ( "During Outreach" ; psp ) ) and

                        IsEmpty ( FilterValues ( "Before End" ; psp ) ) and

                        SD + 150 ≤ Get ( CurrentDate ); "Rmd Before End"))

                        • 9. Re: Problems with Tickboxset
                          erolst

                          Great idea to use Let (), but you can use it even more aggressively, so your code is better readable and understandable for yourself and any code critic who happens to stroll by …

                           

                          Let ( [

                          psp = AppProcess_PostSchool Process ;

                          SD = Application Process_SchoolStartDate ;

                          td = Get ( CurrentDate ) ;

                          week1_2 = not IsEmpty ( FilterValues( "Week 1-2"; psp ) ) ;

                          oneMonth = not IsEmpty ( FilterValues ( "1 Month Before Outreach" ; psp ) ) ;

                          duringOutreach = not IsEmpty ( FilterValues ( "During Outreach" ; psp ) )

                          ];

                           

                          Case (

                           

                          not week1_2 and SD + 10 ≤ td ;

                          "Rmd Week 1-2";

                           

                          week1_2 and not oneMonth and SD + 60 ≤ td ;

                          "Rmd 1 Month Before Outreach" ;

                           

                          week1_2 and oneMonth and not duringOutreach and SD + 135 ≤ td ;

                          "Rmd During Outreach";

                           

                          week1_2 and oneMonth and duringOutreach and IsEmpty ( FilterValues ( "Before End" ; psp ) ) and SD + 150 ≤ td ;

                          "Rmd Before End"

                           

                          ) )

                          • 10. Re: Problems with Tickboxset
                            raphthepenguin

                            Wow, Ok I didnt know that you can be that "agressive" with the let function!

                             

                            You are already using the previous let in the the let...

                            And then you did double negatives, I am impressed!

                             

                            I tried it and it still didnt work... looking over the whole thing I realized I used the wrong field for my checkbox set! *feelingstupid*

                            Now its working!

                             

                            Thanks!