Lookup values from a field in another table for rows in TWO linked row fields

1

We have two linked fields:
“Project 1”, “Project 2”.

They are linked to another table, in which there is an additional field “Call Type”.

lookup (‘Project 1’, ‘Call Type’) works perfectly.

What I would like to get is something like:

lookup (and(‘Project 1’, 'Project 2 '), ‘Call Type’))
or
concat (lookup (‘Project 1’, ‘Call Type’), ’ ', lookup (‘Project 2’, ‘Call Type’)).

So what I would like to get is the (linked) values of Call Type for both Project 1 and Project 2.

2

Hi,

Linked fields are always considered to be a list of items, even if they only contain one item. This is why the concat() function does not directly works.

The solution is to convert the lookup values to a single text and concatenates those texts.

concat (join(lookup (‘Project 1’, ‘Call Type’),''), ’ ', join(lookup (‘Project 2’, ‘Call Type’),''))

3

Dear @frederikdc,

Thanks so much for the input. I still can’t get it to work unfortunately.

concat (join(lookup ('Project Coordinator', 'Action Type'), ''), ' ', join(lookup ('Project Partner', 'Action Type'), ''))

Error in the formula on line 1 starting at letter 6 Invalid letters ’ ', was instead expecting ‘(’.

Can you spot my mistake? I think I have used your exact suggestion and only updated the field names.

4

There should not have been a space between concat and the open parentheses. I adjusted the formula

concat(join(lookup ('Project Coordinator', 'Action Type'), ''), ' ', join(lookup ('Project Partner', 'Action Type'), ''))

5

Yes, I just noticed. Now I got rid of the spaces and get the following error messag:

Error with formula: argument number 1 given to function join was of type single_select but the only usable type for this argument is a list of text values obtained from a lookup, argument number 1 given to function join was of type single_select but the only usable type for this argument is a list of text values obtained from a lookup.

concat(join(lookup('Project Coordinator', 'Action Type'), ''), ' ', join(lookup('Project Partner', 'Action Type'), ''))

Do I need to add totext?

6

Yes, if Action Type is a single select field, you need to use the totext function around the lookup function

7

Next problem, the blanc between the looked up items is weirdly put before the results, there is no blanc separating the results. The lookups may have more than one result, join(totext…) seems to be the reason why there is no blanc between them.

concat(join(totext(lookup('Project Coordinator', 'Action Type')), ''), ' ', join(totext(lookup('Project Partner', 'Action Type')), ''))

Can I somehow avoid the repetition of duplicate lookups?

8

Hi,

You can separate the results by adding a space between the single quotes of the join() function

concat(join(totext(lookup('Project Coordinator', 'Action Type')), ' '), ' ', join(totext(lookup('Project Partner', 'Action Type')), ' '))

A lookup field shows all the corresponding values of the linked field. There is the filter() function that allows you to filter results based on a condition, but I don’t think it is possible to set up a condition that checks if a value occurs more than once.