Sum of total based on checkboxes marked

Help Center Get Answers
1

I send quotes to clients with my knack database. Each quote has many service options the client can chose. What I need is that: when Client selects a checkbox for each service he wants, then the total field at the bottom would update the quote total based on the checkboxes marked. This would be done in real time, without having to submit a form or click any buttons for the Total field to update. Do I need custom code for that? It sounds simple to me, maybe there’s an easy way to do it . Any insights?

2

As a non coder I can tell you it will be a code based solution :man_technologist::+1:

3

Hi @BrunaRego70476, I like a nice challenge like this!
The method below listens for any checkboxes being selected in the Service(s) field and updates the Total price field with the new sum.
quotation-services

Requirements

  • A Services table (object) with the below fields:

    • Service name (short text)
    • Price (currency)
    • Service / price (text formula), with formula {Service name} ({Price}) formula. This should also be set as the display field for the Services table
      Here is an example set of Service records:
      image

  • A Quotations table with the below fields at minimum:

    • Service(s) (connection), with a many-to-many relationship to the Services table (field_yy)
    • Total price (currency) (field_zz)

  • A form to add a new Quotation (view_xx).

Solution

  1. Insert the following code into your Knack JavaScript editor:
$(document).on('knack-view-render.view_xx', function(event, view, record) {

  const connectionFieldKey = "field_yy"; // Services connection field
  const totalFieldKey = "field_zz"; // Total price field
  const connectionFieldControl = $(`#${view.key} #kn-input-${connectionFieldKey}`);

  $(`#${totalFieldKey}`).prop("disabled", true); // Disable the total field;

  // Listen for any checkbox being changed
  connectionFieldControl.find('input[type=checkbox]').change(function() {
    calculateTotal();
  });

  // Find all checkboxes that are checked, extract the prices, sum them, and output the total sum in the total price field
  function calculateTotal() {

    let sumTotal = 0; // Initial sum
    connectionFieldControl.find('.conn_inputs .control').each(function() {
      if ($(this).find('input[type=checkbox]').is(":checked")) { // Check if the checkbox is checked
        const optionPriceString = $(this).find('label span').text().match(/\((.*?)\)/)[1]; // Get price between round brackets, e.g. $40.00 from ($40.00)
        const optionPrice = parseFloat(optionPriceString.replace(/\$/g,'')) // Convert from a string to a float, e.g. $40.00 to 40
        sumTotal += optionPrice; // Add to the total sum
      }
    });
    sumTotal = sumTotal.toFixed(2); // Format with two decimal places, e.g. 40 to 40.00
    $(`#${totalFieldKey}`).val(`${sumTotal}`); // Update the total field

  }

});
  1. Replace view_xx with your Add quotation form view key
  2. Replace field_yy with your Service(s) field key
  3. Replace field_zz with your Total price field key

Code workflow

  1. On render of the view:
    a. Disable the Total price field from editing
    b. Add an event handler to calculate the total price whenever a checkbox is checked
  2. On change of a checkbox:
    a. Loop through each option in the connection field and find each that is checked
    b. Get the checkbox label and extract the value between the round brackets (e.g. $40.00 from ($40.00))
    c. Convert to a numerical value and add to the total price
    d. Add the sum total to the Total price field, and convert to decimals

Notes

  • You must ensure the Services table display field contains the name and price in round brackets (e.g. Dishwashing ($40.00)), otherwise the code won’t be able to find the correct price
  • It is important that the value returned to the Total price field does not have a currency ($) sign, as it does not read it as numerical
  • You can use the Total price field to ensure the price is locked in. That way if any of the Service records change in price, it won’t update this field, unlike a sum field.

Let me know if you have any questions!

7 Likes
4

Very impressive @StephenChapman :facepunch:

5

@StephenChapman could your logic be modified to limit how many selections can be made and alert the user that they can only select x options?

So, what I am trying to do is limit how many selections that user can select in the multiple selection field.

6

Hi @BobLove38845,
Sure thing! You can adjust the code in the ‘connection field control’ change function:

  // Listen for any checkbox being changed
  connectionFieldControl.find('input[type=checkbox]').change(function() {
    var checkedCheckboxes = connectionFieldControl.find('input[type="checkbox"]:checked');
    // Disable any unchecked inputs if 3 have already been checked
    if(checkedCheckboxes.length >= 3){
      connectionFieldControl.find('input[type="checkbox"]').not(':checked').prop('disabled', true);
    }
    // Enable all checkboxes if 2 or less checkboxes are checked
    else {
      connectionFieldControl.find('input[type="checkbox"]').not(':checked').prop('disabled', false);
    }
    calculateTotal();
  });

This will check if 3 or more checkboxes are checked, and will disable the other unchecked boxes.
You can add your own instructions to the field to instruct the user to ‘select only three’.

limit-to-three-selections

1 Like
7

@StephenChapman This so AWESOME! I even was able to adapt for other non-calculating checkbox fields too. I really wish Knack would have this as an option to set these limits when the connection is created with the table/object. But, in the mean time this is truly a golden nugget in my opinion!
Thanks again!
Bob

8

@StephenChapman I was thinking about have a table where this limit number could be stored so that if the end user manager wanted to change; I would not have to give them access to modify the JS. Do you have any thoughts on that? Would I just reference that to be ==“object.field_##”?

Thanks again!