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

[Anonymous]

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]

@Anonymous 

Are you attempting to show "Role" Choices based on which "Department" is selected?

That is to say, are you trying to check what records match a selected department, and of those, what roles are there for those records, and for those roles to show up as options for Role?

[Anonymous]

Yes so when they choose the "news" department, the next dropdown will show list of distinct roles wherever listitems where department =  "news". If you at B2B Marketing as department, only 2 roles should appear.

Let say  I added HR role in the "Business Acumen" listitem then user choose News as department, these 4 roles "Client Advocacy", "Client Operations", "Corp Dev", "Corp Account" plus HR should be listed under Role dropdown. When the user choose any of these roles in the role dropdown, the view courses link will appear in the canvas.

[poweractivate]

@Anonymous 

It looks like what you are providing is probably only running in the beginning, such as when the screen is visible or the app is started. Therefore, when the Department is selected, nothing will be done, so no change is observed.

The below lines need to be OnSelect of your Department button (your ddDept button):

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

However, beware, after doing this, you might get a delegation warning with the formula you have as written.

If it seems to work fine, you may ignore it. But beware, it may not work correctly if the number of records in the List is beyond the delegation limit.

[poweractivate]

@Anonymous 

Also, it may not show anything in the Role dropdown unless you do this:

If you do it, you may also prefer to amend one of your formulas to:

ClearCollect(colRole,{Result:"Select Role"});

[Anonymous]

Almost there, not filtering properly though. B2B should show two roles only. Thank you so much for your patient.

[poweractivate]

@Anonymous 

Is B2B marketing a Department on more than one Record besides Business Acumen? If so, which Records is it on? If it is on more than one Record, and you only want one Record to be chosen, how do you want to decide which Record it is? 

[poweractivate]

@Anonymous 

By the way, the delegation warning comes from using "in", which is not delegable in SharePoint.

Since you allow multiple selections in the Choice column, you must use in I believe.

I am not sure a way around it. To help support more records, you can raise the limit from 500 (default) to 2000 (max)

If you need it to be delegable, you may need to end up iterating through all the records, using something like ForAll.  However, I suggest using your version, even with the delegation warning, and raise the limit to 2000. When you think you are about to go over 2,000 total records in the SharePoint List, you can consider rewriting the query using ForAll or otherwise.

[poweractivate]

@Anonymous 

Also beware that you must click somewhere outside the dropdown after changing the selection for OnChange to take effect with the new value. Otherwise it still shows the previous values. This is out of the box behavior of OnChange. See if this helps @Anonymous 

[poweractivate]

@Anonymous 

Actually, instead of OnSelect you should use OnChange of ddDept 

See if it helps to use OnChange instead @Anonymous