[Implemented] Data merge

[undercovergypsy]

So APPRECIATIVE for your patience and assistance. Yes, and now I got it - duh - text frame...

[dominik]

12 hours ago, dominik said:

I'll try and see if I can find something with sections and master page elements. But not today ... (yawn ... sorry).

@MikeW,

I wonder why your post has vanished?

But, coming back to your example. I tried some things in APub but I came to the conclusion that without some logic and automation (= scripting) it is not possible to accomplish something like in your example. I do not blame Serif for this. Data merge is in a very early stage and for the moment the good thing is that it is being worked on 🙂

For now it seems that data merge will work for anything repetitive where each item has the same structure.

d.

[MikeW]

27 minutes ago, dominik said:

I wonder why your post has vanished?

I hid them. They served their purpose in our conversation and were distracting from Serif's contributions to APub.

[dominik]

1 hour ago, MikeW said:

I hid them. They served their purpose in our conversation and were distracting from Serif's contributions to APub.

Thanks, I understand.

d.

[mswift]

Working on iMac OS Catalina without any crashes. Only thing I couldn't do was adjust space between each item.
Didn't work at first until I discovered a spelling mistake in the data, didn't match the spelling of an image!
Normally I would also add crop marks for each item, In ID I used a script to do this.

[dominik]

41 minutes ago, mswift said:

Only thing I couldn't do was adjust space between each item.

When in the 'Data Merge Node Tool' look in the tool bar for 'Gutter' and adjust. Make sure to check 'Preserve Cell Size'.

There's also a red dot in the grid itself. You can drag this.

d.

[mswift]

2 minutes ago, dominik said:

When in the 'Data Merge Node Tool' look in the tool bar for 'Gutter' and adjust. Make sure to check 'Preserve Cell Size'.

There's also a red dot in the grid itself. You can drag this.

Thanks for that, I'll check it out later.

[Phil Creighton]

If I can get this to work, it will be a game changer for our newspaper's sports tables. 

Before the virus wiped out the season, I was cut and pasting the data from website  > Excel > Affinity, and tidying up accordingly. With more than 20 tables it was an evening's work. 

The big deal would, I think, be the automatic update of the Excel data. 

For the moment, my initial dabbling does not bring the results I'd wish for. Importing using Dominik's guidance doesn't bring in the whole table, while formatting the imported data is a faff. I'm all for more control over the formating of the cells - you can see our house style below. Can Affinity automatically tint every other row outside of the header? 

The hardest thing for me is that we're so busy producing the weekly content that we've not got a lot of time to play. 

 

[Alfred]

On 10/14/2020 at 6:34 PM, wonderings said:

**please delete somehow double posted**

There’s a ‘Hide’ option (accessed via the ‘Options’ link below your posted message) for posts that you don’t want anyone other than the moderators to see.

[dominik]

1 hour ago, Phil Creighton said:

Can Affinity automatically tint every other row outside of the header?

Hello @Phil Creighton,

provided that the table has always the same number of rows you could split merged data and the tinted rows into two objects. The tinted rows are then a seperate object behind the table of results. This could be a table itself or a set of rectangles. All could be grouped together.

The 'Data Merge Node Tool' itself does (yet) not have the ability to alternatively apply different colors. If you look at its output you will find a group that essentially contains text frames for each merged data set. You could style them manually but this is most likely more laborious.

Cheers,
d.

[Phil Creighton]

1 hour ago, dominik said:

Hello @Phil Creighton,

provided that the table has always the same number of rows you could split merged data and the tinted rows into two objects. The tinted rows are then a seperate object behind the table of results. This could be a table itself or a set of rectangles. All could be grouped together.

The 'Data Merge Node Tool' itself does (yet) not have the ability to alternatively apply different colors. If you look at its output you will find a group that essentially contains text frames for each merged data set. You could style them manually but this is most likely more laborious.

Cheers,
d.

Thanks Dominik - really helpful answer. 

In theory the tables should stay the same but sometimes team names go over two lines, such as the last team in the list. 

It's been seven months since I last did a league table in Affinity - thanks Covid - so I can't remember what I did for the previous method, but I think it was set up with the table colouring every other row. 

Cut and pasting the table manually meant that  it always added an additional column which had to be deleted. 

 

I look forward to this developing. In the previous (ahem) software I used, it updated the tables every week once the Excel file had been updated. Hopefully this will be where Affinity gets to. 

 

That and being able to import Google Drive directly ... 

[dominik]

2 hours ago, Phil Creighton said:

but I think it was set up with the table colouring every other row

This is possible with APub tables. It's just not possible with the 'Data Merge Node Tool'. That's the reason why the merged data and the visual representation need to be seperated. This also means, rows could not have altering text colour because text colour is a property of the merged data. (There are ways to do this with coloured layers above that are set e.g. to 'Vivid Light').

 

2 hours ago, Phil Creighton said:

it updated the tables every week once the Excel file had been updated. Hopefully this will be where Affinity gets to. 

