API - Post - Error when posting data

Hi,

Appologies in advance for any 'dumb' things I say in this post - I am new to Retool, have have built several apps successfully querying our on-prem MS SQL instance.

I am trying to setup a link to one of our providers API, I can post data without any issues when doing this using curl directly from a CLI, example below:

curl -X POST https://121prodata.co.uk/api/ -d 'token=xxxx&data={"data":[{"number":"01234567891"},{"number":"07123456789",}]}'

but when trying to replicate the same type of function within Retool, I am always met with no API method specified.

I am really unsure what I am doing wrong here, so any support would be greatly appreciated. An exmaple of one of my attempts is below, but I have tried varying ways of using this information in the body, with and without quotes etc, but I am not having any success at all.

According to their docs that error means it didn't receive any parameters, might be worth passing your data as URL parameters not in the body. eg. I try it like this for the single number check and get told I need a token so it's seemed to have received a param

For your POST call you may want to try changing your body format to JSON and putting the data property in there with the token and also specify the input_format as 'JSON'

Hi Dave,

Than you for replying, I really apprecaite it.

However, you cannot pass it as a url paremeter else it believes it is a 'Single Number' check rather than a bulk check. I have to pass it as a POST, with the info in the body. Below is a link to their website as to how they demo the setup using PHP.

Let me know what you think I am doing wrong, and I'll give it a try.

Many thanks,

Michael.

If I POST it as form data it seems to attempt to validate the token, maybe this might work for you (I don't have a token to confirm). POSTing as JSON didn't seem to like it at all though.

2 Likes

Hi Dave,

Sorry for the delay in getting back to you.

Thank you so much for this, it worked a treat!

Do you have any advice on how to transform an arrary from ["07123456789","01234567891"] into [{number:'07123456789'},{number:'01234567891'}]

Many thanks,

Michael.

Dave strikes again! :raised_hands: :trophy: :goat:

For your latest question, would something like this work for you?

{{["07123456789","01234567891"].map(str => Object.assign({"number": str}))}}

1 Like

Hey Victoria!

Dave was great, and his solution worked perfectly.

The example I gave was a substitution for the data from the result of query. Your solution works partially but I back something a little different. I used:

{{ [MobileNumbers.data.MobileNumber].map(str => Object.assign({"number": str})) }}

and the result was

[{"number": ["07123456789","01234567891"]}]

how would I get [{number:'07123456789'},{number:'01234567891'}]

Effectively, the end result is that I want to be able to use Daves solution of {{JSON.stringify({data:[{number:'07491434879'},{number:'01162983469'}]})}}

but replace the static numbers with the result of a query. So I am hoping to achieve something like {{JSON.stringify({data:[{{ [MobileNumbers.data.MobileNumber].map(str => Object.assign({"number": str})) }}]})}}

Not sure if I am doing this in the right way, so any guidance is greatly appreciated.

Thank you again in advance.

Michael :slight_smile:

Ah, got it!

What is MobileNumbers.data.MobileNumber currently returning?

A screenshot of your data structure (e.g. in the State panel of your left panel) would be most helpful!

It is currently returning the below (I have limited it to 2 for testing):
note, I have erased part of the telephone numebrs as these are customer detials

image

Please also see an image of the state pane below:

Sorry, also forgot to add - MobileNumbers.data.MobileNumber returns ["07123456789","01234567891"]

Thanks again :slight_smile:

Ah, cool!

Then I think you may just have had an extra set of array brackets in your code.

Would you mind trying this?

{{ MobileNumbers.data.MobileNumber.map(str => Object.assign({"number": str})) }}

1 Like

You and Dave are both wizards!

Using this along like {{JSON.stringify({data:MobileNumbers.data.MobileNumber.map(str => Object.assign({"number": str}))})}} works perfectly.

There is one final piece to this puzzle however, the results returned from the api are:

{
"number": "07123456789",
"result": "safe"
},
{
"number": "01234567891",
"result": "safe"
}
],
"reference": "63d84176c9730",
"valid_until": "2023-02-27T22:15:18Z",
"matches": 0,
"credits": 2
}

I now want to send these results to another table in the database, I am trying something like the below:

but I dont get any data back from either of the below:

{{PostTo121ProData.data.number}}
{{PostTo121ProData.data.status}}

any advice how I would take this response and insert them into the table?

Many thanks,

Michael :slight_smile:

Woohoo!

In your screenshot, do you want to pass along the first number and the first result? Or both numbers and both results?

It seems like your api is returning

[{
"number": "07123456789",
"result": "safe"
},
{
"number": "01234567891",
"result": "safe"
}
],
"reference": "63d84176c9730",
"valid_until": "2023-02-27T22:15:18Z",
"matches": 0,
"credits": 2
}]

which is an array of objects.

To grab the first number, for example, we could do {{PostTo121ProData.data[0].number}}

1 Like

Woohoo indeed! :smiley:

Ideally, I would like to insert all the numbers returned along with the valid_until date.

So each row inserted would be like Number, Result, Valid Until

The column names which I would insert to are PhoneNumber, Result and ExpiryDate so the number would go into PhoneNumber, Result into Result and Valud Until would go into Expiry Date.

Hope I'm making sense.

Many thanks,

Michael.

Makes sense! So you'd like to loop through your results and pass each result into another query? Something like this? https://community.retool.com/t/how-to-run-a-rest-api-query-for-each-item-in-an-array-and-return-all-results-together/2352/4

Ahh, I've done this before. Is there a way to bulk insert everying in one go for speed?

Only if your API is built to handle a bulk insert! If your API can handle something like this (an array of values for each key), then we can definitely get it formatted to bulk insert in one go :slight_smile:

This is being written to MS SQL. I can bulk insert directly from SSMS, so I assume I can do the same in Retool? Let me know, and I'll give it a try.

I also realised it is in 'Insert a record' for action type. This should be bulk insert records I believe?

This is the current data structure of the returned data:

And I assume I need either transform the data before inserting or can I do something here?

Many thanks,

Michael.

Ah! Cool. Bulk insert is definitely the action you're looking for.

We just need to pass in an array of objects with the correct keys. Would you mind sharing a screenshot of what PostTo121ProData.data looks like? Ideally from the left panel's State tab:

Sure, is this what you're looking for? :smiley:

Yes!! Thank you.

So it looks like {{ PostTo121ProData.data.data }} will grab us an array of these two objects:

which is exactly what a bulk insert will accept in the "Array of records to insert" field!

Let me know how {{ PostTo121ProData.data.data }} works for you.