Go Back   MarcomCentral (PTI) and FusionPro User Communities > Software-Related Talk > FusionPro® VDP Creator

Notices

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old January 23rd, 2020, 12:21 PM
cdaters cdaters is offline
Junior Community Member
 
Join Date: Aug 2019
Posts: 10
Default total a column of numbers with some complexity...

I need some assistance figuring out how to sum a column of dynamic totals that could be a positive or negative dollar amount, or an indication of stock shares.

I have a tab-delimited text file of donor contributions for that I am matching up against a CSV file of other related customer data that I am using to create a statement letter which will show a "donation history" of a particular donor. Each donor has a different amount of donations, and to complicate things, the column of data for a particular donation record could show either "$1,000.00" or "($1,000.00)" or "2 Shares APPL". The number with the parentheticals is of course, representing a negative number.

At the end of this column, I need to show a string that will read either "Total: $1,000.00," or if any of the donation history contains a donation record that was shares the returned string will simply read, "$1,000.00 & Stock."

I have been racking my brain trying to come up with the JS rule that can achieve this. I have the JS rule that is generating the donation history correctly, but summing the donation amount column is causing me to go crazy...

Here is the JS for generating my donation history list in the letter (this seems to be working fine):

Code:
var contributionList = new ExternalDataFileEx("/~wip/248839 Frontiers/Master Data/Double Data proof.txt", "\t");
var donor_id = Field("Supporter");
var lb = "<br>\n";
var matches = new Array();

for (var i = 0; i <= contributionList.recordCount; i++) {

    var idVariable = contributionList.GetFieldValue(i, "Supporter");
    var dateVariable = contributionList.GetFieldValue(i, "Donation Date");
    var ministryVariable = contributionList.GetFieldValue(i, "Ministry Designation");
    var giftVariable = contributionList.GetFieldValue(i, "Donation Amount");
    var tsSettings = "<p tabstops=19550,Right,,;29600,Left,,;>";
    var ts = "<t>";

    if (donor_id == idVariable)
    matches.push(tsSettings + dateVariable + ts + giftVariable + ts + ministryVariable);
}

//return matches;

return matches.join(lb);
Now here is the JS code that is not working just fine. I am trying to tally the donation amount column, it only returns "Total: $0.00 & Stock" every time (I have tried to explain my thought process via comments):

Code:
var contributionList = new ExternalDataFileEx("/~wip/248839 Frontiers/Master Data/Double Data proof.txt", "\t");
var donor_id = Field("Supporter");

for (var i = 0; i <= contributionList.recordCount; i++) {

	var idVariable = contributionList.GetFieldValue(i, "Supporter");
	var giftVariable = contributionList.GetFieldValue(i, "Donation Amount");
	var sum = 0;
	var shares = 0;
	var tsSettings = "<p tabstops=19550,Right,,;29600,Left,,;>";
	var ts = "<t>";
	var totalStr = "Total ";
	var stockStr = " & Stock";
	var totalFormatted = FormatNumber("$#,###.00", Math.max(0, StringToNumber(sum)));

// Match data from linked file to current Supporter
	if (donor_id == idVariable) {

	// Look at current record and see if it contains the word "Share(s)"
	// or not and act accordingly
		if (giftVariable.match(/(^|\W)share($|\W)/i) || giftVariable.match(/(^|\W)shares($|\W)/i)) {
		// Turn switch "on" if donation amount is a share or shares so
		// we can have the " & Stock" appended to our string.
			shares = 1;
		// Because this donation is/are shares, we must "zero" this
		// amount to make the math work when we sum everything up...
			giftVariable = 0;
		// This is where we are keeping our running total...
			sum += giftVariable[i];
		} else {
		// This record was not a donation of share(s) so we now have to
		// determine whether we are dealing with postive or negative numbers
		// and then strip out all of the non-number characters, remove and
		// replace the () whis just a "-," leaving us with a number we can
		// work with...
			
		// If number has parenthesis, then deal with it...
			if (giftVariable.indexOf("(")) {
			// Strip out all the ()$, characters...
				giftVariable = giftVariable.replace(/[()$,]/g,"")
			// Append the minus sign to the number...
				giftVariable = "-" + giftVariable;
				sum += giftVariable[i];
			} else {
			giftVariable = giftVariable.replace(/[$,]/g,"");
			sum += giftVariable[i];
			}

		}
		
	}

}

// Return Total...
if (shares == 1) {
	return tsSettings + totalStr + ts + totalFormatted + stockStr;
} else {
	return tsSettings + totalStr + ts + totalFormatted;
}
Any assistance would be greatly appreciated!

Last edited by cdaters; January 23rd, 2020 at 01:56 PM.. Reason: Corrected "or" conditional
Reply With Quote
  #2  
Old January 23rd, 2020, 01:50 PM
Dan Korn's Avatar
Dan Korn Dan Korn is offline
FusionPro Senior Engineer / Forum Moderator
 
