Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Problem extracting nested jsonData? #9

Closed
cristoslc opened this issue Sep 24, 2015 · 5 comments
Closed

Problem extracting nested jsonData? #9

cristoslc opened this issue Sep 24, 2015 · 5 comments

Comments

@cristoslc
Copy link

I'm passing a script a parameter that looks like this (shown if you use Get ( ScriptParameter )):

{ "jsonData":{ "idMarkingPeriod":"2014-2015","idRoster":10310,"idSection":"409-1 2014-2015","idStudent":"S0000184","comment":"This is a comment.","created":9/24/2015 4:16:27 PM,"effort":100,"exam":85,"mark":87,"modified":9/24/2015 5:16:27 PM } }

When I try to run jsonGet ( Get ( ScriptParameter ) ; "jsonData" ), the result is a question mark ("?").

The contents of $json.error are:

Expected "," instead of "/" [ch:/] [at:159] [context:created":9/24/2015 4]

Here's the calculation used to created the script parameter:

Let (
[   ~idRoster =  "10300"
;   ~idStudent = "100"
;   ~idSection = "410-M"
]
;   jsonO 
    (   jsonOp ( "idMarkingPeriod" ; "2014-2015") 
    &   jsonOp ( "idRoster" ; ~idRoster )  
    &   jsonOp ( "idSection" ; ~idSection )  
    &   jsonOp ( "idStudent" ; ~idStudent )  
    &   jsonOp ( "comment" ; "This is a comment.") 
    &   jsonOp ( "created" ; Quote ( Get ( CurrentTimestamp ) - 3600 ) )
    &   jsonOp ( "effort" ; "100")
    &   jsonOp ( "exam" ; "85") 
    &   jsonOp ( "mark" ; "87") 
    &   jsonOp ( "modified" ; Quote ( Get ( CurrentTimestamp ) ) )
    )
)
@dansmith65
Copy link
Owner

This is happening because the created and modified date values are not encoded in the sample json you provided.

But, when I copy and paste the example calculation into the sample file that ships with this project (text expression field), then click "Run this test", the Actual Result field does have the created and modified values encoded.

I've got two guesses about what your issue is:

  1. There is something unique about your system's settings that's causing these functions to NOT encode dates. To test this, paste the expression above in a new record of the sample files Test Expression field, then copy the value from the Actual Result field and paste it in http://jsonlint.com/ to see if it's valid.
  2. The expression you're using to generate the json isn't exactly like the sample expression in this post. (review the created and modified sections)

@cristoslc
Copy link
Author

My bad, I accidentally pasted the code from one of my own attempts to fix it. In the failing code, the timestamps are not Quote()d. Here's the correct version:

Let (
[   ~idRoster =  "10300"
;   ~idStudent = "100"
;   ~idSection = "410-M"
]
;   jsonO 
    (   jsonOp ( "idMarkingPeriod" ; "2014-2015") 
    &   jsonOp ( "idRoster" ; ~idRoster )  
    &   jsonOp ( "idSection" ; ~idSection )  
    &   jsonOp ( "idStudent" ; ~idStudent )  
    &   jsonOp ( "comment" ; "This is a comment.") 
    &   jsonOp ( "created" ; Get ( CurrentTimestamp ) - 3600 )
    &   jsonOp ( "effort" ; "100")
    &   jsonOp ( "exam" ; "85") 
    &   jsonOp ( "mark" ; "87") 
    &   jsonOp ( "modified" ; Get ( CurrentTimestamp ) )
    )
)

@cristoslc
Copy link
Author

Ok, after running the calculation result through jsonlint.org and browsing briefly on StackOverflow, looks like dates are a bit of a hassle in JSON (http://stackoverflow.com/questions/4511705/how-to-parse-json-to-receive-a-date-object-in-javascript).

What's the safest way to pass this in FileMaker? Should I GetAsNumber ( Get ( CurrentTimestamp) ) and write that result to the Timestamp field in the database? I'd prefer to avoid needing to Quote() and Evaluate() every field (there's no way to effectively know when one is a date/timestamp field in this part of the app).

@cristoslc
Copy link
Author

Ok, after some further testing, GetAsNumber ( Get ( CurrentTimestamp ) ) does appear to work safely inside of FileMaker (the number is JSON-safe, and FileMaker merrily converts it back to a timestamp when inserting it into a database field).

It looks like the value 9/24/2015 also works, but not 9/24/2015 12:00 PM -- I'm guessing it's the colon that renders the JSON invalid.

Unfortunately, my app is getting data from an API, and does not know in advance whether a given field is a date or not. I'll have to go back to the drawing board to figure out a real-world solution we can apply. I'll leave this open in case you want to add some kind of date-safing (or new $json.error), but understand if you consider it beyond the scope of the module (given that JSON hasn't solved it either). Thanks!

@dansmith65
Copy link
Owner

It turns out this is a bug, but this issue was posted on the wrong project I created an issue for this at: dansmith65/FileMaker-JSON-Functions#34

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants