Go Back   MarcomCentral (PTI) and FusionPro User Communities > Software-Related Talk > Getting Started > Sample Jobs

Notices

Reply
 
Thread Tools Search this Thread Display Modes
  #11  
Old February 2nd, 2011, 10:41 AM
farns farns is offline
Junior Community Member
 
Join Date: Apr 2010
Posts: 38
Default Re: External Data File Sample - Very Simple Statement

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
Reply With Quote
  #12  
Old February 2nd, 2011, 12:42 PM
farns farns is offline
Junior Community Member
 
Join Date: Apr 2010
Posts: 38
Default Re: External Data File Sample - Very Simple Statement

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
Reply With Quote
  #13  
Old February 2nd, 2011, 03:29 PM
Dan Korn's Avatar
Dan Korn Dan Korn is online now
FusionPro Senior Engineer / Forum Moderator
 
Join Date: Aug 2008
Location: Chicago, IL
Posts: 4,322
Default Re: External Data File Sample - Very Simple Statement

Quote:
Originally Posted by farns View Post
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().
__________________
Dan Korn
FusionPro Developer / JavaScript Guru / Forum Moderator
PTI Marketing Technologies | Printable | MarcomCentral
LinkedIn

I am a not a Support engineer, and this forum is not a substitute for Support. My participation on this forum is primarily as a fellow user (and a forum moderator). I am happy to provide help and answers to questions when I can; however, there is no guarantee that I, or anyone else on this forum, will be able to answer all questions or fix any problems. If I ask for files to clarify an issue, I might not be able to look at them personally. I am not able to answer private messages, emails, or phone calls unless they go through proper Support channels. Please direct any sales or pricing questions to your salesperson or inquiries@marcom.com.

Complex template-building questions, as well as all installation and font questions or problems, should be directed to FusionProSupport@marcom.com. Paid consulting work may be required to fulfill your template-building needs.

This is a publicly viewable forum. Please DO NOT post fonts, or other proprietary content, to this forum. Also, please DO NOT post any "live" data with real names, addresses, or any other personal, private, or confidential data.

Please include the specific versions of FusionPro, Acrobat, and your operating system in any problem reports or help requests. I recommend putting this information in your forum signature. Please also check your composition log (.msg) file for relevant error or warning messages.

Please post questions specific to the MarcomCentral Enterprise and Web-to-Print applications in the MarcomCentral forum. Click here to request access. Or contact your Business Relationship Manager (BRM/CPM) for assistance.

Please direct any questions specific to EFI's Digital StoreFront (DSF) to EFI support.

How To Ask Questions The Smart Way

The correct spellings are JavaScript, FusionPro, and MarcomCentral (each with two capital letters and no spaces). Acceptable abbreviations are JS, FP, and MC (or MCC). There is no "S" at the end of "Expression" or "Printable"! The name of the product is FusionPro, not "Fusion". "Java" is not is not the same as JavaScript.

Check out the JavaScript Guide and JavaScript Reference! FusionPro 8.0 and newer use JavaScript 1.7. Older versions use JavaScript 1.5.

return "KbwbTdsjqu!spdlt\"".replace(/./g,function(w){return String.fromCharCode(w.charCodeAt()-1)});
Reply With Quote
  #14  
Old February 3rd, 2011, 09:02 AM
farns farns is offline
Junior Community Member
 
Join Date: Apr 2010
Posts: 38
Default Re: External Data File Sample - Very Simple Statement

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
Reply With Quote
  #15  
Old February 3rd, 2011, 11:29 AM
Dan Korn's Avatar
Dan Korn Dan Korn is online now
FusionPro Senior Engineer / Forum Moderator
 
Join Date: Aug 2008
Location: Chicago, IL
Posts: 4,322
Default Re: External Data File Sample - Very Simple Statement

Quote:
Originally Posted by farns View Post
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:
Code:
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:
Code:
        // 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:
Code:
returnStr += "<br><br>Total purchase: " + FormatNumber("$0.00", total);
__________________
Dan Korn
FusionPro Developer / JavaScript Guru / Forum Moderator
PTI Marketing Technologies | Printable | MarcomCentral
LinkedIn

I am a not a Support engineer, and this forum is not a substitute for Support. My participation on this forum is primarily as a fellow user (and a forum moderator). I am happy to provide help and answers to questions when I can; however, there is no guarantee that I, or anyone else on this forum, will be able to answer all questions or fix any problems. If I ask for files to clarify an issue, I might not be able to look at them personally. I am not able to answer private messages, emails, or phone calls unless they go through proper Support channels. Please direct any sales or pricing questions to your salesperson or inquiries@marcom.com.

Complex template-building questions, as well as all installation and font questions or problems, should be directed to FusionProSupport@marcom.com. Paid consulting work may be required to fulfill your template-building needs.

This is a publicly viewable forum. Please DO NOT post fonts, or other proprietary content, to this forum. Also, please DO NOT post any "live" data with real names, addresses, or any other personal, private, or confidential data.

Please include the specific versions of FusionPro, Acrobat, and your operating system in any problem reports or help requests. I recommend putting this information in your forum signature. Please also check your composition log (.msg) file for relevant error or warning messages.

