Hi, I would like to find out this there anyway to auto summaries the data from table 1 to table 2. It mean when I key in all the items into table 1 and then it will auto summaries in table 2 total qty in each item.
Hello @Alex_Hoo
Welcome to the community! 
Just to clarify, does the summary need to be presented in another table? If the goal is simply to summarize the quantity for each item, it might be simpler to have number fields outside the table for each item and automatically calculate the sum of each item's quantity there.
Let me know your thoughts on this approach.
Hi Chris,
Thanks for your response, at first in my application creation I need to summarize and sum up each item into another table for some other purpose. But if the simple way to do it by summarize with sum each item into the same app with sum up number fields outside the table I'm ok also. Thanks and appreciated your help.
Best Regards
Alex
Hello @Alex_Hoo
Understood.
Yeah, a scripting involving modifying a table, such as adding or deleting rows, can get complex. I'll work on the script that will sum up number fields outside the table and get back to you with it tomorrow.
1 Like
Hi Chris,
Really thanks and appreciated your help.
Best Regards
Alex
Hello @Alex_Hoo
The following script would do the job:
(function() {
'use strict';
var SUBTABLE_CODE = 'Table'; // Subtable field code
var DROPDOWN_FIELD = 'Item'; // Dropdown field code within the subtable
var QUANTITY_FIELD = 'Qty'; // Quantity field code within the subtable
// Define outside field codes
var OUTSIDE_FIELDS = {
'Item 1': 'Item1',
'Item 2': 'Item2',
'Item 3': 'Item3'
};
// Listen for changes in both the Item and Qty fields within the subtable
var itemChangeEvents = [
'app.record.edit.change.' + DROPDOWN_FIELD,
'app.record.create.change.' + DROPDOWN_FIELD
];
var qtyChangeEvents = [
'app.record.edit.change.' + QUANTITY_FIELD,
'app.record.create.change.' + QUANTITY_FIELD
];
kintone.events.on(itemChangeEvents.concat(qtyChangeEvents), function(event) {
var record = event.record;
var aggregates = {
'Item 1': 0,
'Item 2': 0,
'Item 3': 0
};
// Aggregate quantities for each item type
record[SUBTABLE_CODE].value.forEach(function(row) {
var item = row.value[DROPDOWN_FIELD].value;
var qty = parseFloat(row.value[QUANTITY_FIELD].value) || 0;
if (item in aggregates) {
aggregates[item] += qty;
}
});
// Update outside fields based on the aggregates
for (var item in aggregates) {
var fieldCode = OUTSIDE_FIELDS[item];
record[fieldCode].value = aggregates[item];
}
return event;
});
// Disable the outside number fields on form show events
var formShowEvents = [
'app.record.create.show',
'app.record.edit.show',
'app.record.detail.show'
];
kintone.events.on(formShowEvents, function(event) {
var record = event.record;
// Disable each field
record['Item1'].disabled = true;
record['Item2'].disabled = true;
record['Item3'].disabled = true;
return event;
});
})();
Here is the information about the fields used:
- Table - Field Code: Table
- Item - Dropdown Field, Field Code: Item
- Qty - Number field, Field Code: Qty
- Item 1 - Number field, Field Code: Item1
- Item 2 - Number field, Field Code: Item2
- Item 3 - Number field, FIeld Code: Item3
Hi Chris,
Thanks for your coding, I will try it.
Best Regards
Alex
Hi Chris,
Sorry to disturb you again. I just tried your coding and the field required according to the coding. But it does not work after I click save. I'm not sure whether I missed anything.
(function() {
'use strict';
var SUBTABLE_CODE = 'Table'; // Subtable field code
var DROPDOWN_FIELD = 'Item'; // Dropdown field code within the subtable
var QUANTITY_FIELD = 'Qty'; // Quantity field code within the subtable
// Define outside field codes
var OUTSIDE_FIELDS = {
'Item 1': 'Item1',
'Item 2': 'Item2',
'Item 3': 'Item3'
};
// Listen for changes in both the Item and Qty fields within the subtable
var itemChangeEvents = [
'app.record.edit.change.' + DROPDOWN_FIELD,
'app.record.create.change.' + DROPDOWN_FIELD
];
var qtyChangeEvents = [
'app.record.edit.change.' + QUANTITY_FIELD,
'app.record.create.change.' + QUANTITY_FIELD
];
kintone.events.on(itemChangeEvents.concat(qtyChangeEvents), function(event) {
var record = event.record;
var aggregates = {
'Item 1': 0,
'Item 2': 0,
'Item 3': 0
};
// Aggregate quantities for each item type
record[SUBTABLE_CODE].value.forEach(function(row) {
var item = row.value[DROPDOWN_FIELD].value;
var qty = parseFloat(row.value[QUANTITY_FIELD].value) || 0;
if (item in aggregates) {
aggregates[item] += qty;
}
});
// Update outside fields based on the aggregates
for (var item in aggregates) {
var fieldCode = OUTSIDE_FIELDS[item];
record[fieldCode].value = aggregates[item];
}
return event;
});
// Disable the outside number fields on form show events
var formShowEvents = [
'app.record.create.show',
'app.record.edit.show',
'app.record.detail.show'
];
kintone.events.on(formShowEvents, function(event) {
var record = event.record;
// Disable each field
record['Item1'].disabled = true;
record['Item2'].disabled = true;
record['Item3'].disabled = true;
return event;
});
})();
Thanks & Regards,
Alex
Hello @Alex_Hoo
Could you share any error messages you're encountering?
Otherwise, I can't provide much assistance.
Also, please make use of this page to debug the error.
Hi Chris, thanks for your response. I will try to debug and find out the error and get back to you. Thanks
Hi Chris,
I tried debug and I saw this error.
Regards
Alex
Hello @Alex_Hoo
The error appears to be quite general, making it challenging to identify the specific issue.
If the structure of your setup mirrors mine, I suspect the error might be due to discrepancies between your field codes and those used in the script.
Hi Chris,
Thanks for your information, I will try it again.
Regards
Alex
Hi @Chris,
I was using this code to run something similar and the error I get is
It seems to have an issue with line 46 - record[fieldCode].value = aggregates[item];
Below is my code:
(function() {
'use strict';
var SUBTABLE_CODE = 'aged_inv_fg'; // Subtable field code
var DROPDOWN_FIELD = 'type_fg'; // Dropdown field code within the subtable
var QUANTITY_FIELD = 'inv_value_fg'; // Quantity field code within the subtable
// Define outside field codes
var OUTSIDE_FIELDS = {
'Total Inventory Value - FG': 'total_num_fg',
'Total Inventory Value - RM': 'total_num_rm',
//'Item 3': 'Item3'
};
// Listen for changes in both the Item and Qty fields within the subtable
var itemChangeEvents = [
'app.record.edit.change.' + DROPDOWN_FIELD,
'app.record.create.change.' + DROPDOWN_FIELD
];
var qtyChangeEvents = [
'app.record.edit.change.' + QUANTITY_FIELD,
'app.record.create.change.' + QUANTITY_FIELD
];
kintone.events.on(itemChangeEvents.concat(qtyChangeEvents), function(event) {
var record = event.record;
var aggregates = {
'NOLA FG': 0,
'Pass Through': 0
//'Item 3': 0
};
// Aggregate quantities for each item type
record[SUBTABLE_CODE].value.forEach(function(row) {
var item = row.value[DROPDOWN_FIELD].value;
var qty = parseFloat(row.value[QUANTITY_FIELD].value) || 0;
if (item in aggregates) {
aggregates[item] += qty;
}
});
// Update outside fields based on the aggregates
for (var item in aggregates) {
var fieldCode = OUTSIDE_FIELDS[item];
record[fieldCode].value = aggregates[item];
}
return event;
});
// Disable the outside number fields on form show events
var formShowEvents = [
'app.record.create.show',
'app.record.edit.show',
'app.record.detail.show'
];
kintone.events.on(formShowEvents, function(event) {
var record = event.record;
// Disable each field
record['total_num_fg'].disabled = true;
record['total_num_rm'].disabled = true;
//record['Item3'].disabled = true;
return event;
});
})();
This is in my table
I'm trying to take all the NOLA FG selections and add them together to and put into the Total Inventory Value - FG number field and then do the same with all the "Pass Through" sections, going into the Total Inventory Value - RM number field. So if 2 NOLA FG rows have a combined Inventory value of 100 (25 and 75 in the Qty field) then 100 goes into the Total Inventory Value - FG field and same for the Pass Through. Thank you in advance for any corrections, suggestions.
Hey @Chris,
Nevermind I figure it out. I had the right fields just in the wrong spot in the code. I have it working now. Thanks
2 Likes