Filter dropdown based on sharepoint list and collection comparison

[nathanRBG]

Hi,

I currently have a dropdown field which pulls through values from a column in sharepoint table where the value (name) doesn't match a value (name) in a column in a collection within my app. This is working perfectly fine, however... 

In the collection named RBGstaff there are 2 columns - the name and the role (the role is either loader or driver). I need to use the filter that I already have, plus I need to add to the filter to only display those names where the role is "driver" only. I'm struggling to add the role part to the filter because the current formula pulls through values that ARE NOT in the RBGstaff collection, this is what I have so far:

[TylerPA]

Hi nathanRBG,

Sorry, just having trouble visualizing the situation:

• Can you post your dropdown's code? And if possible, the columns from sharepoint?
• Your collection - do you want it to show:
  • Names that don't match (like your dropdown), or people that are drivers
  • Or: names that don't match and are drivers

[nathanRBG]

Hi TylerPA, thanks for your response. So the filters and collection have been tweaked a little since yesterday but the concept is still the same.

Your first point, the code and screenshots are below:

The items property of the dropdown (this is what I need to amend to display the names that do not match the names in the collection and have a role type of "driver)- 

Filter(
Choices([@'UAT - Allocations'].'allocatedDriver: staffName'),
Not(Value in RBGstaff.Name)
)

The column in SP (screenshot only shows the first 5 records) - 

The RBGstaff collection code - 

Clear(RBGstaff);
ForAll(
allocationsGallery.AllItems,
Collect(
RBGstaff,
{
Name: 'allocatedDriver: staffName'.Value,
Role: 'allocatedDriver: txtRole'.Value,
NameRole: allocatedDriver.Value
}

)
);
ForAll(
allocationsGallery.AllItems,
Collect(
RBGstaff,
{
Name: 'allocatedLoader1: staffName'.Value,
Role: 'allocatedLoader1: txtRole'.Value,
NameRole: allocatedLoader1.Value
}
)
);
ForAll(
allocationsGallery.AllItems,
Collect(
RBGstaff,
{
Name: 'allocatedLoader2: staffName'.Value,
Role: 'allocatedLoader2: txtRole'.Value,
NameRole: allocatedLoader2.Value
}
)
);

RBGstaff collection table view (first record) - 

And then to answer your second question, I am trying to filter the names that don't match the collection, AND that are drivers.

Thank you in advance!

[TylerPA]

Thanks, I'm on the same page now - from your sharepoint list with the choice column for people to use, do you have somewhere that says their associated roles already?

[nathanRBG]

Yes that's correct, there's a column which defines their role (either driver or loader).

[TylerPA · Accepted solution]

Ok thanks - so if the choice list for people and roles are both from 'UAT - Allocations' (from below, I was using 'testList' - you'll need to also replace your driver/loader column): 

Wasn't sure if you've got loader/driver setup here as a choice field or not - so here are both configurations. Setup if loader/driver field is a text field:

setup if loader/driver is a choice field:

The result is a dropdown, with people that are drivers, and are not already in RBG staff

[nathanRBG]

Thank you very much! This seems to have done exactly what I need. Much appreciated 

[nathanRBG]

Hi @TylerPA , 

Just off the back of this, I'm trying to order a separate dropdown list with the orderByColumn() function. I'd like to order the first column (name column) ascending, and then secondly order the next column by role. Having difficulty trying to get this to work. My current formula is this:

Filter(
Choices([@'UAT - Allocations'].allocatedLoader1),
Not(Value in RBGstaff.NameRole)
)

Side note: 
- "allocatedLoader1" is a field in SP that joins both the role and the name.

- "RGBstaff.NameRole" is a collection.column which sits within the app but pulls through the same role + name values.

So ideally I'd like the first name column ("allocatedLoader1: staffName") to be asc, and the second column ("allocatedLoader1: roleString") to be desc.

Any help is appreciated, thanks!

[TylerPA]

No problem - here's an example. This was setup with a name text field, job choice field (adjust accordingly):

ClearCollect(
    testDropdown,
    SortByColumns(
        AddColumns(
            testList,
            "jobFix",
            testJob.Value,
            "combined",
            testName & " " & testJob.Value
        ),
        
        "jobFix",
        Descending,
        "testName",
        Ascending
    )
);

Dropdown items:

[nathanRBG]

Hi TylerPA,

Sorry forgot to reply to this. We tried this solution but it didn't work in our scenario. It's not an issue for now but I may come back to revisit this in the future. Thanks for your help.