Calculated field to sum record counts from multiple custom fields

[DarcySm1]

I would like to create a report that will show the sum of record counts for multiple fields in a custom form. Specifically, something like this: If Field A or Field B = Choice 1, then show the total count of Field 1 plus Field 2. If Field A or Field B = Choice 2, show the total count of Field 3 plus Field 4, and so on. I'm terrible anything except the very simplest calculated fields. Is this something can can be done, and if so, can you give me the actual text to do it? Thanks for any help!!!

[skyehansen]

a couple of questions come to mind, and might help you be a bit more prescriptive about your plans.

1) what does a "total count" entail? (what are you counting in fields 1-4? or do you just mean these are all number fields, and a total count just means "field 1 + field 2"?)

2) what if field A = choice 1 and field B = choice 2? Would that involve adding all four fields?

[DarcySm1]

1) I want a total record count of the selections from Field 1 plus Field 2 (or 3 and 4)
2) No, I only want the results of like choices in A and B. If A and B are different choices, it would only count the one choice.

Not sure if that explains it clearly. 

[DarcySm1]

Here is a visual that might help. What I'm looking for is a record count of each like answer from Field 1+2, Field 3+4, etc.

[skyehansen]

Your answers aren't really lining up with your screenshot, or not in a clear way, sorry!

1) sounds like fields 1-4 are multi select fields, and you want to count the number of selections. So if I'm right and field 1 is multi select, the dropdown options are answer 1.1, 2.1, 3.1, and so on. If you select 3 answers, you want the count to be 3. 

2) I have no idea what this is saying. Looking at the table:

* if field A and field B are both choice 1, you want the total answers that are selected in field 1 and 2.

* if field A and field B are both choice 2, you want the total answers that are selected in field 3 and 4.

* if field A is choice 1 and field B is choice 2, what would it count? Nothing since they are not "like"?

[DarcySm1]

I realize this is confusing. I'm sorry I'm struggling to explain it clearly.

1. None of fields are multi-select.

2. There is logic assigned for fields 1 - 8. So, for example, if Choice 1 is selected in Field A, Field 1 is another drop-down of choices. If Choice 1 is selected in Field B, Field 2 is another drop-down of choices. The choices in Field 1 and Field 2 are the same list of choices.

What I'm trying to do is add up all of the like choices. For example, if I have a group of tasks and for 10 of them, Field 1's selection is Answer 1.1 and for 3 of them Field 2's selection is Answer 1.1 -- I want the report to show me a total of 13 for Answer 1.1

[skyehansen]

OK, so to go back to your spreadsheet then, are the following correct?

1) people are always going to have to fill out field A and B

2) However, fields 1-8 only appear based on the field A and B answers.

3) still hazy on this one. You really just want an overall count over a number of tasks.

e.g.

task 1 = Field A / Choice 1 and Field B / Choice 2 means 1x1.1 and 1x1.2

task 2 = Field A / Choice 1 and Field B / Choice 1 means 1x1.1 and 1x2.1

Total 1.1's = 2

Total 1.2's = 1

Total 2.1's = 1

If this is what you're looking for, you would normally be looking to reporting to give this type of total. Which I don't think it's really set up to do as well as you might hope. Ultimately you will need to account for being able to count all 24 different choices, and then configure a report that will show you the 24 choices in some logical order. You wouldn't be able to utilize groupings, so there wouldn't be a totalling as far as I can see. But interested to hear from others once you've confirmed what your needs are.

[DarcySm1]

1) people are always going to have to fill out field A and B 
No. Field A would be required, but field B is optional.

2) However, fields 1-8 only appear based on the field A and B answers.
Correct

3) still hazy on this one. You really just want an overall count over a number of tasks.

e.g.

task 1 = Field A / Choice 1 and Field B / Choice 2 means 1x1.1 and 1x1.2

task 2 = Field A / Choice 1 and Field B / Choice 1 means 1x1.1 and 1x2.1

Total 1.1's = 2

Total 1.2's = 1

Total 2.1's = 1
Yes, I want an over count over a selection of tasks, however Task 1 Field A/Choice 1 then gives them the option of 1.1, 1.2, 1.3, etc. You're missing a step in there. Likewise with Field B.

This might be easier to explain if I give you the actual text rather than generic letters and numbers. I'll follow up with a new spreadsheet.

[skyehansen · Accepted solution]

hi Darcy,

I don't think I'm missing a step. I'm using one example, where in this example:

* for one task, you may have chosen 1.1 and 1.2, and

* for a second task you may have chosen 1.1 and 2.1.

It doesn't mean there aren't other options. It just means I chose the options that I chose, for the two example tasks in order to provide you with an overall total count of choices you selected. I would have said the same thing below -- Task 1: Acute Care / Hospital 1, Acute Care / Hospital 2

Task 2:. Acute Care / Hospital 1, Clinical Service Lines / Behavioral Health

This would lead to 2 counts of hospital 1, 1 count of hospital 2, 1 count of behavioral health. Which I mentioned, I don't know that you can get this kind of count from a report.

The point in the original response being that you're looking to sum over a number of tasks -- and this kind of "aggregation" (sum of several tasks) -- as far as I can tell -- can only be done as a "grouping". I don't see a way for that to happen. I can certainly give you in a task report, task by task, "x number of 1.1, y number of 2.4" and so on, but then I don't see how to sum that count over ten tasks without the benefit of pulling them out into excel. (sorry to get repetitive here; I hope that you get the idea though)

If Scott has a different option that works for you, please feel free to go in that direction.

[DarcySm1]

This is basically where I keep landing. I can get part of the way there with my reports, I just can't get the totals to happen. Thanks for working through this with me!

[DarcySm1]

Here is an updated spreadsheet that, hopefully, will make this more clear. (This is only a sampling of the options to make it more manageable for this purpose.) What I want to get to is a total count for each light blue option, so total of all Hospital 1 responses, total of all Hospital 2 responses, etc.; total count for all light orange options, so total of all Behavioral Health responses, total of all Cancer responses, etc.; and total count for all light green options, so total of all Cardiology responses, total of all Dermatology responses, etc.

 

[ScottMo1]

We have done something similar to what you are looking for but on a custom form.   Briefly, here is how we solved this issue.

We have a question with a "yes", "no", "n/a" for the choices.     We then assigned each label a value in the choices:

With each response have a corresponding value - we are able to create a calculated field for each response and count how many "Yes" , "No" and "n/a" responses were given on the custom form.

Interested to find out if this response gets you close to what you are looking for.

[Doug_Den_Hoed__AtAppStore]

Brilliant @ScottMo1 <grin>. Thanks for sharing.

Regards,

Doug

[DarcySm1]

This might work. I'll give it a try.

[kautuk_sahni]

@DarcySm1 Just checking in — were you able to resolve your issue? We’d love to hear how things worked out. If the suggestions above helped, marking a response as correct can guide others with similar questions. And if you found another solution, feel free to share it — your insights could really benefit the community. Thanks again for being part of the conversation!