Jump to content

Multiplying Data in Excel


rpaterick

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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]);

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

  • 2 months later...

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...