Is it possible to get multiple values from a script object to calculate a total in a field?

meem23

06-02-2017

I have a script object that populates fields.  There are two arrays, one for dates and one for values.

Snippet of script object:

//Array of closing dates.

var closingDate = new Array("",

"1/3/2011",

"1/4/2011",

"1/5/2011",

etc……

"12/31/2016");

//Array of closing values.

var closingValue = new Array(null,

"1271.89",

"1270.2",

"1276.56",

etc……

"1848.36");

//Populate the Closing Value fields

function getValue(ClosingDate, ClosingValue)

{

    var i;

    for (i = 0; i < closingDate.length; i++)

    {

        if (closingDate[i] == ClosingDate)

        {

            ClosingValue.rawValue = closingValue[i];

            break;

        }

    }

}

Only certain dates and values are displayed in the form.  The other dates and values are needed to calculate values during the year.  For instance, there is a field with the date of 1/3/2011 and another populated with the value of 1271.89; a field with 1/3/2012 and another populated with a value of 1227.06 (from the script object).  I would like a field to calculate the values between 1/3/2011 and 1/3/2012. I don’t want to have a field for each date between 1/3/2011 and 1/3/2012 and fields for each value because I would have hundreds of fields for dates and hundreds of fields for values.  Is there a way to pull multiple values from the script object and then add them together to display the total in a field?

Accepted Solutions (1)

Accepted Solutions (1)

_Bruce_Robertson

MVP

14-02-2017

Hi,

I suspect I'm still misunderstanding something here.  But, what I was suggesting was having a function defined in your script object like;

function getTotal(startDate, endDate)
{
var start = util.scand("m/d/yyyy", startDate).setHours(0,0,0,0);
var end = util.scand("m/d/yyyy", endDate).setHours(0,0,0,0);
var result = 0;
    var i;
    for (i = 1; i < closingDate.length; i++)
    {
     var d = new Date(util.scand("m/d/yyyy", closingDate[i]).setHours(0,0,0,0));
     if (d.getTime() >= start && d.getTime() <= end)
     {
      result += parseFloat(closingValue[i]);
     }
    }
    return result;
}

Then you would call it passing in the start and end dates, like;

{scriptobjectname}.getTotal("1/4/2011", "1/5/2011")

Change the "{scriptobjectname}" to whatever the name of the script object is.

Is that close?

Bruce

Answers (3)

Answers (3)

meem23

10-04-2017

This is exactly what I want.  Sorry I didn't respond until now, I had to rework my form and then I was pulled into another project.  Thanks so much!

meem23

14-02-2017

Thanks for your response. 

Parts of the form to hopefully show what I’m trying to do:

form.JPG

The user enters a date in the “Issued on” field.  I have a script in each “Ending Date” field that adds one year and also calls the script object to find the Ending Date and then populate the “Closing Value” field.  I need to total all closing values from 10/21/2011 to 10/21/2012; total all closing values from 10/21/2012 to 10/21/2013 and so on.   There would be hundreds of closing values to add together because there is a closing value for each date in the year - which are stored in the script object. If I were to use another function in the script object like getTotal(startDate, endDate), how do I get all of the closing values from a start date to an end date?

_Bruce_Robertson

MVP

11-02-2017

Hi,

If I understand the problem, it sounds like you need another function in your script object, like getTotal(startDate, endDate).

I think this would be easier if the closingDate values were date objects and the closingValue values were numeric values.  Is there a reason they aren't?

Regards

Bruce