How to return multiple columns from a LookUp functin?

Like (0) Share

Report

Posted on 30 Apr 2021 20:26:11 by rprahalad

377

Hello All,

I need to do a LookUp on a table and return a concatenated return value o two columns. Can you please let me know how to do it?

I tried this and its giving me an error. Here clSearchResults is another collection that has the ID column. That part is not an issue but returning multiple fields in a concatenated form is giving me an error. Thanks

LookUp (Employees,

ID = colSearchResults[@ID].ID,

FirstName & LastName)

Categories:

Connecting to Data

General Questions

I have the same question (0)

All responses (12)

Answers (2)

RandyHayes 76,291 Super User 2024 Season 1 on 30 Apr 2021 at 20:32:25

Like (0)

Report

Re: How to return multiple columns from a LookUp functin?

@rprahalad

I am not entirely sure where a collection fits into this, but your formula will not work as you are trying to do equality operation on the ID and a table of ID's.

So are you trying to get all of the concatenated names based on a collection, or just how does this collection fit into your needs?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on 30 Apr 2021 at 20:34:08

Like (0)

Report
Re: How to return multiple columns from a LookUp functin?

@rprahalad

You need to concatenate the results of two lookup columns. I don't think you can do it within a single lookup.

LookUp(Employees,ID = colSearchResults[@ID].ID,FirstName) &" "& LookUp(Employees,ID = colSearchResults[@ID].ID,LastName)

Was this reply helpful? Yes No
rprahalad 377 on 30 Apr 2021 at 20:34:25

Like (0)

Report

Re: How to return multiple columns from a LookUp functin?

Thanks for your response. Its actually a LookUp column. So, technically I am just doing Select * from Employees where ID= 5. But I just need to do Select FirstName + " " + LastName from Employees where ID=5

Was this reply helpful? Yes No
RandyHayes 76,291 Super User 2024 Season 1 on 30 Apr 2021 at 20:35:31

Like (0)

Report

Re: How to return multiple columns from a LookUp functin?

@rprahalad

Is Employees the foreign (looked up) list?

Was this reply helpful? Yes No
rprahalad 377 on 30 Apr 2021 at 20:36:56

Like (0)

Report

Re: How to return multiple columns from a LookUp functin?

That's what I feared. Making two calls to DataVerse just to get data from two columns is what I am trying to avoid. Thanks for your response though!

Was this reply helpful? Yes No
Verified answer

rprahalad 377 on 30 Apr 2021 at 20:41:08

Like (0)

Report

Re: How to return multiple columns from a LookUp functin?

Yep! Sorry I should have mentioned this. This code is actually part of AddColumns block. It is one-one and I am just querying the Employees table with an absolute scalar ID and i need to return just a concatenated name. Thanks again!

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,291 Super User 2024 Season 1 on 30 Apr 2021 at 20:44:40

Like (4)

Report

Re: How to return multiple columns from a LookUp functin?

@rprahalad

You don't need to make two calls to the datasource. In fact, that is not how it works at all in PowerApps. When you lookup against a datasource, there is one call made to your cloud session table. After that, the app has the value for the future.

LookUp(Employees, ID = 5, FirstName & " " & LastName)

Will return the First and last names combined together for the Employees record with an ID of 5 in this example.

So, your original formula is fine. Your problem is that you are trying to lookup on an ID and a Table of ID's, that will not work.

Was this reply helpful? Yes No
rprahalad 377 on 30 Apr 2021 at 20:55:47

Like (1)

Report

Re: How to return multiple columns from a LookUp functin?

Thanks @RandyHayes ! That was it.

Was this reply helpful? Yes No
Kornel 50 on 26 Jul 2023 at 09:05:48

Like (0)

Report
Re: How to return multiple columns from a LookUp functin?

Hi all,
I am thinking about similar situation, but I need separate columns.
Right now the code looks like:
ClearCollect( colPeopleMaster, AddColumns( colPeople, "name", LookUp('People Details', GPN = colPeople[@sap_coo_gpn], Name), "email", LookUp('People Details', GPN = colPeople[@sap_coo_gpn], Email) ))
I have some collections where I need to pull 5-7 different columns, is there a way to do it in one LookUp?
Thanks!
Kornel

Was this reply helpful? Yes No
Kornel 50 on 03 Aug 2023 at 07:22:29

Like (0)

Report

Re: How to return multiple columns from a LookUp functin?

So more like using join in SQL than vlookup in Excel.
Join two tables with a key.

Was this reply helpful? Yes No