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 ????
These two fields have dropdown list having different values.
That does not tell me if a single field has multiple values or not.
both fields has multiple option.
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.
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.
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.
- 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.
- ExecuteSQL can produce the same counts
- 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.
- 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.
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;
count = 0;
this is what i exactly wanna do..
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?
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.
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.
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.
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.