cancel
Showing results for 
Search instead for 
Did you mean: 
Workflow Hero

Sum a repeating section amount, based on the lookup control?

Jump to solution

I'm now trying to do basically what was accomplished in this thread below.

 

My problem is I have over 80 choices in the drop down lookup control. I'm trying to find a way to do this without having a calculated value for each and every choice. The only thing I can think of is to parse the XML of the repeater in the workflow and fill the information on the form afterwards. Ideally, I could have it calculated on the form though. Anyone have any ideas?

0 Kudos
Reply
21 Replies
Workflow Hero

Re: Sum a repeating section amount, based on the lookup control?

Jump to solution

Hi, 

How do you want to display the result for sum of 80 choices? 

What am meaning you have 80 results to display at least as sum regardless the calculated values for each and every choice.

So it's already complicated.

If you know the set of choices you need to get sum of, it will be easier, otherwise JavaScript will be a better option.

BR,

0 Kudos
Reply
Workflow Hero

Re: Sum a repeating section amount, based on the lookup control?

Jump to solution

Wait... why would you need a calculated field for every choice? 

Do you have a SharePoint List from where you are generating your choices as shown in the Thread that you linked? 

If so - Do you have a Value column for each of the List Items there? 

If that's the case it's an arbitrary thing to pull that value in using a lookup() function in a formula, resulting in one Calculated Control in your Repeating Section Row just like that example shows. 

However, if you're just using a Choice Control and you have manually entered into it a bunch of choices, I would recommend maybe just throwing them into an actual SharePoint list instead as it's far more flexible. 

If that can't be done, then using a javascript formula inside of a Calculated Control outside of your Repeating Section is the way to go. We can go down that road if needed. 

0 Kudos
Reply
Workflow Hero

Re: Sum a repeating section amount, based on the lookup control?

Jump to solution

I hope that I don't.

So with this example, I would ideally be able to show a list that looks like

41030-200 - Food DIET - $2,400(sum of the two rows that have this selected)

41031-200 Snack and Hydration DIET 

etc. 

I want to sum up the values in the Amount field if the Account field is the same selection, and display that on the form.

0 Kudos
Reply
Workflow Hero

Re: Sum a repeating section amount, based on the lookup control?

Jump to solution

So you'd like to have another Choice Control outside of the Repeating Section that lets you select a G/L Account #, and then if there are any corresponding rows with that same G/L Account # in your Repeating Section, sum their Amounts into a single value? 

Is this correct? 

0 Kudos
Reply
Workflow Hero

Re: Sum a repeating section amount, based on the lookup control?

Jump to solution

Not exactly. Ideally, selecting a new option in the lookup drop down(Account) would add it to a panel somewhere else and display a running sum of all amounts that are also of that same lookup choice. If possible, it would be better to avoid having to initiate it with a secondary control; such as a choice.

0 Kudos
Reply
Workflow Hero

Re: Sum a repeating section amount, based on the lookup control?

Jump to solution

Alright. This is a quick and dirty way to maybe accomplish what you'd like. Feel free to edit the answer to your particular desires, or, if you feel that there is something you need further help with, feel free to ask. 

- Form Setup -

