Multiplying Data in Excel

[rpaterick]

I wasn't sure if FP could take data(attached sample.txt) and multiply it based on another column next to it.

 

Customer wants Columns A-E multiplied based on Column F(or QTY.) and also have it output randomly during the run.

 

Would it be easier to just do it in Excel(multiply rows and randomize it) than try to do in FP?

 

Is there an "easy" way to multiply rows based on qty. input in EXCEL, instead of having to copy, drag, and paste?

 

Thanks!

sample.txt

[esmith]

I'm guessing you want something like this.

[rpaterick]

I'm guessing you want something like this.

 

 

Yes. Not sure if that was working or not based on the user post comments.

 

That would be great if that worked or if there is a way in Excel to achieve this.

 

Thanks E!

[Dan Korn]

Just do this in OnRecordStart:

FusionPro.Composition.repeatRecordCount = Int(Field("QTY"));

[rpaterick]

Just do this in OnRecordStart:

FusionPro.Composition.repeatRecordCount = Int(Field("QTY"));

 

 

That worked. Is there a way to randomize it during output?

 

Thanks Dan

[Dan Korn]

That worked. Is there a way to randomize it during output?

You mean you want the records to be composed in a random order? Unfortunately, there isn't really an easy to way to this. FusionPro wants to do things sequentially. That's how it can compose millions of records in the same job, by reading them in and composing them one at a time. Even if you're repeating records, it still generally deals with one record at a time.

 

You could print all the output, then toss it all up in the air.

 

But I think you probably will have better luck using another tool to generate an input file with the records randomly ordered if that's how you need the output.

[rpaterick]

You mean you want the records to be composed in a random order? Unfortunately, there isn't really an easy to way to this.

 

 

Yea, trying to figure out the best route to go. I know how to do it in Excel with selecting all the rows and applying a randomizer on the column, but was hoping to avoid that route.

 

I was trying to tear-apart the Bingo template that FP has and was unsuccessful to see how the randomization works in FP.

 

Thanks anyway Dan, the code you provided would probably help on another print campaign.

[Dan Korn]

Yea, trying to figure out the best route to go. I know how to do it in Excel with selecting all the rows and applying a randomizer on the column, but was hoping to avoid that route.

 

I was trying to tear-apart the Bingo template that FP has and was unsuccessful to see how the randomization works in FP.

 

Thanks anyway Dan, the code you provided would probably help on another print campaign.

Okay, I thought about this a little more, and there is a way to do this all in FusionPro. It involves reading the data in with ExternalDataFileEx, randomly outputting one of the records, and keeping a count of how many of each record are left.

 

First, you need to go into the Data Source Wizard and set your output type to "None". Then you need to add this to the JavaScript Globals:

var DataFile;
var Records;
var totalRecords;

And add this to OnJobStart:

// Replace the path in the line below:
DataFile = new ExternalDataFileEx("C:\\Users\\dkorn\\Downloads\\sample.txt", "\t");
if (!DataFile.valid)
  ReportError("Cannot successfully read/find the external data file.");

totalRecords = 0; // Declared in JavaScript Globals
Records = []; // Declared in JavaScript Globals
var firstRow = [];
for (var rec = 0; rec <= DataFile.recordCount; rec++)
{
   var record = {};
   for (var field = 0; field < DataFile.fieldCount; field++)
   {
       if (rec == 0)
           firstRow.push(DataFile.GetFieldValue(rec, field));
       else
           record[firstRow[field]] = DataFile.GetFieldValue(rec, field);
   }
   if (rec)
   {
       var repeatCount = Int(DataFile.GetFieldValue(rec, "QTY").replace(/\,/g,'')); // / 100 for testing
       totalRecords += repeatCount;
       record._totalToOutput = repeatCount;
       Records.push(record);
   }
}
FusionPro.Composition.composeAllRecords = false;
FusionPro.Composition.endRecordNumber = totalRecords;

Print("totalRecords: " + totalRecords);
return totalRecords;

Finally, add this to OnRecordStart:

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

var recordToCompose = Math.floor(Math.random() * totalRecords);
var recToFind = recordToCompose;
var record;
for (var r in Records)
{
   record = Records[r];
   if (record._totalToOutput)
       recToFind -= record._totalToOutput;
   if (recToFind <= 0)
       break;
}
record._totalToOutput--;
totalRecords--;

for (var field in record)
   FusionPro.Composition.AddVariable(field, record[field]);

[rpaterick]

Awesome, thanks Dan.

 

I will be testing this out hopefully this week.

 

Thanks again!

[rpaterick]

Okay, I thought about this a little more, and there is a way to do this all in FusionPro. It involves reading the data in with ExternalDataFileEx, randomly outputting one of the records, and keeping a count of how many of each record are left.

 

Dan,

 

I've attached the collected FP template and the actual data file.

 

Update. Got everything working except the Barcode. Trying to figure out why it's not seeing the column header at this point or just how to callout a graphic rule at this point.

 

Based on the .eps Barcodes that are supplied from the customer, could FP replicates those if the data had the correct input?

 

Thank You Dan!

FoodCity_Variable.zip

22757_Final.txt

[Dan Korn]

Update. Got everything working except the Barcode. Trying to figure out why it's not seeing the column header at this point or just how to callout a graphic rule at this point.

 

Based on the .eps Barcodes that are supplied from the customer, could FP replicates those if the data had the correct input?

Delete the Barcode rule and add this at the end of OnRecordStart:

FusionPro.Composition.AddGraphicVariable("barcode", CreateResource("0000000" + record.Barcode + ".eps", "graphic").content);

[rpaterick]

Delete the Barcode rule and add this at the end of OnRecordStart:

FusionPro.Composition.AddGraphicVariable("barcode", CreateResource("0000000" + record.Barcode + ".eps", "graphic").content);

 

Dan,

 

Would I need to put

var GraphicName; in the JAVASCRIPT GLOBALS also?

 

Also, how do I assign an image box that would have the field or rule for selection as the drop down?

 

Thanks for your help Dan!

[esmith]

Would I need to put

var GraphicName; in the JAVASCRIPT GLOBALS also?

 

Also, how do I assign an image box that would have the field or rule for selection as the drop down?

After you add the new code to your OnRecordStart rule, you need to add the name of the manually generated variable to your respective graphic frame or inline graphic tag. For the former option, place your cursor in the dropdown where you would normally select a rule and type in "barcode" (without the quotes).

[rpaterick]

For the former option, place your cursor in the dropdown where you would normally select a rule and type in "barcode" (without the quotes).

 

That was too easy. I was going nutz trying to figure this one out.

 

 

Thanks!!

[dreimer]

I was looking into a Bingo card project and I saw mention of a Bingo template in this thread. Does anyone know where I can find that template??

[esmith]

I was looking into a Bingo card project and I saw mention of a Bingo template in this thread. Does anyone know where I can find that template??

Try this thread.