Lookup based on multiple columns

[Anonymous]

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 🙂

Best regards,
Max.

[PavanKumar]

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

[Anonymous]

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]

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

[Anonymous]

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.

[v-xida-msft]

Hi @Anonymous ,

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,

[Anonymous]

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:

This the hardware cost list

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

Thanks,

Max.

[v-xida-msft]

Hi @Anonymous .

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

Best regards,

[Anonymous]

Hi V-xida,

The dataset I have is as follow:

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.

[v-xida-msft]

Hi @Anonymous ,

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,