Goal: Take the JSON string returned by a REST API call and put data from individual fields into text boxes on a Retool form. However, I would settle for being able to put it on a table.
I have a REST API. I can query it just fine. I get the JSON response. But I cannot get anything to display on my Retool app.
Steps I created the REST API resource and a query to call it. -- searchBtnClickHandler. I created the text element where the response is supposed to go -- custCode. I created a transformer -- itemAPITransformer
Details: Here is my Transformer
var restData = searchBtnClickHandler.data;
const parsedData = JSON.parse(restData);
return parsedData;
Since you are using a transformer that is meant to be holding a JSON object, you need to reference the JSON property stored within using something like {{itemAPITransformer.value.CUST_CODE}}.
I am looking at your setup and I am not sure the JSON parse is working as you intend, because you might need to be using JSON.parse(restData.message) since the returned data is held in a property named message.
To get a better idea on what is in the transformer, after your click event query you should be able to right click on the Transformer and select View State, which will have the value as an expandable property.
Are you running the click event as a Preview (selecting Run in the query window) or actually clicking the button in the App to trigger the searchBtnClickHandler?
I think you should move this from a transformer to a JS Query, if possible. Trigger the JS Query after the successful run of the click handler and see how that affects the landscape.
Instead of transformer.value you'll have jsQuery.data to reference.
Problem might be in my JSON itself. I took the returned JSON and just copy/pasted into an online JSON parser, getting error "SyntaxError: JSON.parse: unexpected non-whitespace character after JSON data at line 1 column 5 of the JSON data". I'll see if I can get the JSON fixed and go back to trying the transformer.
Thanks for the help. I got it working. Root cause was indeed bad JSON. Retool did not like the way the API was returning null values. API just left the values blank. I had to change them all to something else, and the String literal "NULL" was as good a choice as anything.
I still couldn't get the Transformer to work, but I did use a little Javascript to fix the problematic JSON response.
var message = searchBtnClickHandler.data.message;
var replacePattern = /\s,/gm;
message = message.replace(replacePattern, " \"NULL\",")
var parsed = JSON.parse(message);
return parsed;
Then I was able to access the value in the form with this
{{ ApiResultsFiller.data.Data[0].CUST_CODE }}
I'm just evaluating Retool for now so keeping it simple. After this I want to try populating the data into a table instead of a bunch of elements on a page.
That should be as simple as using the JS Query as the data source for the table.
Sometimes you may need to format the return value as an object to properly fill out a table so formatDataAsObject(yourQuery.data) and formatDataAsArray(yourQuery.data) will likely come up as you start working with different query responses.