Jump to content
Our response time is longer than usual currently. We're working to answer users as quickly as possible and thank you for your continued patience.

[Implemented] Data merge


Recommended Posts

  • Moderators

@E9B6

This 19 page feature request is not the right place to report bugs in the beta version.

1.9.0.798 macOS was replaced with 1.9.0.801 to address this problem I thought. Please post your findings using any beta in the relevant beta forum, as they will not be addressed here.

Patrick Connor
Serif Europe Ltd

"There is nothing noble in being superior to your fellow man. True nobility lies in being superior to your previous self."  W. L. Sheldon

 

Link to comment
Share on other sites

On 10/18/2020 at 5:11 PM, E9B6 said:

And YES I took note of what Alvarez and Murfee was trying to say.

Happy to hear it ;)

I tried your excel file and crashed on my system, too. In Indesign (using Unicode TXT export from Excel 2019) not problem at all, combined all data.

I can't see anything strange in your excel file (or exported txt), so i can't give you an answer. Bring your file to Serif crew, tehy can do tests with it.

 

By the way, I have tried again to combine data from 2 files. This time I used 2 excel files, previously I tried one xlsl file and one csv one, without result. I click second file fields but they arent added to the text frame. Now I can add fields from both files, but if I have Preview activated in both files, Publisher only shows and combine one of them. If I have preview disabled on both files, I can see both placeholders, but if I press Generate, only fields from first file are shown in combined file.

image.png.6d3f2c68113385852bc760bfa583189c.png

image.png.098501aa2562a850571f832af03d7626.png

I think you havent this problem...

EDIT: I'm sorry, AFP doesn't combine only data from first file. Every excel file have 3 records. AFP creates a 6 pages doc, the first 3 combining data from first file, and last 3 with data from second file but with the first line (the first file one) empty. Strange behaviour, isnt it?

 

Indeed, I tried again to combine excel and csv files together. With Preview OFF on both, I can add fields from both. Thats why I can't do it to work before. AFP adds the second file fields, but doesn't show it.

Maybe I must move all that stuff to Beta forum, like Patrick say to us...

Link to comment
Share on other sites

Hi @Phil Creighton, I enjoy a challenge 😀 I have created another file that is a lot easier. I have used 2 tables, the bottom one is for the titles and coloured rows. The one nested in the DMNT is for the data. 

All you need to do is add the data fields to the top row of the Data Table. I have added a data node with 20 rows that are 7mm high, this matches the row height of the table.

As Dominik explained further up this thread the merge pulls through raw data and can not be formatted. This is the reason for the lower table. The reason I was originally using multiple single row tables & multiple DMNTs was to give more control of the formatting. 

The attached file works fine if you just need coloured rows. All you need to do is add extra rows to the lower table if needed & colour them. If you add extra rows then you will also need to extend the DMNT to match the number and cell height, you will need to drag the lower handle down to cover the last row of your extended table.

Data Merge Tables Sample 2.afpub

 

Link to comment
Share on other sites

Just now, Jose Alvarez said:

So you put a real table behind the DM node to provide the colours that DM doesn't allows to apply. That's what I was thinking that can be a better solution than the previous you used before, but you showed it before I can say. Good work. ;) 

Thanks 😀

I need more control over the formatting, different text colour for different rows, that is why I came up with the multiple DM nodes and single row tables, I couldn't get it to work with a single table. If it is just a simple table with alternate shaded rows then a real table behind the nodes works fine. I was getting pretty frustrated with not being able to control the formatting 😂

Link to comment
Share on other sites

8 minutes ago, Murfee said:

Thanks 😀

I need more control over the formatting, different text colour for different rows, that is why I came up with the multiple DM nodes and single row tables, I couldn't get it to work with a single table. If it is just a simple table with alternate shaded rows then a real table behind the nodes works fine. I was getting pretty frustrated with not being able to control the formatting 😂

Something like that?

image.png.2c88ac0aef46c526e98e82cf44a58009.png

Your DM node is behind the table (one) that I have created.

image.png.a725eda95333a8e75b66da0c06526bbc.png

It's very rough, I'm only trying things.

Link to comment
Share on other sites

