How I Data Merge

We all do things differently. Some of us like to prototype using pen and paper. Some build them completely in an online platform like Tabletop Playground.

When we prototype at Drayer Ink, we use a combination of tools: we create digital assets and artwork, then print them and make them into a physical prototype.

Concept

NEW GAME IDEA
In Mycelia, players represent different families of mushrooms trying to fruit. The aim for each player is to have the most mushrooms fruit in one of the families they are growing.

I trialled square and hexagonal tile shapes, chose hexagonal, then I made a set of basic symbols so we could test the concept.

Data Merge Inputs

The first thing I do is create folders and naming conventions for the game on my PC, and put the images I plan to use in those folders.

Then, I put the folder address and the image prefix and suffix details into the first 3 columns of my Google Sheets spreadsheet.

Next, I create a matrix of options. In this case, I want to be able to show Symbol 1 in every one of the 6 positions on my hex tiles, so I created 6 columns.

I did the same for each symbol, repeating the pattern.

Once I knew the distribution of my symbols, I needed to make them available to my graphics programme’s data merge, so I created a concatenate command that works like this:

1. Check for an “x” symbol
2. If it’s there, and it’s the first one in the row, put the name of its column where the filename goes in the first column of concatenated values.
3. If it’s the second one, put it in the second column of values.

Concatenate

Breaking down the formula, as I understand it:
=CONCATENATE( – this means to combine the following bracketed values into a statement
$A2 – always use the value in column A (that’s what the $ does) but change the row
& – combine with
$B2& – as above
(ArrayFormula(IFERROR(INDEX($G$1:$U$1,SMALL – select the following from columns G to U in order of appearance
(IF($G2:$U2=”x”,COLUMN($G2:$U2)-4,””), – if there is an “x” in columns G to U
COLUMNS($V$2:V2))),””)) – populate column V, row 2 with that value*
&$C2)) – then add the value from C2

* in the first column, you only use V. By the last column, you show the spread, which means the formula knows to put the second, third, fourth, fifth, or sixth value into the second, third, fourth, fifth, or sixth column.

Column V (first concatenated column):
=CONCATENATE($A2&$B2&(ArrayFormula(IFERROR(INDEX($G$1:$U$1,SMALL(IF($G2:$U2=”x”,COLUMN($G2:$U2)-4,””),COLUMNS($V$2:V2))),””))&$C2))

Column AA (sixth concatenated column):
=CONCATENATE($A2&$B2&(ArrayFormula(IFERROR(INDEX($G$1:$U$1,SMALL(IF($G2:$U2=”x”,COLUMN($G2:$U2)-4,””),COLUMNS($V$2:AA2))),””))&$C2))

Please note that I am NOT an expert in Excel/Google Sheets. This is how I interpret the formulae I use, but this interpretation works for me!

Using the Data Merge in Affinity Publisher

Once I have made my spreadsheet, I download it as a CSV (comma separated values) file.

Next, I set up an Affinity Designer file with a hexagon and picture frames for each of the 6 symbols.

Once I have chosen my basic layout, I plug in the values from the spreadsheet, using the Data Merge functionality (Document > Data Merge Manager).
I click the document icon on the bottom left, and tick the “Preview with record” option.

I open the “fields” tab (View > Studio > Fields) and assign each of my concatenated fields to a picture frame by clicking on the frame, then on the name of the field (in this case, Symbol 1)

Then I repeat until all the fields are populated – this works for both images and text. I put any text directly in the spreadsheet column, and instead of using a picture frame in Affinity Publisher, I use a text field.

Once all of my values are showing in my example template, I run the data merge (Document > Data Merge Manager, “generate”), which creates a new file. In that file, there is a page for every row in my spreadsheet.

I can then export all of the files as PNGs, and merge them into a new file to create a PNP (print and play) file to cut up for testing.

Making a PNP file

My next step is to create a second spreadsheet, which takes files from the output of the first one and sorts them into rows to populate the second one.

Next, I create a new Affinity Designer file to fit on a US Letter page. I lay out the number of hex picture frames I want on the page, then I make a spreadsheet with that many columns in each row, in this case, 20 of the 23 available slots (I wanted some blanks).

This spreadsheet has a similar format to the first one – the input link, the filename (minus the number), and the file extension. There are two differences here: I manually input the “zero” value for each row, and the calculation itself requires a blank field, and I don’t know why.

So in this case, we have 1-20 in the first row, and 21-40 in the second row. I input 0 and 20. The formula does this:

=$A2 & $B2 & ($C2 + ((ROW()-1) * $E2) + COLUMN())-5 & $D2

Like the first spreadsheet, it concatenates the values in A, B, and D, then it performs a calculation that populates the number of the file at the end of the name – I didn’t write this, a friend did, so I can’t explain it further, but it works wonderfully! If you copy my layout exactly, it will work for you, too.

Next, I plug the values into the Affinity Publisher file, and generate the data merge in exactly the same way.

Once I export them, we can print and cut up the files easily. It’s also easy to make changes, and run the data merges again, until I have everything the way I want it.

Hopefully, this makes sense! Let me know if you have any questions, or you can explain the formulae more clearly!

Tools

People have also asked what tools we use to assemble the prototypes after printing. We use glue, scissors, paper cutters, punches, scalpels… Check out what your nearest craft store offers, you will be surprised how much can be used for prototyping. And if you are looking for real pro-tips, check out Martin’s Print and Play Hideaway on Facebook – anything we make pales in comparison with some of the games these amazing people craft!

Happy prototyping!