Data Merge from multiple sources

[Daniel Bernardes]

Hi Affinity Team,

Currently on Affinity Publisher one can have multiple sources for data merge but only one can be used at a time so they are interchangeable data sources. My feature request would be to be able to use multiple data sources concomitantly on data merge.

For example:

I created a catalog of people and services for a company. Each set {People, Services} has its own sub-set of characteristics {Name, Department, Expertise, Years of Experience, Etc} {Name, Department, Description, Duration, Etc}. Having to combine both into one data source leads to data duplication for one of the sets into the other one unnecessarily. If I chose to set my data on People, each line (person) will have a complete set of Services making the database unnecessarily big. The workaround today is to link the using formulas but its not ideal.

 

Thank you,

Daniel

[Old Bruce]

I repeat from an earlier reply

"I honestly do not know how that could be implemented. Say you have a spreadsheet/report with Customers' names and addresses. Another Spreadsheet has Names and phone numbers. Unless the two are setup exactly the same chaos will ensue. Imagine just having some customers who have not given you a phone number, they are not entered in the second spreadsheet and so it all goes bad. 36 records in the first and 24 in the second. If the first customer in the first spreadsheet/report is not in the second then the resulting Merged Publisher file will have 24 customers with wrong phone numbers."

[sfriedberg]

If we treat this as a database problem, you need to create/script a query on your catalog that joins the necessary tables and produces a (single) file for use with Publisher data merge.  That way, your database stays nice and normalized (non-redundant), and there's a procedure for getting the data formatted properly for Publisher.

[lacerto]

Multiple data sources could basically be used in situations where each row of multiple sheets has information pertaining to the same entity, person, object, etc., but presenting e.g. time-dependent state of certain objects of interest. One could create a data merge layout of such complex data from multiple sheets instead of needing to combine the information into one very large sheet, which would allow using a kind of relational database where information could be maintained and updated independently. Each sheet would be arranged by a key that identifies the entities described and that would be identical on all sheets so if there is no information related to specific key (data row), the related cell, or even the whole row could be empty.

This could be an Excel 3D database of grouped sheets, or a separate database from which data would be collected and exported to up-to-date multiple sources. A separate database would probably make it possible to easily create an up-to-date single data source of arbitrary number of fields (columns) using a saved query, so this approach would be less useful in this scenario, but in case an Excel workbook would be the primary data source, it would make sense to keep separate "tables" (sheets) and use methods that fetch data directly from such tables instead of combined (calculated) flat sheets.

I have not figured out if data could somehow be merged simultaneously from any of the sheets involved (while the data pointer is at the same row), since when I have tried this, each sheet will be merged in entirety and in sequence, but since the data merge layout is composed having fields from all data sources available for placement at design time, it is easy to plan a well-organized layout which would then be put together by combining the data from different sources from an exported combined into a single PDF document placed multiple times on overlaid layers. 

[Gaspare]

I am Italian and I use google Translate, I hope this is the right post to describe my problem

I have 4 csv files:
one with staff data (yellow background);
one with the data of the players (light blue background);
one with matches (red background);
one with the data of a football team (green background).
Row 2 of all files refers to team A, row 3 to team B, row 4 to team C etc ... (row 1 column header)
In the table I would like to insert the data of each file.
When I generate the file I would like all the tables to be populated and not just one.
I would like to create a page with the data of row 2 of each file (not 4 pages), a page with the data of row three of each file etc ...
It's possible?
I can not do it :(
See attached image

[Old Bruce]

I do believe you will have to make one spreadsheet or CSV file.

Via DeepL.com:

Credo che si debba creare un foglio di calcolo o un file CSV.

[Daniel Bernardes]

Ciao Gaspare,

 

Questo e il mismo problema che c'o. Dovevi combinare i tuoi fogli in un solo. 

This is the same issue I had. You need to combine your spreadsheets into one.  

[Komatös]

For projects with multiple data stems, a relational database, such as one you can create with Microsoft Access, is more suitable.

A database programme can also be used to manage and output more complex data structures. 

[Gaspare]

Old Bruce in this video Data Merge | Tutorial Affinity Publisher per desktop (serif.com) is seen using two csv files at about 4:30

Ciao Daniele, mantenere aggiornato un file csv di circa 1000 righe e 3-400 colonne è molto complicato è molto più semplice gestire 4 file separati
Hi Daniele, keeping up to date a csv file of 1000 rows and 3-400 columns is very complicated it is much easier to manage 4 separate files

Komatös used Access about 20 years ago, it is not possible to have paginated pdfs like Publisher allows

[Old Bruce]

33 minutes ago, Gaspare said:

Old Bruce in this video Data Merge | Tutorial Affinity Publisher per desktop (serif.com) is seen using two csv files at about 4:30

If memory serves me correctly I believe if you watch that video closely you will see only one CSV file is used.  There is the term in the narration "Let us move on to another scenario..." then the same csv file is chosen and a different range of records is chose. At no time are there two separate CSV files used together. Cannot be done.

[Catshill]

I need to produce a catalogue that uses three different products on three different master pages that are displayed using three different data sources.

Given that (from the above) I can only use one data source at a time, is this possible/sensible to import each of these as follows:

Add data source 1 and generate output

Using output generated above, add data source 2 and generate output

Using output generated above, add data source 3 and generate output

 

[Catshill]

3 hours ago, anto said:

You can do this and it works. I checked it out. For the second data merge, add a blank page (Master B, for example) at the end and specify the merge range, i.e., only this page. Repeat this for the next master page for new data source.

Thanks. That might be the best way for me to go.