1 minute ago, Jose Alvarez said:

It's very rough, I'm only trying things.

That will work, I was too lazy to create a full table style. I did try different text styles but it changed them all to the same colour. Let us know how you get on with different font colours on different rows.

Link to comment
Share on other sites

29 minutes ago, Murfee said:

That will work, I was too lazy to create a full table style. I did try different text styles but it changed them all to the same colour. Let us know how you get on with different font colours on different rows.

Thanks for all your hard work. It's amazing to see. 

I need to park my experiments for a few days as I've got to concentrate on producing this week's paper. 

Link to comment
Share on other sites

Thats a view of your file, combined, with the data merge node moved to the right:

image.png.97035bd22995bdcee74c37b7f0f58693.png

I have applied the table style to the table that you have loaded into the node. The columns and text colours are respected, not the rows fill. But you have the other table to do that.

Now I have adjusted the tables to a better look.

image.png.fdb42b097ee083e90edb2c92ba874884.png

Is that what are you looking for?

Link to comment
Share on other sites

Ahhh ok.

I'm trying new things, but I have found something: if you see the combined data using your file, every record in the page have HIS OWN copy of the original frame created with DM node tool. So you have so many frames, and SO MANY TABLES, than records. You can`t get row colour alternance 'cause every record is THE FIRST ROW in his own table. Even more: you can create a DM node with 2 rows, and add text frames in both with different paragrap styles (= colour), but you will have the same result. 

Finally, I can get this:

image.png.1a321ff9a993684b5dc9b65334c1e8fc.png

How? I have created 4 DM nodes, and I have populated them with the needed text frames, each with his field and paragragh style associated. Well, I have created and set the 2 first, and I have duplicated them. If you will reuse this layout again and again and again, you can make the effort creating all that nodes and frames. Your choice. But like you can see, the record order is wrong: 1, 3, 2 and 4. Maybe because I have duplicated the nodes. I have no time to test it not duplicating. Let me know if you go further in this way and your results.

Good luck!

Link to comment
Share on other sites

2 hours ago, Murfee said:

Example: Row 1 needs to be black text, row 2 needs to be grey text

You can achieve this by putting a colored rectangle above the data merge row and give it a vivid blend mode. You just have to play a little with the colors.

BTW: a very interesting ongoing discussion 🙂
d.

Affinity Designer 1 & 2   |   Affinity Photo 1 & 2   |   Affinity Publisher 1 & 2
Affinity Designer 2 for iPad   |   Affinity Photo 2 for iPad   |   Affinity Publisher 2 for iPad

Windows 10 21H2 (19044.2251) 64-bit - Core i7 - 16GB - Intel HD Graphics 4600 & NVIDIA GeForce GTX 960M
iPad pro 9.7" + Apple Pencil

Link to comment
Share on other sites

4 minutes ago, Jose Alvarez said:

Good luck!

Thanks so much for your help. I am happy with the complex table that I have created, I know there are many nodes & single row tables, it does give me the control I need. My records are fine because each layer is in the correct order in the layers panel, the tool is intelligent and seems to know that record 2 needs to be in the second node & table row. It only took about 5 minutes to set up the whole thing so I can live with that 😀

Link to comment
Share on other sites

Just now, dominik said:

You can achieve this by putting a colored rectangle above the data merge row and give it a vivid blend mode

Thanks Dominik, I tried that and the colours weren't quite what I need, I have to stick to corporate colour schemes 😀

Link to comment
Share on other sites

Just now, Murfee said:

I have to stick to corporate colour schemes 😀

Oh, that's a different thing. My method is really just about eyeballing something that somehow goes into the right direction, but it is not very exact.

After all it is just a workaround and I'm curious if we will see more controls in the future that make this possible. But perhaps you are right in what you wrote a little further up and for this we will need linked documents.

d.

Affinity Designer 1 & 2   |   Affinity Photo 1 & 2   |   Affinity Publisher 1 & 2
Affinity Designer 2 for iPad   |   Affinity Photo 2 for iPad   |   Affinity Publisher 2 for iPad

Windows 10 21H2 (19044.2251) 64-bit - Core i7 - 16GB - Intel HD Graphics 4600 & NVIDIA GeForce GTX 960M
iPad pro 9.7" + Apple Pencil

