Cascading Dropdown from a sharepoint list and showing the results in a text label

[lisbonc1987]

Hello. I have been trying to figure this out and it should be simple and I have been stuck for a couple of days now and it's very frustrating. In my sharepoint list called PIID Action Types 2, I have three problematic fields in power apps. The FiscalYear dropdown, ActionType dropdown, and a CurrentValue label. The dropdowns work just fine. I set the formula for the FiscalYear dropdown to below:

Choices('PIID Action Types 2'.FiscalYear)

The ActionType values would base on the selected Fiscalyear from the FiscalYear Dropdown. The function for action type dropdown is below:

Distinct(Filter('PIID Action Types 2', FiscalYear.Value = DataCardValue3.Selected.Value), ActionType)

Now the problem is the label. When I put the lookup function below, I get an incompatible types for comparison. These types can't be compared: text, record.

LookUp('PIID Action Types 2', FiscalYear.Value = DataCardValue3.Selected.Value && ActionType.Value = DataCardValue2.Selected.Value, CurrentValue)

I have been watching a lot of youtube videos about this and I have tried changing a few things and nothing seem to work. I even tried just outputting just the text in the label  (DataCardValue2.Selected.Value) and that says it's expecting a text. I figured it's probably a record of both the fiscal year and action type.

How can I just get the action type value of the dropdown so that I can write a lookup function to compare the results of the fiscalyear dropwdown and the actiontype drowndown and return the CurrentValue label associated with it? Thank you so much for any help. This has been very frustrating for me.

EDIT: I want to add that if I remove the ActionType.Value = DataCardValue2.Selected.Value in my lookup function and just leave the fiscal year, it doesn't throw any error but it doesn't return anything.

[WarrenBelz]

Hi @lisbonc1987 ,

Is ActionType a single or multiple value (I assume Lookup) field and is DataCardValue2 a single or multiple selection ?

[lisbonc1987]

Hello. Fiscal Year and Action Type are both choice columns and should only return one value. For example,

Fiscal Year = 2024, Action Type = Seaport Orders (or a different order)

or

Fiscal Year = 2025, Action Type = F orders (or a different order)

The drop down would only let you select one. Based on the above selection, I want my label to return the CurrentValue column. So if I pick 2025 and F order, I want the CurrentValue to return (which is 1 with this example because they have yet to pick future orders from 2025)

[WarrenBelz]

@lisbonc1987 ,

There is something wrong here as the error you are getting

is saying that ActionType.Value (your Choice field) cannot equal (is a different structure) DataCardValue2.Selected.Value. What is the Items of DataCardValue2 and can you please re-check that you have not allowed multiple selections in ActionType in SharePoint.

[lisbonc1987]

Hello Warren. The Items of DataCardValue2 is the items of ActionType based on FiscalYear. Also, Multiple selection is not allowed in my ActionType column in sharepoint

This second screenshot is the function of DataCardValue2

The CurrentValue field below is showing "1" because I changed the formula to 

LookUp('PIID Action Types 2', FiscalYear.Value = DataCardValue3.Selected.Value, CurrentValue) but it should be looking at both the FiscalYear AND ActionType to determine what the value is.

I created a different lookup list with data types Text for all columns and I was able to get the results that I want. So I think it has something to do with Choice columns having different structures. Thank you for your help!

[lisbonc1987 · Accepted solution]

For some reason, when i tried this formula out earlier, blank was coming up. Now it's working. Weird. Anyway, this is what works: LookUp('PIID Action Types 2', ActionType.Value = DataCardValue2.Selected.Value.Value && FiscalYear.Value = DataCardValue3.Selected.Value, CurrentValue)

[WarrenBelz]

@lisbonc1987 ,

I am running out of ideas here as this is making very little sense - if the Items of DataCardValue2 is

Distinct(
   Filter(
      'PIID Action Types 2', 
      FiscalYear.Value = DataCardValue3.Selected.Value
   ),
   ActionType
)

only a single selection is allowed then this

LookUp(
   'PIID Action Types 2', 
   FiscalYear.Value = DataCardValue3.Selected.Value && 
   ActionType.Value = DataCardValue2.Selected.Value
).CurrentValue

should produce a single (I assume numeric) value for your label. I assume CurrentValue is a numeric field in 'PIID Action Types 2' As a debugging exercise, try this

LookUp(
   'PIID Action Types 2', 
   ActionType.Value = DataCardValue2.Selected.Value
).CurrentValue

[lisbonc1987]

Warren, I got it figured out from the above post. Weird because I tried that eariler and it wasn't working. Like nothing was showing up. But it worked out of nowhere when i tried again.

[WarrenBelz]

@lisbonc1987 ,
As I said from the start, it should work based on what you posted . . .

[lisbonc1987]

Yeah it was just weird because I had tried that before and it just returned blank with no error so I thought it didn’t work. But it worked out of nowhere. Maybe connection issues or whatever it is haha. Thank you for the help!