cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MiteshGovan
Helper V
Helper V

Lookup

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 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi 

 

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

 

MiteshGovan_4-1614878615974.png

 

View solution in original post

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.

View solution in original post

9 REPLIES 9
v-jefferni
Community Support
Community Support

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:

v-jefferni_0-1614841251272.png

 

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.

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.

 

MiteshGovan_0-1614845111301.png

Thank you

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.

Hi

 

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

MiteshGovan_0-1614850002624.png

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

Lookup:

MiteshGovan_1-1614850093136.png

 

 

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.

Hi 

 

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

 

Sharepoint List:

 

MiteshGovan_1-1614874669292.png

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

 

MiteshGovan_2-1614874696590.png

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

MiteshGovan_3-1614874816650.png

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.

 

Hi 

 

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

 

MiteshGovan_4-1614878615974.png

 

View solution in original post

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.

View solution in original post

Hi

 

Thank you.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (15,833)