7 Replies Latest reply on Nov 14, 2017 12:35 AM by gsvivek

    Pulling list of people by profile field

    christine.murray

      Hi all,

       

      I wanted to see if anyone knows how to pull a list from their community based on a profile field? I'm trying to see how many people I have from a certain location in our community from the "work location" field we created, but I don't seem to be doing something right. Can someone help?

       

      Thanks,

      Chrissy

        • Re: Pulling list of people by profile field
          Helen Chen

          Have you looked at the Community Management Reports?  Check out the Profile Completion and download the CSV file.  Use Excel to compile whatever profile data you want. 

            • Re: Pulling list of people by profile field
              christine.murray

              Thanks for the suggestion Helen. When I do this, the "work location" field comes up as a bunch of numbers, not actually a location as it's seen in the community. Do you think this is a bug?

                • Re: Pulling list of people by profile field
                  Helen Chen

                  I would go back to your user profile and confirm what you have for profile fields and how content is being populated.  My community has a profile field called Location and we pull this over from WorkDay as our system of record.

                   

                  Once you understand that, then you can go back and look at the data being displayed in the CMR.  It sounds like that field isn't what you want it to be. 

                  • Re: Pulling list of people by profile field
                    BRENDA PROFFITT-WOLFE

                    HI Christine,

                    This sounds like the ongoing bug JIVE-73720 where select profile fields are displaying numerical values.  We have been experiencing this issue for over a year and are still waiting for it to be resolved.  I run a Profile Completion report every month and spend quite a bit of time manually cleaning up the fields I need as the report displays numbers instead of the actual data.

                     

                    Brenda

                    1 person found this helpful
                    • Re: Pulling list of people by profile field
                      b.taub

                      Christine,

                      As BRENDA points out the display of the data is the product of the bug she cites, but the underlying data is likely correct - but the field you are looking in the Jive Custom Profile fields is probably a "Single-select" field - meaning that a user can only select one value, or it is a field that is pulled in from an HR system such as WorkDay that Helen mentioned.  That means that you can set up a translation table to do the translation for you.  Something like:

                       

                      Value In Excel

                      Meaning in Jive

                      6001

                      Boston Office

                      6002

                      San Francisco Office

                      6003

                      Dallas Office

                       

                      So, build that table once and copy that it into the sheet you are working in.  Then add a column to your main sheet called "Location Value" and the formula for it would be a VLOOKUP to the the translation table.  That will fill in the real values for you in your main sheet automatically.  Look at the Excel help for VLOOKUP - a very handy function for Excel, that gives it more database-like functionality.

                       

                      Ben

                      1 person found this helpful
                  • Re: Pulling list of people by profile field
                    gsvivek

                    If you have access to the Jive DB, this is pretty straightforward. All you need to do is run the following queries:

                     

                    -- This is to get the list of profile fields and their ids so that you can use the id in the next query. If you already know the id, you can ignore this.

                    select * from jiveprofilefield;

                     

                    -- Basic user data is in JiveUser table and user profile data is in jiveUserProfile table. Just update the fieldId from above query

                    select u.firstName, u.lastName, u.email, up.value

                    from jiveuser u, jiveuserprofile up

                    where u.userid=up.userid

                    and up.fieldID=1;

                     

                    If the profile field is a drop down or a user selection, you will need below query:

                    -- Basic user data is in JiveUse tabler, user profile data is in jiveUserProfile table

                    -- In case of dropdown profile, the values are present in jiveProfileFldOpt table

                    -- Just update the fieldId from above query

                    select u.firstName, u.lastName, u.email, pfo.fieldValue

                    from jiveuser u, jiveuserprofile up, jiveprofilefldOpt pfo

                    where u.userid = up.userid

                    and pfo.fieldId = up.fieldId

                    and up.value = pfo.optionID

                    and up.fieldID = 1;