Data Merge and Variable Entries

[CognitionFailure]

I'm looking at using Publisher with Data merge in order to make my content and I'm running into a bit of a roadblock with my conceptual understanding of how it works for my context.

I have two types of entities, lets say people and items. There will be a data file for each, say people.csv and items.csv.

Each record in people.csv will correspond to a single page, so that is easy enough and works very well with the data merge manager (can we call it DMM?).

The hard part is that each person can have multiple different items. Ideally I could define all of my items once in items.csv, and specify for each person record which items it has, ie person A has items X,Y,Z. Person B has items W and Z, etc.

I'm stuck on how to use DMM to do this. My best guess right now is doing a two step process of making another document that is purely items. I would use DMM here to generate docs which I would then export. I could then specify in the 'people' csv which item images to merge into each person record. The items could be formatted like table rows.

Is there a better way of doing something like this?

[Komatös]

Hi @CognitionFailure and welcome to the forums.

With Publisher it is not possible to use more than one data source for data merging at once. You must therefore merge the person database and the item database into one data source.

[Old Bruce]

You need one CSV file. Make one spreadsheet (which will generate the .CSV file) and have one column named People and a second column named Items then each row you put the name of the people in the People column and their stuff in the Items column. You can even have Address and Pet columns in your spreadsheet that you don't have to use when you go to generate the Publisher document.

[lacerto]

If you have Excel, you could simulate a relational database by using data lookup using 3D tables and have a possibility to refresh complex data without needing to recreate relations. Similar functionality is possibly available also in free LibreOffice Calc and Apple Numbers. Then you could update data merge layout to get up-to-date data and regenerate to get updated merged documents.

[CognitionFailure]

Best to provide an illustrative example, see pics. Because each row corresponds to a single 'person' page, I don't think it is as simple as making item columns because each 'item' has its own information as well.

 

45 minutes ago, lacerto said:

If you have Excel, you could simulate a relational database by using data lookup using 3D tables and have a possibility to refresh complex data without needing to recreate relations. Similar functionality is possibly available also in free LibreOffice Calc and Apple Numbers. Then you could update data merge layout to get up-to-date data and regenerate to get updated merged documents.

 

Thanks for the idea - I think this could work, but the spreadsheet would be quite massive. Each person would end up having an additional 56 columns. The spreadsheet could have a number of dropdown/lookup style cells which could macro in the relevant columns from a different table. It wouldn't be clean to look at or design but it would take place in a single step as far as affinity is concerned.

example item:

Edited July 8, 2022 by CognitionFailure
adding picture caption

[Old Bruce]

5 minutes ago, CognitionFailure said:

Each person would end up having an additional 56 columns.

So?

If you are going to be doing this then make a spreadsheet with all the various data. That is what I would do. Sixty or eighty columns if that is what it took. It will be quite easy to update and maintain all that information and add new players and information in a spreadsheet or database.

[CognitionFailure]

My concern is readability. The spreadsheet isn't only a datasource but a reference for myself as the designer of what all the values are at a glance, and having it be easy to read makes it easy to edit/update. A 70+ column spreadsheet isn't easy to read quickly, especially if I need to remember which rows I need to update for a given column whenever I change an item.

However I can probably do what @lacerto suggested and have a secondary spreadsheet of items where I can make changes that will get reflected out across the entire spreadsheet. It's just a lot of upfront work. If the lookup cells for those are a single, easy to read cell then the readability problem is largely mitigated.

It would be cool if Affinity had substitution logic in DMM one day though.

[Old Bruce]

22 minutes ago, CognitionFailure said:

However I can probably do what @lacerto suggested and have a secondary spreadsheet of items where I can make changes that will get reflected out across the entire spreadsheet. It's just a lot of upfront work.

Start small then add things. Plan on using lacerto's suggestion and make some small spreadsheets with closely related stuff and then one big spreadsheet with everything called from the smaller easier to read spreadsheets.  

I would start by making a Numbers Document with three Sheets containing the Blue items and then add more of the items as I go. There would be a fourth sheet that would reference all the columns in Sheets 1, 2 and 3. Note that you may need to have Name be Name_1, Name_2 and Name_3.

You may well want to have more Sheets and organize them differently, you are, I assume, the maker of the game so you know which things go together most logically.