Data Merge Issue - missing data

[E.C.]

Hi All,

 

I'm trying to use the data merge function for my planner layouts.
I followed instructions from several tutorials but there seems to be an issue upon generating the file, as it keeps skipping data, which throws off the rest of the planner dating.

This is how I've set up the fields in my 2 page spread:

This shows what is generated:

 

 

 

Every Friday is missing and the week number is also affected, with some numbers missing. 

Having worked out that there is a pattern to the data that's missing, we adding a line in the excel sheet to act as a spacer, just before each row which has a Friday entry, to see what happens.

This does fix the issue - the only way all the data is completely correct upon generating in Affinity is with spacer rows in my excel file (see below). But this isn't really the solution, as I shouldn't need to have to alter the excel file in this odd way.
 

 

I also created another basic test file, with just a single page spread.

Interestingly, with 7 days of the week placed on the single page, upon generating the file, there was no issue with these initial 7 days. However, the second page onwards encounters the issue of missing data, and the month and week numbers aren't pulling through correctly:

 

I am using Windows 10. I have the most up to date version of Affinity as it was purchased less than a week ago.

I have an older version of excel - thinking this could be the issue, I purchased the latest version and created the file from scratch, but this did not help.

 

I'd really appreciate any suggestions.

 

Thank you,
Elizabeth 

 

[Old Bruce]

2 hours ago, E.C. said:

I'd really appreciate any suggestions.

I think this may be due to the "blank" rows in the spreadsheet.

We would ideally need to see the Spreadsheet and the Publisher document you use to layout the fields.

[Old Bruce]

@E.C.,

I just had another thought, perhaps use Column G for the word Notes.

[E.C.]

Sorry if I did not make myself clear - the only way I can make the data import correctly (without missing data), is to include the blank rows in the spreadsheet. To confirm, adding the empty lines fixed the issue, but it's a terrible workaround, and does not actually fix the underlying problem.

Regarding the 'notes', that's not part of the spreadsheet. It's part of the design on the master page.

I'm happy to share the spreadsheet and the publisher file.
 

[Old Bruce]

2 hours ago, E.C. said:

I'm happy to share the spreadsheet and the publisher file.

That would be great.

[E.C.]

8 minutes ago, Old Bruce said:

That would be great.

Sorry, I'm unsure how to add them?

[Old Bruce]

1 hour ago, E.C. said:

Sorry, I'm unsure how to add them?

Click on the choose files... and navigate to where they are on your computer.

   

This is on Safari on Mac OS your window may look different. There may be a hidden Insert which will appear if you hover the mouse over the name of the file. First as it appears, then with the mouse over the file name.

 

 

[E.C.]

Super

test1.afpub test2.afpub dates_master.xlsx dates_master_with_spacers.xlsm

[Old Bruce]

I am going to have to get back to this tomorrow. Should work but it isn't.

Bye for now.

[tudor]

@E.C. on the right-hand page Data Merge Layout, set the Record Offset to -1.

[E.C.]

5 hours ago, tudor said:

@E.C. on the right-hand page Data Merge Layout, set the Record Offset to -1.

Thanks Tudor,
I can't see how to do this, but it sounds like it might be a sticking plaster (like my spacers) rather than finding the root of the problem...?

[tudor]

@E.C. click on the the Data Merge Layout tool, and then look on the Context bar. You'll see the Record Offset option.

[tudor]

More precisely:

[tudor]

@E.C. I just found the cause of your problems. You have an empty Data merge layout. Delete it. Then you don't have to change anything on the Context toolbar.

[E.C.]

4 minutes ago, tudor said:

@E.C. I just found the cause of your problems. You have an empty Data merge layout. Delete it. Then you don't have to change anything on the Context toolbar.

Thanks so much!! That has fixed the issue on the first file "test1", however, the second file "test2" still has issues (I've attached the up to date file where I deleted the empty data merge layout).

test2.afpub

[tudor]

@E.C. in the second file, delete both Data Merge Layouts for week # and month, and replace them with simple text fields.

[tudor]

Actually, this does fix the days of the week, but you'll have issues with the months

[tudor]

16 minutes ago, tudor said:

Actually, this does fix the days of the week, but you'll have issues with the months

Me again...  As long as the fields for week # and the month are below the Data Merge Layout in the Layers panel, everything seems to work correctly.

I'm slowly starting to understand how this thing works. It's weird, but logical.

[Old Bruce]

I edited the Spreadsheet to get rid of the extraneous information. I also added an empty row after each Sunday so as to give a blank record. The reason for the blank row is explained later.
I edited the Publisher document rather extensively. I changed the layout from having the Data Merge Frames being on the Actual Spread to them being on the Master/Parent page. I then exchanged the pair of Data Merge frames for a single one, a two column four row 'table' over the two pages. 
Having 8 cells for a seven day week is the reason I made the change to the spreadsheet, now we have Monday thru Sunday followed by nothing-day. So nothing-day's empty text from the spreadsheet is placed over the Notes image.
I added some Paragraph styles. The Text Frames for the Month and Week number have some text offsets rather than having those offsets be in the Paragraph style's Spacing section. Just a different way of working, better or worse it is giving the same effect.

Caveats: 
I used Apples Numbers application to open the .xlsx document and also to export a CSV file which I used for the Publisher Document. Once I knew it worked I exported from Numbers an .xlsx file for you as well, this should be fine. I don't like having to change file types using different applications but this should be fine. 
The downside of having the Data Merge fields on the Master/Paren page is that the fields are included in the generated document. There may actually be an upside to having them there but I really don't know for sure.

Day and Date book as edited by bruce.zip

That should work.

------------

A bunch of good advice and a great catch on the empty Data Merge by @tudor. [a kudos emoticon]

[E.C.]

Thank you Tudor - I think changing the month and week# to simple text fields has fixed it (and also changing the order).
I'm going to have a play around, recreating more of my spreads, following these rules, and see how I get on 🤞