Data merging from wrong source despite unique field names

[Meanderling]

I'm working on a calendar based on what @Hangman provided in this thread (great work btw & thanks for sharing!). However I want to add mini calendars for the previous and next months, similar to the template @EatMoreBacon kindly shared here (also great work & thank you!).

The main calendar on its own works perfectly, but when I add the previous/next months (with their own data source and unique field names), all 3 calendars merge from the main data source (ignoring previous/next sources) - this results in 4 output pages instead of 12. 'Preview with record' shows the correct data for each calendar (confirming it can be 'seen' by AP) - so why is it being ignored when merged? 

Each calendar is a separate Data Merge Layout, structured as per @Hangman's example, ie 1 data merge cell, replicated into a 7 x 6 grid. This is quite different to how @EatMoreBacon's template is structured, ie 1 column containing data for 7 days, replicated over 6 rows. It also has DML nested within DML , with the outermost being 1 column x 1 row, with 0 Record offset and 0 Record advance. Overall it's more complicated, but perhaps that's why it works and mine doesn't..? In which case, I'd love to know how you arrived at this solution, as the docs aren't particularly helpful...

In terms of what I've tried - I've searched through loads of threads, changed layer order, grouped and ungrouped layers, nested DML within DML (a la @EatMoreBacon), played with offsets, etc. I've also changed the data for the previous/next month's calendars and can confirm that the correct data shows in the preview, but is not used in the output, ie only the data for the main calendar is being merged. The only other difference I haven't tried (yet) is the data orientation - mine/Hangman's data is aligned vertically (works fine for 1 calendar), whereas EatMoreBacon's is aligned horizontally.

I could probably reverse engineer @EatMoreBacon's example, but would actually like to know if this is a bug or expected behaviour - does anyone know?

Many thanks in advance!!

Edited January 17 by Meanderling
Improved title & body for clarity

[lacerto]

I think that you need to have data for previous and next months in the same source as the data for the main calendar. Separate sources are useful when there is a consecutive layout where you first fetch information for one part of the document, and then for another part, but when you need to fetch complex data (e.g. date information for three successive months) for each composed page, you need to use common data source.

After getting data for the main calendar, you would use negative offset and go back to iterate through the previous month, and when done, again use negative offset and start iterating through the next month. You would need three data merge layout tools for this.

[Meanderling]

Hi @lacerto,

Thanks so much for responding, much appreciated!!

Sorry, maybe I wasn't clear - I'm using a single data source with a separate columns for the main, previous and next months data. These all have unique names and when I preview, it shows the correct data in the correct fields (see image). As you can see, each calendar has its own data merge layout and as mentioned, each calendar has its own unique field names.

I thought it would be easier and less error prone to manage the data in the source, rather than via offsets. Or are you suggesting running the data merge multiple times?

I did try changing the offsets before posting and again today after reading your response, but it's hard to tell what's going on, as the preview is being temperamental... I have to select/turn on the 'Preview with record' for each calendar separately - sometimes it'll only show the data/some of the data, sometimes it won't. Likewise, advancing through the records, sometimes it'll update one or none of the calendars... Perhaps my file is corrupt? However, I did create the above (grab) as a new, simplified version of my actual document, just in case the original document was corrupt or causing issues for some other reason.

If the field names are unique, I still can't see why AP would ignore them, as the data could be completely unrelated. For instance, I could be pulling in data from another year entirely, in which case this still wouldn't work. 

Thinking about it more...I think the issue is probably because I have my data arranged vertically (akin to MS Mail Merge, if I remember correctly). By chance it works for a single month (in which case offsetting might work), but not for multiple months. 

If I'm not mistaken, I think AP can only read data from left to right (column by column), top to bottom (row by row) - with the field names being more 'relative' than 'absolute'. If anyone can confirm, that would be helpful, as I don't recall seeing that documented anywhere.

I think I need to restructure my data a la @EatMoreBacon's example (see below).

Thanks for your help!

 

[lacerto]

