Change cell background based on cell content in Data Merge from Excel using Affinity Publisher

[DiZastor]

I may not have got all the Finnish or the dates perfectly correct, but here are two months just to demonstrate. All field contents are merged from the excel file and I selected Finland from my dropdown list in Excel then updated the Merge Manager

slim finnish calendar jan and april.pdf

[DiZastor]

I printed just January and April to demonstrate the holidays.

[lacerto]

On 5/14/2022 at 1:08 PM, DiZastor said:

DML will be filled in the rest until end of page and a new page started automatically

Basically yes. When you have DML control selected, you have Rows and Columns boxes available on the context toolbar, and these two determine how many times the fields placed in the top left cell of the DML will be repeated in the other cells. If the Record Advance box has the initial value of 1, each instance of the data set will advance the record pointer by 1 so that the next row of the Excel sheet gets pulled (if the advance value is zero, you can repeat the same row all over again, which would be useful e.g. when creating postal return addresses or anything else you want to have same text repeated on all labels).

Having more than one DML control allows you to place fields in specific places on the page (e.g. the month and year fields), and if you use Record Advance as 0 here the second (upper) DML will start pulling records from the first row of the same Excel sheet.

So yes, DML is a powerful control and well worth learning, though setting up the merge document may be a bit tricky.

[lacerto]

1 hour ago, DiZastor said:

I printed just January and April to demonstrate the holidays.

Thanks, the names seem to be correct, just out of curiosity, can they be fetched with an Excel formula or did you search them from the Internet? ("Pääsiäismaanantai" seems to be in bigger font size, though...) 

[DiZastor]

I found a list of Finnish bank holidays on the Internet. Sorted out the dates as some of them change annually, made a new array in my languages sheet added Finland to my Language selection array. So the answer is yes to both. I used Google translate and online listings in Finnish to double check translations so they should be correct. I also put in DST start and finish dates and times.

[DiZastor]

Thank you again for your help and patience, I'm making progress

Ok, I think I've managed to create the excel part of the data merge to use the DML control. I've tested it with different languages and years and also full and abbreviated day names. Tomorrow I shall work on Slim Calendar 2 using the DML command. Let's see what happens. I've attached a screenshot of the data page. All date related data is from an Excel date formula and format. I used EDATE() to give me a reference to generate the month names from a date (selected cell).

Cheers DZ

 

screenshot excel slim calendar2 data file.pdf

[DiZastor]

Just a thought... I'm going to make a few small jpg files of different colours and then use them instead of the emoji. If it works it opens up colour choices.

Cheers DZ

[lacerto]

4 hours ago, DiZastor said:

I'm going to make a few small jpg files of different colours and then use them instead of the emoji.

Yes, that should work well (along with a picture frames), and allows then exact and free color definitions, but requires also preparation of specific bitmaps for each field in context of which they are used.

[DiZastor]

I've been playing with DML and have successfully created a merge master which when I scroll through the fields for preview everything works well. Lines and shading is yet to be resolved, I've had a few goes but couldn't merge these even though they seemed to work when the correct record was selected. However... when I perform the merge, I get strange results. See attached file. It only produces some of the pages and starts around May and the days are out of sync with page layout. The merge master (below) has made the main merge feint thus difficult to see. I can't find a way of clearing this. It was fine but changed for some reason. I'm learning lots, but it's a bit semi-educated trial and error.

Cheers DZ

slim calendar mess.pdf

[DiZastor]

Here is a screen shot of the master showing the DML settings.

 

Cheers DZ

[Old Bruce]

Am I correct in thinking that Paragraph Decorations combined with a Find and Replace won't work for some reason? We just seem way out in the weeds with emoji fonts and JPEGs.

[lacerto]

Based on the screen shots the cause for confused run of days might be that you seem to pull formatting using separate DMLs. By default a DML will forward record pointer by 1, and you now have four on your layout. If you used the Affinity Publisher document that I attached above, then the Month + Year Heading used a DML that had 0 as Record advance value so it does not move the index pointer when days are started to being pulled. The data is fetched in the z-order so the IDML that is  bottommost in the Layers panel will be handled first. If you have the default 1 as the value of the Record advance value of the bottom two DML controls, then you actually advance the record pointer by 3 for each "table" row that is processed, which explains why the days are in dis-synch.

