Populate text input based on Drop down selection with data from another excel table

[Kareem_Amr]

Hello,

I have two data connections from Excel, one is a table Data Set:

SKU Name	SKU Key
SKU1	10
SKU2	11

and the other is a table called Allocation:

SKU Name	CPL	Allocation
SKU1	CPL1	50%
SKU1	CPL2	50%
SKU2	CPL3	100%

I am trying to make the selection in a drop down menu reflect the data on some text inputs based on the choice of the drop down list and a button to load the data needed into the text fields.

For example, I selected SKU1 from the drop down and pressed the load button, it would fetch the data from the allocation table and load the CPL and Allocation per that CPL into the text fields.

I tried using Lookup but it is not accepting my data arguments:

Can anyone help me?

[Talha_Dar]

Hi @Kareem_Amr,

Try using the following formula.

Lookup(AllocationDataSourceName, 'SKU Name' = Dropdown4_1.Selected.Value && CPL = "CPL 2", Allocation)

Here change the "AllocationDataSourceName" to the allocation table data source and "CPL 2" to the one you are trying to find also "Allocation" to the column name whose value is to be fetched.

If you found my answer helpful, please consider giving it a thumbs-up or a like. Your feedback is greatly appreciated!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-hanytian-msft]

Hi @Kareem_Amr ,

Do you want to fetch the allocation Data Which CPL="CPL2" and SKUName equals dropdown selected.

Also because the data source is Excel Table we suggest replace the Column Name "SKU Name" into "SKU Name" .

Please try this:

LookUp(DataSource,'SKU Name'=Dropdown4_1.Selected.Value & CPL="CPL2",Allocation)

Please Note that the Data source is the Table Name of the Second Excel Table.

Hope this will help you,

Best Regards,
Tina

[Kareem_Amr]

Thank you. This works great, but would you happen to know how to make it loads the SKU based on the button press? Like for example I selected 4 SKUs, and the button I press loads the data into the text boxes and it changes based on which SKU I want to load?

TIA

[Kareem_Amr]

This is great, thank you. But is there a way to dynamically allocate the CPLs instead of manually selecting them in the statement?

[v-hanytian-msft]

Hi @Kareem_Amr ,

What kind of Control you want to use If you want to match the CPLs dynamically?

If it is also the Dropdown, please try this:

LookUp(DataSource,'SKU Name'=Dropdown4_1.Selected.Value & CPL = Dropdown.Selected.Value,Allocation)

Hope this will help you,

Best Regards,
Tina

[Talha_Dar]

Hi @Kareem_Amr,

As mentioned by @v-hanytian-msft you can create a drop down and then use the following formula.

LookUp(DataSource,'SKU Name' = Dropdown4_1.Selected.Value && CPL = Dropdown_CPL.Selected.Value,Allocation)

Here change "Dropdown_CPL" to the drop down name of your CPL.

If you found my answer helpful, please consider giving it a thumbs-up or a like. Your feedback is greatly appreciated!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kareem_Amr]

Hi @v-hanytian-msft ,

It is not a control, but the rows from the table it self, like if I set the SKU to 1 on the drop down, it would get me all the values for that SKU on those text inputs. Is there a way to do this?

Thanks

[Talha_Dar]

HI @Kareem_Amr,

Just update the code on default property of each input to the following. or the text property if these are label control.

On CPL 1:

Lookup(AllocationDataSourceName, 'SKU Name' = Dropdown4_1.Selected.Value && CPL = "CPL 1", Allocation)

On CPL 2:

Lookup(AllocationDataSourceName, 'SKU Name' = Dropdown4_1.Selected.Value && CPL = "CPL 2", Allocation)

On CPL 3:

Lookup(AllocationDataSourceName, 'SKU Name' = Dropdown4_1.Selected.Value && CPL = "CPL 3", Allocation)

On CPL 4:

Lookup(AllocationDataSourceName, 'SKU Name' = Dropdown4_1.Selected.Value && CPL = "CPL 4", Allocation)

If you found my answer helpful, please consider giving it a thumbs-up or a like. Your feedback is greatly appreciated!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.