Dependant dropdown issue.

[Anonymous]

Goodday all,
I have a powerapp with a Name , Timeloss These 2 dropdowns are irrelevant for this question. The other dropdowns are relevant.
See ERD/Powerapp below.
I have written placeholders in the place off sensitive data.

PowerApp:

Reason id's are dependent on the category infront of it.

ERD:

I can't seem to get the reason id correct.
in this instance reason_category_2 and _3 are empty and when empty instead of giving the reason id it gives a 1.
Any way to be garantueed to get the correct id? I am so confused by some of this.
There are no duplicate rows. See table example data:

Bassicly what i'd need is to be able to base 1 dropdown off 5 others somehow. Kind of like a hierarchy as seen below. If i would be able to make 1 dropdown with these 5 values i could depend the reasons_id on this one hierarchy dropdown.

budget_cat
    budget_subcat
        reason_cat1
            reason_cat2
                reason_cat3

[BCBuizer]

Hi @Anonymous ,

I ran a quick test without issues:

Can you share a similar screenshot of your app, with the cursor in the formula bar?

[Anonymous]

Goodday, I will send all info i assume is relevant. 
Code in the formula bar:
Coalesce(
reason_category3_dropdown.Selected.Value,
reason_category2_dropdown.Selected.Value,
reason_category1_dropdown.Selected.Value
)

Code in dropdowns(It depends on the dropdown above so names will change per dropdown.
Filter('DD.reason_category', reason_category_2 = reason_category2_dropdown.Selected.reason_category_2)

The submit button is not yet connected. That is a different post of mine.
Data comes from an SQL DB. 
Dropdown looks like this when blank.

Found some more explained error msgs.

[BCBuizer]

Hi @Anonymous ,

Depending on the Items property of the DropDown a different property than Value may need to be selected. You can try deleting ".Value" from your code, then type "." and Intellisense will show you the available options. All you have to do from there is to pick the right property.

In the below example I changed the Items property of the DropDowns to return a column called "Result" which broke my working formula because the wrong property was now selected. As you can see Intellisense is suggesting "Result" as the possible option, so clicking on that will fix this part. This then needs to be repeated two more times for the other two DropDowns after which the formula will be working again.

[Anonymous]

Thank you, Coalesce(
reason_category3_dropdown.Selected.reason_category_3,
reason_category2_dropdown.Selected.reason_category_2,
reason_category1_dropdown.Selected.reason_category_1
)
this gives me the reason category of the lowest none blank reason.
How do i now make it give me the reason_id connected to this reason?

[Anonymous]

Is there anything such as pseudo code below:
DD.reason_category.reason_category_id = Label5.text
or
Filter('DD.reason_category', reason_category_id = Label5.Text)
Bottom seems like it should work but it gives an error due to one value being text and another being an int i think.

[BCBuizer]

Hi @Anonymous ,

So far I understood you just want the ID of the hierarchically lowest selected reason category, not the associated reason_id. If I see things well, the below should give you that. You can test this in a label.

With({ cat3 : reason_category3_dropdown.Selected.Reason_category_3,
       cat2 : reason_category2_dropdown.Selected.Reason_category_2,
       cat1 : reason_category1_dropdown.Selected.Reason_category_1},
   LookUp(
      reason_category,
      (IsBlank cat3 || Reason_category_3=cat3) &&
      (IsBlank cat2 || Reason_category_2=cat2) &&
      (IsBlank cat1 || Reason_category_1=cat1),
      reason_id
   )
)

[Anonymous]

Thanks, i seem to get this error i have gotten before when trying to use multiple functions on one formula. Any help?

With({ cat3 : reason_category3_dropdown.Selected.reason_category_3,
       cat2 : reason_category2_dropdown.Selected.reason_category_2,
       cat1 : reason_category1_dropdown.Selected.reason_category_1},
   LookUp(
      reason_category_id,
      (IsBlank cat3 || reason_category_3=cat3) &&
      (IsBlank cat2 || reason_category_2=cat2) &&
      (IsBlank cat1 || reason_category_1=cat1),
      reason_category_id
   )
)

[Anonymous]

It also seems that reason_category_id is not recognized. How do i call the table then the column? I think thats the solution.

[BCBuizer · Accepted solution]

Hi @Anonymous ,

Apologies, I missed some brackets. The way the LookUp function works is that the first argument is the data source (table). In your case I believe that's called "Reason_Category", so that's what I inserted. Please correct me if I'm wrong. Then the third argument identifies which value to return, in this case "reason_category_id":

With({ cat3 : reason_category3_dropdown.Selected.reason_category_3,
       cat2 : reason_category2_dropdown.Selected.reason_category_2,
       cat1 : reason_category1_dropdown.Selected.reason_category_1},
   LookUp(
      Reason_Category,
      (IsBlank(cat3) || reason_category_3=cat3) &&
      (IsBlank(cat2) || reason_category_2=cat2) &&
      (IsBlank(cat1) || reason_category_1=cat1),
      reason_category_id
   )
)

[Anonymous]

Thanks,

With({ cat3 : reason_category3_dropdown.Selected.reason_category_3,
       cat2 : reason_category2_dropdown.Selected.reason_category_2,
       cat1 : reason_category1_dropdown.Selected.reason_category_1},
   LookUp(
      'DD.reason_category',
      (IsBlank(cat3) || reason_category_3=cat3) &&
      (IsBlank(cat2) || reason_category_2=cat2) &&
      (IsBlank(cat1) || reason_category_1=cat1),
      reason_category_id
   )
)

works. Thanks somuch for your time. Now just gotta figure out how to patch the idea into a table. If u got time i have a post up about this. I just need a line of code which inserts reason_category_id into a table "three". linked to the one_id which is linked to two_id. the two_id is the id of the table with the task/name number on the top left off my powerapp manually chosen by the user. See ERD below. If u got time/questions let me know. I really think this should be do able but cant get the code right.