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

LookUp Function for Columns?

I'm new to Power Apps and have spent too many hours trying to figure this out and need some help.

Using the data source below, I want to build an app that shows the Cost based on the selection of a Handset and a Plan Type (S,M,L,XL and Premium)

For example, if the user selects Apple iPhone XS 64GB (4GX) from the Handsets Column, on a L plan, the cost should show $25 (as marked in the picture)

 

Inked_Example.jpgSource Data

I have tried so many times to execute the requirement on my PowerApp and this is what I have got so far on my app. Basically two dropdown boxes named as 'Dropdown_Plan' to select the plan type (S,M,L,XL or Premium) and 'Dropdown_Handset' to select the handset. I want the Label that says 'Total cost is' to show the cost based on the selection.

 

Example App.JPGApp

 

Formula Used in Label: LookUp(Source, Handset=Dropdown_Plan.Selected.Value, Dropdown_Handset.Selected.Value).

 

 Example App 1.JPG

Since I am using LookUp, the function only returns the first value that matches with the formula (in this case, it simply returns M)

How do I use the LookUp function to tell Powerapps to look into the "column" of the selection and not the value. 

Any help would be massively appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: LookUp Function for Columns?

Hi @iamsujith,

I found a solution but it seems very kludgy to me.  I imported a table of bogus names and prices.  It is shown in the datatable at the bottom of the screen.  I found that the Lookup() function could be used but the third element had to be the actual name of the column head.  This meant that I had to use a variable to substitute in the actual formula into the text box to get the correct answer.  The code below was in the OnChange property of both dropdown controls.  

UpdateContext(
    {
        s: LookUp(
            Prices,
            CustomerName = Dropdown2.Selected.CustomerName,
            sm
        ),
        m: LookUp(
            Prices,
            CustomerName = Dropdown2.Selected.CustomerName,
            Med
        ),
        l: LookUp(
            Prices,
            CustomerName = Dropdown2.Selected.CustomerName,
            Lg
        ),
        x: LookUp(
            Prices,
            CustomerName = Dropdown2.Selected.CustomerName,
            Xlg
        ),
        p: LookUp(
            Prices,
            CustomerName = Dropdown2.Selected.CustomerName,
            Premium
        )
    })

The following formula was in the label.

"The value is: " & If(
    Dropdown3.Selected.Value = "sm",
    s,
    Dropdown3.Selected.Value = "Med",
    m,
    Dropdown3.Selected.Value = "Lg",
    l,
    Dropdown3.Selected.Value = "Xlg",
    x,
    Dropdown3.Selected.Value = "Premium",
    p
)


This worked perfectly when I tried it with my dataset.  However, if there is a more efficient way to accomplish it, I couldn't find another solution that worked and I tried similar methods to those you already tried.

3 REPLIES 3
Highlighted
Super User
Super User

Re: LookUp Function for Columns?

Hi @iamsujith,

I found a solution but it seems very kludgy to me.  I imported a table of bogus names and prices.  It is shown in the datatable at the bottom of the screen.  I found that the Lookup() function could be used but the third element had to be the actual name of the column head.  This meant that I had to use a variable to substitute in the actual formula into the text box to get the correct answer.  The code below was in the OnChange property of both dropdown controls.  

UpdateContext(
    {
        s: LookUp(
            Prices,
            CustomerName = Dropdown2.Selected.CustomerName,
            sm
        ),
        m: LookUp(
            Prices,
            CustomerName = Dropdown2.Selected.CustomerName,
            Med
        ),
        l: LookUp(
            Prices,
            CustomerName = Dropdown2.Selected.CustomerName,
            Lg
        ),
        x: LookUp(
            Prices,
            CustomerName = Dropdown2.Selected.CustomerName,
            Xlg
        ),
        p: LookUp(
            Prices,
            CustomerName = Dropdown2.Selected.CustomerName,
            Premium
        )
    })

The following formula was in the label.

"The value is: " & If(
    Dropdown3.Selected.Value = "sm",
    s,
    Dropdown3.Selected.Value = "Med",
    m,
    Dropdown3.Selected.Value = "Lg",
    l,
    Dropdown3.Selected.Value = "Xlg",
    x,
    Dropdown3.Selected.Value = "Premium",
    p
)


This worked perfectly when I tried it with my dataset.  However, if there is a more efficient way to accomplish it, I couldn't find another solution that worked and I tried similar methods to those you already tried.

iamsujith
Level: Powered On

Re: LookUp Function for Columns?

Hi @Drrickryp,

 

Your idea worked like a charm. Man, you made it look so simple. I have been trying so hard to get this right the last couple of days and I cannot thank you enough for the help.

 

 

iamsujith
Level: Powered On

Re: LookUp Function for Columns?

Hi @Drrickryp,

Thanks again for your help with my above query. I have been trying to manipulate your 'Update Context Method to create new set of Variables' to suit my app requirements  and here's what I have done.

Methods.JPGDropdown Control vs Text Input ControlTo the left, I have applied your method successfully in the 'Dropdown box controls' and obtained the desired result without any problems. However,on the right side - I tried to apply the same method on a different control type (Text Input Control) with the same set of data/source of data, I am unable to obtain the desired result. Infact, the variables that are created under the 'OnChange' property of the TextInput Controls are not assigned a value at all.

Is there a workaround to get your method working on a Text-Input Control?  Please help.

(Background: I am working on developing a cost estimator app that spits out the cost of the selected items (Plan, Phone, AddOns) in a shopping cart styled theme. To achieve the same, I am using Galleries to show the data (Select Plan, Select Handset, Select AddOns) for selections. Everytime the user selects an item, it gets added to a specific collection. And later, all the items in the collection are displayed and are added to give a final cost.)

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

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

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 190 members 4,454 guests
Please welcome our newest community members: