Creating labels

From AUKC Helpsite
Jump to navigation Jump to search

This page shows you how to create labels using information from Charitylog, by exporting it to Microsoft Excel and then using Microsoft Word for the label creation based on the information in the Excel spreadsheet that you've created.

As an example, we are going to create mailing labels for people who receive the Activities programme, but hopefully you'll see that the process can be adapted for plenty of other scenarios too.

The stages of the process are:

  1. Use Charitylog to produce a report which contains all of the people you want to send a mailing to
  2. Add the address data for each person to the report and export it to Excel
  3. Use Word to pull the data from the Excel sheet to form labels


Producing the report[edit | edit source]

The first stage is to use Charitylog to find all the people you want to send a mailing to. In this case, we want to send a mailing to everyone who has a "yes" entered in the field on the Details page which is titled "Do they receive the Activities programme?" - i.e. we want everyone whose record displays like this:

Screen Shot 2018-12-18 at 12.51.43.png


To extract the information, you'll need to use one of Charitylog's reporting features. Because this information is actually contained in an Extension Database (which is the Charitylog term for "a custom set of fields"), you can do this with an Extension Database Report.

From the "Reports" tab, click on "Extension Database Reports".

Screen Shot 2018-12-18 at 12.55.27.png


Then, from the list, select the Extension Database you want to report on - in this case it's the one called "Extra Fields - Details Tab".

Screen Shot 2018-12-18 at 12.56.45.png


You now have selection criteria. Choose options, and the system will return all of the people (or referrals) that match those options. So we'll set the "Do they receive the Activities programme?" option to "Yes, by post" - which matches all of the people that we want to extract.

Screen Shot 2018-12-18 at 12.58.51.png


Then click the "Display In Browser" button.

Screen Shot 2018-12-18 at 13.00.53.png


A list of people will be displayed - but without their address data - don't worry about this. Scroll to the bottom of the page and click on the blue folder, marked with an arrow in the picture below, that you'll find there. You will also see how many people are on the list in total. This is a good time to think about that; does the number sound right? If not, work out why before proceeding.

Screen Shot 2018-12-18 at 14.26.32.png


Cross referencing data and exporting it[edit | edit source]

You're now on the "blue folder lookup" page - or you might hear it referred to as "Run Annual Grid" by the guys and girls on the Charitylog helpdesk, because that's in the page address. You'll see a big table of data at the bottom of the page, which I've redacted in the picture below, as we don't want any client data to be visible in this manual; and also a "block" section above that, which I've marked out in a blue box. This contains the export options.

Screen Shot 2018-12-18 at 13.07.33.png


What we need is a way to "tack on" the address data for every person that's named in the data table below. This is what the Field Sets are for. I've created various Field Sets to get you started, and it's easy for you to create more - but for now, all you need to know is that if you choose the "Name and address fields (version 1)" field set, you'll get the following information added to every person in the data table;

  • Surname
  • First name(s)
  • Title
  • Address lines 1, 2, 3, 4 and 5

So set the middle section of the page as follows:

Selecting RAG.gif


This will produce an Excel spreadsheet which you can download to your computer. Take note of where you're saving it!


Creating the labels in Word[edit | edit source]

You don't actually need to open the spreadsheet in Excel, although if creating the labels doesn't work properly, you might need to, in order to track down problems. But hopefully everything will be fine!

Disclaimer: the following was prepared in Microsoft Word for Mac version 16.19. Other versions may look a bit different, but hopefully the process will be similar.

Open Microsoft Word, create a blank document, then go to the "Mailings" tab.

Click the "Start Mail Merge" drop-down, and select "Labels" (blue arrow in below picture). Do not select "Labels" on the top ribbon itself (red arrow). This won't work later on; goodness knows why Microsoft put these two similar options so close to one another...

Screen Shot 2018-12-18 at 13.29.08.png


You'll now be prompted to choose which labels you want to use - the standard ones which are seven rows of three are equivalent to Avery L7160. Select that and click OK.

Screen Shot 2018-12-18 at 13.29.25.png


You now have a page of blank labels. The next step is to put some information in them. You could do this manually - but of course we want to use the Excel spreadsheet to populate them.

Use the "Select Recipients" drop-down on the ribbon to choose "Use Existing List".

Screen Shot 2018-12-18 at 13.39.24.png


From the dialog box that appears, navigate to find the Excel sheet you downloaded. Here, it's in my Downloads folder - your spreadsheet may well be in a different place, depending on how you have your computer/web browser set up.

Screen Shot 2018-12-18 at 13.44.29.png


You'll then need to select the bits of the Excel file that you want to open. Hopefully it should only have one Worksheet, and "Cell Range" can be left blank (which will import the entire Worksheet) - so basically, just click OK;

Screen Shot 2018-12-18 at 13.46.24.png


Your sheet of labels will change so it says "next record" in all labels apart from the first one.

Screen Shot 2018-12-18 at 13.48.20.png


Now you need to populate the first cell with the data you want to bring in. Click the "Insert Merge Field" dropdown, and you'll see a whole load of headings, which are the column names in the original spreadsheet.

Screen Shot 2018-12-18 at 13.50.16.png


You can pick them one at a time, putting in carriage returns and spaces as you go. So putting together a typical address label might look like this...

Selecting merge fields.gif


Note the spaces between the title and the names, the indents on the address lines, and the fact that you can make merge fields bold (postcode).

Once you've put in the fields you want, click the "Update Labels" button.

Screen Shot 2018-12-18 at 14.05.33.png


This will prepare all the other labels in the document. It will look like this. It's a bit of a mess because the names of the merge fields overflow the labels; don't worry.

Screen Shot 2018-12-18 at 14.07.46.png


Now the fun bit[edit | edit source]

Click the "Preview Results" button. All of the labels on the first page should change to show how they're going to look when you actually output them.

I've had to obscure this screenshot so as not to have any client data on show. The grey boxes hide the full names and addresses. I've left the postcodes.

Screen Shot 2018-12-18 at 14.16.18.png


Providing you're happy with the preview, you can now click the "Finish and Merge" button on the right of the ribbon. You'll have two options:

  • Edit Individual Documents... - recommended - this runs the merge and creates a new document which is your labels. You can look through them and check for mistakes, or places where peoples' middle names might have been included by accident, that sort of thing. You can fix them, then print the document.
  • Print Documents... - for when you're feeling confident - this sends the labels straight to the printer of your choice. No interim checking. YOLO!!

Screen Shot 2018-12-18 at 14.19.43.png


Once it's all printed - you're done!

If you have any problems or need further help, please do give me a ring or an email.

Rob


Data Gardener (talk) 14:24, 18 December 2018 (UTC)