Skip to main content
Topics#Data ManagementPregunta de Nathaniel Martin
Nathaniel Martin (Self) preguntó en #Data Management
16 sept 2022, 19:32

Users between instances

Hi all - have a question about being able to pull user data with SOQL and salesforce inspector on four different instances and being able to find out which users are missing from each instance. I have the SOQL syntax and the data is already pulled into a CSV but now that its in excel I'm not sure how to easily chew through the data sets and find which users are missing from each instance. I'm sure its an easy enough solution, just not sure how to ask google properly. Thanks!

4 respuestas
  1. Andy Heidrich (Cisco)
    16 sept 2022, 20:53

    If I was doing this and just wanted the info quickly:

     

    1. Put each of the 4 instances' query results in different sheets in the workbook
    2. Create a fifth sheet
    3. Copy each of the 4 instances' query results and paste them all into the 5th tab (pasting the header row only once).  So now you have a 5th tab that has every user from every org, including any duplicates (people who are present in more than one org)
    4. Assuming email is your common identifier to match users - in the 5th tab, highlight the Email column.  Go to the Data tab in Excel.  Click Advanced in the Sort & Filter section on the toolbar.  Check Unique Records Only.  Click OK.  This will collapse/hide any duplicate rows (so you have a single filtered list of everybody who's in any of the orgs.
    5. Create a 6th sheet.  Copy the contents of the 5th sheet and paste it into the 6th sheet (so now the 6th sheet just has the unique rows, 1 per person, with no hidden rows).  Go ahead and sort this sheet now.
    6. Delete the 5th sheet (we'll now refer to the 6th sheet as the combined sheet).
    7. Add 4 columns in the combined sheet.  Name each column for one of the 4 orgs.
    8. Do a VLOOKUP in the 6th sheet using ISNA for each of the 4 columns (where the vlookup for each column is looking up to the sheet for that specific org).  In this example, b2 is the field in the combined sheet with the user's email, and Org1 is the name of the sheet for Org 1's user data.  The email address lives in column B in the org 1 sheet:
    IF(ISNA(VLOOKUP(b2,Org1!B:C,2,FALSE)),"","In org")

    What this will do is if the email you're looking up is NOT present in the target sheet, it'll return null, and it'll return "In org" if they're present in that sheet.

     

    The end result is you'll have a matrix of each person with 4 columns, 1 per org, and text indicating when that person is present in each of the orgs.

Escribir una respuesta...
0/9000