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