Link to comment
Share on other sites

1 minute ago, dominik said:

Oh, that's a different thing. My method is really just about eyeballing something that somehow goes into the right direction, but it is not very exact.

After all it is just a workaround and I'm curious if we will see more controls in the future that make this possible. But perhaps you are right in what you wrote a little further up and for this we will need linked documents.

d.

I do a lot of this type of thing and have been stuck with using Word & Excel until now. It would be good to have a simple data link that retains existing formatting. The Word way is so simple, copy the range of cells then paste as a link with the option of using the Excel formatting or the existing Word document formatting, then you can select to update the links. I am not suggesting that Team Affinity use that method but it is pretty much idiot proof 😀

Link to comment
Share on other sites

20 minutes ago, Murfee said:

The Word way is so simple, copy the range of cells then paste as a link with the option of using the Excel formatting or the existing Word document formatting, then you can select to update the links.

They (MS) have their very well integrated system. I hope Serif come up with a way to solidly connect to a variety of data sources and documents. Actually I'm confident 🙂

d.

Affinity Designer 1 & 2   |   Affinity Photo 1 & 2   |   Affinity Publisher 1 & 2
Affinity Designer 2 for iPad   |   Affinity Photo 2 for iPad   |   Affinity Publisher 2 for iPad

Windows 10 21H2 (19044.2251) 64-bit - Core i7 - 16GB - Intel HD Graphics 4600 & NVIDIA GeForce GTX 960M
iPad pro 9.7" + Apple Pencil

Link to comment
Share on other sites

  • Moderators

So firstly I should say this is absolutely not what this feature is for and it's obscene to see it abused in this way but I, like the rest of you, can't resist a challenge! So here's my solution. The main advantage here is I've nested the DM nodes so you only have to create the row table once. Nesting works fine but you have to be careful with the Record Advance. In this case the outer one has a record advance of 0 since the inner one is doing all the advancing. I've done the row shading with a rectangle behind the table.

Now I should do some proper work,

Mark

CrazyDataMergeTable.png

Link to comment
Share on other sites

9 hours ago, Mark Daniel said:

So firstly I should say this is absolutely not what this feature is for and it's obscene to see it abused in this way but I, like the rest of you, can't resist a challenge! 

I agree with you, absolutly! A challenge is a challenge, but I can`t get the point to do DM with a table that you can import and format directly... ;) 

My DM usual tasks are to make certificates (mixing fixed text, data merged and 1 picture), bussiness cards and customizing comercial post or brochures.

Link to comment
Share on other sites

Just as a short feedback to anyone interested: I had the chance to do a data merge in a regular bulk mailing with about 250 recipients from an XLSX-file created in LibreOffice. That went smooth and fast.

d.

Affinity Designer 1 & 2   |   Affinity Photo 1 & 2   |   Affinity Publisher 1 & 2
Affinity Designer 2 for iPad   |   Affinity Photo 2 for iPad   |   Affinity Publisher 2 for iPad

Windows 10 21H2 (19044.2251) 64-bit - Core i7 - 16GB - Intel HD Graphics 4600 & NVIDIA GeForce GTX 960M
iPad pro 9.7" + Apple Pencil

Link to comment
Share on other sites

  • 3 weeks later...

Sadly we can´t test the Data Merge Feature currently, because we are still using the Test-Versions of the Affinity Software. But the Data Merge Feature was the one key Feature that was missing from Indesign. 

Fun Fact: Publisher could IMPORT the Indesign File with the Links Excel Tables and they also showed as "Table" in the Layer-Sction but the Link was gone. 

A little Question: Is it possible to Link to more than one Sheed in an Excel File? For Example we have a Excel Documents with 15 Sheeds, so can we use the Data Merge to Link one Sheed to a different Master-Site? Site 1, Sheed 1
Site 2, Sheed 2
etc.

I know that this is more like Linking Data to a Document than Merging but hey, Solutions must be found. 😁 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...

Important Information

Please note there is currently a delay in replying to some post. See pinned thread in the Questions forum. These are the Terms of Use you will be asked to agree to if you join the forum. | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.