cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CountJuju
Frequent Visitor

Nested Sharepoint List

Hi

I have 2 sharepoint list

 

  • BillTo contains a list of project codes to bill to - Title = cost centre code, Active = Yes/No
  • BillToAssign contains an employee reference -

    Title = name of person cost centre assigned to

    BillTo = lookup record of Cost Centre

I have the following code to show in a drop down list all those records that are not assigned to the current user

 

Filter(
    Choices([@BillToAssign].BillTo),
    Not(
        Value in Concat(
            Filter(
                BillToAssign,
                Title = currentUser.FullName
            ).BillTo,
            BillTo.Value
        )
    )
)

 

 However I only want to display those values that are active = "Yes"

 

Any assistance would be greatly apreciated

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @CountJuju ,

 

Could you tell me:

Is 'Active' a 'Yes/No' Column?

 

If my assumption is correct, please try this formula on Items property of dropdown control:

Filter(
ForAll(
Filter(
    Choices([@BillToAssign].BillTo),
    Not(
        Value in Concat(
            Filter(
                BillToAssign,
                Title = currentUser.FullName
            ).BillTo,
            BillTo.Value
        )
    )
) As A, If(LookUp([@BillTo],ID=A.Id).Active=true,A)),!IsBlank(Id))

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

 

View solution in original post

2 REPLIES 2
v-xiaochen-msft
Community Support
Community Support

Hi @CountJuju ,

 

Could you tell me:

Is 'Active' a 'Yes/No' Column?

 

If my assumption is correct, please try this formula on Items property of dropdown control:

Filter(
ForAll(
Filter(
    Choices([@BillToAssign].BillTo),
    Not(
        Value in Concat(
            Filter(
                BillToAssign,
                Title = currentUser.FullName
            ).BillTo,
            BillTo.Value
        )
    )
) As A, If(LookUp([@BillTo],ID=A.Id).Active=true,A)),!IsBlank(Id))

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

 

View solution in original post

CountJuju
Frequent Visitor

@v-xiaochen-msft Thank you - works a treat

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,952)