Data Merge Lookup (split from Custom Text Variables)

[LS_67]

Hi, I hope this is the good place to ask :

should it be possible to link Item name and price to unique item number of a merge file ?

Merge system in affinity is a great function, but incomplete to make catalogs for instance, because each time, a new document is created, like in Word or Libreoffice. 

We need a system which can update fields, for example price update with modified merge Data files.

So in the Text Variable saved should be a field (ex: price column x) refering to an unique value of the column 1 (item Nr) of the merging Data file.

I'm rather new with Publisher and perhaps these functionalities are available in an add-on, but I didn't find. 

thank you

[Hangman]

Hi @LS_67,

You can obviously do a 'basic' data merge for a price update, so something like this...

 

But I get the impression you want to do something slightly more involved... could you perhaps elaborate a little or provide a screengrab demonstrating roughly what you are looking to accomplish in terms of layout.

I suspect what you want to achieve may not be possible using a data merge owing to the modular nature of how it works but I may also not quite understand what you are looking to achieve.

[LS_67]

Hi Hangman,

thanks for replying and for the video. I will try it again.

What I meant was more like the VLOOKUP function in Excel, applied to a text Variable which could be an Item Nr.

So, in a bloc of Text the Variable refering to an unique Item Nr (first column of the merged data file), and in other blocs field values of the found row of this item (price, description, picture Address, ...

I have just to leave now, I will try to make an example and send it here

[Hangman]

Hi @LS_67,

Thanks for the update, I can't immediately think of any way to achieve Data Merge VLOOKUP type functionality in Publisher but yes, if you can provide an example it may be that there are other approaches that could be adopted...

[LS_67]

Hi Hangman,

as attached files a very simple example : 

JSON : folder for the 2 versions of my data to merge

Model.afpub = model for merging data
Merged1.afpub = 1st result of the merge
Merged1_modified.afpub = insert a picture and put the item in a new page at end of ducument

then replace the ART.json file with the JSON/b_version/  in the main folder and in Publisher reload the file.

Merged2.afpub is the new result of the merging update. This will be identical to Merged1.afpub (only with new Data), but all modifications (insert picture, move item) are lost in this new document.

 

PublisherTest.zip

[LS_67]

please show to this video : https://player.vimeo.com/video/430080228

this is a complete and commercial plugin to Indesign, but it shows the link between Data and merged documents to update them.

The goal is to automate, as far as we can, the creation of our catalog which has actually approx. 300 pages and create versions in other languages.

But perhaps, there are actually other solutions than merge Data, as import Tags to create automatically Text boxes, picture boxes, and insert content ?

[Hangman]

Hi @LS_67,

Thanks for the files, that is really helpful...

If I've correctly understood the steps you've outlined then the 'issue' you have with inserting the picture frame into the Merged1_modified.afpub file is that the merged file no longer has any merge capability so replacing the ART.json file with the JSON/b_version/ in the main folder and reloading the file in Publisher isn't going to have any effect.

If the intention is to include a picture, as per your Merged1_modified.afpub file and then change the data source you would have to set the source merge file up slightly differently, see the Modell_New.afpub file (attached) and then change the data source on the source file rather than the merged file though that would result in two merged files, using the respective data sources, both with a picture in the same position on the merged page, which may or may not be what you actually want?

In terms of the DesignMerge® Catalog plugin for InDesign (which is excellent and provides a huge amount of flexibility and would be extremely welcome), this I would say at the current time, is likely way beyond the scope of Affinity Publisher. Serif would need to open up its software to third-party developers and provide plug-in support to enable this level of functionality.

I have no idea what the current roadmap is for Publisher and whether or not there are any plans to allow third-party developers to write plug-ins for Publisher or for Serif to provide plug-in support in a similar vein to Affinity Photo's plug-in support and I don't think Serif would comment on any future plans in a public forum so it's difficult to know if and when this is ever likely to happen.

PublisherTest.zip

[LS_67]

Hi Hangman, thank you for your quick answers.

I'm not actually using the Beta version, and the current version of Publisher cannot open your file.

This is not really an 'issue', but I'm just searching for a solution to our needs and thought this new functionality may help.

I will ask for Beta version to see what's really usage of Text variables.

thanks

[Hangman]

Hi @LS_67,

22 minutes ago, LS_67 said:

I'm not actually using the Beta version, and the current version of Publisher cannot open your file.

Oops, a V2.1.1 version is attached, the file is now called Modell_Nei.afpub

[LS_67]

Ok I have tested your page under Beta 2.2.

This is quite good, but does not resolve our problem. In attached file an extract of our Catalog.

I think we will create for each category of products a simple merged file (to be updated) and the real document where to copy blocks of items coming from the merged file.

Is there any way to programaticly create elements in Publisher from Text like XTags does ? http://emsoftware.com/products/emtags/
This might be another solution

Catalog_extract.pdf

[Sean P]

Hey all, I've split this into its own thread, as its more of a Data Merge request rather than what the Custom Text Variables feature is.

[MikeW]

2 hours ago, LS_67 said:

...

Is there any way to programaticly create elements in Publisher from Text like XTags does ? http://emsoftware.com/products/emtags/
This might be another solution

Plug-in technology as used with InDesign, which is also what QuarkXPress XTensions are, will come one day in the future to Serif's Affinity applications, as will scripting. But the when these technologies will become available only Serif knows and they will not commit to revealing a timeline. You can watch the changelogs as that's when we common folk will know.

That said, I cannot imagine developers such as Em Software, Meadows et al that develop add-ins for InDesign and/or QuarkXPress, that sell the add-ins for hundreds, sometime thousands of dollars, would invest the time and money to develop these add-ins for Serif's products. Even if one or more would try the waters, low sales would mark the end of such development quickly--which is what happens routinely with developers regarding the other applications (like Em Software and Meadows dropping QXP development).