1 of 1 people found this helpful
I think the problem is coming about as as result of the formatting of the data in the executeSQL statement. For one, you're missing the CarWEight in your SELECT clause. 2nd, the numbers doesn't need the single quotes around them (quotes around numbers arent used in the api documentation), and that is happening because you need single quotes around the car name. The ExecuteSQL statement is trying to collect and format the data all at once, and that gets messy.
I propose a different solution: Use a script to collect and format the data, then plug it into the correct spot in the JS to create the chart.
see the attached file. I created a new layout and a new web viewer that contains how I do this kind of work.
Basically I'm collecting the data from the table, formatting it correctly for the api and then placing it in a field "Data". I then substitute that into an HTML Calc field.
There's many advantages to this method:
1. You don't have to deal with escaping quotes needed for the quotation marks.
2. You can store the data, which makes the web viewer faster to render.
Personally I'd never use ExecuteSQl inside a web viewer. You cannot control when it runs, and the running of ExecuteSQL could take awhile depending on how many records it has to go to, thus the user would be stuck not knowing what is happening. And, as I said above, ExecuteSQL is good at collecting the data, not so good at formatting it correctly.
The revised web viewer shows the data much faster. Of course you do have to actually gather the data from a script, but that isn't a problem if the data doesn't change too often.
Check out my revision of it. It takes a bit more to set up but it, i think, is more efficient.
Jeremy, you my friend are a legend of the digital world. Its all working beautifully over here now, thanks for the tips and lengthy reply and awesome technique with the script to collect all the data, love it. I'm a newbie so this stuff is new to me and the example is gold dust, its to have a simple working solution that I can understand the logic behind and be able to pull apart and replace with new fields. I've added a load of additional series to the graph and its working great. Syonara Filameker native charts, never liked them anyway! I've added a button beside the graph to run the script and update the graph. Thanks again jeremy you've saved my bacon
2 of 2 people found this helpful
You're welcome. I'm glad to help. The Web Viewer is a great tool.
I've done some blog posts about it as well as have some colleagues. I am currently working on a library of integrations that can be used by anyone using the same principle i showed you (my library further breaks down the CSS/ JS into fields and such).