Script not parsing valid JSON

[kevinD]

So I’m trying to parse JSON from a variable in memory , the parsing works in the Data Viewer and I can see the results, but in the script it does not.
I am parsing the elements into TEXT fields in my database.
Here is partial JSON:

indent preformatted text by 4 spaces

{
“RECORDS”:[
{
“Company or Entity”:“Anthem, Inc. Affiliated Covered Entity”,
“Total Records Compromised”:“78800000”,
“Latest Public Disclosure”:“03/13/15 00:00:00”
},
{
“Company or Entity”:“Premera Blue Cross”,
“Total Records Compromised”:“11000000”,
“Latest Public Disclosure”:“03/17/15 00:00:00”
},
{
“Company or Entity”:“Excellus Health Plan, Inc.”,
“Total Records Compromised”:“10000000”,
“Latest Public Disclosure”:“09/09/15 00:00:00”
},
indent preformatted text by 4 spaces

Also the script is here:

Any ideas?
Thanks
Kevin

[samfmp]

Hi, have you place the target fields on the target layout?

If I remember correctly that insert calculated result requires a field existing on the layout.

It is better to use set field to parse your JSON.

If I am wrong, please tell me.

[EfficientBizz]

@samfmp

Hi, have you place the target fields on the target layout?

preferably OFF layout. Right of the layout border.

For many of such fields, you can create a Tab Control panel to carry the fields. Though, you have a basket to move them all together.

[steve_ssh]

I believe that @samfmp has correctly identified the issue.

+1 to using Set Field instead of Insert Calculated Result.

Other observations which I hope will be helpful:

• If ever the supplied JSON contains no records, the script (as shown above) will create a blank record.

• If ever the supplied JSON is mal-formatted, the script may create a small number of garbage records.

The cause for observation #1 is that the test to exit the loop occurs after the New Record step.

The cause for observation #2 is that mal-formatted JSON can result in JSONListKeys returning an error message of several lines in length. The result is that the variable $counter will be set to some small value equal to the number of lines in the error message.

Hope these observations may help out.

Kind regards,

-steve

[anon45965781]

LOL … This looks like data I posted a while back on Hacks and compromised records.

I noticed two problems: (1) your JSON (forum text posting problem?) and your script.

FWIW, I had these data in a MySQL database so I did all the calculations using rocket-fast MySQL – eliminating clumsy JSON processing.

---

Problem 1: the JSON

I noticed … your JSON itself has issues and would not validate. In particular, the JSON’s quotes are not straight. Don’t use curly quotes – could be a forum text posting problem.

I modified your JSON slightly, and using the updated script below, it works now (I posted a screenshot since it seems that when I post straight quotes, this forum converts them to curly quotes which will NOT work in JSON. I also completed the end of the JSON.

(@Cecile: is this curly quotes issue a forum text posting problem?)

---

(I would recommend a JSON tool to help validate JSON outside FMP initially.)

Problem 2: The Script.

Below is a working example for the loop:

---

And, here’s the script I used:

Set Variable [ $index ; Value: 0 ]
Set Variable [ $numEntries ; Value: ValueCount ( JSONListKeys ( $$data ; “RECORDS” ) ) -1 ]
Loop
New Record/Request
Set Field [ TestJSONCalcs::Company ; JSONGetElement (TestJSONCalcs::JSON; “RECORDS[” & $index & “].Company or Entity”) ]
#
Set Field [ TestJSONCalcs::RecordsCompromised ; JSONGetElement (TestJSONCalcs::JSON; “RECORDS[” & $index &"].Total Records Compromised") ]
#
Set Field [ TestJSONCalcs::LatestDisclosuredate ; JSONGetElement (TestJSONCalcs::JSON; “RECORDS[” & $index & “].Latest Public Disclosure”) ]
#
Commit Records/Requests [ With dialog: Off ]
Set Variable [ $index ; Value: $index + 1 ]
#
Exit Loop If [ $index = $numEntries ]
#
End Loop

---

Note: I separated the $index out of the string so it would be parsed by FMP. Otherwise, you get blanks.

HTH

[kevinD]

Thanks fmpdude and other who replied.
The JSON is fine, it works in the Data Viewer and formats perfectly.
(maybe because of posting in the forum it changes over to curly quote marks)

Finally it was fmpdudes solution which worked.
Instead of "RECORDS[$index].Company or Entity"
Should have written it as “RECORDS[ “& $index” &].Company or Entity”.

Worked like charm.
Best
Kevin

[anon45965781]

Excellent. Hopefully @Cecile can tweak the forum so straight quotes stay that way.

Glad you got it working.

[Cecile]

«test”. “Test”
"Test"
"Test”
"Test"

"Markdown typographer deactivated”
"Test"

Since I cannot put two identical " in the typographer replacement list, I had to deactivate it altogether.

Consequently, you will now have to use ASCII (desktop) or select specific symbols (mobile keyboards) for currencies em dash tm copyrights etc

[anon45965781]

Thank you.