Color, styling, formatting with data merge

[sweethoss]

Back story, I use APub for creating baseball cards for a tabletop baseball board game.  Basically, there's one card that gets created for each player on a team (see example images below). I have  large spreadsheet with all the names, player qualities, etc that gets data merged into an APub file and it works awesome.   I've been doing this for years.  The lot of us that make cards have found a styling challenge that I'm hoping there's a nice work-around, or better yet, a first-class feature in APub to handle this.

The final PDF that gets generated has all the players on it, around 24 players each, organized by team, and each team has a color scheme. Approx. 1000 players. 

Obviously when I data merge, it uses the template cell to produce cards, so I don't have a way to set styles based on team, I have to either:

1. Generate the 1000 cards, then go back and change the color on each object on each card.  If there are 3 text pieces that need to be different colors for a team, I have 24 cards x 3 objects x 30 teams = 2160 manual color operators.  BAH!!
2. Set the colors for one team, generate that team, then manually paste the cards into a final document.  Then it's really only 3 manual color sets * 30 teams = 90 color changes.  However, there aren't always exactly 24 cards per team, so pasting becomes a hassle because if I end up with 2 cards on one row, the third and final spot in that row is empty, so I have to paste one card there, and then the remaining cards fill up the rest of the spots, lather, rinse, repeat.  Another drawback of this is that when I decide that I want to tweak my orange, I have to back and change it and regenerate the team and repaste them into the final document.

Is there a better way to accomplish generating cards from a single set of data, but adding custom color for each card or group of cards?  Ideally, I'd love to be able to specify fields in my data merge that set color properties during the merge.  

I'm a software engineer as well, so I was thinking even more awesome would be some kind of CSS stylesheet mechanism where I can create styles for each team and then set what style sheet to use in column in my data sheet.  

For instance, I could create a sheet where I could say:

Style Entry:
baltimore-styles: {
header.font-color: black;
teamName.font-color: orange;
}

milwaukee-styles: {
header.font-color: navy;
teamName.font-color: cream;
}
etc.

Data Sheet:
Team, FirstName, LastName, StyleId
Baltimore, Corbin, Burnes, baltimore-styles
Milwaukee, Christian, Yelich, milwaukee-styles

Obviously this doesn't exist, so outside of Affinity adding this as a feature, what are some workflows to help make this a more automated process?

[loukyn]

I've used Data Merge quite extensively in recent weeks, and it is quite limited. It would be really nice if JSON support was extended to allow sub-objects and sub-arrays. In my case, I had three distinct sections in each record, each with the same 5 fields. I had to flatten this structure down into long prefixed fields, which left me with ~30 properties on each record.

Secondly, there's no real option to format blocks of text. It's possible to merge a string containing newlines into a text field and format the block as a list of bullet points, but there seems to be no way to create a second-level bullet point list. Including tabs in the string only adds a tab character after the bullet, but does not change the bullet itself. An option to swap between pre-defined text styles modally or to define styled ranges within merged strings would be really powerful. As an example, a curly-brace syntax like the one below could work:

{style: Heading 2}A Title{/style}{style: Bullet 1}One point.\nAnother point.{/style}

Being able to merge an HTML string as formatted text and then map HTML elements to particular text styles would be even more capable.

Thirdly, the support for Data Merge seems to be limited to text fields, hyperlinks, QR codes and linked documents. I don't see why it shouldn't be possible for all fields in Affinity to support referencing an external parameter or even a formula based upon one or more parameters, like in a spreadsheet. Imagine being able to specify the width of a rectangle like so:

100 pt * {likes} / ({likes} + {dislikes})

Ultimately, the web platform will always be more capable, although I really think there's an unmet niche here. Since you, @sweethoss, have development experience, have you considered building your cards as some kind of web application? CSS has the @page rule which allows you to specify printed page dimensions, and browsers have print-to-PDF functionality. You could always export SVG versions of your card designs from Designer or Publisher, and even copy that SVG into a React or Vue.js component. You could then apply attributes and CSS rules as you wish. This would also allow you to benefit from the cascade, which is something design software tends to suffer by not supporting. If automating the browser to export 1000 PDFs is awkward, you could instead generate SVGs in JavaScript and then use them as-is or batch-convert them to PDFs with Designer. SVG's colour management doesn't accommodate CMYK, unfortunately, which may be a problem for you.

[GarryP]

Welcome to the forums @loukyn

If you would like to make suggestions for improvements to the software then a Questions section of the forums might not be the best place to do that as Serif don’t look here for that sort of thing.

You might like to use the Feedback & Suggestions forum for that instead. (Please read the guidelines listed on the linked page before adding new suggestions/requests.)

[sweethoss]

Thanks GarryP. I wrote a post last evening over there. 

 

Loukyn, I did consider writing my own. But affinity has some great formatting features and is so close, I was hoping for some more merge features to complete my needs. 

 

I might ultimately have to go that route, we'll see. 

[loukyn]

Thought of a janky workaround for you, sweethoss:

• Add another field to each record, e.g., `colour_offset`. Fill this field with some character – I used "+" – repeated as many times as the index of the team in your team list.
• Add a new right-aligned text object in Publisher. Paste one Unicode "full block" character (█, U+2588) in the box for each team, then finally insert the `colour_offset` field from the Data Merge. As this property gets longer, it will push the colour blocks to the left in constant steps.
• Highlight the block characters one-by-one from left to right and change their fill colour to that of a team, in the same order as the original list.
• Set the text field to a monospaced font. If you want the block to be wider, you can use the Horizontal Scale option in the Character panel. I also set Tracking to 20‰ since the blocks overlapped slightly in the font I used.
• Create a smaller rectangle and place this text element inside it, clipping the field down to just part of one block character. You could scale it up to cover the whole of one of your cards.
• Duplicate the small rectangle and its contents and clip the clones to other elements to change their colour. Alternatively, set the text object to blending mode "screen" (and place a white rectangle at the back of the design).

This doesn't let you actually define colours in your dataset, and complex strokes would still be difficult, but it would work in simpler situations. Note that my screenshot uses left alignment, meaning the team colours run right-to-left and spacer characters are on the left, but the principal is the same. I've also included a faded version of the text box to show how it works.

 

Edited March 29 by loukyn