Jump to content

External Data File Sample - Very Simple Statement


Admin1676454018

Recommended Posts

This sample job demonstrates the ability in the FusionPro VDP products to link to multiple additional external data files, perform a lookup in those files, and return results in the composition process.

 

To demonstrate this capability, the sample job is of a very simple statement that contains multiple items for each recipient.

 

The main input data file contains a customer name and a CustomerID field. There are 3 records in the input data file so 3 statements will be created. Here's the data in the main input data file:

 

Name     CustomerID
Willis   123
Linda    456
Gerry    789

A secondary data file also exists. This data file has the fields CID (the customer ID), Product Purchased, and Price for that product. For each customer, there can be multiple products in this secondary data file and all must be returned in a single statement for the customer. Here's the data in the secondary data file:

 

CID    Product-Purchased                   Price
123    LCD TV                              $1495.00
123    Surround Speakers                   $279.00
123    Tax                                 $141.92
456    Home Media Server                   $2,499.00
456    Universal Remote                    $50.00
456    Media Zone Network Adapter          $79.99
456    Wireless Keyboard/Mouse Combo       $109.00
456    Tax                                 $219.04
789    Goo-B-Gone                          $9.95
789    Tax                                 $0.77

Using the FusionPro JavaScript function "ExternalDataFileEx()", this sample job does the following:

 

 

  • Link to the secondary data file with the purchase information in it (this is done in an OnJobStart rule)
  • For each record in the main input data file, find all items purchased by that customer by cross referencing the "CustomerID" field in the main input data file with the "CID" field in the secondary input data file.
  • Apply basic formatting to the display of the items purchased.
  • If the purchase item is "Tax", format it differently from the rest of the line items.

This sample has additional comments within the JavaScript code that explains the logic in greater detail.

External-Data-File-Example.zip

output-charges-summary.pdf

Link to comment
Share on other sites

Hi all,

 

Just a comment on this job, I wouldn't go so far as to call this a "statement" as it doesn't contain much more than a list of charges for each customer in the primary input data file.

 

This is a simple demonstration of the ExternalDataFileEx() method for working with secondary external data files more than anything and is some logic you certainly could use in a "real" statement (you know - along with the address of the recepient, titles on the columns of data, a "total" for the charges, and other necessary statement info).

 

What's the interest out there for perhaps some more complex statement type samples here in the sample jobs forum?

Link to comment
Share on other sites

  • 6 months later...
  • 2 months later...
  • 1 month later...
Yea- a sample would be great, maybe something that would list transactions and totals with logic for multiple page generation. Maybe Santa has a copy of Fusion Pro. I could put the request for transactional data samples on my Christmas list!
Link to comment
Share on other sites

Hi all,

 

We have been looking at an enhancement or two specifically to make the creation of transactional jobs a bit easier with FusionPro (less coding and FusionPro tagged markup). We are currently tracking this enhancement for the next version of FusionPro - version 6.2.

 

Once we have this released (targeting Q1), we are looking to provide a sample that shows something a bit closer to a complete statement type job.

Link to comment
Share on other sites

Hi all,

 

We have been looking at an enhancement or two specifically to make the creation of transactional jobs a bit easier with FusionPro (less coding and FusionPro tagged markup). We are currently tracking this enhancement for the next version of FusionPro - version 6.2.

 

Once we have this released (targeting Q1), we are looking to provide a sample that shows something a bit closer to a complete statement type job.

 

Cool! This will have many uses...

Link to comment
Share on other sites

  • 1 year later...
Hi all,

 

We have been looking at an enhancement or two specifically to make the creation of transactional jobs a bit easier with FusionPro (less coding and FusionPro tagged markup). We are currently tracking this enhancement for the next version of FusionPro - version 6.2.

 

Once we have this released (targeting Q1), we are looking to provide a sample that shows something a bit closer to a complete statement type job.

 