I think this should work already. As long as you update the data source file (in contrast to exchange it with a different file) you can update it (in the 'Data Merge Manager') and the updated values are in the next generated output file. I just tried it with a simple sports results table.

d. 

[Phil Creighton]

51 minutes ago, dominik said:

I think this should work already. As long as you update the data source file (in contrast to exchange it with a different file) you can update it (in the 'Data Merge Manager') and the updated values are in the next generated output file. I just tried it with a simple sports results table.

I'd be interested to know where I'm going wrong Dominik, as I couldn't get it to work for me - admittedly I was fiddling to see what worked rather than being precise with my formula. 

 

[dominik]

3 minutes ago, Phil Creighton said:

I'd be interested to know where I'm going wrong Dominik, as I couldn't get it to work for me

Make sure that the text object where you insert the fields is within the 'Data Merge Node'. You can check and see in the layers panel. See screenshot.

Does this make sense to you?

d.

[Murfee]

1 hour ago, dominik said:

This is possible with APub tables. It's just not possible with the 'Data Merge Node Tool'. That's the reason why the merged data and the visual representation need to be seperated. This also means, rows could not have altering text colour because text colour is a property of the merged data. (There are ways to do this with coloured layers above that are set e.g. to 'Vivid Light').

Hi Dominik

If you use single row tables nested in the Data Merge Node, have the height of the node tool matching the height of the table row, keep just one column in the node tool and the width matching the width of the table.

Add the fields then copy this nested node layer down as many times as needed for the data (not ideal if there are too many rows) . You can now format the rows & cells as needed. The formatting remains when you generate the data merge and when the data is updated. I have been fiddling this afternoon and the table below updates exactly as originally laid out.

[dominik]

10 hours ago, Murfee said:

If you use single row tables nested in the Data Merge Node, have the height of the node tool matching the height of the table row, keep just one column in the node tool and the width matching the width of the table.

Hi @Murfee,

this is an interesting approach. Can you perhaps post a screenshot with the Data Merge Node Tool activated and also the layers panel visible.

I do not entirely understand the above highlighted sequence. Does the table reach out of the Data Merge area or not?

Cheers,
d.

[Murfee]

Hi @dominik, 

I created a single row table first, then used the Data Merge Node Tool to surround the table and match the size, I nested the table inside the DMNT. I added the fields to each cell of the table, then selected all cells and changed the fill colour. A simple duplicate when the DMNT layer is selected until you have enough rows, then select all and use the Align Tool. It produces a single page file with the table looking correct when the merge is generated

The screenshot shows the top with the DMNT selected and the bottom with the table selected

[dominik]

11 minutes ago, Murfee said:

A simple duplicate when the DMNT layer is selected until you have enough rows, then select all and use the Align Tool. It produces a single page file with the table looking correct when the merge is generated

Hi @Murfee,

thank you for the screenshot and the explanation. So, basically you are using multiple instances of the DMNT (I like that abbreviation) and they behave as if they are one. I tried this and it indeed does work. I find it a little surprising, thought, and wonder if this is a bug or intended behaviour 🙂

I hope one of the moderators takes note of this, because it is really interesting.

d.

[Murfee]

3 minutes ago, dominik said:

I find it a little surprising, thought, and wonder if this is a bug or intended behaviour 🙂

I hope it's intended 😀

Normally for this type of data I would use data linking but that isn't possible yet. 

[dominik]

9 minutes ago, Murfee said:

I hope it's intended 😀

Normally for this type of data I would use data linking but that isn't possible yet. 

I, too, hope it is intended.

Furthermore I have the feeling that with the data merge tool we see the basic technology of linking text and data sources. Just modify the DMNT so that it is only one text frame that adapts it's lenght to the containing text. That way we have a container for linked documents that can be modified from outside of APub and updated within APub.

d.

[Murfee]

Yes, I tried with adding extra data to a cell so that it expands...the table does but the DMNT doesn't so it would be really useful if they could build that intelligence into the tool.

 

[E9B6]

As this is something I desperately need, I decided to give it a try. Loaded OK. Opened a file - several pages of a menu for a function.

Added data source, soon realised it had to be xlsx so converted the old xls file. It has several sheets in it. The program crashed immediately.

Reloaded the document, and converted the xlsw file to a single sheet. Loaded that. Crashed immediately,

So I haven't been able to test anything at this point

C R Goss

[E9B6]

Tried to simplify - new page, reduced number of columns in xlsw file, same result.

xlsw file had 8 columns and 83 lines.

So at the moment NADA

[Murfee]

6 minutes ago, E9B6 said:

Added data source, soon realised it had to be xlsx so converted the old xls file. It has several sheets in it. The program crashed immediately.

 

Hi, I have also had xlsx files crash and reported it. Can you try to save the data as CSV? If so it works quite well. You just need to remember to save over the original CSV when you update any data in your original file.

[Jose Alvarez]

4 minutes ago, E9B6 said:

Tried to simplify - new page, reduced number of columns in xlsw file, same result.

xlsw file had 8 columns and 83 lines.

So at the moment NADA

Why do you say twice "xlsW"? Is a spelling mistake? You know, must be xlsX...