Creating a letter with specific names

[oddflavor]

So I have a project where I need to pull names and place them on a letter.

 

I have an Excel file with 3500+ names. Those names are sorted by office number of which there are 360 offices.

 

Sounds simple enough, right? Well here's where it gets a little difficult.

I have to place only the names of the people who are in the same office on the same letter. So when I'm done I need to have 360 letters and each letter can only have the names of the people who are in that specific office listed in a column.

 

My data looks something like this:

 

FIrst Name Last Name Office Number

Tom Jones 001

Lisa Jones 001

Billy Jones 001

Joe Smith 002

Howard Evans 002

Mark Swan 003

Tina Epstein 004

George Winston 004

Wallace Morgan 005

 

So how can I pull only the people from Office Number 001 and have their names listed (in a column) on one letter and then have only the people from Office Number 002 and list just them and so on and so on...

 

I am using Fusion Pro Desktop 7.2

 

Thank You!

[tou]

http://forums.pti.com/showthread.php?t=4714&highlight=external+data

 

Lots of resources to search from but the one above is a good start.

 

I don't personally have experience in this, but it looks achievable via rule scripting.

 

Good luck.

[oddflavor]

Thank you. This looks like it could be way over my head.

 

I only use FP for simple jobs that require some personalized fields and numbering tickets.

 

Thanks again.

[step]

The thing to keep in mind is that FusionPro, by default, will compose your template (the letter) for each record in your data file. In your case it sounds like your data is set up in such a way that each record (row of data) is a person's name. So one letter would be created for Tom Jones, another separate letter would be created for Lisa Jones, and so on. Ideally, what you'd want is for each record to compose a letter for the Office Number.

 

So if reworking your data is an option, you could make it look like this:

"Office Number" "Workers"
"001"           "Tom Jones,Lisa Jones,Billy Jones"
"002"           "Joe Smith,Howard Evans"

On a side note, I can't help but feel like there is some serious nepotism at office 001.

 

Anyway, then for each record you could create your list by splitting the comma separated "workers" field and joining it with a line break:

return Field("Workers").split(",").join("<br>");

 

In terms of using an external data file, something like this might work for your particular case and wouldn't require you to rework your data:

OnJobStart

FusionPro.Composition.compseAllRecords = false;
FusionPro.Composition.endRecordNumber = 360;

YourTextRule

var ex = ExternalDataFileEx(FusionPro.Composition.inputFileName, ',');
var list = [];
for (var i = 1; i <= ex.recordCount; i++) {
 function ExField(name) { return ex.GetFieldValue(i, name); }
 var officeNumber = ExField("Office Number");
 if (officeNumber == FormatNumber('000', FusionPro.Composition.inputRecordNumber))
   list.push(ExField("First Name") + ' ' + ExField("Last Name"));
}
return list.join("<br>");

 

You might find that you need to add the path to your data file rather than FusionPro.Composition.inputFileName when trying to preview the template.