How to set Series on Y axis to segregate data?

April 29, 20223 yr

I've been able to successfully create the chart below (with tremendous help from comment) - but now have hit a roadblock on how to segregate the data within the month columns.

I know I need to set up a series in the Y-axis, but not at all sure how to create this series correctly.

The goal is to show all inspections by month, for ALL inspectors, with a stacked bar chart showing how many inspections each inspector completed.

I have the chart in a header, the appropriate fields in the body, and a summary field cCount of records in a Sub-summary field when sorted by month.

Any suggestions would be much appreciated!

April 29, 20223 yr

This is not as easy as it would seem. The built-in chart tool is rather limited and not well-suited to this task.

The main obstacle here is that each data series must be defined in advance when setting up the chart. This is not a good match to the data you want to chart, where the number of inspectors performing inspections, as well as their identity, can vary from month to month. Filemaker handles this perfectly when you produce a report with sub-summaries. and you can easily generate a report in the form of:

Jan 2022
• Adam: 7
• Betty: 5
TOTAL: 12

Feb 2022
• Adam: 6
• Cecil: 3
• David: 4
TOTAL: 13

But to reflect this same data in a chart, you must pre-create a data series for each possible inspector and hard-code it to a specific inspector. Which does not sit well with the philosophy of a relational database, where the schema is supposed to accomodate any number of inspectors and keep working as some inspectors leave and others take their place.

This is not to say that the task is impossible. In fact, there is more than one way to solve this. But it will require extra work and some limitations will apply.

Before I continue, let's just make sure we are on the same page regarding your data structure: I presume you have a table of Inspections and a table of Inspectors, related by InspectorID?

--
P.S. I should also point out that these and other limitations to the built-in chart tool drive developers towards more advanced charting available by using Javascript in a web viewer. But that has a learning curve of its own.

April 29, 20223 yr

Author

1 hour ago, comment said:

This is not as easy as it would seem. The built-in chart tool is rather limited and not well-suited to this task.

This actually makes me feel a bit better about all this. I have typically been able to make more progress on a new FM skill, but charting is proving difficult.

1 hour ago, comment said:

Before I continue, let's just make sure we are on the same page regarding your data structure: I presume you have a table of Inspections and a table of Inspectors, related by InspectorID?

Unfortunately no - the inspector name is not a separate table, it is a field on Inspection record, selected via a value list.

April 29, 20223 yr

15 hours ago, Darren Emery said:

the inspector name is not a separate table, it is a field on Inspection record, selected via a value list.

That's not an optimal arrangement, because people's names are neither unique nor permanent. But leaving that aside for now, if you define a summary field named sListInspectors as List of Inspector, you can set up the first data series as:

Series Name =

GetValue ( UniqueValues ( Inspections::sListInspectors ) ; 1 )

Data =

Let ( [
item = GetValue ( UniqueValues ( Inspections::sListInspectors ) ; 1 ) ; 
list = GetSummary ( Inspections::sListInspectors ; Inspections::cMonth )
] ;
ValueCount ( FilterValues ( list ; item ) )
)

For the second series, change the value numbers in the GetValue() functions to 2, and so on.

Note that is semi-dynamic: there wiill be no data series for inspectors that do not have any inspections in the current found set. But, as mentioned earlier, you must create enough data series to accommodate the maximum number of inspectors in any found set you intend to chart.

Edited April 30, 20223 yr by comment