ctoper11
Apprentice

Repeating Table Calculations

Jump to solution

OK so here's an interesting one. I have two lists that I'm working with here. List A is the Suppliers list and List B is the Products. Product lookup is nested in a repeating table along with a single line of text column called Quantity and a calculated field named Calc_Price. This field is looking up the Products list. So using Cascading dropdown you choose a Supplier which then filters out that supplier's product in the nested dropdown then you choose a product and the calc field pulls the price. So I already have a formula in that field. As you can see in my screenshot I have everything totalling up nicely in Order Total field. 

 

My issue is I want to be able to add it by quantity as well. Meaning as in my screenshot if I choose two items in the Quantity field that number should become 200. It should also be noted there's a hidded Calc field that counts all those prices and adds them with Tax and Shipping to give the Order Total at the bottom. Any help would be greatly appreciated. Thanks to everyone in advance.

 

ctoper11_0-1628620624423.png

 

0 Kudos
Reply
5 Replies
ctoper11
Apprentice

Re: Repeating Table Calculations

Jump to solution

Does anyone have any ideas?

0 Kudos
Reply
MegaJerk
Collaborator

Re: Repeating Table Calculations

Jump to solution

If I'm reading correctly, you'd simply like to add a Qty that will affect the overall total in some meaningful way. 

Questions: 

 

  1. Are you using any libraries (like: https://currency.js.org/) to handle the monetary side of these calculations or are you doing it by hand?
    1. If not I highly recommend it as it'll save you a lot of headaches and potential pitfalls of floating-point math!

  2. Can the default Qty value just be 1?

  3. Are there any upper limits to what a Qty should be, or are we alright with any positive integer? 

 

 

0 Kudos
Reply
ctoper11
Apprentice

Re: Repeating Table Calculations

Jump to solution

Thanks a million @MegaJerk for your response!

 

Correct although I want the quantity to affect the total number for each row that gets created in the repeating table and then I get the total number below. To answer your questions:

 

1. I am not for this project although thank you I definitely will bookmark that for a future project. Fortunately for this I am just doing the counting by hand only because the ask here is to just have a document that can be printed out as a PDF. Once closed there is zero logic that would need to be used.

2. Yes I can absolutely make the default value be 1 if need be.

3. There are no upper limits and positive integers would be just fine.

 

0 Kudos
Reply
MegaJerk
Collaborator

Re: Repeating Table Calculations

Jump to solution

Alrighty, let's give this a shot.

 

Below is a simple form: 

MegaJerk_0-1628801071274.png

 

On the form are 5 major Controls, below is a table listing any info about them: 

Control Name Control Type
control_Fruit

Lookup

control_Price Calculated
control_Qty Single Line Text
control_SubTotal Calculated
control_Total Calculated

 

The list that I'm pulling the Fruit values from in my Lookup Control is configured as follows: 

MegaJerk_2-1628801342697.png

 

With that, let's get into the details of the Calculated Controls

 

the control_Price control is configured the following way to get the Price Column value from the Fruits List list:

MegaJerk_3-1628801439795.png

 

The Formula:

lookup("Fruits List", "Title", parseLookup(control_Fruit), "Price")

(Note: you cannot copy paste this as you will need to reference your own controls)

 

 

The control_SubTotal control is configured as shown:

MegaJerk_4-1628801511947.png

 

the formula: 

((control_Price * 100) * control_Qty) / 100

(Note: you cannot copy paste this as you will need to reference your own controls)

 

While the control_Qty control doesn't get a formula, I AM using a *formatting rule* to ensure that the value is ALWAYS an integer above 0

 

MegaJerk_5-1628801650557.png

 

The Formula for that rule is as follows:

(function(formControlCall) {
  "use strict";
  var formControlID = formControlCall.split("'")[1] || "";
  var targetControl = sourceContext.find("[formcontrolid='" + formControlID + "'].nf-filler-control");

  if (!targetControl.closest(".nf-repeater-row").hasClass("nf-repeater-row-hidden")) {
    var targetInput = NWF$(targetControl.find("[formcontrolid][id]")[0]);
    var currentValue = parseInt(targetInput.val(), 10);
    
    if (Number.isNaN(currentValue) || currentValue < 1 || currentValue !== parseFloat(targetInput.val())) {
      targetInput.trigger("focus").val((currentValue > 1) ? currentValue : 1).trigger("blur").trigger("change");
    }
  }
  return false;
}("{Control:Self}"))

(Note: you CAN copy / paste this!)

 

Last but not least, the control_Total control is configured like:

MegaJerk_6-1628801746579.png

 

The formula:

sum(control_SubTotal)

(Note: you cannot copy paste this as you will need to reference your own controls)

 

All of that results in the following:

MegaJerk_7-1628801920498.png

 

MegaJerk_8-1628801947431.png

 

As you can see we have a working Qty, SubTotal, and Total. I hope that this helps you complete the task you are working on. 

 

0 Kudos
Reply
ctoper11
Apprentice

Re: Repeating Table Calculations

Jump to solution
Brilliant!! Sorry was away for two weeks but this is exactly what I needed! Thank you so much for your help!
0 Kudos
Reply