Creating Shot sheets and other reports using Mail Merge

We've gotten some requests for ways to easily create reports or shot sheets.  A very easy way to do this is with Mail Merge in Microsoft Word.

You get the ease of exporting .csv files from Shotgun, and all the formatting tools of Word, updates are a breeze, and setup takes only a few minutes, and it's the same amount of time whether you have 10 items or 10,000!

Screen_Shot_2013-01-29_at_1.05.43_PM.png 

Watch the Embedded Movie or Follow a simple example below the movie.

(API script for copying thumbnails at bottom of this doc)

Here is a simple example:

Overview

The process involves exporting your data to a .csv file, opening Word, selecting the Mail Merge Manager, selecting the .csv file, and dragging your fields onto the document.  

From there, you can use Word's formatting tools and print or save to a .pdf file.

When you need to update it, you create a new .csv file, open the Word doc and point it to the new .csv file.  All the formatting is kept intact.

I'll create a very simple example. Then I'll add thumbnails using some arcane MS syntax.

The Simple Example

Display the columns that you'll need in your report.

Screen_Shot_2013-01-29_at_10.00.55_AM.png

Export the .csv file to Excel from the gear menu.

Open Word and go to Tools -> Mail Merge Manager

Screen_Shot_2013-01-29_at_10.01.48_AM.png

 From the Mail Merge Manager, select Create New -> Form Letters.  Then, Get List -> Open Data Source and select your .csv file.

Screen_Shot_2013-01-29_at_10.02.24_AM.png          Screen_Shot_2013-01-29_at_10.02.38_AM.png

A file conversion dialog will appear.  Hit OK.

Screen_Shot_2013-01-29_at_10.02.57_AM.png

This will provide all of your fields as 'Contacts':

Screen_Shot_2013-01-29_at_10.03.17_AM.png

Click and Drag your fields onto the Word doc.  They will appear with double chevrons around them:

Screen_Shot_2013-01-29_at_10.03.57_AM.png

You can select the Preview button to see what it will look like.

Screen_Shot_2013-01-29_at_10.04.15_AM.png

Remember, since you're in Word, you can format all of this.  You wil generally apply your formatting when NOT in preview mode.

I'm going to use a table to hold Labels and Fields:

Screen_Shot_2013-01-29_at_10.05.08_AM.png

I'll type text into the top cells and drag the corresponding fields into the cells below:

Screen_Shot_2013-01-29_at_10.06.16_AM.png

Preview my work:

Screen_Shot_2013-01-29_at_10.06.33_AM.png

Add another table for Assets and Description:

Screen_Shot_2013-01-29_at_10.09.04_AM.png

Now, the tricky part: Adding Thumbnails

You'll need to have local copies of the thumbnails that you want to use and an easy way to programmatically specify them in excel.  I use an API script that downloads my thumbnails into a local directory and can either name them after the shotcode or the ID.  If your shotcodes are unique, that's a good way to go, otherwise, use the ID.

Open your .csv file in Excel and add a column for the local thumbnails.  You can use the CONCATENATE function to build the path to your thumbnails.

In the first cell of the local thumbnails column, enter:

=CONCATENATE("/path/to/your/images/", B2, ".jpg")

Here, B2 is the cell with my shot code in it.  This function adds the strings together giving you /path/to/your/images/shot_0010.jpg (or whatever your shotcode is).

Screen_Shot_2013-01-29_at_10.16.46_AM.png

Drag the formula down (or cp/paste) for all the cells.

Screen_Shot_2013-01-29_at_10.17.05_AM.png

OK, now I'm going to add the local thumbnails to the mix.  I create a new 2x1 table for the thumbnail and the description (I'll eventually delete the description at the bottom):

Screen_Shot_2013-01-29_at_10.18.51_AM.png

If you hit Preview, you'll only see the path, not the thumbnail:

Screen_Shot_2013-01-29_at_10.19.10_AM.png

This is where the fun begins.  Exit preview mode and highlight your LocalThumbnails field:

Screen_Shot_2013-01-29_at_10.19.24_AM.png 

With the field highlighted, on the Mac, hit command+F9 (Windows, cntrl+F9).  If you are on a Mac laptop, you'll need to hold down command+fn+F9, other wise the F9 key will fast forward your iTunes :-) )

Doing this will add a set of curley braces around the field.  You must do this to get the curly barces, simply typing {} will not work. add the word:

MERGEFIELD

inside the curly braces, highlight the whole thing and repeat command+F9 to add another set of curly braces around what you had so that it looks like this:

Screen_Shot_2013-01-29_at_10.21.19_AM.png

Inside of this new set of curly braces, type:

IF TRUE and you should have something like this:

Screen_Shot_2013-01-29_at_10.21.39_AM.png

Again, you will highlight the whole thing, and press command+F9 again.  Inside of this third set of curly braces you do 2 things.

At the beginning, type:

INCLUDEPICTURE

at the end, type:

\d

so that it looks like this:

Screen_Shot_2013-01-29_at_10.22.16_AM.png

Almost there!  When you hit preview, it will tell you that you have an invalid merge field and ask you to specify a valid mergefield.  You will select your LocalThumbnail field from the drop down at the bottom:

Screen_Shot_2013-01-29_at_10.22.41_AM.png

Hit OK.  Presto:

Screen_Shot_2013-01-29_at_10.22.57_AM.png

