Trying to produce a Summary Report of Multiple Multiple Choice fields.

  • 0
  • 2
  • Question
  • Updated 2 years ago
  • In Progress
I have a database with quite a few records that I've created to track a process end to end, but they different types of record use different parts of the whole process. The database stores the dates of different parts of the process being started and completed (it's in Working days currently).

I've set up a field to compare the duration of each process section vs the targets for the duration of that part of the process. However, that means that all of the 11 comparison fields are Multiple Choice fields that indicate when the Duration is on or below the target with a " Yes", over with a "No", or does not have either a start and/or end date (ie Null value) which produces a 'No Value" entry.

It is quite possible for similar records to omit different parts of the process, thus there will be Null values in the durations as well as 'Yes' and 'No'.

What I would like to do is produce a Summary report with 2 rows (first a count of the 'Yes' and the second a Count of the 'No') for each year.

It would be grouped by Year, Record Type and Module.

My problem seems to be understanding how to build this (and/or whether you need to set up another table to lookup those figures to get that detail) or whether a Summary report might be able to do this (currently I don't see that utility).

Any help would be appreciated.
Photo of Kenneth Melvin

Kenneth Melvin

  • 140 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
A QuickBase report table.

So somehow you will have to consolidate your information into a single table.You seem to say that different parts of the process have their records in separate tables.

Is there a common field that these tables all have? For example can they all linked to a single project number record as children of that record?
Photo of Kenneth Melvin

Kenneth Melvin

  • 140 Points 100 badge 2x thumb
The Fields are already part of a common table that covers the entire project and the various details of it (which means it's a very large table with many fields tracking the process from end to end). In each incident some date fields are used, some are not.

[I'm going to look at pulling out each part into their own table in the future as was suggested, because it would make a lot of sense to do so, and I probably should have done so previously, but that doesn't change the current problem.]

The system is already running and has for quite a while now, and provides normal reports without problems but working out how to show the summarised data (for how many meet the Target and how many do not, whilst not including those who do not provide a value) in a report with multiple criteria (some chosen by the user) has proved a significant problem.

-----
I have the durations worked out and the Multiple choice formula fields to pick out whether the durations meet the target or not (or have 'No Value' ie are Null), but it's how to build a report to do what I need to produce for the User because a normal Report doesn't seem to provide enough utility.

In Quickbase, unless I am wrong, you don't seem to be able to pick out criteria in any summary (only the total number), so I can't pick out how many 'Yes' and how many 'No' there are in each column, which is what the report is trying to display.

I am trying to figure out how it is possible to get that information from the table in a format that works and allows the user to specify their different criteria ( A specific period; Region; Type, etc) grouped by Year (field already created to pick that out),
Region etc

I hope this makes a little more sense now.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
It recommend putting all those "parts/stages" into a child table of the parent project.  Then all your dates would be in one field, all your durations would be in one field, and all your yes/no answers would be in one field, just multiple records.  Then creating the summary reports and durations will be a breeze.

This might take some data export and import, but with some plaining it will be much better in the long run.

And will make your app data structure much cleaner.
Photo of Kenneth Melvin

Kenneth Melvin

  • 140 Points 100 badge 2x thumb
I think I take your overall point regarding parts of the process (and that is probably fair), but I'm not entirely sure I understand what you mean regarding '1 field'.

All of the different Durations (and check fields) are in 1 field each.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
If you put them in a child table, your common duration calculation will only need to be made once.

Say you have 5 parts to the process; Plan, check, build, inspect, finish.
And you have a start date & end date for each of those, and in turn a duration.

If you make a child table you would need 4 fields on the child table;
[Part Type], multiple choice field (Plan, check, build, inspect, finish.)
[Start Date]
[End Date]
[Part Duration] = [End Date]-[Start Date]

Now you would have 5 child records for each part of the project, but the great part here is all your "durations" are in one spot, to if you want to make reports, that compare durations from one part to another, you can.

Overall, your reporting capabilities grow greatly because you have all 'like' data in one field.
Photo of Kenneth Melvin

Kenneth Melvin

  • 140 Points 100 badge 2x thumb
It's not comparing it vs each other, it's comparing the actual figures to the target figures that they should be under, and seeing how many manage to get below the target and how many don't and counting those numbers.

Your point may be valid, but it doesn't help me with the current problem and will be an extended project that simply isn't going to happen any time soon. I'll look into making those changes, but at best they will need a significant amount of time and effort.
Photo of Kenneth Melvin

Kenneth Melvin

  • 140 Points 100 badge 2x thumb
So, the question that actually needs to be answered, is how do you 'count' the number of times
and display that beside each other in a report

The Idea of the Report would be a report over a specific time period (and probably type of Incident also a criteria) and you'd have 'Yes' as the first Row (the count of those that made the target) and below it Row 2; a count of the Durations did not meet their target (but did have a duration because they used that part of the process).

This sort of thing.

YES     8        9          13    4         12

NO      16      15         8     23       15 


Can that be done (as summary just seems to count the number of Incidents as it's default without any utility at all).

Some help regarding that would be helpful.
Photo of Kenneth Melvin

Kenneth Melvin

  • 140 Points 100 badge 2x thumb
I already have multiple choice fields to pick out which hit the targets 'YES' and which don't 'NO'.

It's largely is there any way I can make a report show the count of 'YES' in a particular field in the 1st row, and the 'Count of 'NO' in the 2nd row.

It shouldn't be that difficult, because it's easy to do in excel, but it seems to be an area that, at best, is just not obvious how it is possible. At worst, it's something that Quickbase does not seem to be able to do due to the lack of flexibility in the reports.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
Is this what you are after?

https://yqc.quickbase.com/db/bmp4hpum5
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
Actually, in recre axing your post,in that example, while I don't have the Yeses and the Nos in separate riws, you can use the Rows for some other grouping. that you also need.
Photo of Kenneth Melvin

Kenneth Melvin

  • 140 Points 100 badge 2x thumb
In effect you are correct, that format would be fine (except I'd move around the YES and NO to be alongside each other and the Rows would be replace the months with years, I suspect. And the way you have set it up, naturally makes perfect sense, and I should have thought of it before. I just couldn't see the wood for the trees. That should do it.

I knew that trick to count fields but had completely forgotten.

Thanks very much for reminding me.

----------
Evidently, I've been missing quite a lot of changes that have happened to the customisation of a Page (or Dashboard) that can be done.  I'll need to have a look at that to make sense of it all.

Any suggestions beyond:

http://help.quickbase.com/user-assistance/?&_ga=1.31082623.1385625431.1491159602#customizing_das...

as I've now found that page to work through and try to make sense of how much customisation can be done.

Thanks again.
(Edited)