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));

[Anonymous]

When

When I do this, the dropdown department will filter right away without choosing any role.

[poweractivate]

@Anonymous 

I think you should leave Default blank. This just decides the default for Gallery.Selected, not the whole Gallery. This is usually used in combination with a Form's Item property. I don't think it's relevant for you here. Even when used with a Form's Item property, when this is left blank, the default is the first item of the Gallery, which in many cases, is sufficient even then.

For the Items property of Gallery, use something like this

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

 It's the Items property that decides what shows in the Gallery.

[Anonymous]

I tried yours and I even changed it to If(ddDept.Selected.Value="Select Department",'TDC Admin Portal V1',recsVar), still showing blank during loading and even selecting ddept to "Select Department"

[poweractivate]

@Anonymous 

Reviewing the previous formulas, it looks like recsVar is being generated by a ForAll . This returns a Table, not a Record. 

So I think it should be

For the Items property of the Gallery

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

use the Items property of the Gallery!

Leave the Default property of the Gallery to have nothing in it for the formula bar if there is something in there now, take it out.

 IsEmpty is needed to check if the Table is empty, or else it will not work correctly.

If it gives an error without initializing it to something OnVisible of the Screen, use this to initialize an empty Table:

Spoiler

Set(recsVar,[]);

I recommend not to do this above though - see if it just lets you get away without doing the above, because doing the above may case some other issues later on.

[poweractivate]

@Anonymous 

If the above doesn't work, try the following troubleshooting steps:

For all steps,

use the Items property of the Gallery!

Leave the Default property of the Gallery to have nothing in it for the formula bar if there is something in there now, take it out.

1. try this formula for the Items property of the Gallery:

//does this show everything right away?
If(true,'TDC Admin Portal V1',recsVar)

Does this show everything right away? Yes or No?

2.Now, try this formula for the Items property of the Gallery:

//does this filter everything right away?
If(false,'TDC Admin Portal V1',recsVar)

Does this filter everything right away? Yes or No?

If the answer to any of the above is No, then that part needs to be adjusted.

If the answer to both was Yes above (which I suspect), then read on:

3. If so, then the issue is to figure out what to put here for the Items property of the Gallery:

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

I think Something should be IsEmpty(recsVar) 

4. So for the Items property of the Gallery:

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

but if that doesn't work, and the above two formulas did work individually for the Items property of the Gallery to show all items and filtered items respectively, then it's still the wrong formula, and something else belongs there instead for the Items property of the Gallery.

See if it helps @Anonymous 

[Anonymous]

The answer for both formulas for true or false is still showing empty gallery. Even just using this for testing 'TDC Admin Portal V1' still showing empty.

[poweractivate]

@Anonymous 

For the Default property leave it blank, nothing in the formula bar.

Try putting 'TDC Admin Portal V1'  in the Items property of the Gallery.

Is it still showing absolutely nothing?

If so, 

1. Try these steps, as a one-time troubleshooting step:

a. click db looking icon on the left side

b. click ellipses to the right of TDC Admin Portal V1

c. click Refresh

2. Still not working? Try to close the app, sign out, close the browser, open it again, sign in, and open the app for editing again. 

Is it still empty?

3. Try making a new Gallery

Try putting 'TDC Admin Portal V1'  in the Items property of the Gallery.

Is it still showing absolutely nothing?

If it is still showing nothing, I'm unsure what's going on.

You already reported it was working before and accepted my response as solution, I am wondering what is not working now.

If even putting 'TDC Admin Portal V1' doesn't work, could you tell me, what does work (if anything at all - for troubleshooting, try to get it to work even if it means it shows them pre-filtered for example).

What if you put just recsVar in the Items property of the Gallery. Does this work? 

If not, does recsVar in the Items property of the Gallery work (i.e. show anything at all) if you select something in one of the dropdowns?

If that works, let me know, I might have an idea how to get that to work if recsVar in the Items property of the Gallery at least shows them pre-filtered.

4. Triple-check - is 'TDC Admin Portal V1' the correct name?

If so, is 'TDC Admin Portal V1'  the correct data source?

[Anonymous]

I will post the formulas here...

1. ddDept OnChange

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))));

items = colDept

2. ddRole Items = colRole

3. Gallery Items = 

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

Default is empty.

4. Screen OnVisible = 

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

5. 'TDC Admin Portal V1'  is the correct data source

If I create a new Flexible Height Gallery, items = 'TDC Admin Portal V1' works but once I start using ddDept dropdown, gallery will show correct courses but ddrole dropdown is useless meaning when I changed the role, nothing updates in the gallery.

6. "You already reported it was working before and accepted my response as solution, I am wondering what is not working now." - when I restore everything to the working version, it is still working but the gallery is not showing all courses onload which we are trying to figure now the issue...

[poweractivate]

On the new gallery where the troubleshooting worked, try this:

Gallery Items

recsVar

Screen OnVisible

Set(recsVar,'TDC Admin Portal V1')

Click another screen in the Editor and then click the above screen to fire OnVisible.

Does it work now?

Is the issue only present in Flexible Height Gallery? What about if it's a new Gallery otherwise, such as regular Vertical Gallery, is the issue still there?

[Anonymous]

Yes, it worked on another screen.