Click through the arrows in the preview section of the Mail Merge Manager to see your other records.

Finally, complete the merge

At this point, I save this file and use it as a template for future reports.  Then, when I update my csv file, I just go to step 2 of the Mail Merge Manager and select the new .csv file and it hooks up the new data.

Once you've saved the template, go to the bottom of the Mail Merge Manager (6).  Here you can either print or output to a new document.

I generally create a new document so that I can add any formatting or header/footer info that I want.  When you merge to a new document, watch the status at the bottom of the document, it will show you what record it is on.  This is fairly fast (about 10 or 15 seconds for 700 pages), but wait for it to complete before you start working with the document.  From there you can print and/or save as pdf.

Advanced tip:

I use an API script to export thumbnails to a local directory and a field in Shotgun to hold the local path.  When I run it, it downloads all of the latest thumbnails, and uploads their local paths to Shotgun.  Then, I export the .csv and there is a field with my local thumbnails already.  This is a big help because you don't have to open the csv file and create the LocalThumbnail field every time.  

#!/usr/bin/env python
import sys,os
import urllib
import shotgun_api3 as shotgun


#################################################################################
# Fill this stuff in:
#################################################################################
URL = 'https://your_URL_here.shotgunstudio.com'
SCRIPT = 'your_scriptname'
SCRIPT_KEY = '27your3459script9832key9826here097'

###
# Specify the project you want using the id
project_id = 68

###
# Generally "Shot" or "Asset" depending on what thumbnails you want
entity_type = "Shot"

###
# optional field on Shots in SG to store the local file paths
# this is a text field on your entity in Shotgun where the script will upload the local file path.
# if you set it to empty quotes  (local_thumbnails = "") it won't try and update it
local_thumbnails = "sg_local_thumbnails"

###
# if use_code = 1 (True) it will name the local thumbnails the shot code name (shot name), 
# Good if all your shot names are unique.  Otherwise, set to 0 and it will use the shot id to name them.
use_code = 1

###
# your local directory to write the thumbnails
# By default, I put them in /tmp/thumbs/{project_id}/{entity_type}
# i.e. /tmp/thumbs/68/shots/
# feel free to change that to suit your needs
local_dir = '/tmp/thumbs/'+str(project_id)+'/'+entity_type+'/'

#################################################################################



#########
# if the dir for the thumbnails doesn't exist, make it
###
def makeSureLocalDirectoryExists(l_d):
	if not os.path.isdir(l_d):
		try:
			os.makedirs(l_d)
			print "Made: "+l_d
		except:
			print "Could not make directory: "+l_d
			sys.exit(-1)



#########
# copies the thumbnails to a local directory
# udates the {local_thumbnails} field in SG with the path to the local files
###
def doLocalThumbs(sg, p_id, l_d, u_c, e_t):
	
	batch_data = []

        filters=[['project', 'is', {"type":"Project","id": p_id}]]
        fields = ['image', local_thumbnails, 'code']
        result = sg.find(e_t, filters, fields)

	for r in result:
		if (u_c):
			tmpfile = l_d+str(r['code'])+'.jpg'
		else:
			tmpfile = l_d+str(r['id'])+'.jpg'
		thumbnail = r['image']

		if thumbnail:
			print "copying: "+thumbnail +" to "+ tmpfile
			urllib.urlretrieve(thumbnail, tmpfile)
			data = {local_thumbnails:tmpfile}
			batch_data.append( {"request_type":"update","entity_type":e_t,"entity_id":r['id'], "data":data} )
		else:
			print "No thumbnail available:",r

	if local_thumbnails != "":
		print "Updating: "+local_thumbnails
		try:
			sg.batch(batch_data)
		except:
			print "\nERROR: \tSomething didn't work updating the paths in Shotgun field: "+local_thumbnails+"  \nCheck that your field exists in Shotgun and that you are specifying the proper field name.  \nNote, custom fileds created in Shotgun will begin with sg_ and will be all lowercase, i.e. sg_localthumbnails\n"


########
if __name__ == '__main__':

	makeSureLocalDirectoryExists(local_dir)

	# Shotgun!
        sg = shotgun.Shotgun(URL, SCRIPT, SCRIPT_KEY)
	doLocalThumbs(sg, project_id, local_dir, use_code, entity_type)

If you'd like any help, please email support@shotgunsoftware.com and let us know.

Follow

2 Comments

  • 0
    Avatar
    Hana

    Hello! Thank you for the article, it's very useful and easy to follow!

    I have a question, though. I'm using MS Office 2016 and it looks like the Mail Merge feature is slightly different. I've managed to get all the way through the wizard and create all the shot sheets I need but the images aren't properly displayed. I get the message 'image moved, renamed or deleted' although testing the path it's definitely the correct one. Could it be the {INCLUDEPICTURE} doesn't work for this version of Office? Do you have any ideas or solutions, what I could do?

    Thank you very much for you time!

    All the best, 

    Hana

  • 0
    Avatar
    Matt Welker

    Hi Hana,

    Sorry, I'm not really sure. I did find this online:

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_word-mso_mac/relative-path-in-includepicture-field-in-word-2016/72514388-8579-4090-89b4-b21b0c416910

    It seems like the relevant info is:

    Do note that the braces for the FILENAME field (i.e. {}) can't be copied & pasted or typed - you need to create them (as a pair) via Cmd-F9.

    I hope that helps.

    Matt

Please sign in to leave a comment.