Formulas and Reports

There is not a simple way to make a pie chart when use a multi-select field like that.

If you are really committed to this pie chart you would have to create a child table where your Project record would have up to five child records, one for each of your selections.

So now the question is how would you initialize this table and how would you keep it maintained?

The table could be initialized manually by running a list of project records are subject to a filter where that multi-select field contained one of the choices. You would manually run that report and copy these records to the child table being sure to map the record idea of the project record into the field related project.

Now, the question is how to maintain this going forward. There are a few ways to do this but I think the best way would be to wait until December 16 when the next release comes out. When it comes out you will be able to use a new feature which would be to copy records into a table. So you would set up the automations first step to delete any child records it may have already had, and then you would have five more automations that would each run conditioned on whether or not the project contain contained one of the five choices. If it did contain one of the five choices it would copy the parent record into the trout record that�s creating one of the children.

Feel free to post back if you get stuck anywhere or else contact me via my website if you would like one on one assistance. This table could be initialized by setting up five automations. Each automation Would run 105 save table to table imports. Each of the save table to table imports would import the project record into the child record and will have a filter to only copy pro jacks where the multi-select field contained one of the 5 choices.

I would suggest that the first step in the automation would be to delete all the trail of records that may or may not already be there. That way you can run the automation every time a parent record is either added or modified or deleted. Then the next steps in the automation would be to run each of the five automations if required.

QuickBaseCoach.com

I don't understand what this means:

The field "equity focus area" is in a table named "Project" and each "Project" can have multiple "equity focus areas".

Two tables in a parent child relationship can be described as "Table1 has many Table2". But it does not make sense to say "Table 1 has many fields" if you are trying to describe a relationship between two tables.

Are you trying to say that there is one table named Projects and that it has several fields each of which is is a multiple select text field possibly named like this:

[equity focus areas 1]
[equity focus areas 2]
[equity focus areas 3]

Please clarify.

I think Karmen just had a Project Record with a multi Selectt field on it with up to 5 choices being able to be selected.

Are you saying you have data like this:

[Record ID#], [equity focus areas]
1, "race ; gender ; national origin ; SES"
2, "race ; gender"
3, "gender"
4, "race ; gender ; national origin ; religion"
5, "religion"

And want to count up all the instances of each value across all records and produce a pie chart based on this aggregated data:

Value            | Count
=================+=======
race             |  3
gender           |  4
national origin  |  2
religion         |  2
SES              |  1

I can't and understand what the goal is but I am 100% confident it can be done using one native Rich Text Formula Field!

Yes I want a pie chart with five slices like you have above except not all the slices are the same size.

yes. That is exactly what I am saying. 

No problem. I think I will make the problem simpler by introducing four regions. Here is some sample data:


I have one table Programs with three fields:

[Program Name] Text 
[Region] Multiple Choice with values {North,East,South,West}
[Equity Focus Areas] - Multiple Selection with values {Race,Gender,National Origin,Religion,SES}

There are 24 Programs code-named after the 24 letters in the Greek alphabet.

I will produce four pie charts for various "views" of this data - one for each region. The pie charts will have up to five slices and correspond to the following aggregations across reports for each region:

North
===== 
Value            | Count
=================+=======
Race             |  3
Gender           |  2
National Origin  |  2
Religion         |  3
SES              |  2

East
==== 
Value            | Count
=================+=======
Race             |  3
Gender           |  2
National Origin  |  0
Religion         |  2
SES              |  3 
South
===== 
Value            | Count
=================+=======
Race             |  2
Gender           |  2
National Origin  |  1
Religion         |  0
SES              |  3 
West
==== 
Value            | Count
=================+=======
Race             |  2
Gender           |  3
National Origin  |  1
Religion         |  3
SES              |  2

In fact, I am going to go even further to simplify this demo. I am going to structure the pie chart generation by specifying a query ti specify which to records to aggregate over. So if you wanted to aggregate over all records in say the North and South regions, you would just specify a query that returned those records and one aggregate pie chart will be generated per query.

To make the demo even simpler I am going to use only one Rich Text Formula field to do everything. No relationships, no user defined variables, no code pages, no summary or lookup fields. Just one Rich Text Formula Field.

Here is a demo of more than what you asked for.

Social Workers perform great service to our society. However, like all humans Social Workers need proper motivation and this hypothetical QuickBase customer decided to hold a motivation program and award the top performing region (North, East, South and West) an all expenses paid vacation to The Gathering of the Juggalos. Each of four regions were ranked and judged on the number of social programs they managed in these four categories (equity focus areas):

• Race
• Gender
• National Origin
• Religion
• SES

Top management needed a set of pie charts for a variety of queries and here is what a QuickBase builder came up with using only one native Rich Text Formula Field. This is truly an amazing accomplishment.

Social Worker Motivational Program ~ List All Programs ~ Group By Region
https://haversineconsulting.quickbase.com/db/bn8if57nu?a=td



Pie Charts ~ List All
https://haversineconsulting.quickbase.com/db/bn8im3hj7?a=td

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=704

Environmental Conservation Statement
No (1) relationships, (2) user defined variables, (3) code pages, (4) form rules, (5) actions, (6) external services, (7) &rdr parameters or (8) lookup / summary fields were consumed in the construction of this demo. Just one Rich Text Formula Field was used.

Kareem,
There is another no code approach here. Instead of the multi select field, use a child table where you will add up to 5 children. Then on December the 16th their will be a new release which allows a text field to be suamrized and appear similar to how a multi Select field appears.

My �Final Answer� here is to do a one time conversion of your data to use a child table and then use that new summary field type to roll up, up to 5 equity focus areas to the Project Record using the new summary field type.

If you need a faster way to add the Equity focus areas, there is a solution for that.

>My �Final Answer� here is to do a one time conversion of your data to use a child table and then use that new summary field type to roll up, up to 5 equity focus areas to the Project Record using the new summary field type. 

@Mark: I didn't know this feature was coming but I am curious how the Pie Charts would be generated when this feature lands. Are the Pie Charts generated (1) based on all the child records or (2) is the Pie Charts generated off of the new Text Summary Field?

In any event, I love it when QuickBase adds new native features as it enlarges the JavaScipt playground as now you have new territory to call the methods and APIs QuickBase used to  implement the new feature.

Often user choose a multi select field and then run into all kinds of data analysis issues as they do not really lend themselves to any kind of reporting.  The Pie charts would be done off the child records, and this new feature coming out on December 16th will allow the unique child values to be summarized up to the parent as a text string.

I had the opportunity to see this is action on a test app as I was giving feedback to Product Manager.  This will save me a lot of time in explaining how to do reverse relationships,  here on this forum,  to float up text values from children to parents, as now it will be a native feature.