I'm wondering if there is a way to manipulate fields that are in a secondary list but are added on the primary form as a Data Source and List/Library section.
Specifically, what I am trying to do is based on a condition on the primary form, (field a = abc) then hide/show fields that are available on the secondary list.
Thanks for your input.
Hello @syeda33,
You can specify the list view that is displayed in the form in Control Settings >> General >> Data Source >> View.
If you need to change the view depending on the field values, you can add multiple List or Library controls with different views to the form and show/hide them dynamically.
Thank you, this worked!
Next issue I am facing is doing calculations on the parent list based on what entries on the child lists. I'm using this article as a reference https://plumsail.com/docs/forms-web/how-to/data-table-to-sp.html
but I am having issues accessing fields through Javascript. Specifically, I'm getting an error accessing the value method.
function Calculations(){
if (fd.field('Budget_x0020_Entry_x0020_Type').value == 'Permanent Budget Transfer (Non-Position)' ){
fd.control('SPDataTable1').$on('change', function(value){
var toJournalEntryTotal = 0;
var isTableModified = false;
if(value){
for (var i = 0; i < value.length; i++){
if(value[i].Debit_x0020_Amount){
toJournalTotal += parseInt(value[i].Debit_x0020_Amount);
}
else if (value[i].Credit_x0020_Amount){
toJournalTotal -= parseInt(value[i].Credit_x0020_Amount);
}
else{
console.log('error bro')
}
}
}
if(isTableModified){
fd.control('SPDataTable1').value = value;
}
fd.field('To_JournalEntry_total').value = toJournalTotal;
});
}
Here is a screenshot of what my debug session looks like. it seems like that 'value' being returned is not an array but just a singular object:
Hello @syeda33,
This article is for Public Forms and describes work with Data Table control.
And if you need to calculate totals of items in List or Library control, you can use the code from this post:
I'm using the following code in my function:
function toCalcs(){
var value = fd.control('SPDataTable1').widget.dataItems();
var toJournalTotal = 0;
var isTableModified = false;
if(value){
for (var i = 0; i < value.length; i++){
if(value[i].Debit_x0020_Amount){
toJournalTotal += value[i].Debit_x0020_Amount;
isTableModified = true;
}
else if (value[i].Credit_x0020_Amount){
toJournalTotal -= value[i].Credit_x0020_Amount;
isTableModified = true;
}
}
if (isTableModified) {
fd.control('SPDataTable1').value = value;
}
fd.field('To_JournalEntry_total').value= toJournalTotal;
}}
The issue I am having is that the += and -= function is not working correctly. The variable that is being assigned is something like this:
Not really sure how to resolve this issue any help would be appreciated. Thanks
Hello @syeda33,
Your code has lines that are required for calculating the totals of DataTable control. As you are using List or Library control, you don't need them. I've commented them out, please see below.
Also, I've removed else from the second conditional statement, as in this case, if the first condition is true, the second condition is ignored.
function toCalcs(){
var value = fd.control('SPDataTable1').widget.dataItems();
var toJournalTotal = 0;
//var isTableModified = false;
if(value){
for (var i = 0; i < value.length; i++){
if(value[i].Debit_x0020_Amount){
toJournalTotal += value[i].Debit_x0020_Amount;
// isTableModified = true;
}
//replaced else if with if statment
if (value[i].Credit_x0020_Amount){
toJournalTotal -= value[i].Credit_x0020_Amount;
// isTableModified = true;
}
}
// if (isTableModified) {
// fd.control('SPDataTable1').value = value;
//}
fd.field('To_JournalEntry_total').value= toJournalTotal;
}}
Please try out this code, and if it still won't work, please share the screenshot of the errors in the browser console(F12).
Hello @syeda33,
Where are you using the code? On Parent or Child Form?
Please share the code that is related to calculating totals and the screenshot of the error in the console tab, not the source tab.
I'm using it on the parent form and it looks like no relevant error is generated when the code is executed. Also, please note that the fields that I am trying to calculate are sharepoint fields of type Currency USD thanks.
Below is the code
function toCalcs(){
var value = fd.control('SPDataTable1').widget.dataItems();
var toJournalTotal = 0.00;
//var isTableModified = false;
if(value){
for (var i = 0; i < value.length; i++){
if(value[i].Debit_x0020_Amount){
toJournalTotal += value[i].Debit_x0020_Amount;
// isTableModified = true;
}
//replaced else if with if statment
if (value[i].Credit_x0020_Amount){
toJournalTotal -= value[i].Credit_x0020_Amount;
// isTableModified = true;
}
}
// if (isTableModified) {
// fd.control('SPDataTable1').value = value;
//}
fd.field('To_JournalEntry_total').value= toJournalTotal;
}}
})Thanks for all your help. I was able to use the following function/regex to solve my issue:
var creditCurrency = value[i].Credit_x0020_Amount;
toJournalTotal -= Number(creditCurrency.replace(/[^0-9\.-]+/g,""));Spoke too soon. Looks like my $on('change') method executes one method too late..
For example, when I save an item in my child list, my onchange does not get executed. When I enter a second item in my child list, the onchange gets executed for the previous values. Here is my code as shown in the docs:
fd.spRendered(function() {
JournalEntryhideOrShow();
fd.control('SPDataTable1').ready().then(function(){
toCalcs();
fd.control('SPDataTable1').$on('change', toCalcs);
});Fixed by using the setInterval method on my function call. Not sure if this is a good approach but it works so far.
Hello @syeda33,
Yes, you need to call function repeatedly and under the spBeforeSave event:
fd.spRendered(function() {
//refreshing the total
var myVar = setInterval(toCalcs, 2000);
});
fd.spBeforeSave(function(spForm) {
//refresh the Total before save
var myVar = setInterval(toCalcs, 2000);
toCalcs();
clearInterval(myVar);
});Hi @mnikitina
I am finding that some of my lists are hitting the default item limit of 30, causing only the first 30 items to be included in the calculation of the total. Is there any way to force the calculation to calculate over all the records in the linked list AND maintain the paging at 30 items?
Hello @Andre,
To calculate the total of the related items ignoring paging on Edit or Display forms, you can use PnPjs library. Please see the code example:
fd.spRendered(function(){
var total = 0
pnp.sp.web.lists.getByTitle("ListName").items.select('Parent/Id', 'Price').expand('Parent').filter("Parent/Id eq " + fd.itemId).get().then(function(items){
items.forEach(function(item) {
total += parseInt(item.Price);
});
fd.field('Total').value = total;
});
});
ListName - is the internal name of the child list.
Parent - is the internal name of the Lookup field that binds parent and child lists.
Price - is the internal name of the column in the child list