4 minutes ago, Meanderling said:

Or are you suggesting running the data merge multiple times?

No, the merge would be performed only once (for the whole year), but using negative offsets to do multiple iterations within the same data table (per page, main calendar month). Offsets can be a bit tricky but you should be able to preview their working by choosing appropriate preview records. Offsets need to be equal for each month. I have months aligned in a 6 row 7 column table so that dates of successive months are aligned by weekday, so i have negative offset of -42 when iterating through month before and month after (after having fetched day data for the main calendar).

 

[Hangman]

2 hours ago, lacerto said:

Offsets can be a bit tricky but you should be able to preview their working by choosing appropriate preview records. Offsets need to be equal for each month. I have months aligned in a 6 row 7 column table so that dates of successive months are aligned by weekday, so i have negative offset of -42 when iterating through month before and month after (after having fetched day data for the main calendar).

I couldn't set a negative offset greater than -32 so I had to adopt a somewhat simplistic approach which works though is perhaps not the most elegant...

Though it’s just occurred to me that it’s likely because I don’t have a large enough data set to offset greater than -32 so I need to revise accordingly which will make for a much neater solution…

2025 Calendar UK.zip

[Meanderling]

Thanks to both of you for getting back to me, I really do appreciate it!!

@lacerto Thanks for clarifying the merge was for the whole year, I thought that's what you meant, but merging a merged doc is also a possibility, so I thought it best to check. Yes, offsets are tricky, especially when AP isn't playing nicely. As mentioned AP is showing the preview sometimes, but not others and sometimes it'll advance through the data, sometimes it won't. Either way it's hard to know what it's showing me...

Regarding the below quote, are you referring to your data source? If so, it's the same/similar configuration to @EatMoreBacon's example (screen shot above). Whereas mine is 3 columns (current, previous, next), 42 rows (per month), which is the same/similar to @Hangman's example. And as you said, the offset needs to be the same for all months, which I tried, but got even weirder results.  However, I've had a bit of a revelation looking at @Hangman's response & example docs compared to my own...

1 hour ago, lacerto said:

I have months aligned in a 6 row 7 column table so that dates of successive months are aligned by weekday, so i have negative offset of -42 when iterating through month before and month after

 

@Hangman Thanks so much for your input here - the penny has dropped somewhat, as to what AP's doing...  As mentioned, I didn't have any issues with high negative offsets, as far as I know, despite the fact that overall it didn't work for me. However, looking at your example, I couldn't work out where the merge was getting the following month's data from - considering you've only got data for the current and previous months in the data source.

1 hour ago, Hangman said:

I couldn't set a negative offset greater than -32 so I had to adopt a somewhat simplistic approach which works though is perhaps not the most elegant...

Then it dawned on me that you're actually leveraging how AP behaves and the document's layer order (which is bottom to top), to get the right result without the need for any offsets at all...very clever!! I actually think it's an extremely elegant solution - it requires less data than mine and really helped me see what's going on 'behind the scenes'.

I've tended to think about it like a developer with a database, which isn't the case (I'm not quite a developer & a spreadsheet isn't exactly a database), so seeing your extension to your original example was really helpful. 

My head's spinning, but thank you both so much for your help!! 

p.s @Hangman Does 'Preview with record' work consistently for all 3 groups or is it a bit hit and miss for you as well? For me, as with my file, sometimes all 3 will show data, but typically if I select one of the groups, one or more of them will stop responding until I've clicked around enough to get them all back in sync again...

[lacerto]

3 hours ago, Meanderling said:

As mentioned AP is showing the preview sometimes, but not others and sometimes it'll advance through the data, sometimes it won't

Yes, there are issues with refreshing of previewed record (when that is changed).

The limitation with the offset seems to be a weird macOS related bug. As can be seen, it is possible to work around this issue. I personally find the offset feature pretty handy as that allows rather complex merge layouts.

Please find attached my data merge layout, perhaps it helps you to spot the issue with your setup.

Calendar.csv

calendar2025.afpub

