cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mdoe82
Level: Powered On

Lookup based on multiple columns

Hi,

 

I'm trying to create an asset mangement app.

For this app I got a list "Asset Management" with selection field for hardware, purchase order and a bunch of others.

In this list I also have a column named Price.

 

I've got a seperate list called "hardware cost" with the fields hardware, purchase order and the price.

 

What I want to do is, have the column price in the "Asset Management" filled with the price from the "Hardware Cost".

 

So the price column in "Asset Management" will take the purchase order and hardware combination from "Asset Managment" and use that to check the price in "Hardware Cost" 

 

I'm rather new to Powerapp, I figure I need to make a look up field, but I'm not certain how I can do this with a combination of columns, so say I got a Dell laptop with Purchase order 12345 it would need to find the dell laptop wit PO 12345 in "Hardware Cost" and give back the price.

 

I hope I've made my problem somewhat clear and someone did something like this before and can help me out with this Smiley Happy

 

Best regards,
Max.

9 REPLIES 9
PavanKumar
Level 8

Re: Lookup based on multiple columns

Hi, Could you please simplify the question if possible with screenshot. That helps me to solve it. Warm Regards, Pavan Kumar Garlapati
mdoe82
Level: Powered On

Re: Lookup based on multiple columns

Hi PavanKumar,

 

Thanks for your response, so basicly it is as follow:

 

I got a list "asset mangement" with columns:

- Hardware type

- Purchase order

- Price

 

Hardware type and purchase order are filled and I want to use the price column as a lookup

 

I got a list "Hardware Cost" with columns:

- Hardware type

- Purchase order

- Price

 

Al columns in this list are filled.

 

I want the price column in "asset management" to take Hardware type and  Purchase order and check that against the same column in "Hardware Cost" and put the price that is in that combination in the price column in asset management.

 

I'm not able to make screenshots at this time. If thats needed I'll try to do so later today.

 

Thanks,

Max.

 

 

PavanKumar
Level 8

Re: Lookup based on multiple columns

Hi,

 

Please find below this will answer

 

//Hardware Cost list

ClearCollect(HardwareCost,
{ HardwareType1: "HA", PurchaseOrder: "POA", Price: 100},
{ HardwareType1: "HB", PurchaseOrder: "POB", Price: 200},
{ HardwareType1: "HC", PurchaseOrder: "POC", Price: 300}
);

 

//Asst Managment list where we don't have any price value which we need to get from Hardware cost list.

ClearCollect(AssetMangement,
{ HardwareType: "HA", PurchaseOrder: "POA", Price: 0 },
{ HardwareType: "HB", PurchaseOrder: "POB", Price: 0 },
{ HardwareType: "HC", PurchaseOrder: "POC", Price: 0 }
);

 

// Update the Price Management depending upon hardware type from hardware cost ( add as many conditions as you want)

UpdateIf(AssetMangement,Price = 0,{Price : First(Filter(AssetMangement1,HardwareType1 = HardwareType)).Price});

 

Now Asset management has price value as well.

 

Hope this answered your question. Please let me know if further questions/doubts.

 

Mark answer as verified it it solved your problem.

 

Regards,

Pavan Kumar Garlapati

mdoe82
Level: Powered On

Re: Lookup based on multiple columns

Hi Pavan,

 

Thanks for your reaction.

 

And I'm sorry for being such a complete noob, but where would I put those query's?

 

Thanks,

Max.

Community Support Team
Community Support Team

Re: Lookup based on multiple columns

Hi @mdoe82 ,

Could you please share a bit more about the Hardware type column and Purchase order column in your "Asset Mangement" list and "Hardware Cost" list? Are they both Text type column or Choice type column?

 

I assume that you generate an app based on "Asset Mangement" list, and you want to auto-fill the Price field value within the Edit form based on the selected Hardware value and Purchase order value, is it true?

 

Based on the issue that you mentioned, I think the LookUp function could achieve your needs. I have made a test on my side, please take a try with the following workaround:

1. If the Hardware type column and Purchase order column in your "Asset Mangement" list and "Hardware Cost" list are both Text type column:

Within your Edit form, set the Default property of the Text Input box within the Price Data card to following:

 

LookUp('Hardware Cost', 'Hardware type' = HardwareTypeTextBox.Text && 'Purchase order' = PurchaseOrderTextBox.Text, Price)

Or

LookUp('Hardware Cost', 'Hardware type' = HardwareTypeTextBox.Text && 'Purchase order' = PurchaseOrderTextBox.Text).Price

 

2. If the Hardware type column and Purchase order column in your "Asset Mangement" list and "Hardware Cost" list are both Choice type column:

Within your Edit form, set the Default property of the Text Input box within the Price Data card to following:

LookUp('Hardware Cost', 'Hardware type'.Value = HardwareTypeDropdownBox.Selected.Value && 'Purchase order'.Value = PurchaseOrderDropdownBox.Selected.Value, Price)

Or

LookUp('Hardware Cost', 'Hardware type'.Value = HardwareTypeDropdownBox.Selected.Value && 'Purchase order'.Value = PurchaseOrderDropdownBox.Selected.Value).Price

Please consider take a try with above solution, then check if the issue is solved.

 

More details about the LookUp function, please check the following article:

LookUp function

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mdoe82
Level: Powered On

Re: Lookup based on multiple columns

Hi V-Xida

Edit: something went wrong with the screensot.

 

Thanks for the quick response, they are indeed choice fielfds.

This is the Asset Management list:

 

asset.PNG

This the hardware cost list

 

hardware.PNG

 

I think with the explenation you gave I will get a long way.

 

Thanks,

Max.

Community Support Team
Community Support Team

Re: Lookup based on multiple columns

Hi @mdoe82 .

Yeah, I think the solution (second solution) I provided above could achieve your needs. Please consider take a try with it.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mdoe82
Level: Powered On

Re: Lookup based on multiple columns

Hi V-xida,

 

The dataset I have is as follow:

 

Data set.PNG

 

And the query I've got is as follow:

 

LookUp('Item/Part', 'Item/Part'.Value = Item/PartValue.Selected.Value && 'Po'.Value = POValue.Selected.Value, Sum of Amount)

But gives me an error:

Invalid number of arguments 1 received 2-3 expected

 

Thanks again for your help,

Max.

Community Support Team
Community Support Team

Re: Lookup based on multiple columns

Hi @mdoe82 ,

Based on the formula you provided, I think there is something wrong with it.

 

The first argument of the LookUp function should be filled with a data source rather than a column name. On your side, you should type 'Hardware Cost'.

 

In addition, I found that the 'Item/Part' is a Text type column in your 'Hardware Cost' list, and the PO is a Number column in your 'Hardware Cost' list. Please consider modify your formula as below:

 

LookUp('Hardware Cost', 'Item/Part' = Item/PartValue.Selected.Value && PO = Value(POValue.Selected.Value), PRICE)

 

If you want to get the corresponding "Sum of Amount" column value, please modify above formula as below:

LookUp('Hardware Cost', 'Item/Part' = Item/PartValue.Selected.Value && PO = Value(POValue.Selected.Value), 'Sum of Amount')

Please take a try with above solution, check if the issue is solved.

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 335 members 1,827 guests
Please welcome our newest community members: