calc

[johan]

Hi
I use the calc object in a subform

Code: Select all

nuTotal('bron.bron_vte')

Is there any possibility to count only those rows that match a condition, e.g. where the date in the row >is bigger than curdate()

JOhan

[kev1n]

Hi,

You can't do that with a calc object and will need JavaScript. Call the function updateSum() whenever a date or number field is changed.
Please note that the current selector is targeting elements with the ID 'bron_date'. You will need to update this selector to match the ID of your specific element. If your date field has a different ID, please replace 'bron_date' with your element ID in the updateSum() function and in other places where it is referenced. The same applies to "total_value"

Code: Select all

// This function calculates the sum of values in the specified subform
// where the date is greater than the current date and the row has not been deleted.
function subFormCalculateSum(subform, fieldDate, fieldNumber) {

  // Get the subform object and the indices of the specified fields
  const sfObj = nuSubformObject(subform);
  const [columnDate, columnNumber] = [sfObj.fields.indexOf(fieldDate), sfObj.fields.indexOf(fieldNumber)];

  let sum = 0;

  // Iterate through each row in the subform
  sfObj.rows.forEach((row, i) => {
    if (sfObj.deleted[i] == 0) { // Exclude rows that have been deleted
      const dateValue = row[columnDate];
      const numberValue = row[columnNumber];

      if (isDateGreaterThanCurrentDate(dateValue)) { // Check if date is greater than current date
        sum += Number(numberValue); // Add the number value to the sum
      }
    }
  });

  return sum; // Return the sum of number values
}

// This function checks if the given date is greater than the current date.
function isDateGreaterThanCurrentDate(dateString) {
  const currentDate = new Date();
  const inputDate = new Date(dateString);

  return inputDate > currentDate;
}


function updateSum() {

  // Call the subFormCalculateSum function to get the sum of values
  const sum = subFormCalculateSum("bron", "bron_date", "bron_vte");  <---- RENAME "bron_date"
  
  // Set the sum value in the total field
  nuSetValue('total_value', sum);  <---- RENAME "total_field"
}

function attachChangeListener() {
	$('[id^=bron][id$=bron_date], [id^=sf][id$=bron_vte]').off('change', updateSum).on('change', updateSum); <---- RENAME "bron_date"
}

attachChangeListener();

To ensure the event listeners are attached to all fields, including newly created ones, also add an afterinsertrow event to the subform. This event will attach the event listeners to all fields in the newly created row. You can add the following code to your JavaScript:

Code: Select all

attachChangeListener();

[johan]

Kev1n
Thanks for your help.
I'll try it.
Johan