You know that a week after you get the trend line, they're going to ask for another Excel charting feature...?
You could maybe look at scripting the export of the data and import to Excel with a pre-formatted chart. FM can export the data and open the Excel file automatically. The excel file can be set to referesh the chart data upon opening, so to the FM user it looks pretty seamless. You can even embed the excel chart in a container field (but that's my hobby-horse, so I'll pipe down now.).
Thanks sorbsbuster, we're trying to step away from Excel - and keep everythin Filemaker based...
I'm just trying to emulate the Excel chart (which I've done except the trend line) for ease of data entry for a large amout of users... I was
wondering if you might be able to tell me more about what you explained in your above comment?
Ideally I would like to have a calculation that can express the trend line embedded within my Filemaker chart, but it's looking like it may be to
much of a complex equation so if the best solution is to embed an Excel chart in my database; i'd be happy to go with that.
Let me know,
I won't quote you detail as I don't remember it accurately enough, and I would only mis-lead you. The background to this was similar to your post - we wanted to have as many pivot table features as possible directly from FM. We stumbled across inserting an Excel sheet into a container field and it worked brilliantly - the user double-clicked the container field and was presented with a fully-operational Excel sheet. The next record could hold another sheet, and the next record another... After standing back in amazement at the power of what we had 'discovered', we noticed a few bugs. The Excel sheet would sometimes open squashed into the container field borders, and was unreadable. And the FM file size exploded (like it does when you insert unlinked images into a container). But the potential was awesome - in a tightly controlled demo it looked like we could deliver the power of FM and XL in one really tight package.
It went no further because no-one in FM would help us with the couple of bugs (which I'm sure were just our mis-understanding). A Big Zero interest.
Anyway: what we do now, in principle, is:
- have a script find and export the data as a merge file, to chart. Always to the same destination filename and path.
- design an Excel sheet to import the data and refresh any charts, pivot tables etc
- have a macro do the import refresh, worksheet re-draws etc in XL, and set it to run on opening (makes it more seamless, but may not be what you want every time for every user)
- have a container field on the FM record link to that file, or have a 'Send event' script step to open the xl file and bring it to the front.
Works for us.
This is another way to chart and display trendlines for line charts entirely within FM. It just requires you to calculate the equation of the line. Then add the line as another data series.
Unfortunately, I am trying to do this for scatter plots, and I don't see a workaround for including a line in a scatter plot.