Jump to content

total a column of numbers with some complexity...


cdaters

Recommended Posts

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

 

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

 

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!

Edited by cdaters
Corrected "or" conditional
Link to comment
Share on other sites

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

data.zip

rules.zip

Link to comment
Share on other sites

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:

 

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:

 

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

248839A_Imposed_2sided_example.zip

Edited by cdaters
Link to comment
Share on other sites

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:

 

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

XDFs.zip

Edited by cdaters
Link to comment
Share on other sites

What you have looks pretty good. The _totalGift2 rule could be simplified a bit, like so:

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...