I have a little calculated data source that joins up a couple of tables, does some cold calculations on the page using functions, and then displays them in a big table. It has a month and year selector, as it's dealing with monthly invoicing. It'll return every month, except for one. Which of course, is the most recent (September). The table is set to not load automatically, so I'm not looking at the entire datasource at once. Normally, one would set the month, set the year, hit generate, and the spinner runs for a few seconds and then displays.
For September, it runs for a few moments, and then in the console displays the titular error "com.google.apps.appmaker.shared.json.JsonException: JSON is not an object"
I've tried swapping to different filters in the where clause, I've been randomly deleting lines from my dataset, thinking that perhaps it's a data issue, since it'll work for all my other data. Maybe an unescaped character somewhere is messing with the source. Worth noting, I'm not doing any JSON manipulation that I know of.
Here's the query for the datasource
SELECT ParticipantData.VRS_Office as Office,
ServiceData.Participant_Name as Consumer_Name,
ServiceData.Service as Service,
ServiceData.Auth_Number as Auth_Num,
MONTH(ActivityData.Date) as ActivityMonth,
YEAR(ActivityData.Date) as ActivityYear,
Round(SUM(ActivityData.Hours_Used),1) as Hours,
Round(SUM(ActivityData.Mileage_Used),1) as Miles,
ServiceData.ReadyForBilling as ReadyForBilling,
ServiceData.OtherDescription as OtherDescription,
ServiceData.OtherLumpSumAmount as OtherAmount,
ServiceData.Start_Date as Start_Date,
ServiceData.End_Date as End_Date,
ServiceData.InvoicingComplete as InvoicingComplete,
ServiceData.Participant_ID as Participant_ID
FROM (( ActivityData
INNER JOIN ServiceData ON ActivityData.Auth_Number = ServiceData.Auth_Number)
INNER JOIN ParticipantData ON ActivityData.Participant_ID = ParticipantData.Participant_ID)
WHERE MONTH(ActivityData.Date) = :MonthSelector AND YEAR(ActivityData.Date) = :YearSelector
GROUP BY Auth_Num, ActivityMonth, ActivityYear, Office, Consumer_Name, Service, Participant_ID, OtherDescription, OtherAmount, Start_Date, End_Date, InvoicingComplete, ReadyForBilling
ORDER BY Consumer_Name ASC;
The data is sensitive, but I can provide what information I can. Recently the databases were changed to use datetime instead of date as a sql field type, but I don't know if that is coincidental or not.
Are there any special characters that I'd have to keep an eye out for that might cause an error like this?
Some of the values that are provided were cold calculated in the table to generate amounts against a table of rates.
In short, someone had put a description into a field that it was trying to do math with. I corrected the data, and the error evaporated.
I arrived at it in the most support-y way I think i could have. I randomly deleted records in the QA environment that I have set up until it worked, and then narrowed it down from there to being a small set of the records. Then it was just spotting the difference.
Lesson learned, data validation required.