Headache of Incompatible types for comparison in cascading dropdowns with lookup columns

[normmsnpoweruse]

I have a list with 4 columns as you see below. The department and role are choice field dropdowns.

- If a user chooses "News" under department dropdown will show distinct values "Client Advocacy", "Client Operations", "Corp Dev", "Corp Account under Roles dropdown

- If a user chooses "B2B Marketing" under department dropdown, only "Client Advocacy", "Client Operations" will show under Roles dropdown

I have this so far and role is NOT filtering properly in the canvas app.

It is driving me crazy so any help is highly appreciated.

ClearCollect(colDept,"Select Department");
Collect(colDept, Choices('TDC Admin Portal V1'.Department));
ClearCollect(colRole,"Select Role");
Collect(colRole, Distinct(Ungroup(Filter('TDC Admin Portal V1', ddDept.Selected.Value in Department.Value).Role,"Role"),Value));

[poweractivate · Accepted solution]

@normmsnpoweruse 

Just change the Visible to true for that one.

For ddDept and for OnChange use this 

ClearCollect(colRole,"Select Role");
Set(recsVar,Filter('TDC Admin Portal V1', ddDept.Selected.Value in Department.Value));
Collect(colRole, Distinct(Ungroup(recsVar.Role,"Role"),Value));

Or this below

ClearCollect(colRole,{Result:"Select Role"});
Set(recsVar,ForAll('TDC Admin Portal V1',If(ddDept.Selected.Value in ThisRecord.Department.Value,ThisRecord)));
Collect(colRole,Filter(Distinct(Ungroup(recsVar,"Role"),Value),Not(IsBlank(ThisRecord.Result))));

In the Gallery Items property, change it to:

recsVar

See if it works now @normmsnpoweruse 

P.S.

To keep the above solution working for as long as possible when your data source starts getting more records, raise the default of 500 to the max of 2000 with below steps:

1. Click Settings

2. Scroll down, and then change 500 to 2000 in the Data row limit. After that you can close the modal, this change should save.

After you have more than 2000 records, this solution will not work anymore because anything after the 2000th record will not be considered. For that, a more complex version is needed, and I will not provide you it for now as it is more complex than this version.

I recommend to use this version, for as long as you can, and see if it works for you.

[normmsnpoweruse]

FINALLLLLLY, it worked using this formula "

ClearCollect(colRole,{Result:"Select Role"});
Set(recsVar,ForAll('TDC Admin Portal V1',If(ddDept.Selected.Value in ThisRecord.Department.Value,ThisRecord)));
Collect(colRole,Filter(Distinct(Ungroup(recsVar,"Role"),Value),Not(IsBlank(ThisRecord.Result))));

You have no idea how relieved I am. I could hug you in person! Thank you so much!

[normmsnpoweruse]

Thank you for the limit row adjustments. I set it to 2000 but from what I heard from the HR dept, they won't go over 500.

One last thing, how do I load all courses in the gallery as a default?

[poweractivate]

@normmsnpoweruse 

NOTE: recsVar might need to be checked for whether it has something in it, or not, with IsEmpty since it might be a Table, not a Record

The variable recsVar will initially be Blank since there is no selection for the Department and the recsVar has not yet been Set, so in the beginning so you could do this to populate the default by checking if recsVar is Blank. To do this, try as follows:

For Items property of Gallery do this:

If(IsBlank(recsVar),'TDC Admin Portal V1',recsVar)

To make it cleaner, the recsVar should be explicitly set back to Blank() later when user has caused there to be no department seelcted i.e. when the "Select Department" option itself you had there, is chosen by the user.

For OnChange of the ddDept (if there is an error, try the next step of this post first just in case)

//pseudocode - not tested 
If(
   //condition - if the current selected value of this dropdown is "Select Department" 
   Self.Selected.Value = "Select Department",
   //if the above is true, set recsVar to Blank() and clear Role dropdown entries
   Set(recsVar,Blank());
   Clear(colRole),
   //else if the condition was not true, execute the working formula
   ClearCollect(colRole,{Result:"Select Role"});
   Set(recsVar,ForAll('TDC Admin Portal V1',If(ddDept.Selected.Value in 
   ThisRecord.Department.Value,ThisRecord)));
   Collect(
      colRole,
      Filter(
         Distinct(Ungroup(recsVar,"Role"),Value),Not(IsBlank(ThisRecord.Result))
      )
   );
   
)

Before debugging the above, if the above does not work or continues to give an error on recsVar, also try this below first:

On the first part, such as the Screen OnVisible you can do this - add the Set(recsVar,Blank()):

ClearCollect(colDept,"Select Department");
Collect(colDept, Choices('TDC Admin Portal V1'.Department));
Set(recsVar,Blank());

If there are no errors, or if you adjusted it to remove the errors, try removing  the set recsVar to blank from the above

ClearCollect(colDept,"Select Department");
Collect(colDept, Choices('TDC Admin Portal V1'.Department));

since it may not be necessary to have the set recsVar to blank there, the variable should just be blank by default, and because the variable is used somewhere in the app, the variable should exist and just be blank in the beginning without explicitly declaring it. If the error reappears when removing the line though, then it means the set recsVar to blank explicitly may have to be in the OnVisible initial part as well to avoid errors.

See if the above helps @normmsnpoweruse 

[normmsnpoweruse]

Still working on this since the suggestions won't work. I think it is because I chose Flexible Height Gallery, "BLANK" layout so I am changing it to "News" layout and see....

[poweractivate]

@normmsnpoweruse 

What specifically does not work at the moment?  Are you referring to just the Gallery default part and everything else is working, or are you referring to something else?

[normmsnpoweruse]

All the courses loading at the beginning before any filtering dropdown is applied.

[poweractivate]

@normmsnpoweruse 

What formula do you have for the Default property of the Gallery right now?

[normmsnpoweruse]

If I add in the default this, I get an error data type = table. If I leave it blank and just used Data Source properties, it will populate the gallery but then the dropdown stopped working.

[poweractivate]

@normmsnpoweruse 

Do you want to try recsVar there to see if it works?