Transformer to format my query results from OpenAI

[maillme]

Hello,

Sorry - complete low-coder here........
I've got a response coming in from OpenAI, that I want to format so that it can populate my table.
I've asked in my prompt that GPT-4 give me the results in JSON however, it still needs some tweaking here and there and of course, I have no idea how to do this.......

I'm hoping someone can help me with some transformer JS.

The format my table would like is:

{
"id": 1, 
"material_topic_name": ,
"definition":,
"source":,
]

And the response I get from GPT is:

"Here are the top 5 material topics concerning the Pharmaceutical industry: 1. { "id": 1, "material_topic_name": "Drug Discovery and Development", "definition": "This involves the process of discovering new drugs and their development, covering stages from initial concept to bringing the drug into the market for public consumption.", "source": "https://www.ncbi.nlm.nih.gov/books/NBK56976/" }, 2. { "id": 2, "material_topic_name": "Regulatory Compliance", "definition": "This refers to pharmaceutical companies' adherence to laws, regulations, guidelines and specifications relevant to their business processes. Violations of regulatory compliance often result in legal punishment.", "source": "https://www2.deloitte.com/us/en/pages/regulatory/articles/regulatory-compliance-in-pharma-industry.html" }, 3. { "id": 3, "material_topic_name": "Pharmaceutical Pricing and Reimbursement", "definition": "This pertains to the methods pharmaceutical companies use to price their products and the strategies employed to ensure these drugs are included in health insurance coverages.", "source": "https://www.ncbi.nlm.nih.gov/pmc/articles/PMC4046457/" }, 4. { "id": 4, "material_topic_name": "Patient-Centric Care", "definition": "This concept involves re-orienting the health care system's focus from diseases to patients, thus putting patient welfare first in every decision-making process.", "source": "https://www.ncbi.nlm.nih.gov/pmc/articles/PMC3789163/" }, 5. { "id": 5, "material_topic_name": "Pharma Supply Chain Management", "definition": "This pertains to the methods and processes employed to ensure the efficient and effective distribution of pharmaceutical products from the manufacturer to the consumer.", "source": "https://pubmed.ncbi.nlm.nih.gov/19534505/" }"

thank you very much for any help, Neil

[ScottR]

The format should be

[
{
"id": 1, 
"material_topic_name":"" ,
"definition":"",
"source":""
}
]

Or you could, in the Data Source field, surround that data payload with [ and ] and remove the [ and ] from the formatted payload....

Since the ID is the same number as the number of the topic in the top 5 list, you don't need it (AFAIK) - it will depend on what you need to display

Here is how the code should be formatted:

return [ {"id": 1, "material_topic_name": "Drug Discovery and Development", "definition": "This involves the process of discovering new drugs and their development, covering stages from initial concept to bringing the drug into the market for public consumption.", "source": "https://www.ncbi.nlm.nih.gov/books/NBK56976/"},{ "id": 2, "material_topic_name": "Regulatory Compliance", "definition": "This refers to pharmaceutical companies' adherence to laws, regulations, guidelines and specifications relevant to their business processes. Violations of regulatory compliance often result in legal punishment.", "source": "https://www2.deloitte.com/us/en/pages/regulatory/articles/regulatory-compliance-in-pharma-industry.html" }, { "id": 3, "material_topic_name": "Pharmaceutical Pricing and Reimbursement", "definition": "This pertains to the methods pharmaceutical companies use to price their products and the strategies employed to ensure these drugs are included in health insurance coverages.", "source": "https://www.ncbi.nlm.nih.gov/pmc/articles/PMC4046457/" }, { "id": 4, "material_topic_name": "Patient-Centric Care", "definition": "This concept involves re-orienting the health care system's focus from diseases to patients, thus putting patient welfare first in every decision-making process.", "source": "https://www.ncbi.nlm.nih.gov/pmc/articles/PMC3789163/" },{ "id": 5, "material_topic_name": "Pharma Supply Chain Management", "definition": "This pertains to the methods and processes employed to ensure the efficient and effective distribution of pharmaceutical products from the manufacturer to the consumer.","source": "https://pubmed.ncbi.nlm.nih.gov/19534505/" } ]

[ScottR]

[maillme]

Thanks @ScottR,

Yes, the problem I am having is I don't know exactly how to format the output I get from my AI query, to then get it ti populate my table.

I have gotten a bit further, but for some reason - the table will not take it as an array - it will only take it as text (I think).

So I need to somehow take the results of the AI query, 'transform' it- and then the table can read it.

I'm just struggling to figure this out......
If I take the results form the AI query myself, and paste into the table as data source JSON - it works fine.
I think i just somehow need to convert from a string to an array - that my table can then recognise.

screen shot that may help me explain better
thanks again for your help

[ScottR]

You have extra brackets in the Data source field you probably don't need.

{{AIQuery.data}} only Remove [ and ] surrounding it as it is already in the data itself.

[maillme]

Thanks Scott,
Unfortunately - that doesn't seem to be the issue. I already tried that

It seems to be that the results are a string, and So I just need a way, I think, to remove the " " from the queries result....

thanks again,

[ScottR]

Ask Open AI NOT to return a string, ask it to return an array only....

[maillme]

Also tried that, but it seems (i think) to be something in Retool - adding the "".
Because if I take the query results into a text box - there are no quotes, but when I preview the results of the query- there are quotes....

So I think it is something to do with that.......

[ScottR]

Correct

[maillme]

So I guess that even If i can somehow 'transform' the results, using a transformer, it will not strip the "" ?

thanks
Neil

[ScottR]

Let me try something....

[ScottR]

Try the following:
In the data source field remove the extra [ and ] and then after data add
{{AIQuery.data.slice(1, -1)}}

[maillme]

Still no luck....
I also had a thought - because the text box is displaying the code as I'd like it to be seen, then perhaps I could populate the rows of the table by referencing the text box, but it still gives an odd error - it thinks it is empty, but I guess that's because the data source is looking for an array, and not a string from a text field, right?

[Skizhu]

You can try the following

Ask to your AI query to return just the Array (the output should be a string with the array), then in a transform use JSON.parse() and in your table set the data source to be the transformer

[maillme]

@Skizhu

Thank you! I got that working!!
This was exactly what I was looking for - some how a way to parse the response and format it.
I tried to transform the results of the query using the transformer within the query itself and the same code - but that didn't work, however - having a separate transformer done the trick.

thanks a lot!
Neil

[Skizhu]

You can skip the separate transformer by using the JSON.parse() in the AI query in the transform data section

im glad it worked for you