I starting to set up a job that will have variable pages for each record (some will be 1 page, some 2) Its an end of year tax statement. I'm looking to have it print duplexed, and the 1 page statements will get a blank page for the back. Also need to have it total on page 1 or page 2, depending on the number of lines. (Not number of records. Some records will only use 1 line, some will use several)

 

Were any sample jobs mentioned above ever posted?

 

I'm still fairly new to Fusion Pro (running 6.2 right now) and this will be the most complex job I have set up to this point. Looking for as much info as possible.

 

Thanks for any assistance!

 

Ronnie

Link to comment
Share on other sites

  • 2 weeks later...

Is there an easy way to accomplish this with only one datasource? I have a spreadsheet from which I'm to make invoices... Some customers have only one purchase, others have multiple. Data looks like:

 

CustID Customer Product

1 Bob Widget

2 Jane Book

2 Jane Cassette

2 Jane Kindle

3 Jeremy Laptop

3 Jeremy Coffee Cup

 

 

I need to create 3 invoices, #1 has only one line item, # 2 has three line items, and #3 has two line items. I will need to perform math on the lines as well, and so I'll need to think my way through that still... But let me know what you guys think on this from a single input file method. thanks!

 

Farns

Link to comment
Share on other sites

oh snap. I guess that won't work anyway.... I have to pre-sort the list for mailing anyhow. So maybe I will need two lists. There's 4000 records in the file, and that will turn into just about 3200 mailers we think.

 

So I guess maybe I go back to this original idea here, but then I need to figure out a safe and accurate way to split out my data I guess... Hmm... Any thoughts?

 

Farns

Link to comment
Share on other sites

Is there an easy way to accomplish this with only one datasource? I have a spreadsheet from which I'm to make invoices... Some customers have only one purchase, others have multiple. Data looks like:

 

CustID Customer Product

1 Bob Widget

2 Jane Book

2 Jane Cassette

2 Jane Kindle

3 Jeremy Laptop

3 Jeremy Coffee Cup

 

 

I need to create 3 invoices, #1 has only one line item, # 2 has three line items, and #3 has two line items. I will need to perform math on the lines as well, and so I'll need to think my way through that still... But let me know what you guys think on this from a single input file method. thanks!

 

Farns

You should be able to set your main input type to None, and then load that single data file with ExternalDataFileEx, like in the example, in the OnJobStart function, adding the data back into a JavaScript array which will hold individual records of data, and then use FusionPro.Composition.endRecordNumber to set the number of records to compose. Then you can look up each record's data in a rule with CurrentRecordNumber().

Link to comment
Share on other sites

Hey Dan,

 

So using this rule is working awesome for me. I presorted my list for mailing, and removed all duplicate entries of account numbers, so I have my main input file, and then I'm using that to reference the full data file, and it's working GREAT! Accounts with 1 record get one line item, and accounts with multiple records get as many lines as needed! I love it!!

 

I saw somebody ask the question back in '09 on this thread, about totaling the values up. I will need to do this. I need to take a total of the last field in all lines, whether it be one line or several, and perform some math on that number. Can you point me in the right direction on this?

 

If I have learned anything here in the past 24 hours, is that I need to spend some reading this forum any chance I get. I can't believe how much stuff FP can do that I had no idea about!

 

Farns

Link to comment
Share on other sites

I saw somebody ask the question back in '09 on this thread, about totaling the values up. I will need to do this. I need to take a total of the last field in all lines, whether it be one line or several, and perform some math on that number. Can you point me in the right direction on this?

Sure, that's pretty easy to do. In the rule in the example, you can do it with just a few extra lines of code.

 

First, before the loop, declare a variable to hold the total:

var total = 0;

Then, inside the loop, and also inside the first "if" block which finds the matching records (I put it at line 50), add the current price to the total:

        // Regular expression to strip dollar signs and commas to convert the value to a number for math.
       total += StringToNumber(externalDF.GetFieldValue(recordWalker, 'Price').replace(/[\$,]/g, ''));