But what is more important is that the formatting is not going to work simply by having it in layers that are under the text layers showing the calendar data, because the elements where the color is fetched from the emojis need to be clipped to show correctly. So for the gray cell background, there needs to be a rectangle that clips the color of a scaled-up emoji glyph, and for the red holiday text, the red square emoji needs to be clipped into the CalendarData Frame Text layer and positioned at the location of the Holiday field at the right edge of the row. 

Since you seem to set this up on Windows, you also need to ensure that the emoji font used is Segoe Emoji.

If you want me to have a look on these and do not want to place your documents on the forum, you can send them to me using a private message (the button is available when you hover the mouse pointer on top of the user avatar (for you and  me just the default initials "D" and "L").

[DiZastor]

3 hours ago, Old Bruce said:

Am I correct in thinking that Paragraph Decorations combined with a Find and Replace won't work for some reason? We just seem way out in the weeds with emoji fonts and JPEGs.

I'm not sure. Excel I think I can handle, but I'm struggling with Affinity Publisher DML settings. Playing with emoji fonts and jpgs has so far led me nowhere except for the moon phases font. My Excel sheet has 372 records, which should exactly fill 12 pages with 31 DML fields. I can preview any record correctly in the Data Merge Manager but on merging get a weird fill. The grey fill and line separators are correctly triggered by the merge but I haven't yet worked out how to depict them in Publisher hence the struggle and discussions on emoji fonts and jpegs. Can you suggest how I can use find and replace interactively with my triggers to get a light grey filled text box and not have an underline on blank records at the end of short months?

Cheers DZ

[DiZastor]

1 hour ago, lacerto said:

Based on the screen shots the cause for confused run of days might be that you seem to pull formatting using a separate DMLs. By default a DML will forward record pointer by 1, and you now have four on your layout. If you used the Affinity Publisher document that I attached above, then the Month + Year Heading used a DML that had 0 as Record advance value so it does not move the index pointer when days are started to being pulled. The data is fetched in the z-order so the IDML that is  bottommost in the Layers panel will be handled first. If you have the default 1 as the value of the Record advance value of the bottom two DML controls, then you actually advance the record pointer by 3 for each "table" row that is processed, which explains why the days are in dis-synch.

But what is more important is that the formatting is not going to work simply by having it in layers that are under the text layers showing the calendar data, because the elements where color fetched from the emojis need to be clipped to show correctly. So for the gray cell background, there needs to be a rectangle that clips the color of a scaled-up emoji glyph, and the red holiday text, the red square emoji needs to be clipped into the CalendarData Frame Text layer and positioned at the location of the Holiday field at the right edge of the row. 

Since you seem to set this up on Windows, you also need to ensure that the emoji font used is Segoe Emoji.

If you want me to have a look on these and do not want to place your documents on the forum, you can send them to me using a private message (the button is available when you hover the mouse pointer on top of the user avatar (for you and  me just the default initials "D" and "L").

Thank you. I'm on my mobile now. I'll do that Monday afternoon when I'm back home.

Cheers DZ

[lacerto]

15 hours ago, DiZastor said:

The grey fill and line separators are correctly triggered by the merge but I haven't yet worked out how to depict them in Publisher hence the struggle and discussions on emoji fonts and jpegs.

In short: you need to have a Picture Frame to display an image based on a data source that defines a valid path to a supported image resource (full or relative paths are ok). For Emoji-based coloring, you need to have Text frames (that take directly the glyph as the data source similarly as when you have text as data source).

The trick with the Emoji is just that you normally have to scale up the emoji glyph vertically and horizontally and position it vertically and horizontally to clip off unwanted parts like borders of the square emojis to display the evenly colored area of them. 

In the case of using a glyph of a color font you additionally need to clip the color source e.g. into a rectangle shape so that the coloring is restricted to a clearly defined area like an area mimicking a "table cell". When coloring text, clipping needs to be done also when using an image as the coloring source so that the coloring is only applied to character shapes.

This is of course a kind of a work around in a situation that there is no direct support for data source based formatting, but using either a bitmap image path along with a Picture frame, or a color font (like Apple Color Emoji on macOS or Segoe UI Emoji on Windows which represent the only kinds of color fonts supported at the moment by Affinity apps and which are also available on all up-to-date operating systems without needing a separate installation) along with a text frame, clipping applied when necessary, are precise answers to the topic of the thread. Any other reply means just raising up hands and providing a manual work around, which is just fine to get the desired result but is not necessarily any easer to achieve. The benefit of learning a coloring trick is that you can subsequently easily use the same method to apply coloring to any other merge based job effectively and conditionally without needing any manual work after the merge. This can be a huge time saver in a complex job where formatting cannot be easily applied afterwards with a selection and/or Find Replace based style formatting.

Here the coloring trick is also used to display and hide information -- another common need in data merge based jobs -- so that the rectangle that is used as a separator line between the "table rows" gets color whenever it is wanted to be displayed, and no fill color when it is wanted to be hidden (the element itself is still included in the merge but it is just invisible).

[Old Bruce]

18 hours ago, DiZastor said:

... My Excel sheet has 372 records, which should exactly fill 12 pages with 31 DML fields. ...  not have an underline on blank records at the end of short months?

Cheers DZ

First thing is maybe have an Excel sheet with 365 (or 366) records. Then you don't have extra blank records. You'll need to redesign the Excel sheet.

18 hours ago, DiZastor said:

...Can you suggest how I can use find and replace interactively with my triggers to get a light grey filled text box...

Use Paragraph Styles with Decorations set up with Grey fill or whatever colour you need.

 

I think you are trying to get the Weekend days coloured differently from the Weekday days. Am I correct? If you search for (Saturday|Sunday) and replace with \1 and The Weekend Fill Paragraph Style that should do it. You could even have a Regular Day Fill Paragraph Style and a Blank No Day Paragraph Style to take care of your current setup. Start with everything having Blank No Day, then change to Regular Day Fill for days with words in them and finally Saturday Sunday get changed to Weekend Fill.

[DiZastor]

Thank you Old Bruce.

Does the "\1" open up the ability to dynamically create paragraph styles in a text box in the merge master file or is this a process I would have to do manually after creating each individual merged calendar?

[DiZastor]

Just now, DiZastor said:

Thank you Old Bruce.

Does the "\1" open up the ability to dynamically create paragraph styles in a text box in the merge master file or is this a process I would have to do manually after creating each individual merged calendar?

Sorry, I meant dynamically select...

[Old Bruce]

When you say dynamic I think you want something that will be set in the Excel sheet and cause some formatting in the Publisher document. I don't think that is possible. We cannot change the Excel sheet and have that change occur in the Merged Publisher document.

Please see my very first reply to you. It still stands. This dynamic term is confusing me, I can change the colour of the decorations in the Publisher document by changing the settings in the Paragraph style. I can do that before or after merging and I can do that as often as I want without having to merge the Excel file.

Myself I would have text and only text in the Excel file, the one exception would be the phases of the moon. All the colouring of the background would be done in Publisher.

[Old Bruce]

27 minutes ago, anto said:

As for me, it is very easy to do in Excel or Libreoffice. Only prepair several images jpg with different colours. After Merging data very easy also to change this colours. Enough only replace image in folder and update Resource manager.

After the merge has been done what do I do if I want to use a slightly darker colour for the background? In Publisher using a Paragraph style I can use the colour picker or choose among some swatches.

Compare that to your method of having the colours in the excel file. If I understand you correctly I would need to generate some jpeg files with the desired colours and then use the Resource Manager in Publisher to swap the jpegs. 

I will use my method for solid colours or gradients as background colours, your method would be useful if specific image backgrounds are required, I have done that too.

[Old Bruce]

4 minutes ago, anto said:

You have only replace file with darker colour in folder or change color and re-save the file. (1.jpg replace with 1.jpg)

Is this actually easier and quicker than choosing a new colour for the background using the colour picker of my choice? Where did the second 1.jpg come from? Did I have to generate that one from scratch?

[DiZastor]

I've just created a light grey jpg in Affinity Designer. It was very easy and quick. I'll see how it works on the merge master and merge tomorrow. Cheers DZ

[DiZastor]

Thank you for all your help. I've tried both the emoji route and jpg route. They both work well. In my instance where I only need two variations, one black and one grey, the better option is to use the black emoji in both instances and reduce opacity for the grey instance. Cheers DZ