1 2 Previous Next 19 Replies Latest reply on Jan 14, 2015 6:21 PM by marksealey

    Calculation field to display values of checkboxes

    marksealey

      Simple, I know; but…

       

      how do I build a calculation field (I assume that's what I want) which outputs/displays a composite string with the values from other tables' fields, please?

       

      This kind of thing does not work:

       

      outputString=""

      &

      If (other_table::checkbox1 = "Y"; outputString ="green"; outputString ="blue")

      &

      If (other_table::checkbox2 = "Y"; outputString ="circular"; outputString ="square")

      &

      // etc

      outputString

       

      Am I on the wrong track altogether?

       

      TIA!

        • 1. Re: Calculation field to display values of checkboxes
          jlamprecht

          Hi there,

           

          What you are looking for is a Let function.

           

          Let (

           

          [

          outputString = If(other_table::checkbox1 = "Y"; "green";"blue");

          outputString2 = If (other_table::checkbox2 = "Y";"circular"; "square")

          ];

          outputString & " " & outputString2

          )

           

          That would result in "green circular" if other_table::checkbox1 = "Y" and other_table::checkbox2 = "Y". I hope this helps

          • 2. Re: Calculation field to display values of checkboxes
            vcirilli

            you could do something like this.

             

            put this in a Calculation Field:

             

            Let ( [

             

            cb1Color = if ( other_table::checkbox1 = "Y"; "Green", "Blue");

             

            cb2Color  = if ( other_table::checkbox2 = "Y"; "Circular", "Square");

             

            outputString = cb1Color  & " " & cb2Color ] ;

             

            outputString )

            • 3. Re: Calculation field to display values of checkboxes
              marksealey

              Hi jlamprecht and vcirilli!

               

              Thanks so much for your quick and helpful replies.

               

              I was trying Let statements before I wrote this; now I know that's what I have to do.

               

              I apologise for asking the question with checkboxes.

               

              I'm actually using No and Yes as Custom Values in a Radio Buttons Value List.

               

              And it doesn't work with:

               

              other_table::field1 = "Yes" etc


              It always returns "No", IOW - the test always fails.


              How do I detect values in Radio Buttons and test for (Yes/No) there, please?


              Thanks again!

              • 4. Re: Calculation field to display values of checkboxes
                jlamprecht

                Can you show us the calculation you are using?

                • 5. Re: Calculation field to display values of checkboxes
                  mtwalker

                  The radio buttons are just a way of displaying the value in the field so you don't need to check that the field is a radio button, check just the value. See if there is more than one value in the field. Sometime multiple values get entered when you changing the value list the field uses. For example the field might actually have:

                  Y

                  Yes

                   

                  if so that doesn't equal "Yes".

                   

                  You could use the data viewer to check the contents of the field or put other copy of the field on the layout as a regular edit box to see if there are multiple values in the field.

                  • 6. Re: Calculation field to display values of checkboxes
                    marksealey

                    Of course!

                     

                    Thanks - adapted. No syntax errors; just always returns "not 1  not 2" even though I have checked "No" in each of the Radios in turn…

                     

                    Let (

                            [

                              firstString = If(othertable::choice1 = "Yes"; "1"; "not 1");

                              secondString = If(othertable::choice2 = "Yes"; "2"; "not 2");

                              outputString = firstString  & "  + " & secondString        

                    ];

                              outputString

                    )

                    • 7. Re: Calculation field to display values of checkboxes
                      erolst

                      It may be a good idea if you describe your real tables / data; your examples don't make too much sense:

                      marksealey wrote:

                      other_table::checkbox1 = "Y"; outputString ="green"; outputString ="blue")

                      Why use flag fields, instead of having value lists with colors (green, blue, whatever) and shapes (square, round, oblong, whatever) so that you can simply use the existing value?

                       

                      And when you're using flag fields, I suggest employing numeric fields with a value list of '1' (and formatted as a checkbox for data entry), which makes querying them much more straightforward.

                      marksealey wrote:

                      I apologise for asking the question with checkboxes.

                      I'm actually using No and Yes as Custom Values in a Radio Buttons Value List.

                      Speaking of data entry: when you use field data in a calculation, it makes no difference how a field is formatted for data entry (which is a setting for a field object on a layout, of which there could be many, all different), i.e. how the data got in there.

                      • 8. Re: Calculation field to display values of checkboxes
                        erolst

                        marksealey wrote:

                        firstString  & "  + " & secondString    

                         

                        Use '&' as the concatenation operator; contrary to (some) other programming languages, '+' does not work with strings.

                        • 9. Re: Calculation field to display values of checkboxes
                          marksealey

                          Todd,

                           

                          Thanks for that!

                           

                          I suspect my Relationships now because when I display the related fields from the related table on the Layout in question, but as Edit boxes, you're right - they are empty.

                          • 10. Re: Calculation field to display values of checkboxes
                            marksealey

                            erolst,

                             

                            It may be a good idea if you describe your real tables / data; your examples don't make too much sense:


                            I'm sorry. Yes, of course.


                            Here's the actual calculation field:

                            Let (

                                    [

                                      sherryString = If(Wishart Clusters Xistics::Wishart Cluster Sherry = "Yes"; "sherry"; "not sherry");

                                      fruitString = If(Wishart Clusters Xistics::Wishart Cluster Fruity = "Yes"; "fruity"; "not fruity");

                                      outputString = sherryString  & "  + " & fruitstring        

                            ];

                                      outputString

                            )

                             

                            And the Relationships on which it's based… a database to log whiskies :-))))

                             

                            relationships.jpg

                            • 11. Re: Calculation field to display values of checkboxes
                              marksealey

                              erolst,

                               

                              Sorry if that wasn't clear: the plus is actually a literal inside quotes between the two variable names.

                              • 12. Re: Calculation field to display values of checkboxes
                                Fred(CH)

                                marksealey wrote:

                                 

                                […]

                                I'm actually using No and Yes as Custom Values in a Radio Buttons Value List.

                                And it doesn't work with:

                                other_table::field1 = "Yes" etc

                                It always returns "No", IOW - the test always fails.

                                How do I detect values in Radio Buttons and test for (Yes/No) there, please?

                                […]

                                Hi,

                                 

                                Maybe you can try to simulate boolean type field, instead of using a text type with value list that contains Yes and No.

                                 

                                You can do this in a number field, with an auto-enter calculation like this :

                                 

                                GetAsBoolean ( Self )

                                [ Systematic evaluation checked ]

                                 

                                And use a value list with only "1" as unique value on your layout to control the checkbox field.

                                 

                                It can seem trivial, but it can really facilitate related aggregation.

                                 

                                Then, from the parent table, you can use aggregate function like :

                                 

                                GetAsBoolean ( List ( OtherTable::Checkbox ) ) // to know if there is at least one related record that have a True value

                                Sum ( OtherTable::Checkbox ) // to know how many related record have a True value

                                 

                                Don't know if it cans help you. Good luck.

                                 

                                Bye, Fred

                                • 13. Re: Calculation field to display values of checkboxes
                                  erolst

                                  marksealey wrote:

                                  And the Relationships on which it's based… a database to log whiskies :-))))

                                  I've seen stranger things …

                                   

                                  As suggested, you could a number fields like isSherry, isFruity (, isFloral etc.), and a calculation like

                                   

                                  Let ( [

                                    sherryString = Case ( not Wishart Clusters Xistics::wishartIsSherry ; "not " ) & "sherry");

                                    fruitString = Case ( not Wishart Clusters Xistics::wishartIsFruity ; "not " ) & "fruity");

                                    ] ;

                                    Substitute ( List ( sherryString ; fruitString ) ; ¶ ; " + " )

                                  )

                                   

                                  Ba aware that this calculation will only evaluate the first related record; but since I'm assuming (well…) that this is a one-to-one relationship to get that bunch of fields out of the way, that's OK.

                                  • 14. Re: Calculation field to display values of checkboxes
                                    erolst

                                    marksealey wrote:

                                    Sorry if that wasn't clear: the plus is actually a literal inside quotes between the two variable names.

                                     

                                    You're totally right; I need new eyes glasses …

                                    1 2 Previous Next