Filter gallery by multiple choice fields

[tjtaylor1164]

I have a SharePoint list with column names FollowupLead, FollowupLead2, FollowupLead3  - text fields

In the same SharePoint list, I have column names FollowupLeadCompleted, FollowupLead2Completed, FollowupLead3Completed - choice fields. The choice field columns all have the same information for users to choose from: "Not Started" "Completed" and "In-process"

I would like users to filter data based on "Not Completed" "Completed" or "In-process" that would pull information from the three choice field columns. 

The second scenario would be for users to search for their name from the three text fields so only their name appears and all results show from the choice fields, or they could filter based on "Not Started" "Completed" or "In-process."

What is the best way to handle this? I think I'm confusing myself.

Thank you!!

[WarrenBelz]

Hi @tjtaylor1164 ,

Thanks @mdevaney ,

I have read it three times and think I might have it. Going right back to the start - slight reorder and also summary of words
I have a SharePoint list with column names FollowupLead, FollowupLead2, FollowupLead3  - text fields

In the same SharePoint list, I have column choice fields names

FollowupLeadCompleted, FollowupLead2Completed, FollowupLead3Completed  
with options Not Started, Completed and In-process

Need - filter data based on Not Completed, Completed or In-process .

Second need users to search for their name from the three text fields and filter accordingly

So you have:-

• Six fields in the list - essentially three sets of matching pairs.
• The drop-downs show Status (for want of better word)
• The Text fields contain people's names, which may be the user

You want

• One filtered gallery based on the status,which will include any of the three choice fields
• A further filter if a name is typed in the text box to match any of the Follow-up lead fields.

You have also not given the control names (only the field names) - I will use dd1, dd2, dd3 for your drop-downs and Text1 for the text box (rename as appropriate). So - try this

Filter(
   YourListName,
   If(
      IsBlank(Text1),
      true,
      StartsWith(FollowUpLeadName,Text1.Text) &&
      (
         FollowupLead=dd1.Selected.Value ||
         Followup2Lead=dd2.Selected.Value ||
         Followup3Lead=dd3.Selected.Value
      )
   )
)

it should get you a list, but you will have to display the date plus all six fields. To be honest, your data is possibly managing you rather than the converse. Have you considered a follow-up sub-list with one of plus a date for each?

If you want to display if all in one lot of fields (I cannot possibly test this and have free-typed it) this might work

AddColumns(
   Filter(
      YourListName,
      If(
         IsBlank(Text1),
         true,
         StartsWith(FollowUpLeadName,Text1.Text) &&
         (
            FollowupLead=dd1.Selected.Value ||
            Followup2Lead=dd2.Selected.Value ||
            Followup3Lead=dd3.Selected.Value
         )
      ),
      "FUpName"
      FollowupLeadName & " " & Followup2LeadName & " " & Followup3LeadName,
      "FUpComplete",
      FollowupLeadCompleted & " " & Followup2LeadCompleted & " " & Followup3LeadCompleted,
   )
)

 would give you additional columns FUpName and FUpComplete with columns combined, however you would have to get rid of duplicates - so being more adventurous

Distinct(
   AddColumns(
      Filter(
         YourListName,
         If(
            IsBlank(Text1),
            true,
            StartsWith(FollowUpLeadName,Text1.Text) &&
            (
               FollowupLead=dd1.Selected.Value ||
               Followup2Lead=dd2.Selected.Value ||
               Followup3Lead=dd3.Selected.Value
            )
         ),
         "FUpName"
         FollowupLeadName & " " & Followup2LeadName & " " & Followup3LeadName,
         "FUpComplete",
         FollowupLeadCompleted & " " & Followup2LeadCompleted & " " & Followup3LeadCompleted,
      ),
   FUpName
)

The first one should work - the other two are bonuses if they do.

[tjtaylor1164]

Your diagram is exactly what I have in my database - a column for each.

[tjtaylor1164]

This is the first app I've ever built so I'm always looking for suggestions and ideas. This forum is THE BEST! I added a little more information to see if that helps explain my situation. 

Below is a screen capture showing part of the SharePoint list (Notes😞

The information is collected through a form (form_NewNote😞

Then I set up a gallery (gallery_FollowUp) for users to view follow-up information. I'm struggling with how to show this information. Someone asked to see all the follow-up Status that has not been completed by everyone. 

[WarrenBelz]

@tjtaylor1164 ,

Have you read my post and tried the first bit of code on a gallery? 

We can only provide guidance and need to know if the direction of what is provided suits your requirements.

Also as I have mentioned, your data structure does not lend itself to ease of reporting.

[tjtaylor1164]

Thanks!  The data has become quite cumbersome.  You mentioned sub-lists. Would putting the items "Not Completed" "Completed" and "In-process" in a different list help?

[WarrenBelz · Accepted solution]

@tjtaylor1164 ,

Not as much as putting the leads and completions as separate items (three fields each - status, date and number) in a sub-list rather than the six you have. All your reporting issues would go away. You would need another field (I call mine IDRef) defaulted to the ID of the parent list so these can be easily displayed in a gallery and updated as required. If you want to go down this track, it is a separate subject from your post, so you need to accept this one and post a new thread.

You can tag either myself or @mdevaney - we can both help you.