Hello,
I'm at a bit of a conundrum with this form that's been in use since around 2020 before I came on board at this organisation. It was set up to use a Collection to enter payslip information in the form of:
- Week Ending (date field)
- Total Hours Worked (single line field)
- Hourly Rate of Pay (currency field)
- Gross Weekly Earnings (currency field)
I want to revise the form to multiply the total hours worked by the hourly rate of pay to return the result in the gross weekly earnings field for each row of the collection. The issue appears to be that the total hours worked is a single line field, which doesn't allow for the usual Expression to be used such as
=MULT(INDEX(Collection.variable,ROW()), Collection.variable2,ROW()))
I've gotten something similar to work thanks to this previous thread to return the total of the hours worked column in a field outside the collection by using the JavaScript provided. I've tried modifying and consulting AI to rewrite the functionality to return the value I need using this script:
$(document).ready(function () { $('.cf-collection-block').on('keyup change', 'input', multiplyTotal); function multiplyTotal() { var total = 1; // Start with 1 as multiplication identity element $('.cf-collection-block ul').each(function () { var lineFieldValue = parseNumber($(this).find('.lineField input').val()); // Value from single line field var currencyFieldValue = parseNumber($(this).find('.currencyField input').val()); // Value from currency field total *= lineFieldValue * currencyFieldValue; // Multiply the values }); $('.totalCurrencyField input').val(total); // Populate the total currency field. } function parseNumber(n) { console.log("Input value:", n); // Log the input value if (!n || n.trim() === '') { console.log("Returning 0 because input is empty"); return 0; // Return 0 if n is undefined, null, or empty } // Replace commas with empty string and attempt parsing var cleanedValue = n.replace(/,/g, ''); console.log("Cleaned value:", cleanedValue); // Log the cleaned value var parsedValue = parseFloat(cleanedValue); console.log("Parsed value:", parsedValue); // Log the parsed value // Check if parsedValue is a valid number if (!isNaN(parsedValue) && isFinite(parsedValue)) { return parsedValue; } else { console.log("Parsing failed, returning 0"); return 0; // If parsing fails, return 0 } } });
Although this just returns 0 in the desired total field no matter what the values are. Is there any fix for this in Forms 10.4? I'm aware that simply swapping the single line field out for a number field would allow for the expression to be used and not result in the need for JS, however this form sends an external link with another form that pulls the data from a table, which is managed by an external managed service provider, which would cost money to replace and amend. I want to avoid this unless it becomes the absolute last resort to get this working.
Any help or advice is greatly appreciated!
Thank you