calendar2025_merged.afpub

Btw, I normally work with Excel sheets as that allows very effective processing (e.g. date calculations), including macros and scripts, e.g., to create language- and year-versions of calendars, with images, and variable data (holidays, moon phases, etc.) easily. There are still many bugs though, so e.g. results of string functions are not transferred to data merge, and therefore it is often necessary to create a .csv source (or flatten Excel files in other ways).

[lepr]

15 hours ago, lacerto said:

The limitation with the offset seems to be a weird macOS related bug.

Unsurprisingly still not fixed in current beta 2.6.

[Meanderling]

@lacerto Thanks for sharing your docs! It's interesting to see the 3 data sources together (see image) and that they're all vertically aligned.

 

I'm still not entirely sure why mine didn't work as anticipated in my (numerous) tests. However, I'm merging 2 dates per cell (as is @Hangman), whereas you're merging just 1. Likewise @EatMoreBacon is merging more than 1 field at a time, but that data source is arranged horizontally and the AP configuration is also quite different. For that reason I haven't ruled out the vertical vs horizontal configuration of the data, combined with the layer order in AP being factors. 

I could 'hack' my way to a result, but I like to at least try to understand why something does what it does, which is why I found @Hangman's solution so intriguing. I will continue playing with the files to see what I can learn, but at least now I have working versions to compare with

With regard to your comment re data processing...

20 hours ago, lacerto said:

There are still many bugs though, so e.g. results of string functions are not transferred to data merge, and therefore it is often necessary to create a .csv source (or flatten Excel files in other ways).

I use LibreOffice for data processing, date calculations, etc, which I save as .xlsx files for use in AP. Have you tried formatting the result as text within the function itself, ie wrap the function in another function that outputs the overall result as a string? I've found that works for me - give it a try. Good luck!!

 

[lacerto]

6 hours ago, Meanderling said:

whereas you're merging just 1.

I did this just for simplifying the demonstration. As mentioned, in my calendars, I have multiple source fields accesses per record, like Week number, moon phase, holiday, color (free holidays), etc.m, so it is certainly not a limitation. 

6 hours ago, Meanderling said:

Have you tried formatting the result as text within the function itself, ie wrap the function in another function that outputs the overall result as a string?

Yes, there are multiple ways to "flatten" Excel data, but having the end result converted to .CSV is the simplest. I always keep the .XLSX as a source (I occasionally also use Libra Office Calc and Numbers, but mostly just for testing).

I hope you manage to find the most suitable method for your purposes, for me the "vertical" alignment, as you call it, feels most natural, but the way source data is organized, and technical reasons (like limitations in using record offsets) may certainly encourage to use alternative layouts!

[anto]

@Meanderling you can look also this thread

you can see how to make such and other types of calendars and how to organize fields for merging

  

[Meanderling]

11 hours ago, lacerto said:

I did this just for simplifying the demonstration. As mentioned, in my calendars, I have multiple source fields accesses per record, like Week number, moon phase, holiday, color (free holidays), etc.m, so it is certainly not a limitation. 

Fair point. Maybe it's a date 'thing'...? Ultimately, at this point I think it's fair to say, I still don't really know why the merge failed - it went from outputting 12 calendar pages to only outputting 4 when I added the additional date merge.

 

Yes, a 'flat' CSV file is probably the simplest and most battle tested! The vertical arrangement feels the most natural to me too - will continue tinkering, but thanks so much for all your input!!

 

@anto Thanks for the link to that thread, it looks super interesting!! It didn't come up in any of my searches, but I look forward to translating it. Hopefully Google can do a good job...I did Italian at school, but that was a long time ago!  

 

[anto]

6 minutes ago, Meanderling said:

but I look forward to translating it

Install a browser add-on that translates web-pages.

[Meanderling]

31 minutes ago, anto said:

Install a browser add-on that translates web-pages.

In Chrome, right click > Translate to English

I'm reading it now...HTML, JavaScript, JSON oh-my! 😍