Skip to main content

Convert Lookup field data to multi select list

  • October 28, 2020
  • 2 replies
  • 146 views
C

i have a multi select field in one table of my base (Grants). i need to get that information into the Organizations table of my base. i have used a Lookup field to get the data into the other table. however, in the Lookup field, the data is converted to text. is there a formula that i can use to convert the information in the Lookup field to a new multi select field in Organizations?

(i know i can just convert the field type from Lookup to multi select, but i want this to happen whenever new data is entered into Grants, not have it be a manual process of converting the field.) thank you for any insight!

2 replies

Justin_Barrett
Forum|alt.badge.img+21

This is doable with the help of an automation, and thankfully it doesn’t require custom scripting.

First, you’ll need identical multiple-select fields in your two tables. If you don’t already have this set up, this can be done pretty easily using a trick I share in this video:

With that done, the bulk of the work will happens in the [Organizations] table. You’ve already got a lookup field pulling in the multiple-select values from the linked [Grants] records. I’ll refer to that as {MS Lookup} going forward; replace that name with your field name in all of the actions below. Unfortunately that lookup field can’t be used directly by the automation that we’ll make later, but even if we could use it, its output isn’t formatted for what we’ll be doing, so add a formula field with this formula (I named mine {MS Echo}):

ARRAYJOIN({MS Lookup}, ",")

I made a test setup, and this is what I get when looking at the lookup field compared to the formula:

Before working on the automation, let’s copy this echoed output into the multiple-select field, and you’ll see that we have an exact match:

Now that everything is synced, we can make the automation, which will take care of the sync going forward.

Create a new automation using the “When a record is updated” trigger. The field to watch will be the {MS Lookup} field, which will update based on any changes made in the linked records. Make sure that you test the trigger before continuing.

The only other step we’ll need is an “Update record” action, set up like this:

As you see, the only field we’re updating is the multiple select field, which will get the contents of the formula field echoing the lookup. In other words, the update is effectively performing the same copy-paste that we did earlier, but on a record-by-record basis as the lookup changes.

Here’s an example where I make changes in the source table, and then switch to the target table to watch the update made by the automation. I left the helper fields visible for the demo, but obviously you can hide them and only leave the multiple-select field visible.

I also recommend locking the field permissions of that target multiple-select to prevent accidental changes. You’ll have the option of letting automations update the contents.

M
N
J
C
  • cori_parrish
  • Author
  • New Participant
  • October 30, 2020

This is doable with the help of an automation, and thankfully it doesn’t require custom scripting.

First, you’ll need identical multiple-select fields in your two tables. If you don’t already have this set up, this can be done pretty easily using a trick I share in this video:

With that done, the bulk of the work will happens in the [Organizations] table. You’ve already got a lookup field pulling in the multiple-select values from the linked [Grants] records. I’ll refer to that as {MS Lookup} going forward; replace that name with your field name in all of the actions below. Unfortunately that lookup field can’t be used directly by the automation that we’ll make later, but even if we could use it, its output isn’t formatted for what we’ll be doing, so add a formula field with this formula (I named mine {MS Echo}):

ARRAYJOIN({MS Lookup}, ",")

I made a test setup, and this is what I get when looking at the lookup field compared to the formula:

Before working on the automation, let’s copy this echoed output into the multiple-select field, and you’ll see that we have an exact match:

Now that everything is synced, we can make the automation, which will take care of the sync going forward.

Create a new automation using the “When a record is updated” trigger. The field to watch will be the {MS Lookup} field, which will update based on any changes made in the linked records. Make sure that you test the trigger before continuing.

The only other step we’ll need is an “Update record” action, set up like this:

As you see, the only field we’re updating is the multiple select field, which will get the contents of the formula field echoing the lookup. In other words, the update is effectively performing the same copy-paste that we did earlier, but on a record-by-record basis as the lookup changes.

Here’s an example where I make changes in the source table, and then switch to the target table to watch the update made by the automation. I left the helper fields visible for the demo, but obviously you can hide them and only leave the multiple-select field visible.

I also recommend locking the field permissions of that target multiple-select to prevent accidental changes. You’ll have the option of letting automations update the contents.


thank you! this is very helpful.

Terms & ConditionsAccessibility statement