Jump to content

Need help charting 'snapshot' historical record data


Grundly

This topic is 3054 days old. Please don't post here. Open a new topic instead.

Recommended Posts

What I have:

 

• A table of Animal. Four records in this example. Three fields: a primary ID (number), a Name (text), and Quantity (number). TheQuantity number field's contents gets changed by users at various, random intervals. 

 

• A table of Snapshots. Twenty records in this example. Four fields: a primary ID (number), a foreign key (for Animal), Date, and Quantity.

 

• A script that runs once every 24 hours (perhaps from Server). The script simply looks at the current state of each record in the Animal table (Quantity), then goes to the Snapshots table, and creates one record for each Animal (foreign key), and its currently sampled Quantity. The result is a table which shows the state of the Animal table as it was when the script ran, once per day.

 

What I want:

• The goal is to chart this Snapshots data (Day-by-Day) using FileMaker's built-in native charts (no web viewer/javascript/etc), and also, to not use ExecuteSQL.

 

• Bonus would be the ability to allow us to select which of the Animal we want to chart (we may not always want to chart all Animals)

 

• Bonus #2 would be to allow us to select the date range of the snapshots (we may not always want to chart all snapshot days)

Thanks!!

Animals.fmp12

Link to comment
Share on other sites

I am unable to look at your file, but since no one picked this up, I'll make a few general statements:

The way Filemaker's charting tool is built, they expect you to compare the data by producing an individual chart for each animal, something like:

multi.png.36229119c5450cb8ba35788f4b5f9c

This is very easy to produce: use a layout of your Snapshots table, place the chart in a sub-summary (by AnimalID) part, and set the chart to use data from current found set, with 'Show data points for groups of records when sorted' checked. You can also easily select which animals to include and which date range to chart simply by performing a find.

If you want the comparison to appear in a single chart like this:

series.png.2bf0fd0e69d805064542d59d37083

then it gets more complicated. The chart tool requires each data series to be hard-coded - which means you must know in advance the maximum number of animals that will ever be charted, and provide a data series for each one. The chart above shows three series, and if you decide to show only two animals the legend will still show three items. In addition, Filemaker will not collect the data from sorted groups into the data series automatically: you must script this (or use some other method, e.g. ExecuteSQL).

Another thing to keep in mind when charting multiple series is that each series must have exactly the same data points (dates in this example). Otherwise the chart will show wrong data for the missing dates.

 

Edited by comment
Link to comment
Share on other sites

Excellent information, Comment. I shall try to implement this, thank you. If I get it working, I'll post the new file up here for anyone who wants to see. (Perhaps I should .zip it next time?)

Link to comment
Share on other sites

19 hours ago, comment said:

The chart tool requires each data series to be hard-coded

Quick question... How about using $$globalVars there in place of hard-coding? Any problems you can forsee using that approach?

Thanks

Link to comment
Share on other sites

2 hours ago, Grundly said:

How about using $$globalVars there in place of hard-coding?

I am afraid you may have missed the point about hard-coding. Of course you would use variables as the data points/series in a scripted solution. The thing is that data series cannot be created dynamically. You must create, in advance, as many series as you will ever want to chart.

Link to comment
Share on other sites

This topic is 3054 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.