14 Replies Latest reply on Aug 3, 2017 7:22 PM by philmodjunk

    multiple condition check in IF statement in calculation field

    anjani

      Hi,

       

      I am trying to check two different fields condition in calculation field and count the number of occurrence of the particular value in the field. "Status List " is one field with values and "Group List" is another field with other values. Now m trying to count occurrence of value "WAMS" if "status list" is "IE " and selected group list is "WAMS"

      My syntax is

      If ( Status List = "IE" and Group list = "WAMS"; PatternCount ( Group list; "WAMS" ))

       

      Also I tried case statement:

      Case ( Status List = "IE" and Group list = "WAMS"; ValueCount ( "WAMS" );0 )

       

      But both are not working. something i am doing wrong.

        • 1. Re: multiple condition check in IF statement in calculation field
          philmodjunk

          Sounds like you have multiple values in the same field of the same record. Is this the case? If so, why? Might be better to use a related table with one value in each related record as this makes it easier to work with such a list in most cases.

           

          If you do have a list of multiple values in the same field, what format does that list take? Are they separated by returns? commas? or ????

          • 2. Re: multiple condition check in IF statement in calculation field
            anjani

            These two fields have dropdown list having different values.

            • 3. Re: multiple condition check in IF statement in calculation field
              philmodjunk

              That does not tell me if a single field has multiple values or not.

              • 4. Re: multiple condition check in IF statement in calculation field
                anjani

                both fields has multiple option.

                • 5. Re: multiple condition check in IF statement in calculation field
                  anjani

                  Field "Status list" has values = IE, USG, G

                  Filed  Group list has values = "WAMS, POSC, SAMM

                  all separated by commas.

                   

                  now i need to count the occurrence of each group values depending on the status value.

                  I hope m clear with my question.

                  • 6. Re: multiple condition check in IF statement in calculation field
                    philmodjunk

                    Sorry, but details are very important here.

                     

                    Does that mean that users hold down a modifier key (Shift, command, control...) and select more than one value from the list?

                     

                    If so, you are producing a list of values separated by returns. They would not be separated by commas. A comma separated list would have to be produced by other means such as calculation that replaces the returns with commas. Note that a return separated list will be easier to work with than one where the list is separated by commas.

                     

                    If the user does not hold down a modifier key, they are only entering a single value into the field for any given record.

                    • 7. Re: multiple condition check in IF statement in calculation field
                      anjani

                      Capture2.PNG

                      Here, I wanna count occurrence of "SACM" if my status is "IE" selected . that is output should be 2. How can i do this ?

                      • 8. Re: multiple condition check in IF statement in calculation field
                        philmodjunk

                        What I see is one value to a record and no commas.

                         

                        Is this a set of related records already linked in a relationship or do you need this count over all the records in the table shown?

                         

                        There are multiple approaches that can work for this depending on the context in which you want to display the count and what else you might need to do with this count once you have it.

                         

                        1. A summary report with a summary field that counts could be used. This could produce the single count of your last example or a count for multiple values of Group over all records or a subset of all records in this table.
                        2. ExecuteSQL can produce the same counts
                        3. A relationship can be set up to match only to the desired records matching on both fields. Then either a calculation using Count or a counting summary field defined in the table shown in your reply can return the count.
                        4. This same summary field could be placed in a single row filtered portal to this table to count records.

                         

                        I can provide more detail on any one of these approaches. You may want to provide more detail on what you want to do with this count to help use recommend a specific method.

                        • 9. Re: multiple condition check in IF statement in calculation field
                          anjani

                          i only need to get the total number of repetitive value satisfying the mentioned criteria. 

                          if(field 1 = a)

                             {

                              if( field b = c) then

                                    count c = c+1;

                          }

                          else

                             count = 0;

                                    

                            this is what i exactly wanna do..

                          • 10. Re: multiple condition check in IF statement in calculation field
                            philmodjunk

                            All of the above methods can do that. As I posted before, the differences that can make one option better than another depend on context and what, if anything you need to do with the count once you have it.

                             

                            To expand on context, do you need to display this count from a layout based on the table shown or from a layout based on a different table?

                            • 11. Re: multiple condition check in IF statement in calculation field
                              glennsingleton

                              Unfortunately you need to make your table definition more complex.

                               

                              You could do the following to solve your immediate problem.

                               

                              Create a field called IESACM =If ( Status List = "IE" and Group list = "SACM";1;0)

                              then duplicate that for each combination you wish to count. This is horrible design, but will achieve the result you are after.

                              Now a simple summary field Total IESACM = Count(IESACM)

                              will give you the result you need.

                              A better way would be to create another table that uses a relationship formed by combining the two fields and counting the occurrences in the related data table, then you can add each test i.e. IE & WAMS, just as another record in the new related table.

                               

                              I will leave you to decide which suits you better.

                              • 12. Re: multiple condition check in IF statement in calculation field
                                anjani

                                Thank you for the solution . I tried the first one but its only giving me the total no. of student lists. The if-statement condition is not working.

                                • 13. Re: multiple condition check in IF statement in calculation field
                                  glennsingleton

                                  If you have created the field IEACM, can you see the results if you put them on the layout? They should be showing as 1 or 0, possibly you need If ( (Status List = "IE") and (Group list = "SACM");1;0). If they are showing all 1 or all 0, then it is a calculation error. Make sure it is not a Global Calc. If you are getting 1's and 0's where you would expect the summary field ,Total IESACM = total of IEACM, should be correct.

                                  • 14. Re: multiple condition check in IF statement in calculation field
                                    philmodjunk

                                    Numbers correspond to my earlier posts:

                                     

                                    1) Perform  find specifying "SACM" in Group and "IE" in Status sm17. Then Get ( FoundCount ) will return the number of records.

                                     

                                    or show all records and sort your records by both the Group and Status sm17 fields in that order. A "count of summary field placed in a sub summary layout part for the sub summary heading this specific group of IE records will return the count. Note that this is how to get such sub totals for multiple values over large number of records.

                                     

                                    2) ExecuteSQL ( "

                                    SELECT Count ( /"Group/" ) FROM YourTableNameHere

                                         WHERE /"Group/" = 'SACM' AND /"Status sm17/" = 'IE' " ; "" ; "" )

                                     

                                    3) Define a relationship in a different table with two text fields used as Match Fields. Put "SACM" into one and link that field to Group in the table shown. Put "IE" in the second field and link it in the same relationship to Status sm17.

                                    Then this calculation: Count ( ThisrelatedTable::Group ) will return this count. Note that by editing these two text fields, you can count different groups of records with different values. A summary field from the table being counted could also return this same count.

                                     

                                    4) Define a relationship such as one with a Cartesian join: SomeTable::anyField X YourTableToCount::anyfield. Put a one row portal to YourTableToCount on a layout based on SomeTable. Specify a portal filter such as:

                                     

                                    YourTabletocount::Group = "SACM" AND YourTabletoCount::Status sm17 = "IE".

                                     

                                    The same summary field mentioned in  1) and 3), if placed in the row of this portal, will give you the count. Note that this option can be used with multiple one row portals that each filter for different values in your fields to provide different counts.

                                     

                                    Each of these provide the type of count in your example table. Each do so in different contexts and formats. Which is best for you, if any depends on what you want to do with these counts once you get them.