Changing data field to show look-up ID rather than original text

[oliverk]

Hello

I've imported a large quantity of data into a Caspio table.

I'm now creating look-up tables for some of the fields. For those fields, I want to store the Primary Key from the look-up table, rather than the text itself.

Does anybody know of a simple way to either a) replace the data in the field with the primary key from the look-up table, or b) place the primary key from the look-up table in a new field, depending on the text stored in the original field?

For example, I want to replace "Apples" with "7KGG3ORQ" and "Oranges" with "4H3FPTNU" etc.

I could delete all the data, make the changes on excel, and re-upload the data. But that doesn't feel ideal...

Many thanks

Oliver

[NiceDuck]

You will need a task setup like this:



On this case, the values on the answers_lookup.Id is the values that actually on the Connector field. If I want the connector field to have the actual labels, then I will join them by the connector first and update the connector value.

[NiceDuck]

Hello 

If you already have a lookup table for values you want to replace, you can easily process that by using task.

https://howto.caspio.com/tasks/ 

 

[NiceDuck]

Or you can simply set a table relationship between the two and set the actual values to be the display value of the table. 

[NiceDuck]

OH! and here's the article about tables and relationship. https://howto.caspio.com/tables-and-views/relationships/database-relationships/

You may want to check this video as well: 

Its a bit old but still very helpful.

[oliverk]

On 10/18/2022 at 2:42 AM, NiceDuck said:

You will need a task setup like this:



On this case, the values on the answers_lookup.Id is the values that actually on the Connector field. If I want the connector field to have the actual labels, then I will join them by the connector first and update the connector value.

Really helpful! Just what I needed. Thanks v much!