Merge multiple records into one?

I am hoping this is possible. I'm sure it is, I just don't know if I need to do it at the data stage (in Excel) or if there's some way to do it in Fusion Pro.

 

I have data which has a salesman and then their account. Some have multiple accounts, some do not. For example:

 

Name | Account

John | Jones Plumbing

John | Smith Electrical

Scott | Western Apparal

Gary | Southside Dental

Gary | Northern Lights

Gary | Blue Seas Fisheries

 

I would like John to merge into one record and the second account to go into a field named Account2.

 

Can I do this in Fusion Pro? Or do I need to get the data reworked?

 

KayEll

Yes, you can do this in FusionPro, using the ExternalDataFileEx object to load in the data as a secondary data file.

 

Normally, a FusionPro job has a data file with one line per record, which drives the composition to create one output record per line in the data file. In a statement-type job like what we're talking about in this thread, a secondary data file is typically used in conjunction with the primary data file, as in this example, and a field in the primary data file is used as a "key" to look up corresponding values in the secondary file. However, in this case, there's only the one file, so there's no primary key value to drive the composition and look up values in the secondary file. But we can still make this work.

 

The first thing you need to do is to set your job to not have a main input file. From the menu in Acrobat, select FusionPro -> Data Definition -> Wizard, click "Next," select "None," click "Next" again, then "Finish."

 

Now, add the OnRecordStart callback rule (FusionPro -> Edit Rules, click "New," click "Callback," select OnRecordStart, and "Next"), and paste this into it:

var XDF = new ExternalDataFileEx("sample.txt", "\t"); // <- YOUR DATA FILE NAME HERE
if (!XDF.valid)
  ReportError("Cannot successfully read/find the external data file.");

// Populate an array of record objects, each with a name and an array of accounts:
var Records = [];
var previousName = "";
for (var rec = 1; rec <= XDF.recordCount; rec++)
{
   var name = XDF.GetFieldValue(rec, "Name");
   if (name != previousName)
       Records.push({name:name, accounts:[]});

   Records[Records.length-1].accounts.push(XDF.GetFieldValue(rec, "Account"));
   previousName = name;
}

// Set repeat count:
FusionPro.Composition.repeatRecordCount = Records.length;

// Now set up variables for this record.

// Clear Account variables for a new repeat:
for (var i = 1; i <= XDF.recordCount; i++)
 FusionPro.Composition.AddVariable("Account" + i, "");

// Set up variables Name, Account1, Account2, etc., for use in text frames:
var ThisRecord = Records[FusionPro.Composition.repeatRecordNumber-1];
FusionPro.Composition.AddVariable("Name", ThisRecord.name);
for (var i = 0; i < ThisRecord.accounts.length; i++)
   FusionPro.Composition.AddVariable("Account" + (i+1), ThisRecord.accounts[i]);

// Globals to use in other rules instead of calling the Field function:
Name = ThisRecord.name;
Account = function(i) { return ThisRecord.accounts[i-1]; }
NumberOfAccounts = ThisRecord.accounts.length;

return Records.length;

You'll need to change the file name, and possibly the delimiter, on the first line. (I assumed it was a tab-delimited file named "sample.txt". Also, if the file is in the same folder as your template, you only need the file name, not the full path.)

 

Please note that this assumes that all the records for each salesman are contiguous in the data file, that is, you can't have a line for John, then a line for Scott, and then another line for John; all the "John" records need to be together. (If this isn't the case, the logic can be reworked.)

 

Once you have that rule in place, you should be able to use the Name variable, as well as variables Account1, Account2, etc., in the Variable Text Editor for text frames, just like you would use other text fields. (You'll need to type each variable name into the Variable drop-down list, though, before clicking Insert.)

 

If you have any other rules doing things with the Name and Account fields, you'll need to change them just a bit. Instead of calling Field("Name"), you can just use the Name variable directly. And instead of calling Field("Account"), you need to call the Account function with an index, such as Account(1). I also added a NumberOfAccounts variable for other rules to access that information.

 

However, I'm presuming that, instead of calling Account(1) and Account(2) in your rules, or referencing the variables Account1, Account2, etc. directly in your text frames, you'll be doing something with the entire array of accounts to generate a report (statement) for each salesman's output record. Here's an example of another rule which generates a very simple statement for each salesman:

if (FusionPro.inValidation)
   Rule("OnRecordStart");

var result = TaggedTextFromRaw(Name) + "'s Accounts:"; 

for (var i = 1; i <= NumberOfAccounts; i++)
   result += "\n<br>* " + TaggedTextFromRaw(Account(i));

FusionPro.thisRuleReturnsTaggedText = true;
return result;

You should be able to do something similar to present the data however you want in the output.

 

The only real downside with this approach is that you'll only be able to preview the first record in FP Desktop; you need to compose to see all the records. The way around this is to create another data file which just has the names, one line for each salesman name, and use that to look up the account names in the secondary file, again, just like this example.

 

Finally, we're looking at enhancements to FusionPro to simplify these kinds of data merging operations for statement jobs without scripting (although the GUI to define how to merge the data could easily be just as complicated, and potentially confusing, as the JavaScript code), and to be able to preview merged records. But for now, this approach will work.

I'm so glad that I found this! It worked perfectly for a project I am working on. Is it possible to remove the Name of the Sales person from the list of Accounts in your example? I need to list the Accounts in a text box with columns and the Sales person in another text box.

I figured it out. Nothing like a good night sleep to refresh the brain. I knew it would be easy.

 

 
if (FusionPro.inValidation)
   Rule("OnRecordStart");

var result = ""; 

for (var i = 1; i <= NumberOfAccounts; i++)
   result += "* " + TaggedTextFromRaw(Account(i)) + "\n<br>";

FusionPro.thisRuleReturnsTaggedText = true;
return result;