Default Drop Down

[Kayssarr]

Hi , 

I was wondering if someone can assist with a below: 

I have a 2 page Power app for the purposes of writing comments to SQL table.

The first page is a search page with 2 sections  1. a bunch of drop down and  2. a Gallery (GlrChosen) where they can modify an existing comments  or press  (+) button and open an empty form on the second page. If a comment is chosen (using the >) arrow a variable called VarMode is set to Edit to indicate it is being edited otherwise it is set to New.

On the second page: 

There are bunch of dropdown and a textbox for someone to write a comment.  For each of the dropdown if the varMode is edit it will bring the Gallery  values over (GlrChosen.Selected.FieldName) using the DefaultSelectedItem function, otherwise in the Item are the values from the SQL table i want  the user to pick from. 

For editing a row, the user then has the option of clicking the drop down function and showing all the entries in the dropdown not just the defaultedSelectedItem and change accordingly. 

The problem i am having is as below: 

Dropdown1: 

DefaultSelectedItem:   If(varMode = "Edit", {Value: GlrChosen.Selected.AccountType})

Items: ["Revenue", "Expense"]

Dropdown2 : 

DefaultSelectedItem: If(varMode = "Edit", {Value: GlrChosen.Selected.AccountCategory})

Items:  

 With(
{wAccountCat: Filter(SQLTable1 ,NaturalAccType = Dropdown1.Selected.Value).AccountGroupDescription},
Distinct(Sort(wAccountCat, AccountGroupDescription, Ascending), AccountGroupDescription))

Dropdown3: 

DefaultSelectedItem: If(varMode = "Edit", {Value : GlrChosen.Selected.AccountSubCategory})

Items: 

With(
{wAccountSubCat: Filter(SQLTable1 ,NaturalAccType = Dropdown1.Selected.Value && AccountGroupDescription = dropdown2.Selected.Result).AccountSubGroupDescription},
Distinct(Sort(wAccountSubCat, AccountSubGroupDescription, Ascending), AccountSubGroupDescription))

If I want to change the value of Dropdown2 after it is defaulted, I can see the full list of options that relate to that field restricted by Dropdown1.

However, when I want to do the similar thing to dropdown3,  i only get the defaulted value and no other options. The database shows combination of Dropdown1 and dropdown2 , i should be able to chose from 5 other options including the default

An Example is :

Dropdown1 has Revenue and Expense

Dropdown 2 has for Revenue (Training & Other revenue) and for Expenses (Machine & Depreciation)

Dropdown3 has for Training (Higher education, Book, Fees, ....) and for Other revenue (Hire of Facilities, Other revenue...)

If i follow the trail : 

Dropdown1 defaults to  Revenue as per client entry

Dropdown2 defaults to Training  as per client Entry , but i can chose Other revenue as it appears in the list

Dropdown3  defaults to Higher education as per client entry, and no other option in the list  

The dropdown 3 is where i need help with, I followed the same setup as Dropdown2 but cant get the other options to load 

Appreciate any Help

[cwebb365]

What is SQLTable1? I'm assuming it's a table with just defined list of these Account Types? Not an actual table of the entries? Create another drop down and manually put in some data and see what happens. Got to be a data mismatch not filtering right in there. 

In OnStart do something like 

ClearCollect(colTemp,Filter(SQLTable1 ,NaturalAccType = "Revenue"  && AccountGroupDescription = "Revenue (Training & Other revenue)").AccountSubGroupDescription) 

Then check the collection see if it returns more than 1 record in there, if not, then play around with your filter. This will help eliminate if it's a data matching issue or something up with the controls passing data properly. 

[Kayssarr]

Yes the SQLRable1 is an SQL server table with a lot of columns including NatAccType, AccountGroupDesc, and AccountSubGroup. 

The problem with doing that OnStart is when I refresh Dropdown2 the Collection doesn't refresh. 

I managed to get it to work if i change Dropdown2  into an actual array rather than a column from a table. This is not ideal as the list changes and grows

[Kayssarr · Accepted solution]

Thanks for that. it gave me an idea and this has been fixed. it was a 3 part : 

1. On the > arrow,  Create a collection using the GlrChosen as parameters

2. On Items reference the collection. 

3. On dropdown2 OnChange,  refresh the collection using the Form Combos and not the GlrChosen fields