How to create a Bar Chart that shows the Office divisions on the X-Axis and the Number of Staff on the Y-Axis?

[emtau]

I have a simple database with Field Staff Name, Gender and Office Division. Then I wanted to create a Chart that will show how many staff are on each Division. I am thinking of a Bar Chart or a Pie Chart. I have no experience working with Charts so if you could please help me with this simple one and I will try my best to build maybe a complex chart later.

Thank you.

[comment]

It's not the simplest chart you could think of, but see if the attached demo helps.

ChartSortedGroups.fmp12

[emtau]

13 minutes ago, comment said:

It's not the simplest chart you could think of, but see if the attached demo helps.

Thank you.

Edited September 7, 2023 by emtau

[emtau]

I would like to extend on this question, is it possible to create Chart from a different table and what would be the relationship between the Char table and the Staff/Groups table? I asked this for the purpose of maybe a Dashboard where the Dashboard will show Charts of sorts as for analysis view, but those Charts actually reflect the data on other tables related to this Dashboard table?

[comment]

A chart can use data from one of the following sources:

• records in the found set of the current table;
• records in a related table;
• return-delimited list/s (held e.g. in a calculation field or in a variable).

If you wanted to produce the chart from a Dashboard table and still have it reflect all staff records, you would need to make all staff records related by using the X relational operator. Alternatively you could script the process and create return-delimited variables to be used by the chart. This would not require a relationship at all (and might be preferable especially if you also wanted to show data from other tables).

 

[emtau]

I noticed that the Chart response to the sorting of data so it can summarize groups of records in the current found set properly. The issue I have is that I want to show two different Charts side by side so that the user can view them both on the same layout for comparison, etc. I've attached the same database that you've given me as an example and make some changes to reflect what I want it to do but I'm having trouble with the issue I just explained. Is there any way both charts can be shown at the same time but each Chart response to their own sorting of data? Currently both Charts are affected by the same sorting.

Please shed some light on this and if possible please show it on the database uploaded so I can better understand how it works.

Thank you again.

ChartSortedGroups.fmp12

[comment]

2 hours ago, emtau said:

Is there any way both charts can be shown at the same time but each Chart response to their own sorting of data?

 

On 9/7/2023 at 10:16 AM, comment said:

Alternatively you could script the process and create return-delimited variables to be used by the chart.

Since you need to populate the variables with sub-summary values, I suggest you study a technique known as Fast Summaries by Mikhail Edoshin.

 

[emtau]

Greetings again "comment" and whoever is willing to help. I haven't look at Fast Summaries but I managed to solve my problem with using two table occurrences of the main table. Each table occurrence links to each Chart and this works very well that I can do sorting, filtering etc... on the main table without affecting the two Charts. Now there is only one thing that I need both charts to reflect on: and based on the filemaker sample file that you've uploaded, I've edited it to better suit my example. You will notice I added a new Field called "Inactive_Since" and this field defines when the staff is no longer working at the workplace. I don't want to delete the staff record but rather keep it but not show it. So I was thinking around something like when I first open the database... an open script will run a find to show only Active Staff (that is Staff without "Inactive_Since" Field's value). this works fine when I run the script but the Charts are not reflecting it maybe since they are on a difference table occurrence and those occurrences are not on the layout running the script or whatever.

This is only the last part that I need and I'm all set with the Chart that I'm trying to build. If only the two chats can:

- sort independently to show different charts (solved)

- main table sorting and filtering etc doesn't affect the two charts (solved)

- if "Inactive_Since" field has value, then exclude it from find result but also show it on both Charts (NOT Solved)

Could anyone please explain what needs to be done since I'm kinda out of Ideas. Please check the uploaded file to better understand my problem.

ChartSortedGroups.fmp12

[comment]

Performing a find has no effect on which records are related. If you want your charts to chart data from related records, you need to control which records will be included at the relationship level.

To be able to select between active and inactive staff, you will need to add two more fields: a calculation field, let's call it IsActive (result is Number) =

IsEmpty ( Inactive_Since )

and a global Number field, say gActiveStatus. Then define the relationships as:

Staff::gActiveStatus = Staff_ChartXYZ::IsActive

Now make your buttons populate the global field with either 0 or 1.

Edited October 2, 2023 by comment