Highlight Duplicates in Gallery Based on Multiple Criteria Within SharePoint List

[mkusler]

I have a gallery that is connected to a SharePoint list and serves as a work queue. What I am trying to do is give a visual indicator that a form that creates the record may have been potentially submitted twice on accident. It would do this by comparing a four specific data points out of many that are collected by the form driving the work queue (WO#, SO#, Cabinet ID, Part Number) and then highlight the rows and group them together if all four criteria matched.

For example....

Any help on how to do this would be greatly appreciated!

[TheRobRush · Accepted solution]

If this is a gallery, just put a rectangle behind your text and make it color you want then set its visibility to something like 

If(
   CountRows(
             Filter(              
                    DataSource,
                              And(
                                   Critera1=ThisItem.Criteria1, 
                                   Criteria2=ThisItem.Criteria2, 
                                   Criteria3=ThisItem.Criteria3, 
                                   Criteria4=ThisItem.Criteria4
                                  )
                    )
              )=0,
false,
true)
               

[mkusler]

@TheRobRush 

I put the following, but the icon is visible on every single record in the gallery, along with a delegation warning.

If(CountRows(Filter('Daily Picking Data - NEW DRAFT',

And(

'WO#' = ThisItem.'WO#',
'SO#' = ThisItem.'SO#',
'Cabinet ID' = ThisItem.'Cabinet ID',
'Part Number' = ThisItem.'Part Number'
)

)
)=0,
false,
true)

Any other thoughts about what I'm doing wrong?

[mkusler]

@TheRobRush 

Actually....I changed the code to say "=1" and everything seems to be working in my tests. I continue to evaluate.

[mkusler]

@TheRobRush 

If I wanted to add an additional piece of criteria that's a little different than the others, would I be able too? 

Here is the code I have so far that works:

If(CountRows(Filter('Daily Picking Data - NEW DRAFT',And(

'WO#' = ThisItem.'WO#',
'SO#' = ThisItem.'SO#',
'Cabinet ID' = ThisItem.'Cabinet ID',
'Part Number' = ThisItem.'Part Number'
)
)
)=1,false,true)

What I would like to do to further enhance this is look to see if the SharePoint List data field, "Cancellation" has been selected as "Cancelled". If the field is showing "Cancelled" on any of the records that match the other 4 criteria it wouldn't display the duplicate flag. 

For example... the icon would become visible in the first example on both records, but not in the second:

Because this additional criteria piece doesn't require counting any rows, I wasn't sure if they can be used together.

Thanks!

[TheRobRush · Accepted solution]

just wrap your working code in something like a switch

switch works by checking an item, then doing diff things based on its value. Switch(check,possibility1, do this, do this otherwise)

So you would have something like 

Switch(
        thisitem.whatevernameofcancelledfieldit,
        "Cancelled",
        false, 
        If(
           CountRows(
                     Filter(
                            'Daily Picking Data - NEW DRAFT',
                                 And(
                                     'WO#' = ThisItem.'WO#',
                                     'SO#' = ThisItem.'SO#',
                                     'Cabinet ID' = ThisItem.'Cabinet ID',
                                     'Part Number' = ThisItem.'Part Number'
                                    )
                            )
                    )=1,
         false,
         true
    )