Chart portal records but by totals

[Clayton King]

Hi everyone - 

I have a portal that consists of many rows - as few as 1 and as many as 50. In each portal record is a field called Tempo, which contains one of six values from a value list.

I'd like to create a pie chart consisting of six (or less) slices based on the portal records. In other words, perhaps counting records with Tempo=X and using the result to chart.

Is this possible?

[comment]

It's not only possible, it's quite easy.

In the Data Source section of the Chart Setup, select 'Related Records' as the Data Source, and select the portal table as the Related Table. Next, select 'Summarized groups of records' and specify sort order by the Tempo field*.

Then, in the Chart section, select the Tempo field as the Category Labels and a summary field counting the records in the portal table as the Slice Data.

--
(*) It is customary to order the slices from the largest to the smallest. To do so, choose 'Descending order', specify 'Reorder based on summary field' and select the same summary field as before.  

 

[Clayton King]

48 minutes ago, comment said:

a summary field counting the records in the portal table as the Slice Data.

Sorry to be dense... does the summary field go in the parent table or the portal table? And is the calculation count(Tempo) or something else?

I have a few summary fields used for a variety of things (i.e, one for counting portal records in total, or by a couple of other options).

Thanks!!!

[comment]

The summary field needs to be in the child (portal) table. There is no calculation involved; the field should be defined as Count of [ any field that cannot be empty ]. Usually you would use the table's primary key field for this. If you already have such field, then use it.

 

 

[Clayton King]

Thanks, @comment. I'm making progres!

Below is a grab of the chart being generated. I'm not sure if this is the expected result, or if I didn't explain my desire well... Instead of each record in the portal showing as a slice, I'm trying to get all the songs of the same tempo to be a slice. So at most, I would ever have six slices - Ballad, Moderate, Downtempo, Freeform, Upbeat and Fast.

Can you tell me what I've done wrong?

As an added challenge, I have conditional formatting in other layouts that change the color of the portal record based on this field. Ideally, I'd like to use that color scheme in the pie slices. But that's also a pie-in-the-sky request (pun intended).

Clayton

This chart's Data Source has "Individual record data" selected. The one below has "Summarized groups of records" selected.

Edited May 2, 2021 by Clayton King

[comment]

1 hour ago, Clayton King said:

Can you tell me what I've done wrong?

All I could do is guess.

Compare your file to the attached demo.

ChartRelatedSet.fmp12

[Clayton King]

@comment, thank you so much! Silly mistake!

My chart was set up exactly as yours was. To troubleshoot, I copied your chart into my layout and still encountered the same issue. But that led me to dig deeper and I discovered the Tempo field had erroneously gotten  changed to a number field instead of text. It now works! I'm going to keep trying to play with the color issues, but this is exactly what I was attempting to do.

Thanks for taking the time to put that together. I've learned a lot from this exercise!

Clayton

[comment]

20 minutes ago, Clayton King said:

I'm going to keep trying to play with the color issues

Unfortunately, Filemaker's chart tool will not let you control the colors beyond choosing from a list of color schemes. However, you could use a web viewer to generate your own chart using HTML/CSS/Javascipt.

 

[Clayton King]

Just now, comment said:

Unfortunately, Filemaker's chart tool will not let you control the colors beyond choosing from a list of color schemes. However, you could use a web viewer to generate your own chart using HTML/CSS/Javascipt.

 

I was just researching that. I'm on FM18 and would only use the solution on my computer, so not sure that would work for me.

Thanks for responding!!!

[comment]

9 minutes ago, Clayton King said:

I'm on FM18 and would only use the solution on my computer, so not sure that would work for me.

I don't see why not. It's a lot of work, though.