Updating a lookup column through API

Developers Central
1

When using the API to create or update rows, what format should I be using to pass values for a lookup table column? I’ve tried passing the values back as a comma-separated list, and it results in the column appearing to be populated with the correct values, except that each cell has the little red error triangle, with the message that it’s not a valid row reference, even though that value matches the display column. I’ve also tried passing the row IDs, and this didn’t work either. The format I’m using is:

                    {
                        'column': '<Lookup Column Name>',
                        'value': <lookup display value A>,<lookup display value B>
                    },
2

Hey @UPPERCASE_Customer_Service,

Sorry you’re having troubles with the API. I just tested this scenario and it seems to work correctly. Here are some things to check:

  • Are you sure you have “Allow Multiple Selections” enabled for the column?
  • Do you have the right lookup table configured on the column and does the lookup table have the expected display column?

If it helps, here’s my setup:

Image%202019-02-11%20at%203.12.57%20PM.png892×745 26.6 KB

And here’s the request body:

{
	"rows": [
		{
			"cells": [
				{
					"column": "Name",
					"value": "Fred"
				},
				{
					"column": "Fave color",
					"value": "Red"
				},
				{
					"column": "Fave colors",
					"value": "Red,Blue"
				}
			]
		}
	]
}

As you can see, the row was created successfully. Happy to help troubleshoot this further if you’re not able to address the issue.

3

Thanks for this, I’ve mostly got it working. It turns out the problem seems to have been that my lookup display column consists of numbers, and I think the API call that was populating this column was creating them as numbers (even though it’s a text column), and the lookup was attempting to treat the numbers as text strings, and so the lookup was failing because it was trying to match text strings to numbers, even though the contents of both were the same. I put punctuation around both the numbers for both writing them and looking them up, and that seems to have solved it.

4

I see - lookup columns are always strings in the API, so let me see what we can do on our end to make this easier in the future.

5

Does not work with unicode characters.

6

Realized I never updated this thread – we made changes to our API a few weeks ago to fix these edge cases around working with tables whose display columns have numbers in them.

7

@oleg Sorry to resurrect this thread. I noticed an issue today as follows:

  • I am using Make.com to upsert rows into a table.
  • If the API updates a lookup column for which the display value is a formula, it does not find the value when my document is updated.
  • I presume this is because the display column is a formula column. It seems to work for other column types.
  • Therefore I have had to configure an automation that updates the value of the column based on a text column with the value we want.
  • I have not disabled parsing in the query parameters.

Would you be able to test the scenario and confirm whether I am correct in saying this?