Data merge: filling from different excel sheets on one layout page

[Julez]

I'm designing menus for restaurants. My customers have differently formatted menus i.e. a table menu a flyer and an overview which lies next to the register.. They're content is the same but there are changes from time to time (pricing, dishes, opening hours, etc.). I'd love to provide the content with an excel file with the different food categories on separate sheets so that I önly have to finetune the layout after content updates. I've tried it, but the different merge boxes always show the same content from one excel sheet instead of the content of the different sheet as they're supposed to. Any ideas how I can realize my menu projects without editing the content separately in every single format? - Many thanks in advance! Julez

[Old Bruce]

Just to be clear in my own mind am I correct in assuming that there are three menus and one set of products with their prices?

5 hours ago, Julez said:

I'd love to provide the content with an excel file with the different food categories on separate sheets

To make your life easier it would be best to use one sheet. Have the Burger listed and have the various prices, sit-down/take out/delivery/etc all in that sheet, it will look unwieldy but it will make your life easier.

[thomaso]

Hi @Julez, welcome to the Affinity Forums!

In addition to @Old Bruce's hint about collecting all data on 1 common sheet, this video (and the entire thread) may shed some light on your desire to use several sheets or data sources:

[Julez]

Hey guys, thanks a lot for your quick and friendly answers!! @Old Bruce, yes there is one set of products (with descriptions and prices) and three differently formatted menus. This is, how the flyer is supposed to look like (Screenshot 1). But I can't make it work, that two (or more) merge boxes on one layout page show different content (Screenshot 2). Screenshot 3: my Settings.

[Julez]

By the way, here I've put all the content in one xlsx-sheet already, as you suggested.

[Julez]

Dear @thomaso, thanks for your reply! I had a look at this video, I've tried to make my menu work like this, but it seems to me it doesn't because I'm working on one layout page. I have the feeling, that I can't fill two merge boxes with different content on one layout page.

[NathanC]

Hi @Julez,

After collating your menu data onto a single spreadsheet have you tried using the Data merge layout tool in Publisher? Using the layout tool allows for multiple records to be used on the same page sequentially in each grid cell on a page, avoiding the problem shown in your earlier screenshot of the same record being repeated on the same page.

In the example below, I created a menu with 8 items on an .xlsx and split this across four separate data merge layout layers in my data merge template. To ensure that the records ran sequentially I put Section 1's data merge layout at the bottom of the layers stack and 4 at the top.

When I generate the Merge using all 8 records, this then generates a unique record in each field on the same page in sequential order.

I'd recommend reading the help guide to get a better understanding of how the tool works before trying: https://affinity.help/publisher2/English.lproj/pages/Tools/tools_dataMergeNode.html

[anto]

@Julez  This kind of catalog is very difficult to make in Publisher, given that you have different lengths for titles and descriptions and you cannot predict the height of the cell.
This is very easy to do in Word with a few mouse clicks. Watch the video

 

@Натан К  could you help promote this method as a priority? Thanks

 

[anto]

@Julez With Publisher, you can create even such complex catalogs (someone once asked for help on the forum here and I managed to do it), but only if you have the same type of elements with approximately the same length.

 

[Julez]

Hey guys, you are amazing! Unfortunately I don't seem to be able to do the same. Here your see my setting: In the background is my layout, on the right my excel-table and in the middle the merge-settings. To be continued ... Screenshot 1:

[Julez]

When I hit "generate" now, I'm getting this:

[Julez]

I don't get in my head how to "tell" the merge boxes which content belongs to which. And in this case it simply left the second box unfilled ... ? I don't understand how it works

[anto]

Could you attach your Excel and Publisher file, leaving just a few demo data?

[Julez]

You are very supportive, thanks a lot!!

Merge-Test-DaVinci-Flyer-Forum.afpub DaVinci-Menu-Merge.xlsx

[anto]

The way you have organized the data is very difficult to do what you want.
Why?
1. You have a different number of items for each product.
2. On Publisher page, the names of the sections are already entered. With this organization, you will get the following section Pasta under the wrong heading.
I suggest that you reorganize the data in the following way.
Take a look at the video.

Try to use p. 2 with sheet "Menu" and then page 3 with sheet "Menu_2"

DaVinci-Menu-Merge.xlsx

Merge-Test-DaVinci-Flyer-Forum.afpub

Edited April 5 by anto
Added new files. See also next post.

[anto]

And more simplest way. Change your data in Excel like in video. You can also create a text style for each field so that you can quickly edit everything after the merge.

 

[Julez]

Dear Anto, thanks for your reply! I've noticed it but couldn't watch it by now since the weekend is packed. I'm going to watch it tomorrow morning and answer then. Thanks a lot already for your efforts!!!

[Julez]

Dear Anto, thanks for your reply! I'm sorry for beeing quiet for such a long time, I really tried to figure out what you did here, but still could't make it unfortunately. It is fascinating how you manage to make it work, but even when I work along watching your video (the last one), I still don't get a reasonable result. I don't even understand the logic when I watch your video. It doesn't make the slightest sense for me, that the merging boxes overlay each other and when I try it anyway, it generates the content still unsufficiently.

I do get the basic merge system of affinity (for layouting business cards for example), but obviously I can't adapt it for my case. What a pity, but I guess I have to throw the towel here.

Thank you so much for your efforts. I'm really impressed by your skill and supportiveness. Thanks a lot!

[anto]

4 години тому Julez сказав:

that the merging boxes overlay each other

The essence of this method is as follows.  In one place in the Publisher, you put values from two cells from Excel.
That is, you take values from two columns that overlap each other.
If there is a value in cell A1, it is inserted, respectively, cell D1 must be empty so that the values do not overlap.
Then you get the following.
A2+D2 === A2 (because there is nothing in D2)
A3+D3 === D3 (because there is nothing in A3)
A4+D4 === D4 (because there is nothing in A4)
.......
A10+D10 === A10 (because there is nothing in D10)

[Julez]

Hey Anto! Okay, I get that, thanks for explaining this in detail! I guess I'll still need quite some time experimenting , but well, noone is born a master!