Finally, just before the last line which returns the result, append the total, with some formatting:

returnStr += "<br><br>Total purchase: " + FormatNumber("$0.00", total);

Link to comment
Share on other sites

Awesome, worked perfectly!

 

When that variable is defined in THIS rule, can other rules take advantage of it too? I need to put this total in a couple other places (different variable text boxes where the math will be performed on it) can I call it from those rules, or would I need to basically use another instance of this rule with all the other stuff weeded out (I have about 9 columns of data piped into this now that have nothing to do with the math)?

 

Also, that format number function is beautiful, but I can't seem to get it to work in the if statement. I have tried it a few different ways, that I thought made sense, but I either get syntax errors, or in one case it fouled up the math, and still didn't apply the formatting. I'd like to do the "$***,***.XX" formatting to the numbers.

 

returnStr += externalDF.GetFieldValue(recordWalker, 'Total Cost');
Link to comment
Share on other sites

Awesome, worked perfectly!

 

When that variable is defined in THIS rule, can other rules take advantage of it too? I need to put this total in a couple other places (different variable text boxes where the math will be performed on it) can I call it from those rules, or would I need to basically use another instance of this rule with all the other stuff weeded out (I have about 9 columns of data piped into this now that have nothing to do with the math)?

You can always declare total to be a global variable in the JavaScript Globals. Actually, I would also declare the returnStr variable as a global, and then I would move the logic to build that and calculate the total to OnRecordStart. Then other rules can simply access those global variables for the record.

 

If you can post an example of what you have so far, it would be easier to make more specific suggestions.

Also, that format number function is beautiful, but I can't seem to get it to work in the if statement. I have tried it a few different ways, that I thought made sense, but I either get syntax errors, or in one case it fouled up the math, and still didn't apply the formatting.

I'm not sure I understand exactly where you're trying to use the FormatNumber function. Again, if you can post an example, I might be able to troubleshoot it.

I'd like to do the "$***,***.XX" formatting to the numbers.

Try "$0,000.00" as the format string.

intersting.... I typed number symbols where I was showing the desired formatting, but the forum converted it to asterisks....?

Sorry about that. The vBulletin forum does some certain character substitutions, which we don't really have much control over from our end. You can always post an attachment.

Link to comment
Share on other sites

I did go ahead and do the duplicated rule situation, and it's working wonderfully. But you're saying by defining it as a global, that would be more streamlined? I will look into that and try to understand that better. I'm all for streamlining!! This is a job of a few thousand records, that will only be done once a year, and buy has it been a learning experience! I should like to learn more about how to do these more efficiently.
Link to comment
Share on other sites

Ok, I am SOOOO Close here! I think I just need a tiny tiny bit more help here.

 

Below is the code that I expanded from your sample. There are several columns of data carried in there (and I just realized I could put the <t> tags on the same line probably, but I"m not gonna change it now, just need to get this done! LOL.

 

Anyway, several text fields come into the invoice, then two more with prices, and those work great. The problem is the total. This script is doing exactly what I need it to, and it's perfect save one thing. It won't work if the total is less than $1000. I've been at it for 3 hours tonight, and I can't figure it out. Works perfect in every aspect, unless it's under a grand. I have formatting to align by decimal on that tab stop, and since it drops the $ and the cents, it just scoots over to the right, out of alignment. I just get a whole dollar like "660" Instead of "$600.00". So I'm trying to figure out something with the FormatNumber, right? But no matter what I do, it won't work. Please tell me it's something simple to fix this! :)

 

What do you think?

 

Farns

 

PS - I tried to read what I could to understand the ".replace" part of the string to number function... but couldn't find anything. Where can I read up on these things? Also, same thing for the recordWalker function. Thanks!

 

 

 

//Create an empty variable that will be populated with a

//string of text that lists the customer's purchases

returnStr = '';

 

//The following if statement will detect if we are currently in preview

//mode or editing this rule (versus composing output). If so, the script

