Hi,
I have a Data Table and found an awesome article about cascading dropdown. So I made two different lists and linked them with the help of Lookup column. It works perfect, but I need a little customization.
When I choose A Category - for example "Category 1", the second column after click on the cell in the same row verifies if the "Category 1" has any models in column 2. Well, when the Models prepopulate and the data is set I need to wait until the user choose something - for example "Model 2" or "Model 3" and then take this value and based on some filter I use some data via pnp and fill a different column.
Every Category has several Models, but every model has one specific number which I need to prepopulate in the same row of data Table for a specific model.
Is it possible?
I use this code for prepopulating two columns - Category and Models.
const dt = fd.control("dtItems");
dt.$on("edit", function (e) {
if (e.column.field === "Category") {
console.log(e.model);
// Clear Model input field because Category is changing
// pass widget + current Category value
populateCategories(e.widget, e.model.Category);
}
if (e.column.field === "colModel") {
// pass widget + current Category and Product value
populateProducts(e.widget, e.model.Category, e.model.colModel);
}
});
function populateCategories(widget, value) {
// will show as loading
widget._showBusy();
sp.web.lists
.getByTitle("Categories")
.items.select("ID", "Title")
.get()
.then(function (items) {
// set options
widget.setDataSource({
data: items.map(function (i) {
return i.Title;
}),
});
// set value if one was selected
widget.value(value);
// hide loading state
widget._hideBusy();
});
}
// const clearInputField = (columnIndex) => {
// dt.columns[columnIndex].setDataSource([]);
// };
function populateProducts(widget, parentValue, value) {
// will show as loading
widget._showBusy();
sp.web.lists
.getByTitle("Models")
.items.select("ID", "Title", "lookupCategories/Title")
.expand("lookupCategories")
.filter("lookupCategories/Title eq '" + parentValue + "'")
.get()
.then(function (items) {
widget.setDataSource({
data: items.map(function (i) {
return i.Title;
}),
});
// set value if one was selected
widget.value(value);
// hide loading state
widget._hideBusy();
});
}
I have also tried a different approach, which is:
- Choose Category and then Model - it is OK
- What if a user change Category, I need automatically to disappear the value in the column "Model" - is it possible?
- I tried to bind a "widget" object but I was unsuccesful.
Thank you so much in advance
Dear @StepanS,
Yes, it's possible, you can clear a value like this:
dt.$on("edit", function (e) {
if (e.column.field === "Category") {
console.log(e.model);
// Clear Model input field because Category is changing
// pass widget + current Category value
populateCategories(e.widget, e.model.Category);
e.model.set('colModel', '');
}
if (e.column.field === "colModel") {
// pass widget + current Category and Product value
populateProducts(e.widget, e.model.Category, e.model.colModel);
}
});
As for setting the other value, yes, it's also possible. You can try to retrieve all the associated numbers when the form loads, and set the value when the number is selected, similar to this (but first use pnpjs on form load) - DataTable — SharePoint forms
1 Like
That worked perfectly and based on some chosen field like "Category" to pair it with "models" where users can select only models === category. I used your documentation and it helped me.
I also tried having background color based on specific column "Supplier" for "tr" element in the Data Table:
When I add a new record every record in newform lost its background-color, until I click in the cell in the categories or model. Then the code $on("change") triggers and dye it all.
When it works - the color is set based on the value in the last column, for example:
if (item.supplier === "7896") {
// Set the color
// The rest of the code
}
But when I click on "Add new record" the table looks like this - it completely lose colors.
When I edit a cell in the new row - for example under the second row - double click - the table is colorful again.
Interesting is, that when I list all the tr's in the table, and use Developer tools and hover over one element: It looks like it is selected and the element in the developer console is assigned to a form element.
After I add a new record, open a developer console and see the same array and when I hover over index 1, 2, 3 whatever, no line is "selected" on the canvas. It looks like the tr's does not exist in this state.
I use this code to change background color for all rows:
// Select Data Table
const dt = fd.control('dtItems');
// If anything in data table changes
dt.$on('change', val => {
// Reset counter
let totalPieces = 0;
// Background color if supplier is different
const rowElements = Array.from($(dt.$el).find('tr'));
// console.log(rowElements);
for (let i = 0; i < val.length; i++) {
if (supplierColorMap.hasOwnProperty(val[i].dodavatel)) {
const backgroundColor = supplierColorMap[val[i].dodavatel];
$(rowElements[i + 1]).css('background-color', backgroundColor);
}
}
This is mapped supplierColorMap variable:
Can you help me?
Thank you.
Stepan
Dear @StepanS,
Here's what you can try:
fd.rendered(() => {
const dt = fd.control('DataTable1');
const paintRows = val => {
const value = dt.value; // current datatable value
const rowElements = Array.from($(dt.$el).find('tr'));
for (let i = 0; i < value.length; i++) {
//custom color selection logic
const backgroundColor = value[i].Column1 ? 'orange' : 'violet';
$(rowElements[i + 1]).css('background-color', backgroundColor);
}
};
//initial paint
dt.ready(paintRows);
//paint after changes
dt.$on('change', paintRows);
//paint when entered edit mode
dt.$on('edit', paintRows);
})
Dear @Nikita_Kurguzov ,
When initiated - it works like a charm.
When edited - it works like a charm.
When changed - it does not. Now even when I delete a column not a row is painted. When I "add a new record," the change event is triggered three times.
(No errors related to this script are present in the developer console)
Could that be a problem?
Thank you so much,
Stepan
EDIT: I also tried a different approach to addClass like this:
// Select Data Table
const dt = fd.control('dtItems');
const paintRows = val => {
console.log(val);
const value = dt.value; // current datatable value
const rowElements = Array.from($(dt.$el).find('tr'));
for (let i = 0; i < value.length; i++) {
//custom color selection logic
// const backgroundColor = value[i].Column1 ? 'orange' : 'violet';
$(rowElements[i + 1]).addClass('one-color');
}
};
//initial paint
dt.ready(paintRows);
//paint after changes
dt.$on('change', paintRows);
//paint when entered edit mode
dt.$on('edit', paintRows);
On Edit, it works and also when the form is initiated. But after I make a change, like adding a new record or deleting one, the background-color is gone.
Dear @StepanS,
Okay, it seems to be trickier than expected. Here's one more thing that you can try:
fd.rendered(() => {
const paint = () => {
const value = dt.value; // current datatable value
for (let i = 0; i < value.length; i++) {
//custom color selection
const backgroundColor = value[i].Column1 ? 'orange' : 'violet';
// important: correct row selection
const row = $(dt.$el).find('tr[data-uid="' + value[i].uid + '"');
row.css('background-color', backgroundColor);
}
};
const dt = fd.control('DataTable1');
dt.ready(paint);
//repaint on any changes
new MutationObserver(paint).observe(dt.$el, {
childList: true,
subtree: true,
});
});
1 Like
Dear @Nikita_Kurguzov ,
perfect! Awesome, thank you.
1 Like