There are (3) main controls (excluding the Repeating Section itself), they are as follows

  1. AccountNumber - Choice Control with a few selections:

    (Easy to copy options:
    10000-200 - Apple
    10001-200 - Apricot
    10002-200 - Avocado
    10003-200 - Banana
    10004-200 - Blueberry
    10005-200 - Cherry
    10006-200 - Coconut) 
  2. CountQuantitiesSingle Line Text Control
  3. bulkText - Rich Text Control

    For the Default Value of this control, you'll set it to this empty table with labels. 

    <table id="tallyTable" style="width:100%">
      <tbody>
        <tr>
          <th>Account Number</th>
          <th>Instances</th>
          <th>Total Qty</th>
        </tr>   
      </tbody>
    </table>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍


    Additionally, you should create a new Formatting Rule on the Rich Text Control configured as: 


    With the formula code of:

    (function(accountNumbers, countQuantities) {
      var currentStateObject = {};

      /* Number.isNumber polyfill */
      Number.isNaN = Number.isNaN || function(value) {    
          return value !== value;
      };

      accountNumbers.forEach(function(accountNumber, accountIndex) {
        if (!Number.isNaN(accountNumber) && accountNumber) {
          var countQuantity = parseInt(countQuantities[accountIndex]);
          if (Number.isNaN(countQuantity) || countQuantity < 1) {
            countQuantity = 0;
          }

          if (currentStateObject.hasOwnProperty(accountNumber)) {
            currentStateObject[accountNumber].totalQty = currentStateObject[accountNumber].totalQty + countQuantity;
            currentStateObject[accountNumber].totalInstances = currentStateObject[accountNumber].totalInstances += 1;
          } else {
            currentStateObject[accountNumber] = {
              totalQty: countQuantity,
              totalInstances: 1
            };
          }
        }
      });

      var tallyTable = NWF$("#tallyTable");
      tallyTable.find("tr[class]").remove();

      Object.keys(currentStateObject).forEach(function(accountNumber){
        var newTableRow = NWF$("<tr><td></td><td></td><td></td></tr>");
        newTableRow.addClass(accountNumber);
        NWF$(newTableRow.children()[0]).text(accountNumber);
        NWF$(newTableRow.children()[1]).text(currentStateObject[accountNumber].totalInstances);
        NWF$(newTableRow.children()[2]).text(currentStateObject[accountNumber].totalQty);
        tallyTable.find("tbody").append(newTableRow);
      });

      return true;
    }(AccountNumber, CountQuantities))

    /* if the Control represented by 'AccountNumber' is a Lookup Control, you'll need to chagne the passed in arguments to (parseLookup(AccountNumber), CountQuantities) */

    (Please Note: both the {AccountNumber} and {CountQuantites} as shown in the above code (at the bottom), should be replaced by the appropriate NAMED CONTROLS)



- Results -

Once all of the above is in place, it's rather straight forward. 

Selecting an Account Number from the Choice Control will add it to the Display Table along with the Instances (the number of times that particular Account Number has been selected) as well as the current total of all similar Account Numbers. 

When running in Edit Mode it looks like: 

While in Display / View Only Mode it looks like: 

Hopefully this accomplishes what you are trying to do. Because this data is being saved to the Item / Form, you can also probably use it in other ways (like in a Workflow using a Query XML action or drawn as a table in SharePoint proper). 


Reply
Workflow Hero

Re: Sum a repeating section amount, based on the lookup control?

Jump to solution

Instead of a drop down, I have a lookup control, and instead of quantity(integer) I have amount(currency). 

At first I tried it with these fields. The error I received was "Object doesn't support property or method 'isNaN'". 

Thinking it might be ab issue with the lookup, I added a choice control and used that instead of the lookup and got the same error. This is happening when opening the form, or selecting options in controls. This happens if I type in the name of the field, or select it from the Formula Builder.

So I tried this out on a new form and was getting the same error. Am I supposed to change the variable names throughout the code, or just the last line?

Screenshots below:

(function(accountNumbers, countQuantities) {
  var currentStateObject = {};
  accountNumbers.forEach(function(accountNumber, accountIndex) {
    if (!Number.isNaN(accountNumber) && accountNumber) {
      var countQuantity = parseInt(countQuantities[accountIndex]);
      if (Number.isNaN(countQuantity) || countQuantity < 1) {
        countQuantity = 0;
      }

      if (currentStateObject.hasOwnProperty(accountNumber)) {
        currentStateObject[accountNumber].totalQty = currentStateObject[accountNumber].totalQty + countQuantity;
        currentStateObject[accountNumber].totalInstances = currentStateObject[accountNumber].totalInstances += 1;
      } else {
        currentStateObject[accountNumber] = {
          totalQty: countQuantity,
          totalInstances: 1
        };
      }
    }
  });

  var tallyTable = NWF$("#tallyTable");
  tallyTable.find("tr[class]").remove();

  Object.keys(currentStateObject).forEach(function(accountNumber){
    var newTableRow = NWF$("<tr><td></td><td></td><td></td></tr>");
    newTableRow.addClass(accountNumber);
    NWF$(newTableRow.children()[0]).text(accountNumber);
    NWF$(newTableRow.children()[1]).text(currentStateObject[accountNumber].totalInstances);
    NWF$(newTableRow.children()[2]).text(currentStateObject[accountNumber].totalQty);
    tallyTable.find("tbody").append(newTableRow);
  });

  return true;
}(parseLookup(choice1), parseLookup(txt1)))

Am I supposed to hand type the names of the controls, or select them from the builder?

0 Kudos
Reply
Workflow Hero

Re: Sum a repeating section amount, based on the lookup control?

Jump to solution

You should be selecting them from the Named Controls tab of your formula builder. 

0 Kudos
Reply
Workflow Hero

Re: Sum a repeating section amount, based on the lookup control?

Jump to solution

I must be missing a piece then. All I get is this:

0 Kudos
Reply