Good day!
I have a form with an list and library control. Can i on change of this library list control to copy all items to an datable?. I need this because items from list library control are in another list, but i need in current list view to show info.
Hello @ixxxl,
You can use the code below to populate the DataTable control rows with items from List or Library control.
fd.control('SPDataTable1').$on('change', function() {
fd.control('SPDataTable1').refresh().then(function(value){
var dt_rows = [];
var items = fd.control('SPDataTable1')._widget.dataItems();
for (let i = 0; i < items.length; i++) {
var obj = {
'Column1': items[i].Title,
'Column2': items[i].Field1
};
dt_rows.push(obj);
}
fd.control('DataTable1').clear();
fd.control('DataTable1').value = dt_rows
});
});@mnikitina
Hello,
i have an error
fd.control('SPDataTable1').$on('change', function() {
fd.control('SPDataTable1').refresh().then(function(value){
var dt_rows = [];
var items = fd.control('SPDataTable1').widget.dataItems();
for (let i = 0; i < items.length; i++) {
var obj = {
'Column1': items[i].Titlu,
'Column4': items[i].User2
};
dt_rows.push(obj);
}
fd.control('DataTable1').clear();
fd.control('DataTable1').value = dt_rows
});
});
Try out this code instead:
fd.control('SPDataTable1').$on('change', function() {
fd.control('SPDataTable1')._widget.dataSource.read().then(function(){
var dt_rows = [];
var items = fd.control('SPDataTable1').widget.dataItems();
for (let i = 0; i < items.length; i++) {
var obj = {
'Column1': items[i].Title
};
dt_rows.push(obj);
}
fd.control('DataTable1').clear();
fd.control('DataTable1').value = dt_rows
})
});
1 Like
@mnikitina
Good day !
thank you for now it works!!
one more thing when i try to put the date - datatable is empty.. i put column3 as a date
fd.spRendered(function(){
fd.control('SPDataTable1').$on('change', function() {
fd.control('SPDataTable1')._widget.dataSource.read().then(function(){
var dt_rows = [];
var items = fd.control('SPDataTable1').widget.dataItems();
for (let i = 0; i < items.length; i++) {
var obj = {
'Column1': items[i].Title,
'Column4': items[i].User2,
'Column2': items[i].Creat
};
dt_rows.push(obj);
}
fd.control('DataTable1').clear();
fd.control('DataTable1').value = dt_rows
})
});
});
ps.
if column3 -is string - column2 is empty. if column2 is a date type - column2 show null
Probably the problem is in the date format. Try to format the date before creating a new line in the DataTable:
fd.control('SPDataTable1')._widget.dataSource.read().then(function(){
var dt_rows = [];
var items = fd.control('SPDataTable1').widget.dataItems();
for (let i = 0; i < items.length; i++) {
//format date
var st = items[i].Date;
var pattern = /(\d{2})\.(\d{2})\.(\d{4})/;
var dt = new Date(st.replace(pattern,'$3-$2-$1'));
var obj = {
'Column1': items[i].Title,
'Column2': dt
};
dt_rows.push(obj);
}
fd.control('DataTable1').clear();
fd.control('DataTable1').value = dt_rows
})
1 Like
@mnikitina
Thank you!! it works !! and as a previous code also work! the issue was ,that i need internal field name Created , i was wrong putted a display name Creat
1 Like
@mnikitina
Good day,
can i now based on a datable or maybe from list and library control create new items in a list ?
datatable value field:
[{"Column1":"Concediu neplătit (cont propriu)","Column2":"26.04.2023","Column3":"26.04.2023","Column4":"1"},{"Column1":"Concediu suplimentar plătit pe motiv familial (Decesul fratelui/surorii, bunicului/bunicii)","Column2":"25.04.2023","Column3":"25.04.2023","Column4":"1"}]
and add some fields from current list item: Title, function, currentUser.
then to create new items one by one in other new list.
new item:
Title(from current item)
Function(from current item)
currentUser(from current item)
DateStart (Column2 from datable)
DateEnd (Column3 from datable)
VacationType (Column1 from datatble)
DaysNumber (Column4 from datatable)
Hello @ixxxl,
You can create list items using PnPjs. You must loop through objects in the array and create an item for each. Please see PnPjs function example in this post.
Or you can create list items with the flow. Please find the instructions in Сreate SharePoint items from Data Table rows with Power Automate article. This is for Public forms, but it is also applicable for SharePoint.
@mnikitina
i'm trying to
fd.control("SPDataTable1")
._widget.dataSource.read()
.then(function () {
var item = fd.control("SPDataTable1").widget.dataItems();
for (let i = 0; i < item.length; i++) {
pnp.sp.web.lists
.getByTitle("Excep%C8%9Bii%20Suspendare%20Accese%20Concedii")
.items.add({
Title: item[i].Title,
DeLa: new Date(item[i].Dat_x0103__x0020__x00ee_nceput), //dateField
P_x00e2_n_x0103_La: item[i].Dat_x0103__x0020_sf_x00e2_r_x021, //dateField
AngajatId: item[i].Nume_x0020_prenume[0].id,
});
}
});
and have problems with date fields. if i put like this:
P_x00e2_n_x0103_La: item[i].Dat_x0103__x0020_sf_x00e2_r_x021, //dateField
in a new item created i put the date :11.05.2023, but is showing as 05.11.2023
if i use new Date () - the date in new item is empty.
Get the data from the property that stores it in a date format:
For this, you need to use the property name with the dot in the end:
fd.control("SPDataTable1")
._widget.dataSource.read()
.then(function () {
var item = fd.control("SPDataTable1").widget.dataItems();
for (let i = 0; i < item.length; i++) {
pnp.sp.web.lists
.getByTitle("Excep%C8%9Bii%20Suspendare%20Accese%20Concedii")
.items.add({
Title: item[i].Title,
DeLa: new Date(item[i]['Dat_x0103__x0020__x00ee_nceput.']), //dateField
P_x00e2_n_x0103_La: new Date(item[i]['Dat_x0103__x0020_sf_x00e2_r_x021.']), //dateField
AngajatId: item[i].Nume_x0020_prenume[0].id,
});
}
});
@mnikitina
good day, i tried, but it creates empty dates, and in console shows invalid date:
fd.control("SPDataTable1")
._widget.dataSource.read()
.then(function () {
var item = fd.control("SPDataTable1").widget.dataItems();
for (let i = 0; i < item.length; i++) {
console.log(new Date(item[i]["Dat_x0103__x0020__x00ee_nceput."]))
pnp.sp.web.lists
.getByTitle("Excep%C8%9Bii%20Suspendare%20Accese%20Concedii")
.items.add({
Title: item[i].Title,
DeLa: new Date(item[i]["Dat_x0103__x0020__x00ee_nceput."]),
P_x00e2_n_x0103_La: new Date(
item[i]["Dat_x0103__x0020_sf_x00e2_r_x021."]
),
AngajatId: item[i].Nume_x0020_prenume[0].id,
});
}
});
new list item
The source and destination fields are Date only, the same. just checked.
Please run the code from the browser's console to check the internal names and data format of date columns.
For this define fd globally in the JavaScript editor:
window.fd = fd;
Run this code from the browser's console:
//change index if necessary
fd.control("SPDataTable1").widget.dataItems()[0]
You will see the internal names and values of all columns. Find the date columns and check their internal names and data format. It should be like this:
Make sure you ae using internal names of the columns in the code.
@mnikitina
i verified. the names of fields are internal:
when i try in console to put console.log(new Date(item[i]['Dat_x0103__x0020__x00ee_nceput.']))
fd.control("SPDataTable1")
._widget.dataSource.read()
.then(function () {
var item = fd.control("SPDataTable1").widget.dataItems();
for (let i = 0; i < item.length; i++) {
console.log(new Date(item[i]['Dat_x0103__x0020__x00ee_nceput.']))
pnp.sp.web.lists
.getByTitle("Excep%C8%9Bii%20Suspendare%20Accese%20Concedii")
.items.add({
Title: item[i].Title,
DeLa: new Date(item[i]['Dat_x0103__x0020__x00ee_nceput.']), //dateField
P_x00e2_n_x0103_La: new Date(item[i]['Dat_x0103__x0020_sf_x00e2_r_x021.']), //dateField
AngajatId: item[i].Nume_x0020_prenume[0].id,
});
}
});
it returns invalid date:
if i put simple item[i].Dat_x0103__x0020__x00ee_nceput - it returns string date. with new Date and with new Date +'.' = invalid date
it must work on sharepoint 2019 ?
Since List or Library control in SharePoint 2019 does not store date column data in ISO format, you need to convert the string to date as follows:
var item = fd.control("SPDataTable1").widget.dataItems();
for (let i = 0; i < item.length; i++) {
var dateColumn1 = item[i].Dat_x0103__x0020__x00ee_nceput;
var dateColumn2 = item[i].Dat_x0103__x0020_sf_x00e2_r_x021;
pnp.sp.web.lists
.getByTitle("Excep%C8%9Bii%20Suspendare%20Accese%20Concedii")
.items.add({
Title: item[i].Title,
DeLa: new Date(dateColumn1.split(".")[2], dateColumn1.split(".")[1] - 1, dateColumn1.split(".")[0]),
P_x00e2_n_x0103_La: new Date(dateColumn2.split(".")[2], dateColumn2.split(".")[1] - 1, dateColumn2.split(".")[0]),
AngajatId: item[i].Nume_x0020_prenume[0].id,
});
}
1 Like