Update record with an API url that dynamically changes

[Alc2020]

Hi Experts,

I have 3 tables.

Table 1: List of phones of different models, serial numbers, extension, description, phone_id and etc.

Table 2: Is similar to Table 1 but no phone_id.  The purpose of this table is to update the records in table 1.  There are about a 1000 records in this table.

To update records in Table 1 I need to upload them in JSON format to an API using a url like https://api.test.com/phone/phone_id

The phone_id changes per record.  How can I update Table 1 in a way that phone_id will be automatically appended to the API url dynamically for each record that I would like to update?

Example:

Table 1:

phone_id	model	serial_no	extension	description
phone_id1	Apple-100	serial_no1	100	silver
phone_id2	Samsung-100	serial_no2	200	blue

Table 2:

serial_no	extension	description
serial_no1	500	orange
serial_no2	900	yellow

Desired Output:

URL1: https://api.test.com/phone/phone_id1

JSON:

{
"serial_number": "serial_no1",
"extension": 500,
"description": "orange"
}

URL2: https://api.test.com/phone/phone_id2

JSON:

{
"serial_number": "serial_no2",
"extension": 200,
"description": "blue"
}

....

...

more record follows

I have tried playing around with the append, json, and download tool but I cannot figure on how to attached the phone_id dynamically to the API url.

Thank you very much in advance.

[DavidP · Accepted solution]

If I understand correctly, you want to add a URL column to Table1.

You can do this with a formula tool as shown below. I'd suggest you start in a web browser and construct and test a specific url to ensure it works and has the correct format. Then copy this into a formula tool as shown below and replace the dynamic variables one by one. Note that I used single quotes to encase the text for the url since it contains double quotes already.