Join Date: Aug 2008
Location: Chicago, IL
Posts: 4,483
Default Re: total a column of numbers with some complexity...

Can you attach at least a sample of the data file, so that the rule can be debugged? Also, what should the expected result be, and how is it different than the actual result?
__________________
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
  #3  
Old January 23rd, 2020, 02:32 PM
cdaters cdaters is offline
Junior Community Member
 
Join Date: Aug 2019
Posts: 10
Default Re: total a column of numbers with some complexity...

I've attached the data for this job. It contains the initial Data file (the .csv that we're using to proof with), the donation history external data textfile, as well as the rules being used.

I have one text frame that, with a rule, generates a three column list of the donation history (pulled from the external data file) of that donor in this format:

Date: <tab> Donation Amount: <tab> Ministry Area:

Beneath this list (which may wrap to a second page) is a second text frame that would be a sum of all the donor history records taken from the "Donation Amount:" column of the external data file for that donor. It contains the rule which will generate a string that would read either:
Total <tab> $1,000.00
or,
Total <tab> $1,000.00 & Stock
Now that "Donation Amount" column (of the external data file) for a particular donor might contain:
$75.00
($1,000.00)
$1,500.00
2 Shares APPL
1 Share TSLA
($75.00)
I need to be able to sum the column and return that total. If there is a donor that has given a mix of monetary gifts and stock then I need to return the total and stock ("$1,000.00 & Stock" as opposed to "$1,000.00"). The entries with parentheticals denotes a number that is negative, ($1,000.00) would really be "-$1,000.00".

Using the above example, the return string should be:
Total <tab> $500.00 & Stock
Attached Files
File Type: zip data.zip (2.0 KB, 0 views)
File Type: zip rules.zip (2.5 KB, 0 views)
Reply With Quote
  #4  
Old January 24th, 2020, 12:29 PM
cdaters cdaters is offline
Junior Community Member
 
Join Date: Aug 2019
Posts: 10
Default Re: total a column of numbers with some complexity...

I am sure that this could be much cleaner and there is probably a lot of redundancy here, but in the interest of keeping this going, I have continued to tweak the code and working through trial and error...

For the sake of what is looking to be achieved, here is what we are striving for:

A text frame that will be a list of donation history that appears in this format:
Date: <tab> Donation Amount: <tab> Allocated to Ministry Area:
At the end of this, which could wrap to a second page (the donation history data, that is) will be another text frame with:
Total: <<amount>>
You can see in my previous post how this data might appear in the external data list, and how the total line might read. You can look at those attached data files.

I have since modified my rules.

So the rule (_donationHistory) that displays the donation history now looks like this:

Code:
var contributionList = new ExternalDataFileEx("/~wip/248839 Frontiers/Master Data/Double Data proof3.txt", "\t");
var donor_id = Field("Supporter");
var lb = "<br>\n";
var matches = new Array();

for (var i = 0; i <= contributionList.recordCount; i++) {

    var idVariable = contributionList.GetFieldValue(i, "Supporter");
    var dateVariable = contributionList.GetFieldValue(i, "Donation Date");
    var ministryVariable = contributionList.GetFieldValue(i, "Ministry Designation");
    var giftVariable = contributionList.GetFieldValue(i, "Donation Amount");
    var tsSettings = "<p tabstops=19550,Right,,;29600,Left,,;>";
    var ts = "<t>";
    var giftFormatted = FormatNumber("$#,###.00", Math.max(0, StringToNumber(giftVariable)))

	if (donor_id == idVariable) {
        if (giftVariable.match(/(^|\W)share($|\W)/i) || giftVariable.match(/(^|\W)shares($|\W)/i) || giftVariable.match(/(^|\W)stock($|\W)/i)) {
            matches.push(tsSettings + dateVariable + ts + giftVariable + ts + ministryVariable);
        } else {
            matches.push(tsSettings + dateVariable + ts + giftVariable + ts + ministryVariable);
        }
    }
        
}

//return donation history

return matches.join(lb);
My rule (_totalGifts) for the gift total line now looks like this:

Code:
var contributionList = new ExternalDataFileEx("/~wip/248839 Frontiers/Master Data/Double Data proof2.txt", "\t");
var donor_id = Field("Supporter");
var matches = new Array();
var share = 0;
var total = 0;
var tsStr = "<p tabstops=19550,Right,,;29600,Left,,;>";
var tabStr = "<t>";
var totalStr = "Total ";
var stockStr = " & Stock";
var lb = "<br>\n";


