×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Sharepoint 2013 PS to populate fields in list based on other items in list

Sharepoint 2013 PS to populate fields in list based on other items in list

Sharepoint 2013 PS to populate fields in list based on other items in list

(OP)
Does anyone know if I can run a PowerShell script to (maybe) create a 'temporary' SharePoint list, then use that list to create a new list? I've got script working to complete erase and regenerate a user list pulling from Active Directory. That part is fine. But I want to be able to populate the user's Manager Phone and Email addresses, as well. So, the script would need to look at the list, find user1's manager name, search the list for the manager's information, grab the phone and email, then finish populating user1 item. I may be overthinking it. If anyone knows, please let me know. Thanks!

Example

CODE --> html

  1. user1Name
  2. user1Phone
  3. user1Email
  4. user1ManagerName
  5. user1ManagerPhone - (pulling from Manager's info)
  6. user1ManagerEmail - (pulling from Manager's info)

Thank you, in advance!

~ lahddah

RE: Sharepoint 2013 PS to populate fields in list based on other items in list

You could just get the manager information from AD when you're getting the user information.

What is the code you are using to get the user information?


Light travels faster than sound. That's why some people appear bright until you hear them speak.

RE: Sharepoint 2013 PS to populate fields in list based on other items in list

(OP)
Thank you, blister911. I'm actually running an sql via crystal report & outputting to csv so that I can then import the csv into the SharePoint list on a nightly basis. The sql I'm running for the report is:

CODE --> sql

SELECT sAMAccountName, Title, Department, givenName, sn, Manager, distinguishedName
                       FROM 
                         'LDAP://DC=(###),DC=(company),DC=COM'
                       WHERE 
                         objectCategory ='Person'
                                    AND
                                      objectClass = 'User' 

AD only has the manager name (as distinguishedName), but not phone number and email on each user entry. So I need to populate those fields in the SharePoint list, somehow. I don't know if it would be through the sql command in the report/csv or in the powershell command to import the csv.

~ lahddah

RE: Sharepoint 2013 PS to populate fields in list based on other items in list

Your code doesn't match your output, with regards to which fields you need, so I went with what you provided as output.

I would do it like this:

CODE --> PowerShell

## Set export location
$export_location = "C:\Temp\SharePoint_Users.csv"

## Get all users and necessary attributes
$users = Get-ADUser -Filter * -Properties OfficePhone, mail, Manager

## Initialize export array
$export = @()

foreach ($user in $users)
	{
	 ## write current user to the console to monitor progress (this can be remove if the script is automated
	 $user.name
	 
	 ## Create custom object for export
	 $output = New-Object PSObject
	 $output | add-member -memberType noteproperty -Name UserName -value $user.Name
	 $output | add-member -memberType noteproperty -Name UserPhone -value $user.OfficePhone
	 $output | add-member -memberType noteproperty -Name UserEmail -value $user.mail
	 
	 ## Find manager information if the user has a manager set
	 if ($user.manager)
		{
		 $manager = Get-ADUser -Filter * -Properties mail, OfficePhone | where {$_.distinguishedname -eq $user.manager}
		 $output | add-member -memberType noteproperty -Name MgrName -value $manager.Name
		 $output | add-member -memberType noteproperty -Name MgrPhone -value $manager.OfficePhone
		 $output | add-member -memberType noteproperty -Name MgrMail -value $manager.mail
		}
	 else
		{
		 $output | add-member -memberType noteproperty -Name MgrName -value ""
		 $output | add-member -memberType noteproperty -Name MgrPhone -value ""
		 $output | add-member -memberType noteproperty -Name MgrMail -value ""
		}
		
	 ## Copy object to export array
	 $export += $output
	}

## Export array to file
$export | Export-Csv $export_location -NoTypeInformation 


Light travels faster than sound. That's why some people appear bright until you hear them speak.

RE: Sharepoint 2013 PS to populate fields in list based on other items in list

(OP)
Thank you, blister911!

This was a great help! We do not have the Active Directory module installed on our SharePoint server, so I had to tweak your example a bit. This was why I needed the CSV import workaround. I pulled the AD information in using Crystal Reports & exporting to CSV. Then, using powershell, I import the CSV, load the manager information and then add the field values to the SharePoint list I'm using for many things I want to customize in SharePoint.

Here is your example how I used it & it seems to be working nicely. Thank you, again!

CODE --> PowerShell

## Set location of raw file to be used

$FilePath = "http://[site]/SharedDocuments/SharePointUsersRaw.csv"

## Set the export location

$export_location = "C:\temp\SharePointUsers.csv"

$wc.DownloadFile($FilePath, $export_location)

## Get all users and necessary attributes

$users = Import-Csv $export_location -header("sAMAccountName", "DisplayName", "mobile", "telephoneNumber", "title", "Department", "PhysicalDeliveryOfficeName", "mail", "SN", "whenCreated", "Manager", "ManagerPhone", "ManagerEmail")

## Initialize export array
$export = @()

foreach($line in $users)
 {  
	 ## write current user to the console to monitor progress (this can be remove if the script is automated
	 
	$line.sAMAccountName
	 
	 ## Create custom object for export

	 $output = New-Object PSObject
	 $output | add-member -memberType noteproperty -Name sAMAccountName -value $line."sAMAccountName"   
	 $output | add-member -memberType noteproperty -Name DisplayName -value $line."DisplayName"
	 $output | add-member -memberType noteproperty -Name mobile -value $line."mobile"
	 $output | add-member -memberType noteproperty -Name telephoneNumber -value $line."telephoneNumber"   
	 $output | add-member -memberType noteproperty -Name title -value $line."title"
	 $output | add-member -memberType noteproperty -Name Department -value $line."Department"
	 $output | add-member -memberType noteproperty -Name PhysicalDeliveryOfficeName -value $line."PhysicalDeliveryOfficeName"   
	 $output | add-member -memberType noteproperty -Name mail -value $line."mail"
	 $output | add-member -memberType noteproperty -Name SN -value $line."SN"  


	 ## Find manager information if the user has a manager set

	 if ($line.manager)
		{
		 $manager = $users | where {$_."DisplayName" -eq $line."Manager"}
         $output | add-member -memberType noteproperty -Name MgrName -value $manager."DisplayName"
		 $output | add-member -memberType noteproperty -Name MgrPhone -value $manager."telephoneNumber"
		 $output | add-member -memberType noteproperty -Name MgrMail -value $manager."mail"
		}
	 else
		{
		 $output | add-member -memberType noteproperty -Name MgrName -value ""
		 $output | add-member -memberType noteproperty -Name MgrPhone -value ""
		 $output | add-member -memberType noteproperty -Name MgrMail -value ""
		}
		
	 ## Copy object to export array
	 $export += $output
	}

## Export array to file
$export | Export-Csv $export_location -NoTypeInformation 

~ lahddah

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close