Hello. I have a field "equity focus area" that has five different data choices (race, gender, national origin, religion, and SES). The field "equity focus area" is in a table named "Project" and each "Project" can have multiple "equity focus areas". For example, one project could be about "race" and "religion". I am interested in learning how I can count each instance of an "equity focus area" to display in a report such as a pie chart. Currently, the "equity focus area" field is a multi-select text format.
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.
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]
So there is one table and one field in question and you want a pie chart to summary each record? If this is the case the pie chart would have slices that are all the same size. Or are you trying to summarize into a pie chart over a report's worth records? I don't understand the structure - sample data would help.
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 andSouth 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 oneRich Text Formula field to do everything. No relationships, no user defined variables, no code pages, no summary or lookup fields. Just oneRich Text Formula Field.
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.
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 oneRich 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.