Please post questions specific to the MarcomCentral Enterprise and Web-to-Print applications in the MarcomCentral forum. Click here to request access. Or contact your Business Relationship Manager (BRM/CPM) for assistance.

Please direct any questions specific to EFI's Digital StoreFront (DSF) to EFI support.

How To Ask Questions The Smart Way

The correct spellings are JavaScript, FusionPro, and MarcomCentral (each with two capital letters and no spaces). Acceptable abbreviations are JS, FP, and MC (or MCC). There is no "S" at the end of "Expression" or "Printable"! The name of the product is FusionPro, not "Fusion". "Java" is not is not the same as JavaScript.

Check out the JavaScript Guide and JavaScript Reference! FusionPro 8.0 and newer use JavaScript 1.7. Older versions use JavaScript 1.5.

return "KbwbTdsjqu!spdlt\"".replace(/./g,function(w){return String.fromCharCode(w.charCodeAt()-1)});
Reply With Quote
  #16  
Old February 3rd, 2011, 12:28 PM
farns farns is offline
Junior Community Member
 
Join Date: Apr 2010
Posts: 38
Default Re: External Data File Sample - Very Simple Statement

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.

Quote:
returnStr += externalDF.GetFieldValue(recordWalker, 'Total Cost');
Reply With Quote
  #17  
Old February 3rd, 2011, 12:29 PM
farns farns is offline
Junior Community Member
 
Join Date: Apr 2010
Posts: 38
Default Re: External Data File Sample - Very Simple Statement

intersting.... I typed number symbols where I was showing the desired formatting, but the forum converted it to asterisks....?
Reply With Quote
  #18  
Old February 3rd, 2011, 12:57 PM
Dan Korn's Avatar
Dan Korn Dan Korn is online now
FusionPro Senior Engineer / Forum Moderator
 
Join Date: Aug 2008
Location: Chicago, IL
Posts: 4,322
Default Re: External Data File Sample - Very Simple Statement

Quote:
Originally Posted by farns View Post
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.
Quote:
Originally Posted by farns View Post
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.
Quote:
Originally Posted by farns View Post
I'd like to do the "$***,***.XX" formatting to the numbers.
Try "$0,000.00" as the format string.
Quote:
Originally Posted by farns View Post
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.
__________________
Dan Korn
FusionPro Developer / JavaScript Guru / Forum Moderator
PTI Marketing Technologies | Printable | MarcomCentral
LinkedIn

I am a not a Support engineer, and this forum is not a substitute for Support. My participation on this forum is primarily as a fellow user (and a forum moderator). I am happy to provide help and answers to questions when I can; however, there is no guarantee that I, or anyone else on this forum, will be able to answer all questions or fix any problems. If I ask for files to clarify an issue, I might not be able to look at them personally. I am not able to answer private messages, emails, or phone calls unless they go through proper Support channels. Please direct any sales or pricing questions to your salesperson or inquiries@marcom.com.

Complex template-building questions, as well as all installation and font questions or problems, should be directed to FusionProSupport@marcom.com. Paid consulting work may be required to fulfill your template-building needs.

This is a publicly viewable forum. Please DO NOT post fonts, or other proprietary content, to this forum. Also, please DO NOT post any "live" data with real names, addresses, or any other personal, private, or confidential data.

Please include the specific versions of FusionPro, Acrobat, and your operating system in any problem reports or help requests. I recommend putting this information in your forum signature. Please also check your composition log (.msg) file for relevant error or warning messages.

Please post questions specific to the MarcomCentral Enterprise and Web-to-Print applications in the MarcomCentral forum. Click here to request access. Or contact your Business Relationship Manager (BRM/CPM) for assistance.

Please direct any questions specific to EFI's Digital StoreFront (DSF) to EFI support.

How To Ask Questions The Smart Way

The correct spellings are JavaScript, FusionPro, and MarcomCentral (each with two capital letters and no spaces). Acceptable abbreviations are JS, FP, and MC (or MCC). There is no "S" at the end of "Expression" or "Printable"! The name of the product is FusionPro, not "Fusion". "Java" is not is not the same as JavaScript.

Check out the JavaScript Guide and JavaScript Reference! FusionPro 8.0 and newer use JavaScript 1.7. Older versions use JavaScript 1.5.

return "KbwbTdsjqu!spdlt\"".replace(/./g,function(w){return String.fromCharCode(w.charCodeAt()-1)});
Reply With Quote
  #19  
Old February 3rd, 2011, 01:47 PM
farns farns is offline
Junior Community Member
 
Join Date: Apr 2010
Posts: 38
Default Re: External Data File Sample - Very Simple Statement

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.
Reply With Quote
  #20  
Old February 3rd, 2011, 10:43 PM
farns farns is offline
Junior Community Member
 
Join Date: Apr 2010
Posts: 38
Default Re: External Data File Sample - Very Simple Statement

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!



Quote:
//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(recordWalk er, 'Total Cost').replace(/[\$,]/g, ''));

{
returnStr += ' <p tabstops="0;8900,Center,,;13380,Center,,;18200,Cen ter,,;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;
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -7. The time now is 09:30 AM.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) 2011, PTI Marketing Technologies™, Inc.