Lookup

[MiteshGovan]

Hi 

I trust that you are well. I am currently using dropdowns for selection in the columns for Division, Formulation Tech, Packaging Tech and Manufacturing Capability that is all stored on one list on a sharepoint. Is there a way where I can use a Lookup formula to search for the items that i have selected to give me the total duration. 

Can you please assist me?

Please see the attached images below.

Kind Regards,

Mitesh 

[v-jefferni]

Hi @MiteshGovan ,

Could you please share more details about your scenario? Is the “Total Duration” also a column in the SP list as the four columns you mentioned above? If so, which column types are all the five columns?

In fact, in Power Apps, using Dropdowns /Combo boxes to filter data sources is a very common process. Assuming that you are using Choices type columns for that four choices, then in your App it would be:

1\ Items property of the Dropdowns would be like:

Choices(SPlist.ChoicesColumnName)

2\ Items property of the Gallery/Data Table Control to display items of the SP list:

Filter(DataTable, Division.Value = DivisionDropdown.Selected.Value, ‘Formulation Tech’.Value = FormulationTechDropdown.Selected.Value, ‘Packaging Tech’.Value = PackagingTechDropdown.Selected.Value, ‘Manufacturing Capability’.Value = ManufacturingCapabilityDropdown.Selected.Value )

On your end, DataTable would be the SharePoint list so use list name in the formula.

Then in the Gallery or Data Table you would be able to choose which columns to display via Fields property on the right-hand Properties pane:

Please refer to the official doc to know more about Filter and LookUp functions.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[MiteshGovan]

Hi 

Thank you responding.

The total duration is also on the sharepoint list. 

Scenario:

In the above attached images i have provided. If i had to select the a column drop down list of an item. At the end of the selection it requires to give me a total duration for the items that i have selected. I only want to show the drop down list for Division, Forrmulation, Packaging Tech and Manufacturing capability which i already have and not a table on my app. But i am struggling to get obtain the lookup formula based on the selection of items that i have chosen. All my dropdowns are choice columns.

Thank you

[v-jefferni]

Hi @MiteshGovan ,

So you would like to display a column value based on those four selections, right?

If so, you need to add .TotalDuration at the end of the LookUp formula since a Label Control need Text value and the Total Duration is text type column.

In addition, you will need to modify your LookUp function since Choices columns value need to be like ColumnName.Value to be evaluated with the selected value.

Have you set Items of the Dropdowns to be Choices(SPlist.ColumnName) as in my reply above?

If so, the Text of the Label would be:

LookUp(DataTable, Division.Value = DivisionDropdown.Selected.Value, ‘Formulation Tech’.Value = FormulationTechDropdown.Selected.Value, ‘Packaging Tech’.Value = PackagingTechDropdown.Selected.Value, ‘Manufacturing Capability’.Value = ManufacturingCapabilityDropdown.Selected.Value ).'Total Duration'

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[MiteshGovan]

Hi

The dropdowns are not choices> Would i require to put it as choices.

 I have tried your formula. But it isn't working.

Lookup:

[v-jefferni]

Hi @MiteshGovan ,

If columns are not Choices but Text type, I think you were using text strings in Items of them at the beginning. You could use Distinct to retrieve distinguish values from the list via formula as below:

Distinct(SPlist, ColumnName)

Then the formula in Text of the Label would be:

LookUp(DataTable, Division = DivisionDropdown.Selected.Result, ‘Formulation Tech’ = FormulationTechDropdown.Selected.Result, ‘Packaging Tech’ = PackagingTechDropdown.Selected.Result, ‘Manufacturing Capability’ = ManufacturingCapabilityDropdown.Selected.Result ).'Total Duration'

So you could see the point:

If using Choices columns in SP list, all selections of the column could be retrieved in to items of a dropdown via Choices function. However, if are Text columns, you will need Distinct function to retrieve distinguish results from Text column existing values, or directly use single column table ["TextValue1", "TextValue2", "TextValue3",...] as Items of Dropdown.

Base on the way you populate Items of Dropdown, the evaluation would be different as well. Choices columns need Column.Value to evaluate, Text columns only need column names.

Hope it helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[MiteshGovan]

Hi 

I have tried the above but it isn't working. 

Sharepoint List:

This is a normal single text type column on each columns.

I have tried the above formula on 2 columns and it doesn't work. 

In the above image i tried it and it worked but there's an error. 

How do i select each item in the column to give me the Total Duration. The above options you have give me isn't working? Is any alternative option that i can do in order to get the value displayed in the label box.

[MiteshGovan · Accepted solution]

Hi 

I managed to sort it out. Thank you for your help.

[v-jefferni · Accepted solution]

Hi @MiteshGovan ,

That formula is uh... just the same as in my post.

Syntax of LookUp function could be:

LookUp(DataTable, conditions , ColumnValueToDisplay)

or

LookUp(DataTable, conditions).ColumnValueToDisplay

So is the Filter function.

You could have a try.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[MiteshGovan]

Hi

Thank you.