Post

3 followers Follow
0
Avatar

Calculate the total of each category instance in separate calculation fields.

I am trying to create a calculation field that simply counts the instances of a particular choice in another field within the same app. I will then use this number via a relationship in another app, but first things first. 

So:  The 'Software' category field has 'Software 1', 'Software 2', 'Software 3' options. 

In the 'Software 1 Use' field, I want a calculation that displays the number of items with 'Software 1' selected.

Seems like it should be so simple, but I've not been able to find any guidance. 

Thanks!

Andrew Francis

Please sign in to leave a comment.

4 comments

0
Avatar

Could you provide screenshot of your item?
I dont understand how many category fields you have

Andrew Pryhodchenko 0 votes
Comment actions Permalink
0
Avatar

The example is hypothetical - one category field with five options - we'll call them Opt1, Opt2, Opt3, Opt4, Opt5

I want a single calculation field that can tell me how many items have Opt1 selected. 

I am designing this for software license tracking in a workstations inventory app. I have 50 licenses for W10 LTSC (Opt1) for example, and want to have a summary in my workstation inventory app to calculate: Total license (50) minus the number of workstations with the W10 LTSC category option selected. This would show on the dashboard so that I can see a snapshot of how many licenses are available, and of course, would transfer this knowledge to many other uses once I know how to do it. 

Hope that is clearer. Thanks! 

Andrew Francis 0 votes
Comment actions Permalink
0
Avatar

If i understood your task correctly:

I created 3 categories with same options and selected different combinations.
In other field i wrote what i want to count, and counted it in calculation field;
3 sreenshots with different selections and count result attached,

My code not so pretty, but working) :

var c1=@Category 1.toString();
var c2=@Category 2.toString();
var c3=@Category 3.toString();
var f=@Want to find;
var c;
var t=0;

c=c1+","+c2+","+c3;
c.toString().split(",")

for (var i=0; i<c.length; i++)
{
if (c[i]==f){t++}
}

t;

 

Andrew Pryhodchenko 0 votes
Comment actions Permalink
0
Avatar

Thanks again Andrew - works as you created it inside each item, but does not count across all items in the app (like the data in the dashboard does) -

It DOES NOT work if I change the category options to text and include those options in the 'Want to count' field.

It DOES work if I include a number that is found in the field data - so, for example, 'W10 Enterprise LTSC' includes a 1 - if my 'Want to count' field has 1 in it, it will be counted, but if it has 'Enterprise' in it, the category will not be counted because, I assume, 'Enterprise' is text. 

Also, I only need one category field and have simplified my test app to have one field, but once I have it working, I may have several summary fields. These summary fields will then be used elsewhere by other apps. 

The example in the screenshot is local to my test app (no relationships) but I want to develop this to count the instances of each category option in other apps as well as this one. My understanding is that I need to create a relationship on all items to the other app, then change the @OS field to @all of OS? 

It's too bad the dashboard summaries can't be referenced...(or can they?) as they do exactly what I want to do. Summarize the number of category options in each item for the app. 

 

Many thanks! 

 

Andrew Francis 0 votes
Comment actions Permalink