Solved

Repeating Table Calculations

  • 10 August 2021
  • 5 replies
  • 32 views

Badge +8

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.

 

19130i0279D564363FBE78.png

 

icon

Best answer by MegaJerk 12 August 2021, 23:00

View original

5 replies

Badge +8

Does anyone have any ideas?

Userlevel 5
Badge +14

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? 


 


 

Badge +8

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.


 

Userlevel 5
Badge +14

Alrighty, let's give this a shot.


 


Below is a simple form: 



 


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: 



 


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:



 


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:



 


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


 



 


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:



 


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:



 



 


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. 


 

Badge +8
Brilliant!! Sorry was away for two weeks but this is exactly what I needed! Thank you so much for your help!

Reply