Forum Discussion

KimRavizza's avatar
KimRavizza
Qrew Cadet
2 years ago

Formula Query to find duplicates in a specific field

Hi All,

I've watched all the youtube videos and looked at all the posts, but I can't seem to get my head around this. 

I have a table with the following fields:

  1. store number
  2. User ID
  3. Selected as Admin (checkbox)

A user ID can be found in more than 1 store, but cannot be selected as Admin more than once. So basically I want to say that if the user ID is selected as Admin in another store already, it can't be selected in this store. 

I've embedded a user record report in the Store form to show all the users for that store. If a user in that store has already been selected as Admin in another store, I don't want them to show on this report.

I'm thinking that I need to create a 4th field "Already Selected as Admin", and somehow look for duplicates. If one of the records in the set of duplicates is selected as Admin, set "Already Selected as Admin" to true for all of them. Then I would just filter out records that way.

Hoping someone can help!

Thank you,

Kim



------------------------------
Kim
------------------------------
  • TammieKing's avatar
    TammieKing
    Qrew Cadet
    2 years ago

    If you have Admins in another table you could roll up the number of admins and then use that field to filter the report. If it is in the same table or there is no relationship, you can use a Formula Summary Query like:
    Size(GetFieldValues(GetRecords("{11.EX.'" &  [UserName] &"'}AND{20.EX.1}","br6abcde"),3))
    (Where 11 is the User and 12 is the admin checkbox field)

    Feel free to reach out privately if you want more specific help.



    ------------------------------
    Tammie King
    ------------------------------

    • KimRavizza's avatar
      KimRavizza
      Qrew Cadet
      2 years ago

      Thanks so much for getting back to me, Tammie.

      Yes it is in the same table, like this:

      Here's the formula I used:

      Size(GetFieldValues(GetRecords("{6.EX.'" &  [UserPrincipalName] &"'}AND{13.EX.1}","bs26ababa"),13))

      (Where 6 is the UserName field and 13 is the Admin selection checkbox.)

      In the table above, user TEST.TEST@TEST.COM is a user in both store 962 and 963. I need to count how many records with the UserPrincipalName TEST.TEST@TEST.COM have the CTID Admin checkbox checked. So essentially, both records in this grid edit should have a value of "1" in the # of CTID Admin Selected field, but only the checked record is getting it.

      Hope that makes sense.

      Kim



      ------------------------------
      Kim
      ------------------------------