for (var i = 0; i <= contributionList.recordCount; i++) {

	var idVariable = contributionList.GetFieldValue(i, "Supporter");
	var giftVariable = contributionList.GetFieldValue(i, "Donation Amount");
    var pointNumber = parseFloat(giftVariable);

	if (donor_id == idVariable) {

		if (giftVariable.match(/(^|\W)share($|\W)/i) || giftVariable.match(/(^|\W)shares($|\W)/i) || giftVariable.match(/(^|\W)stock($|\W)/i)) {
			share = 1;
			matches.push(total += 0);
        } else {
			matches.push(total += pointNumber);
			}

		}

}

//return totals

if (share == 1) {
    return tsStr + totalStr + tabStr + FormatNumber("$#,###.00", Math.max(0, StringToNumber(total))) + stockStr;
    } else {
    return tsStr + totalStr + tabStr + FormatNumber("$#,###.00", Math.max(0, StringToNumber(total)));
}
It seems to be working for now, though I am sure there could be some issues that we just haven't encountered yet. I am hoping that someone could look this over and let me know if I messed anything up or didn't accomodate for?

I've attached a collected zipfile of this job...
Attached Files
File Type: zip 248839A_Imposed_2sided_example.zip (4.32 MB, 1 views)

Last edited by cdaters; January 24th, 2020 at 12:41 PM..
Reply With Quote
  #5  
Old January 27th, 2020, 12:20 PM
Dan Korn's Avatar
Dan Korn Dan Korn is offline
FusionPro Senior Engineer / Forum Moderator
 
Join Date: Aug 2008
Location: Chicago, IL
Posts: 4,483
Default Re: total a column of numbers with some complexity...

Quote:
Originally Posted by cdaters View Post
I've attached a collected zipfile of this job...
Thanks. Can you include the XDFs ("Double Data proof2.txt" and "Double Data proof3.txt") as well?
__________________
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
  #6  
Old January 27th, 2020, 12:41 PM
cdaters cdaters is offline
Junior Community Member
 
Join Date: Aug 2019
Posts: 10
Default Re: total a column of numbers with some complexity...

Sorry Dan, here are the XDFs...

I was playing with converting that column to just numbers in the data itself to see what I could get away with, and that is why you'll see the numbers formatted differently between the two XDFs.

In the final version of this rule, I'd prefer to have the JS determine/convert to number, total the data and then format (i.e., $1,111.11) at the end.

While I was playing around early on, it was just printing like thus:

Code:
$75.00
$75.00$75.00
$75.00$75.00$75.00
$75.00$75.00$75.00$75.00
etc. etc.
So I figured that I needed to convert the string to a number first and that is what I was trying to do in that final code/thread post last week....
Attached Files
File Type: zip XDFs.zip (3.2 KB, 1 views)

Last edited by cdaters; January 27th, 2020 at 01:00 PM..
Reply With Quote
  #7  
Old January 28th, 2020, 02:22 PM
Dan Korn's Avatar
Dan Korn Dan Korn is offline
FusionPro Senior Engineer / Forum Moderator
 
Join Date: Aug 2008
Location: Chicago, IL
Posts: 4,483
Default Re: total a column of numbers with some complexity...

What you have looks pretty good. The _totalGift2 rule could be simplified a bit, like so:
Code:
var contributionList = new ExternalDataFileEx("Double Data proof2.txt");
var total = 0;
var share = false;

for (var i = 0; i <= contributionList.recordCount; i++)
{
    var idVariable = contributionList.GetFieldValue(i, "Supporter");

    if (Field("Supporter") == idVariable)
    {
        var giftVariable = contributionList.GetFieldValue(i, "Donation Amount");
    
        if (isNaN(giftVariable))
            share = true;
        else
            total += parseFloat(giftVariable);
    }
}

var totalStr = FormatNumber("$#,###.00", Math.max(0, total)) + (share ? " & Stock" : "");
return "<p br=false tabstops=19550,Right,,;29600,Left,,;>Total<t>" + totalStr;
The isNaN() function (i.e. "is Not a Number") determines whether the entire string is numeric, and returns true if it's not (and false if it is), so I think you can just use that instead of all the Regular Expression matching.

Also, I would assume that a well-formed data set (such as a dump from a database table with typed columns) would have a single column representing the monetary value of each donation, and a separate column for any descriptive text such as "2 Shares APPL", which would remove any need to examine the value in each record to determine its type. But, we don't always live in a well-formed, ideal world.
__________________
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
  #8  
Old January 28th, 2020, 02:49 PM
cdaters cdaters is offline
Junior Community Member
 
Join Date: Aug 2019
Posts: 10
Default Re: total a column of numbers with some complexity...

Quote:
But, we don't always live in a well-formed, ideal world.
Very true!

I will see what I can do with this Dan, thank you very much. The RegEx stuff throws me every time, and I am sure that I could have condensed that down into one statement instead of three "or" conditionals, lol!

Of course since our initial proof, they came back with many more changes.

I am going to talk to them about standardizing and normalizing their data a little bit better (thinking of their stock donations here) if we do more of these in the future.

Again, thank you for taking a look at this and offering your input!
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 10:49 AM.


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