//will manually call the OnJobStart rule so the link to the external

//data file can be established. OnJobStart is called automatically when

//a full composition is run.

if(FusionPro.Composition.isPreview == true || FusionPro.inValidation == true)

{

Rule("OnJobStart");

}

 

 

//Get a count of the total number of records in the external data file

numRecsExtDF = externalDF.recordCount;

var total = 0;

 

//Now, loop through all records in the external data file and

//find the records that belong to the customer. This is done

//by comparing the value of "CustomerID" from the main input

//data file to the value of the "CID" field in the external data

//file.

//

//Note that in this example, there can be multiple records in the

//external data file for each customer which is why we are going to

//look at every record in the external data file.

 

 

 

 

for (recordWalker=1; recordWalker <= numRecsExtDF; recordWalker++)

{

//Compare the value of the CID field in the current record

//we are looking at in the external data file to the value

//of the CustomerID field in the main data file.

 

 

 

 

if (externalDF.GetFieldValue(recordWalker, 'AccountNum') == Field("Account"))

{

//Check to see if this is a TAX item. If it is, let's format it differently

// if (externalDF.GetFieldValue(recordWalker, 'Product-Purchased') == 'Tax')

// {

// returnStr += '<br><b>Total Tax - ' + externalDF.GetFieldValue(recordWalker, 'Price') + '</b>';

 

// }

// else OK - so this is a normal product - not a Tax item

 

// Regular expression to strip dollar signs and commas to convert the value to a number for math.

total += StringToNumber(externalDF.GetFieldValue(recordWalker, 'Total Cost').replace(/[\$,]/g, ''));

 

{

returnStr += ' <p tabstops="0;8900,Center,,;13380,Center,,;18200,Center,,;23520,Center,,;28375,Left,,;36000,Decimal,.,;41900,Decimal,.,;49000,Decimal,.,;"> ';

returnStr += externalDF.GetFieldValue(recordWalker, 'Field1');

returnStr += ' <t> ';

returnStr += externalDF.GetFieldValue(recordWalker, 'Field2');

returnStr += ' <t> ';

returnStr += externalDF.GetFieldValue(recordWalker, 'Field3');

returnStr += ' <t> ';

returnStr += externalDF.GetFieldValue(recordWalker, 'Field4');

returnStr += ' <t> ';

returnStr += externalDF.GetFieldValue(recordWalker, 'Field5');

returnStr += ' <t> ';

returnStr += externalDF.GetFieldValue(recordWalker, 'Field6');

returnStr += ' <t> ';

returnStr += FormatNumber("$##,###.00", (externalDF.GetFieldValue(recordWalker, 'Price')));

returnStr += ' <t> ';

returnStr += FormatNumber("$##,###.00", (externalDF.GetFieldValue(recordWalker, 'HandlingFee')));

returnStr += ' <t> ';

returnStr += externalDF.GetFieldValue(recordWalker, 'Total Cost');

}

}

}

 

returnStr += "<br><br>Total: " + FormatNumber("$#,###.00", total);

 

 

return returnStr;

Link to comment
Share on other sites

Sorry, one more little bit to add....

 

it works great on the very last line there where I'm posting "Total =...." I get the dollar sign, the two decimal places... it's just up in the line item part. If I try to apply the FormatNumber, it wipes the value out. I'm guessing a conflict from us stripping those elements out at the top, and the putting it back in at the bottom of the loop, eh?

 

And copying the rule to other rules to calculate payment plans and discounts worked perfectly. I love this little rule! Just gotta figure out a way to make it work for the cheapskates hahaha

 

Farns

Link to comment
Share on other sites

I was able to get a stay of execution on this job until Monday. I'm going to keep working on it over the weekend and see if I can figure out what's going wrong, but if there's any chance you read this forum over the weekend Dan, I'd really appreciate any input or direction you can give me on it. Would some screenshots of the results or any other information be helpful in seeing what's up with it?
Link to comment
Share on other sites

