Document 3-up horizontal with variable data on the Sheet

OL Connect Professional
1

I had a very unusual job and wanted to share how I worked it out. Hopefully it will help someone else, including me if there are helpful criticisms.

The data is a spreadsheet. There is data in Column A that we need to capture, the first two rows: cell A1 and cell A2 that need to print as a header on the final “sheet”. Columns B through H contain actual records. We want to print the data 3 records at a time, horizontally, on the sheet.

I used two data maps. One extracts the data from cells A1 and A2 then quits. I have the data map set with a two row boundary, and I have a single extract step followed by a “Stop Data Mapping” action.

In Workflow, I Execute that Data Map, with “Metadata” checked, so that I can read the two mapped values out of metadata and into local variables via Set Job Infos & Variables, Metadata Location.

The second data map is set with a 3-row boundary, and uses Automation Variables to read in the values from the first data map. Those values become the Data Model “record”, and the data from the 3 rows become a detail table. So every record has 3 detail records.

In the template, instead of inserting a Detail Table, I simply layout my “imposed document” as a DIV, and put 3 of those DIVs into a table.

The trick is, for the variables that are used in the DIVs, edit them to hardcode in the proper detail record index (0,1,2).

I create the variable script the standard way, edit it, click “Expand” to see the Javascript, and change the index value to 0, 1, or 2, to get the proper detail record field:

results.each(function(index) {
var field, result = “”;

field = record.tables[“detail”][0].fields[“Contact”];
if (field !== “”) result += field;

this.html(result);
});

And for the variable data on the “sheet’s” header, I use the variables from the Record that were passed in via the first data map.

Hope that helps, and thanks for reading.

2

That’s pretty clever, which is what I would expect from you!

In the past, I would have probably tried to find a better way to do this in order to avoid the unnecessary double-datamapping operation, but we have introduced the option to Stop Data Mapping exactly for these types of processes. The impact on overall performance is minimal since you stop after the first record, and it greatly simplifies the rest of the process.

So I’m glad to see a feature is being used precisely in the context we designed it for, and also to see that someone was clever enough to put it to good use!

Thanks for sharing the technique!

1 Like