Answered

Querying a table in If or Switch Function

(0) Share

Report

Posted on by SS-18112330-0

I have a canvas app that is connected to a SP List. The list has a column "ProjectType" that is a multiselect choice field. I am trying to do some If/Switch statements based on the Project Type (Which is displayed in DataCardValue46), but those statements are only querying the last selection of the choice field rather than all selections in the choice field. How do I need to change my formula to query through all of the selections? See the attached screenshots.

Here is a test formula:

If(

    "Retracement" in DataCardValue46.Selected.Value,

    [

        "Project Details",

        "Surveyor",

        "Missoula Public Health",

        "Planning, Development, & Sustainability",

        "Public Works",

        "County Attorney",

        "Clerk & Recorder"

    ],

    ["test"]

)

Categories:

Building Power Apps

All responses (2)

Answers (1)

Sort by

Verified answer

Mark Nanneman 993 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

There are (at least) two or three ways you could write your formula to get this to work. You do have to make sure you're using the "selectedItems" property of your multiselect control.

A. Concat Method
(Basically, we're converting your selectedItems table into a comma seperated string.

If(

"Agg" in Concat(ComboboxCanvas1.SelectedItems,Value&", "),

["Project Details",

"Surveyor",

"Missoula Public Health",

"Planning, Development, & Sustainability",

"Public Works",

"County Attorney",

"Clerk & Recorder"],

["Test"]

)

B. LookUp Method
SelectedItems is a table with one column, Value. You can do a LookUp on it (or a search or a filter or a CountIf). Check if the result is blank or not.

If(

!IsBlank(LookUp(ComboboxCanvas1.SelectedItems,"Agg" in Value).Value),

["Project Details",

"Surveyor",

"Missoula Public Health",

"Planning, Development, & Sustainability",

"Public Works",

"County Attorney",

"Clerk & Recorder"],

["Test"]

)

C. CountIf / Search / Filter Methods
CountIf(ComboboxCanvas1.SelectedItems,"Agg" in Value) > 0

CountA(Search(ComboboxCanvas1.SelectedItems,"Agg",Value))> 0

CountA(Filter(ComboboxCanvas1.SelectedItems,"Agg" in Value))> 0

If this helped you, please click "Does this answer your question" and give it a like to help others in the community (+ close the ticket)!

Power Platform Developer | LinkedIn: Mark Nanneman | Blog: Power Stuff | YouTube: Mark's Power Stuff |
SS-18112330-0 4 on at

Like
a
(1)

Report
Copy link

Link copied!

Option A worked perfectly. Thank you so much!