it works great on the very last line there where I'm posting "Total =...." I get the dollar sign, the two decimal places... it's just up in the line item part. If I try to apply the FormatNumber, it wipes the value out. I'm guessing a conflict from us stripping those elements out at the top, and the putting it back in at the bottom of the loop, eh?

FormatNumber only works on numbers, not on strings which already contain symbols such as dollar signs. You need to strip those out using logic like where we're adding to the total to convert the field values with the symbols to valid dollar amounts. Something like this:

returnStr += FormatNumber("$##,###.00", StringToNumber(externalDF.GetFieldValue(recordWalker, 'Price').replace(/[\$,]/g, ''));

Of course, it would be better if the data contained simple numbers and didn't try to pre-format things for you, but it is what it is.

 

I like to make little convenience functions for these kinds of things. For instance, if you add this code at the top of your rule (or in the JavaScript Globals):

function NumberFromDollars(text)
{
   return StringToNumber(text.replace(/[\$,]/g, ''));
}

function FormatDollars(text)
{
   return FormatNumber("$##,###.00", NumberFromDollars(text));
}

Then you can simply do this:

returnStr += FormatDollars(externalDF.GetFieldValue(recordWalker, 'Price'));

PS - I tried to read what I could to understand the ".replace" part of the string to number function... but couldn't find anything. Where can I read up on these things?

The ".replace" is the String.replace function. I'm using it with a regular expression, which is a bit of advanced trickery, but it's all documented here:

https://developer.mozilla.org/en/JavaScript/Reference/Global_Objects/String/replace

And here:

https://developer.mozilla.org/en/JavaScript/Reference/Global_Objects/RegExp

Also, same thing for the recordWalker function. Thanks!

That's not a function; recordWalker is simply a variable used in a for loop. It could just as easily be named "r".

https://developer.mozilla.org/en/JavaScript/Reference/Statements/for

Link to comment
Share on other sites

By the way, here's my updated version of the rule from the sample job:

function NumberFromDollars(text)
{
   // Strip dollar signs and commas to convert the value to a number for math.
   return StringToNumber(text.replace(/[\$,]/g, ''));
}

function FormatDollars(text)
{
   return FormatNumber("$##,###.00", NumberFromDollars(text));
}

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

var returnStr = "";
var total = 0;

for (var r = 1; r <= externalDF.recordCount; r++)
{
   // Convenience function to simplify code below.
   function ExDataField(name) { return externalDF.GetFieldValue(r, name); }

   //Compare the value of the CID field in the current record
   //we are looking at in the external data file to the value
   //of the CustomerID field in the main data file.
   if (ExDataField('CID') == Field("CustomerID"))
   {
       var product = ExDataField('Product-Purchased');
       var price = ExDataField('Price');

       //Check to see if this is a TAX item.  If it is, let's format it differently
       if (product == 'Tax')
           returnStr += '<br>\n<b>Total Tax - ' + FormatDollars(price) + '</b>';            
       else //OK - so this is a normal product - not a Tax item
           returnStr += product + ' - ' + FormatDollars(price) + '<br>\n';

       total += NumberFromDollars(price);
   }
}

return returnStr + "<br><br>\n\nTotal purchase: " + FormatNumber("$0.00", total);

Link to comment
Share on other sites

Thanks so much Dan! This all made good sense, and the adjustment made everything work perfectly. I just thought it was odd that it worked on everything over $1000, but nothing under. I suppose there's a logical reason, if one understands the code better... LOL...

 

I'm curious why this forum doesn't arrange the responses chronologically? It's almost like different responses break off and start their own thread or something. I kept refreshing the original post, but you can't see many of the replies without clicking that link that says "more posts deeper than this" or something like that. Your answer had been sitting there forever before I found it, because it was branched off in another direction. I've been on a lot of forums but never seen one behave like this. What's the secret to being able to follow these threads more intelligently?

 

Thanks again for all your help on this!!

 

Farns

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...