Create a JSON from the found set to use in a java-chart

[andersmnystrom]

I'm learning JSON to collect data into a java graph. The reason is that I want to show the answers to a survey question in a line chart. The table structure looks like this:

(I do not know why the graph shows the relationships as many to many, they are not, but it is actually one to many. If anyone knows why it is like this then please tell..)

I have a list view based on the responses table. I want the graph to show the requested result in the list view. The results are always from one question but the question is asked three times a day (in three different surveys) so there are three lines per date. The fields in the list view that I would like to collect in a JSON are
SurveyMoment::date (date)
Svara » Survey::name (text)
Responses::answer (number)
I would like a graph showing three lines where each line shows the answers from the different surveys. Currently, my script that creates JSON looks like this:

The calculation in the Set Variable on row 7 is this:
JSONSetElement ( $theArray ; "[" & $index & "]" ; 

JSONSetElement ( "" ; 
["date" ; Responses::date; JSONString ] ;
["answer" ; Responses::answer; JSONNumber] ;
["survey" ; Svara » Survey::name; JSONString]
)

; JSONObject )

Which results in a JSON that looks like this:
[
    {
        "answer" : 3,
        "date" : "2019-10-07",
        "survey" : "Survey 1 morning"
    },
    {
        "answer" : 3,
        "date" : "2019-10-07",
        "survey" : "Survey 2 lunch”
    },
    {
        "answer" : 2,
        "date" : "2019-10-07",
        "survey" : "Survey 3 evening"
    },
    {
        "answer" : 1,
        "date" : "2019-10-08",
        "survey" : " Survey 1 morning "
    },
    {
        "answer" : 2,
        "date" : "2019-10-08",
        "survey" : "Survey 2 lunch"
    },
    {
        "answer" : 2,
        "date" : "2019-10-08",
        "survey" : "Survey 3 evening"
    }, 
etc…..
]

This works if I constrain the result so that it only shows answers from one survey. But if I want three lines in the graph then JSON would have to look like the following.
[
    {
        "date" : "2019-10-07",
        "survey 1 morning" : 3,
        "survey 2 lunch" : 3,
        "survey 3 evening" : 2
    },
    {
        "date" : "2019-10-08,
        "survey 1 morning" : 1,
        "survey 2 lunch" : 2,
        "survey 3 evening" : 2
    }, 
etc…..
]

Anyone know how to create it based on the found set in the list layout based on the Responses-table? The idea is that I can filter the found set by choosing the question I want to see the answers from and choosing the time period. So, every time the found set is updated, the chart should be updated automatically and only show the answers from the found set.
 

Thanks!

Edited October 25, 2019 by andersmnystrom

[comment]

This is a great exercise in constructing JSON. See if the attached demo works for you.

 Grouped2JSON.fmp12

 

4 hours ago, andersmnystrom said:

why the graph shows the relationships as many to many,

Because Filemaker doesn't know it's supposed to be one-to-many. You need to either validate the parent's ID field as unique, or make it auto-enter a serial number/UUID.

 

Edited October 25, 2019 by comment

[andersmnystrom]

Thanks! i will look at the demo tonight. 

That's what is so strange. The parent Id fields are set as unique and Im using Get ( UUID ) as auto-enter but still it looks like many to many in the relational graph.

[comment]

19 minutes ago, andersmnystrom said:

The parent Id fields are set as unique and Im using Get ( UUID ) as auto-enter but still it looks like many to many in the relational graph.

Can you post a file showing that?

[andersmnystrom]

2 hours ago, comment said:

Can you post a file showing that?

Sure but I cant get it to upload. Going to try again a little later or try to send it to you via pm.  But in practice, I think it works as one to many relationships but that the relationship graph shows it wrong for some reason. It became this way when I started to use FileMaker 17 and then the same in 18. If I open the same file in FileMaker 16, everything looks right....

Big thanks for your demo file! Looked at it a bit now and it seems to work just as I wanted it. Going test it in my file now.