I can't update the record in the status column of the table with the script

Forum|Forum|2 years ago
February 24, 2023
4 replies
72 views

+5

NikPaw
New Participant

Hello all.
I have an order table with a status column and a dropdown list.
When the status column has no defined values, it launches an automation that needs to run a script every 15 minutes that queries the status via API from a remote server.
I need to properly parse the response from the server and write it to the status column.
At the moment I can't figure out how to pull the json status from the response where the status of the request is and update the desired row.

I will be very grateful for help.

const shopid = 'b6e1835a-85a8-4f34-b445-d9d0934ffcec';

let inputConfig = input.config();

let orders = inputConfig.order;

 let table = base.getTable('Orders');

 let id = inputConfig.record;

const options = {

    method: 'POST',

    headers: {

    'Accept': 'application/json',

    'User-Agent': 'Airtable',

    'Content-Type': 'application/json'

    },

    body: JSON.stringify({

   shop_id: `${shopid}`,

   tx_id: `${orders}`

    })

};

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)

.then((response) => response.json())

.then((response) => console.log(response))

.catch(err => console.error(err));

return await table.updateRecorsdAsync( id, {

    "order status": response.status

});

+22

Ben_Young1
Brainy
Forum|Forum|2 years ago
February 24, 2023

Hey @NikPaw!

From what I can see, you have two things that I believe are causing this issue.
There are people much more qualified and knowledgeable on the forums than I am, but I'll give it a shot.

You want to write the returned data from you API call to the answer variable. Currently, answer returns undefined because you're not actually returning anything from the promise.
The end of the then...catch chain resolves in a console.log() but doesn't return anything back up to the variable.

So instead of this:

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
.then((response) => response.json())
.then((response) => console.log(response))
.catch(err => console.error(err));

You can use this instead:

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
.then(response => response.json())
.catch(err => console.error(err));

console.log(answer);

Alternatively, you could use this:

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
.then(response => {
    console.log(response.json());
    return response.json();
})
.catch(err => console.error(err));

The other thing that will cause you issues is that if you're writing to a single select field, you need to pass a choice object to update the field.
This will throw an error (assuming the order status field is a single select field) because you're passing the update value as a string.

await table.updateRecorsdAsync(id, {
    "order status": answer.status
});

You'll want to change it around to something like this instead:

await table.updateRecorsdAsync(id, {
    "order status": {name: answer.status}
});

You can find more information about how to pass data to the single select field type here.
Altogether, it'd probably look something like this:

const config = input.config();
const { order, record } = config;
const shopid = "b6e1835a-85a8-4f34-b445-d9d0934ffcec";
const table = base.getTable("Orders");
let orders = order;

const options = {
  method: "POST",
  headers: {
    "Accept": "application/json",
    "User-Agent": "Airtable",
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    shop_id: shopid,
    tx_id: order,
  }),
};

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
  .then(response => {
    console.log(response.json());
    return response.json();
})
  .catch(err => console.error(err));

await table.updateRecorsdAsync(id, {
  "order status": {name: answer.status}
});

N

Like

+25

Alexey_Gusev
Brainy
Forum|Forum|2 years ago
February 25, 2023

Hey @NikPaw!

From what I can see, you have two things that I believe are causing this issue.
There are people much more qualified and knowledgeable on the forums than I am, but I'll give it a shot.

You want to write the returned data from you API call to the answer variable. Currently, answer returns undefined because you're not actually returning anything from the promise.
The end of the then...catch chain resolves in a console.log() but doesn't return anything back up to the variable.

So instead of this:

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
.then((response) => response.json())
.then((response) => console.log(response))
.catch(err => console.error(err));

You can use this instead:

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
.then(response => response.json())
.catch(err => console.error(err));

console.log(answer);

Alternatively, you could use this:

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
.then(response => {
    console.log(response.json());
    return response.json();
})
.catch(err => console.error(err));

The other thing that will cause you issues is that if you're writing to a single select field, you need to pass a choice object to update the field.
This will throw an error (assuming the order status field is a single select field) because you're passing the update value as a string.

await table.updateRecorsdAsync(id, {
    "order status": answer.status
});

You'll want to change it around to something like this instead:

await table.updateRecorsdAsync(id, {
    "order status": {name: answer.status}
});

You can find more information about how to pass data to the single select field type here.
Altogether, it'd probably look something like this:

