Hello.
I have a calculated column in a data table that must return a different value when required items are missing from the rest of the row data.
I can successfully achieve this using a JavaScript formula. However, the calculated value does not update when I set the missing values into the table row programmatically.
Is there a reliable way to force the calculated column to refresh when the row data is updated via code?
Thank you!
Hello @chs-web,
The calculation triggers only when the row is edited manually, and if you change or add a row, you must update the value of the calculated column as well.
So you must add a calculation inside the function that updates the row.
Hi Margo, thank you.
For context, we have forms using data tables to store private information, and so we have hidden certain columns and overridden the edit function to use our own edit form.
We use a calculated column to create an “Edit” button in the table. We want that button to indicate when there is required data missing from any of the hidden columns. Here is what we have so far:
The code behind for this looks like so:
if (!data.Organization ||
((!isFlagSet(data.Privacy, Privacy.EMAIL)) && (!data.Email)) ||
((!isFlagSet(data.Privacy, Privacy.PHONE)) && (!data.Phone)) ||
((!isFlagSet(data.Privacy, Privacy.ADDRESS)) && (!data.Address)) ||
((!isFlagSet(data.Privacy, Privacy.CITY)) && (!data.City)) ||
((!isFlagSet(data.Privacy, Privacy.STATE)) && (!data.State)) ||
((!isFlagSet(data.Privacy, Privacy.ZIP)) && (!data.Zip))) {
return '<a role="button" class="k-button chs-row-edit-button" href="javascript:void(0)" onClick="editDTRow(\'' + data.uid + '\')">Edit<span style="color: #df3f3f;">*</span></a>';
} else {
return '<a role="button" class="k-button chs-row-edit-button" href="javascript:void(0)" onClick="editDTRow(\'' + data.uid + '\')">Edit</a>';
}
As you can see we are returning HTML markup.
(Privacy is a number column that contains a bitmask to indicate which of the columns contain private information and are thus not editable. It’s not really relevant for this discussion, but the upshot is we only care about missing information when the user can edit it.)
Here is the function that updates the table row from our custom edit form:
// Validate form and update edited row
window.updateRowData = async function() {
console.log('updateRowData');
if (await validateEditForm()) {
let dt = fd.control('MyOrganizations');
let dtRows = dt.value;
if(rowData.uid) {
// Updating an existing row
// Find the row we need to change
for (let i = 0; i < dt.value.length; i++) {
if (dt.value[i].get('uid') == rowData.uid) {
dt.value[i].set('Organization', fd.field('Organization').value);
dt.value[i].set('USEFNo', fd.field('USEFNo').value);
if (!isFlagSet(rowData.Privacy, Privacy.EMAIL)) { dt.value[i].set('Email', fd.field('Email').value) };
if (!isFlagSet(rowData.Privacy, Privacy.PHONE)) { dt.value[i].set('Phone', fd.field('Phone').value) };
if (!isFlagSet(rowData.Privacy, Privacy.ADDRESS)) { dt.value[i].set('Address', fd.field('Address').value) };
if (!isFlagSet(rowData.Privacy, Privacy.CITY)) { dt.value[i].set('City', fd.field('City').value) };
if (!isFlagSet(rowData.Privacy, Privacy.STATE)) { dt.value[i].set('State', fd.field('State').value) };
if (!isFlagSet(rowData.Privacy, Privacy.ZIP)) { dt.value[i].set('Zip', fd.field('Zip').value) };
}
}
} else {
// Adding a new row
let newRow = [{
Organization: fd.field('Organization').value,
USEFNo: fd.field('USEFNo').value,
Email: fd.field('Email').value,
Phone: fd.field('Phone').value,
Address: fd.field('Address').value,
City: fd.field('City').value,
State: fd.field('State').value,
Zip: fd.field('Zip').value,
Privacy: Privacy.NONE
}
];
dtRows.push(newRow[0]);
}
showEditForm(false);
$('.edit-form-invalid').css('display', 'none');
} else {
$('.edit-form-invalid').css('display', 'block');
}
}
Are you suggesting that we can simply set the Edit column value with the correct HTML markup here? Will that break the calculated column code in any way?
Well, I ended up trying this. Unfortunately, it appears that calculated column values cannot be set programmatically. When I tried to set the value of this column directly, there was no change, even after I made the column editable.
I also tried making the Edit column just a regular text column so I could set its value, but then the markup was not escaped and it was treated as regular text.
Am I approaching this correctly?
I think I am probably going to abandon this direction for now. Thanks for the initial suggestion.
All we are really trying to achieve is a simple visual indicator in the table row for when the row is invalid. I know that when editing the row, individual fields show errors for missing data, but it would be nice if rows with missing data were highlighted in some way when the table is validated, even when not being edited.
Thanks for listening.
Hello @chs-web,
I tried to reproduce the set up of your Data Table and setting the calculated column value should work. In your case the calculated column must be a string with HTML. So first you create an object and then push it to the existing data in Data Table.
let row = {
Column1: `<a role="button" class="k-button chs-row-edit-button" href="javascript:void(0)" onClick="editDTRow('ee9b3822-cc12-4d94-9ff4-fd0200c64275')">Edit<span style="color: #df3f3f;">*</span></a>`,
Column2: '58'
}
fd.control('DataTable1').value.push(row);
Before creating an object you can add a condition as you have in Calculated column to build a string.
About coloring rows, you can add CSS class to a row:
let table = fd.control('DataTable1');
let value = table.value;
let row = $(table.$el).find('tr');
if(value){
for (var i = 0; i < value.length; i++){
if(value[i].Column2 == '1' ) {
//sets the background color of the row to red
$(row[i+1]).addClass('class-name');
}
}
}
CSS:
tr.class-name {
background-color: red;
}