cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LinaSaid
Frequent Visitor

Converting data type "table" / "record" to "value" so selected results can be used in calcuations

Hi,

I'm tyring to build an app that allows me to first filter prices/fees based on selected results within a data source, and also conver the selected result to a value so it can be used in a mathematical formula/calculation.

 

For example:

 

A) Dropdown 1 should select a Country

 

B) Dropdown 2 should be filtered and only shows available results within the selected result of Dropdown 1

 

C) Text Field should show selected result of Dropdown 2 


D) Text Field should be converted to value so it can be used in a mathematical formula (addition / multiplcation)

 

Example table below:

 

  • In the example where "Canada" is selected in Dropdown 1, only "Apples" and "Bananas" should appear in Dropdown 2.
  • Text Field should show "$1.50" if "Apples" are selected
  • Text Field should be able to be referenced in a label and multiplied / divided, etc

     

    CountryProductPrice
    CanadaApples$1.50
    CanadaBananas$2
    MexicoAvocado$1
    USAApples$1.00
    USABananas$1.25
    USAAvocado$3

 

Any help is greatly appreciated! Do let me know if you need any further details to help clarify my query.

11 REPLIES 11
eka24
Super User III
Super User III

In the Items Property of Dropdown1:

Distinct(CountryTable,Country)
 
In the Items Property of dropdown2:
Filter(CountryTable, Country = Dropdown2.Selected.Result )
 
In the Default of Textbox1 for the Price:
LookUp(CountryTable, Country = Dropdown2.Selected.Result ,Price)
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.
Drrickryp
Super User II
Super User II

@LinaSaid 

@eka24 has given a great example of how to do it. What you are describing is called "Cascading dropdowns".  You can find more information about them by searching on that term. 

LinaSaid
Frequent Visitor

Thanks for this!

I see the logic of the steps you have outlined and believe it should work for me, however, when I try to implement I recievd the following error message:

 

LinaSaid_0-1627040878691.png

 

When I look at the table in the datasource the records do match exactly so I am not sure why it's not working. Could there be something else in the datasource that is causing an error? Any advice would be welcome. 

KvB1
Solution Specialist
Solution Specialist

There was a small typo in the items property of dropdown 2, where its filtered by itself. It should be filtered by dropdown1.

 

Also, you might need to specify which part of the record you want to use in the filter. For example, even though the dropdown shows certain text, the dropdown.selected.result could contain the entire record, and not the value in the record you want to use in the next filter. Try adding . after the .Selected in the filter, and select the correct value. The suggestions for this is pretty useful.

LinaSaid
Frequent Visitor

Thanks.

Unfortunately, I'm still getting an error.

When I go to play the app, I see this banner at the top of the screen (in reference to mty data source)"

The requested operation is invalid. Server Response: [NameofTable] failed: Expression "Title eq null" is not supported. 

KvB1
Solution Specialist
Solution Specialist

Is Country the Title (the default column in your sharepoint list that requires a value) column in your sharepoint list?

LinaSaid
Frequent Visitor

"Country" is the name of the first column I am filtering data from. I am building this off of an Excel datasource, not SharePoint list. Is that problematic? Are there other potential common issues in Excel datsources that I could be blinding falling into?

eka24
Super User III
Super User III

Which of the codes do you get the error and what is the actual code that you are using?

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

LinaSaid
Frequent Visitor

I'm not sure how to answer (I'm self-taugh in PowerApps so not 100% familiar with all the terminology). What would a "code" refer to?

When I hover over the formula in TextBox1, specifically over the word "Result" (in "Selected.Result, Price"), I see a hover/pop up that says "Name isn't valid. This identifier isn't recognized". The word "Result" is also underlined in red

 

Example given previously

 

In the Items Property of Dropdown1:

Distinct(CountryTable,Country)
 
In the Items Property of dropdown2:
Filter(CountryTable, Country = Dropdown2.Selected.Result )
 
In the Default of Textbox1 for the Price:
LookUp(CountryTable, Country = Dropdown2.Selected.Result ,Price)

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,622)