const config = input.config();
const { order, record } = config;
const shopid = "b6e1835a-85a8-4f34-b445-d9d0934ffcec";
const table = base.getTable("Orders");
let orders = order;

const options = {
  method: "POST",
  headers: {
    "Accept": "application/json",
    "User-Agent": "Airtable",
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    shop_id: shopid,
    tx_id: order,
  }),
};

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
  .then(response => {
    console.log(response.json());
    return response.json();
})
  .catch(err => console.error(err));

await table.updateRecorsdAsync(id, {
  "order status": {name: answer.status}
});

Would like to add, it won't work if

the status column has no defined values,

Field options should be updated first.

But instead of doing that, I would:
- ensure that answer or answer.status contains exactly that value to be written to field

- put it to output.set('query_answer', answer.status)

use 'query_answer' in a next step 'Update record'

N

Like

N

+5

NikPaw
Author
New Participant
Forum|Forum|2 years ago
February 25, 2023

Would like to add, it won't work if

the status column has no defined values,

Field options should be updated first.

But instead of doing that, I would:
- ensure that answer or answer.status contains exactly that value to be written to field

- put it to output.set('query_answer', answer.status)

use 'query_answer' in a next step 'Update record'

Thanks for the tip. It really worked.
I wish I could still update the record inside the script body.
Tell me more about it. Do I need to arrange the array of values in the dropdown menu like this?

 
{
choices: Array<
// New choice format
| {name: 'NEW', color?: '#FFFFFF'} | {name: 'OVERDUE', color?: '#1234'} | {name: 'PERFORMED', color?: '#5678'}
>

}

Like

N

+5

NikPaw
Author
New Participant
Forum|Forum|2 years ago
February 25, 2023

Hey @NikPaw!

From what I can see, you have two things that I believe are causing this issue.
There are people much more qualified and knowledgeable on the forums than I am, but I'll give it a shot.

You want to write the returned data from you API call to the answer variable. Currently, answer returns undefined because you're not actually returning anything from the promise.
The end of the then...catch chain resolves in a console.log() but doesn't return anything back up to the variable.

So instead of this:

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
.then((response) => response.json())
.then((response) => console.log(response))
.catch(err => console.error(err));

You can use this instead:

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
.then(response => response.json())
.catch(err => console.error(err));

console.log(answer);

Alternatively, you could use this:

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
.then(response => {
    console.log(response.json());
    return response.json();
})
.catch(err => console.error(err));

The other thing that will cause you issues is that if you're writing to a single select field, you need to pass a choice object to update the field.
This will throw an error (assuming the order status field is a single select field) because you're passing the update value as a string.

await table.updateRecorsdAsync(id, {
    "order status": answer.status
});

You'll want to change it around to something like this instead:

await table.updateRecorsdAsync(id, {
    "order status": {name: answer.status}
});

You can find more information about how to pass data to the single select field type here.
Altogether, it'd probably look something like this:

const config = input.config();
const { order, record } = config;
const shopid = "b6e1835a-85a8-4f34-b445-d9d0934ffcec";
const table = base.getTable("Orders");
let orders = order;

const options = {
  method: "POST",
  headers: {
    "Accept": "application/json",
    "User-Agent": "Airtable",
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    shop_id: shopid,
    tx_id: order,
  }),
};

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)
  .then(response => {
    console.log(response.json());
    return response.json();
})
  .catch(err => console.error(err));

await table.updateRecorsdAsync(id, {
  "order status": {name: answer.status}
});

For some reason, my ready-made script is not working. I get a response from the server that the transaction was not found.
I changed it a little bit in my own way and it works.
But inside the body of the script I cannot update the records in the table.

const config = input.config();

const { order, record } = config;

const shopid = "b6e1835a-85a8-4f34-b445-d9d0934ffcec";

const table = base.getTable("Orders");

let orders = order;

const options = {

    method: 'POST',

    headers: {

    'Accept': 'application/json',

    'User-Agent': 'Airtable',

    'Content-Type': 'application/json'

    },

    body: JSON.stringify({

   shop_id: `${shopid}`,

   tx_id: `${orders}`

    })

};

let answer = await fetch(`https://api.roundsquare.biz/v1/status`, options)

.then((response) => response.json())

//.then((response) => console.log(response))

.catch(err => console.error(err));

console.log(answer);

output.set('query_answer', answer.status);

Like

1 Attachments

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded