DocumentChange API
The DocumentChange API is allows to make changes to an open accounting file. There are two possibility to program an extension:
- Create a DocumentChange JSON (documentChange) and use the Banana.document.applyDocumentChange(documentChange)
- Create a DocumentChange JSON (documentChange) and return it in the exec() function.
Logic of the change process
Contrary to other APIs that allow to directly modify the data, Banana Accounting has decided for an approach that gives the user full control of such data.
- You cannot directly modify the content of a file.
- You must create an Extension that return a Document Change JSON.
- Banana Accounting use the JSON and Apply the contained change to the existing file.
- The program shows to the user what changes will take place and will ask the permission to apply the changes. Changes are discarded if not approved.
- If the user agrees, the changes are applied to the document.
- The user can undo and redo the changes.
Use Cases
The DocumentChange API can be used for:
- Add, modify and delete rows in a Table.
- Add, modifify and remove columns in a Table.
- Modify the File Properties
- Importing Transactions.
- Importing Accounts, Suppliers and Customers.
- Importing Invoice and Estimates.
- Create sort of "macro" that takes a user input to create one or multiple transactions.
- Calculate and modify columns content.
Examples
- Examples Rows
- Examples Columns
- Example FileProperties
- Examples General
- Examples Invoices
- Example Change Date
Document Change Extension
Is a Banana Extension that return a Document Change JSON :
- It can be a Productivity or Import Extension.
- The Extensions main function exec() returns a JSON document that describes what changes will be applied to the file content.
- The extension must create and return a Document Change JSON that contains the change that will be applied to the existing data.
Example of an Extension returning a DocumentChange
The extension creates a DocumentChange JSON and returns it to the program.
// Creates a JSON DocumentChange which adds a row to the transaction table.
//
// @id = ch.banana.example.documentchange
// @api = 1.0
// @pubdate = 2024-03-25
// @doctype = *.*
// @description = JSON Change adding a row
// @task = app.command
// @timeout = -1
function exec() {
var strChange = ` {
"format": "documentChange",
"error": "",
"data": [
{
"document": {
"cursorPosition": {
"operation": "move",
"tableName": "Transactions",
"columnName": "Description",
"rowNr": -1
},
"dataUnits": [ {
"nameXml": "Transactions",
"data": {
"rowLists": [ {
"rows": [ {
"fields": {
"Date": "2025-03-25",
"Description": "Total sales 25-03-2025",
"Amount": "2000"
},
"operation": {
"name": "add"
},
"style": {
"fontSize": 12,
"bold": true,
"color": "red",
"background-color": "#00FFFF"
}
} ]
} ]
}
} ],
"fileVersion": "1.0.0",
"id": ""
}
} ]
} `;
var jsonChange = JSON.parse(strChange);
return jsonChange;
}
DocumentChange JSON
The DocumenChange Json is a JSON object that contains the following elements.
- format need to be "documentChange"
- error used to return an error from the script.
- creator information regarding the change.
It will be automatically added by the program.- executionDate time stamp of the script that has created the change.
- executionTime h.
- name (description) comment .
- version version of the script.
data is a JSON array of JSON objects (document) with the changes to be applied.
- Each element of the array is a single change.
- Changes are applied in sequence.
- document is a change to be applied to the accounting file
- id document identifier (usually empty).
"id":"currentRow" used by JsAction::updateRow() - dataUnits the element to be changed (corresponds to the table in the accounting file).
- fileVersion the version of the documentChange specification
- cursorPosition cursor position when changes have been applied (see example code)
- operation "move" The cursor moves to the last position of the content (the only attribute available at the moment. Future attributes: select, deselect)
- tableName xml tablename, where to position the cursor
- columnName xml columnname, where to position the cursor
- rowNr index of the row, where to position the cursor (-1 move to the last row of the table)
- id document identifier (usually empty).
Properties of DataUnits
The dataUnits array element defines the dataUnits which is going to be changed and includes also the list of changes.
- nameXml is the xml name of the data structure.
It can be- A name of an existing Table ("Account, Transactions, ...)
- FileInfo for the file properties (Menu > Tools > File Info).
See Example DocumentChange FileProperties.
- nid is the id of the data structure. It is optional and can be passed in place of the nameXml
- rowLists Is an array containing rows
- rows Is an array containing all the rows can be also defined as "Views" when we are working with columns
- fields Is an Object which with the column names that are going to be modified and the value.
(Example: "fields": {"Date": "2019-01-05", "Description": "Sell of goods"} - style Is an object which define the row style
- fontSize define the size of the font, can ben one of 8, 10, 12, 14 or 0 (use default style)
- bold true for bold
- italic true for italic
- color the text color (From version 10.1.20)
- "none" the color will be removed.
- background-color the background color for the row. (From version 10.1.20)
- "none" the color will be removed.
- operation Is an object which defines the operation that has to be executes on the relative row or column.
- name define the type of operation
- add, add a new row
- sequence: 1.1 to add after the row 1.
- sequence: 1.2 to add after the 1.1
- if no sequence is given the row is added to the end of the table.
- delete
sequence indicates the row or column to be deleted from the accounting file. - modify
sequence indicates the row or column we want to modify with the previously specified fields or properties - move
sequence indicates the row or column we want to move and moveTo the row or column destination; - replace
sequence indicates the row or column we want to replace with the previously specified fields or properties;
- add, add a new row
- name define the type of operation
- sequence
Is the row or column number. The sequence is always referred to the status of the file, prior to the changes.- Use 1.1 to add a new row after the existing row at line 1.
- fields Is an Object which with the column names that are going to be modified and the value.
- rows Is an array containing all the rows can be also defined as "Views" when we are working with columns
- viewList Is an object containing the array views
- views Is an array containing views
- view Is an array of columns
- views Is an array containing views
Example Document Change JSON
The Document Change consists of a JSON object with a list of document changes (data). Each document change (document) contains the list of changes that must be applied to the accounting file. The basic structure is:
{
"format": "documentChange",
"error": "",
"data": [{
//First document
"document": {
"id": "firstChange",
"dataUnits": [{
"data": {
"rowLists": [{
"nameXml": "Base",
"rows": [{
"operation": {
"name": "modify"
},
"fields": {
"SectionXml": "Base",
"IdXml": "HeaderLeft",
"ValueXml": "Changed header1 with documentChange"
}
}]
}],
},
"nameXml": "FileInfo",
"nid": ""
}]
}
}, {
//Second document
"document": {
"id": "secondChange",
"dataUnits": [{
"data": {
"rowLists": [{
"rows": [{
"operation": {
"name": "delete",
"sequence": "10"
}
}]
}]
},
"nameXml": "Transactions"
}]
}
}, {
//Third document
"document": {
"id": "secondChange",
"dataUnits": [{
"data": {
"rowLists": [{
"rows": [{ // remove the existing account at line 7
"operation": {
"name": "delete",
"sequence": "7"
}
}, { // add a new account at line 7
"fields": {
"Date": "2025-01-04",
"Description": "Bank Account",
"Account": "1001"
},
"operation": {
"name": "add",
"sequence": "7"
}
}]
}]
},
"nameXml": "Accounts"
}]
}
}, {
//Fourth document
"document": {
"id": "secondChange",
"dataUnits": [{
"data": {
"rowLists": [{
"rows": [{ //add first transaction
"fields": {
"Date": "2025-01-04",
"Description": "Purchase of goods",
"AccountDebit": "4200",
"AccountCredit": "2001",
"Amount": "1300"
},
"operation": {
"name": "add"
}
},
{ // add second transaction, using the account added in the previous document
"fields": {
"Date": "2025-01-05",
"Description": "Sell of goods",
"AccountDebit": "1001",
"AccountCredit": "3000",
"Amount": "1500"
},
"operation": {
"name": "add"
}
}
]
}]
},
"nameXml": "Transactions"
}]
}
}]
}
Sequence property for rows
When modifying, deleting rows you need to specify the sequence.
- The sequence is the existing row number.
- The first row is 0.
- The number refer always to existing row number.
- If the row number refer to a non existing row, the single/whole ?? change is refused.
- When deleting or inserting a new row the existing row numbers are not changed.
- When adding use a decimal value.
- sequence 1.1 means the row will be added after the row 1.
- sequence 1.2 means the row will be added after the row 1.1.
- sequence -1 means the row will be added before the 0.
- When adding or moving you can use any sequence number, if will simply be used for sorting.
Sequence numbers and operation:
- Command Modify.
The number of the row to be modified.
For example sequence: 1, will change the existing row 1 - Command Replace.
The number of the row to be replaced.
The content of the row is completely replaced. It is like removing a line and adding a new one.
For example sequence: 1, will replace the exiting row 1 - Command Delete.
The number of the row to be deleted,
For example sequence: 1, will remove the existing row 1 - Command Move.
The number of the row to be moved.
For example sequence: 1, moveTo: 5.1 will move row 1 after the existing row 5. - Command Add.
The index of the row to be added.
Use -1 or -10 to add a row before the 0.
For example 1.1 add a line after the row 1.
If no sequence is indicated, the new row will be appended.
The engine applies the changes using the following logic:
- It first process the instructions to modify the rows.
- The existing rows sequence remains unchanged.
- It add the new rows.
- Therefore you cannot modify a rows that has been added.
- It remove the rows to be deleted.
- It sort the rows using the new sequence number.
For unchanged rows the existing sequence number is used.
Sequence property for columns
For columns the sequence property is used to specify the display sequence.
Document for Multiple change steps
Within the same DocumentChange JSon you can have a series of changes that will be applied in several steps. This allows to do complex changes to the accounting file, without triggering errors.
For example s assume you need to remove and add accounts and transactions at the same time. If you add transactions that use a new account before the account is added, there will be an error. The multi-step approach allows you to submit a change that ensures system integrity. In this case the script will return a JSON object with a list of document changes,
- First document: Change the accounting settings
- Second document: Remove existing transactions
- Third document: Remove and add accounts
- Fourth document: Add the new transactions
The "data" element is an array of object of type "document" that contains the changes.
You can have multiple "document" with changes that will be applied in the specified sequence.
{
"format": "documentChange",
"error": "",
"data": [{
//First document
"document": {
"id": "firstChange",
"dataUnits": [{
"data": {
"rowLists": [{
"nameXml": "Base",
"rows": [{
"operation": {
"name": "modify"
},
"fields": {
"SectionXml": "Base",
"IdXml": "HeaderLeft",
"ValueXml": "Changed header1 with documentChange"
}
}]
}],
},
"nameXml": "FileInfo",
"nid": ""
}]
}
}, {
//Second document
"document": {
"id": "secondChange",
"dataUnits": [{
"data": {
"rowLists": [{
"rows": [{
"operation": {
"name": "delete",
"sequence": "10"
}
}]
}]
},
"nameXml": "Transactions"
}]
}
}, {
//Third document
"document": {
"id": "secondChange",
"dataUnits": [{
"data": {
"rowLists": [{
"rows": [{ // remove the existing account at line 7
"operation": {
"name": "delete",
"sequence": "7"
}
}, { // add a new account at line 7
"fields": {
"Date": "2025-01-04",
"Description": "Bank Account",
"Account": "1001"
},
"operation": {
"name": "add",
"sequence": "7"
}
}]
}]
},
"nameXml": "Accounts"
}]
}
}, {
//Fourth document
"document": {
"id": "secondChange",
"dataUnits": [{
"data": {
"rowLists": [{
"rows": [{ //add first transaction
"fields": {
"Date": "2025-01-04",
"Description": "Purchase of goods",
"AccountDebit": "4200",
"AccountCredit": "2001",
"Amount": "1300"
},
"operation": {
"name": "add"
}
},
{ // add second transaction, using the account added in the previous document
"fields": {
"Date": "2025-01-05",
"Description": "Sell of goods",
"AccountDebit": "1001",
"AccountCredit": "3000",
"Amount": "1500"
},
"operation": {
"name": "add"
}
}
]
}]
},
"nameXml": "Transactions"
}]
}
}]
}
Convenience class for Document change
The InvoiceApp is an Invoice Dialog Extension that internally use a DocumentChange class that provide a convenient way to create a Document change JSON.
It should be ported to be used to simplify the creation of DocumentChange JSON programmatically.
Basic Examples DocumentChange operations on rows
Example adding a row
The following example shows how to append a row to the Transactions table. If you indicate the sequence, the row will be inserted at the given position. Try this operation
{
"format":"documentChange",
"error":"",
"data":[
{
"document":{
"dataUnits":[
{
"data":{
"rowLists":[
{
"rows":[
{
"fields":{
"Date":"2019-01-04",
"Description":"Invoice 304"
},
"operation":{
"name":"add"
},
"style": {
"fontSize":12,
"bold":"true",
"italic":"false"
}
}
]
}
]
},
"nameXml":"Transactions"
}
]
}
}
]
}
Example modifying a row
The following example shows how to modify the existing row at line 2 in the Transactions table.
{
"format":"documentChange",
"error":"",
"data":[
{
"document":{
"dataUnits":[
{
"data":{
"rowLists":[
{
"rows":[
{
"fields":{
"Description":"Invoice Hello World"
},
"operation":{
"name":"modify",
"sequence":"1"
}
}
]
}
]
},
"nameXml":"Transactions"
}
]
}
}
]
}
Example replacing a row
The following example shows how to replace the existing row at line 2 in the Transactions table.
{
"format":"documentChange",
"error":"",
"data":[
{
"document":{
"dataUnits":[
{
"data":{
"rowLists":[
{
"rows":[
{
"fields":{
"Date":"2020-01-05",
"Description":"Invoice 304",
"Amount":"300.00"
},
"operation":{
"name":"replace",
"sequence":"1"
}
}
]
}
]
},
"nameXml":"Transactions"
}
]
}
}
]
}
Example deleting a row
The following example shows how to delete the existing row at line 2 in the Transactions table.
{
"format":"documentChange",
"error":"",
"data":[
{
"document":{
"dataUnits":[
{
"data":{
"rowLists":[
{
"rows":[
{
"operation": {
"name": "delete",
"sequence": "1"
}
}
]
}
]
},
"nameXml":"Transactions"
}
]
}
}
]
}
Example moving a row
The following example shows how to move the existing row at line 2 to line 5 in the Transactions table.
{
"format":"documentChange",
"error":"",
"data":[
{
"document":{
"dataUnits":[
{
"data":{
"rowLists":[
{
"rows":[
{
"operation":{
"name":"move",
"sequence":"1",
"moveTo":"4"
}
}
]
}
]
},
"nameXml":"Transactions"
}
]
}
}
]
}
Example adding rows to recurring transactions table
The following example shows how to add rows to the table Recurring transactions.
{
"format": "documentChange",
"error": "",
"data": [
{
"document": {
"dataUnits": [
{
"data": {
"rowLists": [
{
"nameXml": "Templates",
"rows": [
{
"fields": {
"Date": "20200705",
"Description": "compleanno Anna",
"Doc": "r1"
},
"operation": {
"name": "add"
}
}
]
}
]
},
"nameXml": "Transactions"
}
]
}
}
]
}
Example adding JSON content in a custom column in the Transactions table
DocumentChange allows you to update a MIME field of type text/json. In this field it is possible to insert several json objects identified by a key.
- The contents of the field must be passed as a string
- Other types of MIME fields (text/html, image/jpg, ...) are currently not supported.
- In Banana you can create a column of type MIME using the menu command: Data - Columns setup, Add new column and choosing Data type Mime
{
"format":"documentChange",
"error":"",
"data": [
{
"document": {
"dataUnits": [
{
"data": {
"rowLists": [
{
"rows": [
{
"fields": {
"Date": "2020-08-07",
"Description":"Writing data to json/text field",
"MyMimeColumn":{
"Object1Key":"{\"amount\":\"100.00\",\"currency\":\"CHF\"}",
"Object2Key":"{\"amount\":\"200.00\",\"currency\":\"EUR\"}"
}
},
"operation": {
"name": "add"
}
}
]
}
]
},
"nameXml": "Transactions"
}
]
},
"fileVersion": "1.0.0"
}
]
}
Example adding attachments (text/plain) to the table Documents
DocumentChange allows you to insert a text in the Attachments Column available in the Documents table. The MIME type is text/plain and cannot be changed.
- The contents of the field must be passed as a string
- Other types of MIME fields (image, HTML text, Markdown code, CSS stylesheet, Javascript code) are currently not supported.
This property has been introduced in BananaPlus 10.1.20
// Creates a JSON DocumentChange which adds a row with an attachment to the Documents table.
//
// @id = ch.banana.example.documentpatchMime
// @api = 1.0
// @pubdate = 2024-04-17
// @doctype = *.*
// @description = Add attachments to Documents Table
// @task = app.command
// @timeout = -1
function exec() {
var jsonContent = {
"billing_info": {
"total_amount_vat_exclusive": "51.00",
"total_amount_vat_exclusive_before_discount": "51.00"
}
};
var jsonChange = {
"format": "documentChange",
"error": "",
"data": [
{
"document": {
"dataUnits": [ {
"nameXml": "Documents",
"data": {
"rowLists": [ {
"rows": [ {
"fields": {
"RowId": "MyDocument 1",
"Description":"Writing data to the attachmnents field",
"Attachments": JSON.stringify(jsonContent, null, 3)
},
"operation": {
"name": "add"
} } ]
} ]
} } ]
} } ]
};
return jsonChange;
}
Examples DocumentChange operations on columns
With the DocumentChange it is also possible to perform operations on columns, in case you want to modify, add or replace a column.
Columns properties
- alignement
- type: text
- values: left|right|center
- description
- type: text
- header1
- type: text
- header2
- type: text
- name
- type: text
- nameXml
- type: text
- width
- type: number
- values: in mm, max value 10000
- definition
- type: object
- values: text, number, amount, date, time, bool, timestamp, timecounter, links,textmultiline, markdown, mime, textencrypted, Default value: text
- decimals
Number of decimals for types amount and number. Default value: 2
- operation
- values: add|delete|modify|move|replace
{
"name": "MyColumn",
"nameXml": "MyColumn",
"description": "This is my column with an amount",
"header1": "MyColumn",
"definition" : {
"type": "amount",
"decimals": "4"
},
"operation": {
"name": "add"
}
}
Example adding a column
The following example shows how to append a column to the Accounts table. If you indicate the sequence, the column will be inserted at the given position. try this operation
{
"format": "documentChange",
"error": "",
"data": [{
"document": {
"dataUnits": [{
"data": {
"viewList": {
"views": [{
"columns": [{
"alignement": "center",
"description": "center column",
"nameXml": "CenteredColumn",
"header1": "My Header 1",
"header2": "My Header 2",
"width": 200,
"operation": {
"name": "add",
"sequence": 0
}
}],
"id": "Base",
"nameXml": "Base",
"nid": 1
}]
}
},
"id": "Accounts",
"nameXml": "Accounts",
"nid": 100
}]
}
}]
}
Example modifying a column
The following example shows how to modify the existing column at line 2 in the Transactions table.
{
"format": "documentChange",
"error": "",
"data": [{
"document": {
"dataUnits": [{
"data": {
"viewList": {
"views": [{
"columns": [{
"header1": "NewDescription XXXX",
"nameXml": "Description",
"operation": {
"name": "modify",
"sequence": "1"
}
}],
"id": "Base",
"nameXml": "Base",
"nid": 1
}]
}
},
"id": "Transactions",
"nameXml": "Transactions",
"nid": 103
}]
}
}]
}
Example replacing a column
The following example shows how to replace the existing column at line 24 in the Transactions table.
{
"format": "documentChange",
"error": "",
"data": [{
"document": {
"dataUnits": [{
"data": {
"viewList": {
"views": [{
"columns": [{
"nameXml": "Amount",
"header1": "AmountAsText",
"definition": {
"type": "text"
},
"operation": {
"name": "replace",
"sequence": "23"
}
}],
"id": "Base",
"nameXml": "Base",
"nid": 1
}]
}
},
"id": "Transactions",
"nameXml": "Transactions",
"nid": 103
}]
}
}]
}
Example deleting a column
The following example shows how to delete the existing column at line 2 in the Accounts table.
{
"format": "documentChange",
"error": "",
"data": [{
"document": {
"dataUnits": [{
"data": {
"viewList": {
"views": [{
"columns": [{
"nameXml": "Description",
"operation": {
"name": "delete"
"sequence": "1"
}
}],
"id": "Base",
"nameXml": "Base",
"nid": 1
}]
}
},
"id": "Accounts",
"nameXml": "Accounts",
"nid": 100
}]
}
}]
}
Example moving a column
The following example shows how to move the existing column to line 2 in the Transactions table. To know the number of a column, double-click on the header of one of them, in the dialog that opens you can see all the available columns, they are represented in ascending order. values start from 0.
{
"format": "documentChange",
"error": "",
"data": [{
"document": {
"dataUnits": [{
"data": {
"viewList": {
"views": [{
"columns": [{
"nameXml": "Description",
"operation": {
"name": "move",
"sequence": "2"
}
}]
}]
}
},
"nameXml": "Transactions"
}]
}
}]
}
Example DocumentChange FileProperties
When changing data to the File Properties (Header, date begin, date end) the change is structured like a change to the table, but using a special table FileInfo.
See: Menu > Tools > File info for the list of elements.
Structure of the DocumentChange:
- You can only use the operation "modify".
- The extension can change only the elements that the user can change.
- The element to be changed
- SectionXml
The name of the section, for example "SectionXml":"Base" - IdXml
The identification, for example "IdXml":"HeaderLeft"
- SectionXml
- The value to be changed
- ValueXml
The contents of the field in Xml format, for example "ValueXml":"Changed header1 with documentChange"
- ValueXml
Example changing file properties
The following example shows how to change accounting data properties as accounting header.
{
"format":"documentChange",
"error":"",
"data":[
{
"document":{
"dataUnits":[
{
"nameXml":"FileInfo",
"data":{
"rowLists":[
{
"rows":[
{
"fields":{
"SectionXml":"Base",
"IdXml":"HeaderLeft",
"ValueXml":"Changed header1 with documentChange"
},
"operation":{
"name":"modify"
}
},
{
"fields":{
"SectionXml":"Base",
"IdXml":"HeaderRight",
"ValueXml":"Changed header2 with documentChange"
},
"operation":{
"name":"modify"
}
}
]
}
]
}
}
]
}
}
]
}
Generale Examples Document Change API
Complete example creating and running a DocumentChange
The following extension creates a DocumentChange, which adds and changes some rows in the Transactions table.
To try this extension, you can copy and paste the code into a text file and save it in ".js" format.
To install it in Banana you have to click on the Menu Extensions (command Manage extensions...) and then add the file you just created using "Add from file" button.
After this procedure, the file can be run from the Menu Extensions under the new command "Modify ac2 file using documentChange".
// @id = ch.banana.test.create.documentchange.ac2
// @api = 1.0
// @pubdate = 2019-10-09
// @publisher = Banana.ch SA
// @description = Modify ac2 file using documentChange
// @task = app.command
// @doctype = 100.*;110.*;130.*
// @timeout = -1
function exec(inData) {
if (!Banana.document)
return "@Cancel";
var documentChange = {
"format": "documentChange",
"error": "",
"data": []
};
//1. Appends a row to the transaction table
var jsonDoc = transactionRowAppend();
documentChange["data"].push(jsonDoc);
//2. Insert a row to the transaction table at the beginning of the rows
jsonDoc = transactionRowInsert();
documentChange["data"].push(jsonDoc);
//3. Modify all rows in the transaction table
jsonDoc = transactionRowEdit();
documentChange["data"].push(jsonDoc);
// Banana.Ui.showText("json object: " + JSON.stringify(documentChange, null, 3));
return documentChange;
}
function getCurrentDate() {
var d = new Date();
var datestring = d.getFullYear() + ("0" + (d.getMonth() + 1)).slice(-2) + ("0" + d.getDate()).slice(-2);
return Banana.Converter.toInternalDateFormat(datestring, "yyyymmdd");
}
function getCurrentTime() {
var d = new Date();
var timestring = ("0" + d.getHours()).slice(-2) + ":" + ("0" + d.getMinutes()).slice(-2);
return Banana.Converter.toInternalTimeFormat(timestring, "hh:mm");
}
function initDocument() {
var jsonDoc = {};
jsonDoc.document = {};
jsonDoc.document.fileVersion = "1.0.0";
jsonDoc.document.dataUnits = [];
jsonDoc.creator = {};
jsonDoc.creator.executionDate = getCurrentDate();
jsonDoc.creator.executionTime = getCurrentTime();
jsonDoc.creator.name = Banana.script.getParamValue('id');
jsonDoc.creator.version = "1.0";
return jsonDoc;
}
function transactionRowAppend() {
//row operation
var row = {};
row.operation = {};
row.operation.name = "add";
//row fields
row.fields = {};
row.fields["Date"] = getCurrentDate();
row.fields["Description"] = "Executed transactionRowAppend()";
//rows
var rows = [];
rows.push(row);
//table
var dataUnitTransactions = {};
dataUnitTransactions.nameXml = "Transactions";
dataUnitTransactions.data = {};
dataUnitTransactions.data.rowLists = [];
dataUnitTransactions.data.rowLists.push({
"rows": rows
});
//document
var jsonDoc = initDocument();
jsonDoc.document.dataUnits.push(dataUnitTransactions);
return jsonDoc;
}
function transactionRowEdit() {
// Read the table row by row and save some values
var rows = [];
var table = Banana.document.table('Transactions');
for (var i = 0; i < table.rowCount; i++) {
var tRow = table.row(i);
var description = tRow.value('Description');
if (description.length <= 0)
continue;
//row operation
var row = {};
row.operation = {};
row.operation.name = "modify";
var sequence = i.toString();
if (sequence.length <= 0)
sequence = "0";
row.operation.sequence = sequence;
//row fields
row.fields = {};
row.fields["Date"] = getCurrentDate();
row.fields["Description"] = description + " + transactionRowEdit()";
rows.push(row);
}
//table
var dataUnitTransactions = {};
dataUnitTransactions.nameXml = "Transactions";
dataUnitTransactions.data = {};
dataUnitTransactions.data.rowLists = [];
dataUnitTransactions.data.rowLists.push({
"rows": rows
});
//document
var jsonDoc = initDocument();
jsonDoc.document.dataUnits.push(dataUnitTransactions);
return jsonDoc;
}
function transactionRowInsert() {
//row operation
var row = {};
row.operation = {};
row.operation.name = "add";
row.operation.sequence = "0";
//row fields
row.fields = {};
row.fields["Date"] = getCurrentDate();
row.fields["Description"] = "Executed transactionRowInsert()";
//rows
var rows = [];
rows.push(row);
//table
var dataUnitTransactions = {};
dataUnitTransactions.nameXml = "Transactions";
dataUnitTransactions.data = {};
dataUnitTransactions.data.rowLists = [];
dataUnitTransactions.data.rowLists.push({
"rows": rows
});
//document
var jsonDoc = initDocument();
jsonDoc.document.dataUnits.push(dataUnitTransactions);
return jsonDoc;
}
Complete example loading and running an existing DocumentChange
The following code will ask for a DocumentChange file as input, after which it will process the operations present in the document and modify the accounting file accordingly.
To try this extension, you can copy and paste the code into a text file and save it in ".js" format.
To install it in Banana you have to click on the Menu Extensions (command Manage extensions...) and then add the file you just created using "Add from file" button.
After this procedure, the file can be run from the Menu Extensions under the new command "Read document change files (task: app.command)".
// @id = ch.banana.test.read.documentchange
// @api = 1.0
// @pubdate = 2019-12-19
// @publisher = Banana.ch SA
// @description = Read document change files (task: app.command)
// @task = app.command
// @doctype = *
// @docproperties =
// @timeout = -1
function exec(inData) {
if (!Banana.document)
return "@Cancel";
var fileContent = '';
var fileName = Banana.IO.getOpenFileName("Select open file", "", "Json file (*.json);;All files (*)")
if (fileName.length) {
var file = Banana.IO.getLocalFile(fileName)
//file.codecName = "latin1"; // Default is UTF-8
fileContent = file.read();
if (file.errorString) {
Banana.Ui.showInformation("Read error", file.errorString);
return "@Cancel";
}
} else {
Banana.Ui.showInformation("Info", "no file selected");
return "@Cancel";
}
var jsonData = {
"format": "documentChange",
"error": "",
"data": []
};
try {
jsonData = JSON.parse(fileContent);
} catch (e) {
Banana.Ui.showInformation("Info", "error parsing documentChange");
Banana.Ui.showText(fileContent);
return "@Cancel";
}
if (!jsonData)
return "@Cancel";
//Banana.Ui.showText("json object: " + JSON.stringify(jsonData, null, 3));
return jsonData;
}
Invoice Examples Document Change API
Example adding an Invoice
the following example shows how to use document change to create invoices with Document Change in Banana. These rows can only be inserted in the Invoices table using the Estimates and Invoices application. The Invoice JSON object represents the data structure through which a new invoice can be created.
{
"format": "documentChange",
"error": "",
"data": [
{
"document": {
"dataUnits": [
{
"nameXml": "Invoices",
"data": {
"rowLists": [
{
"rows": [
{//Invoice 1
"operation": {
"name": "add"
},
"fields": {
(Invoice JSON Object)
},
{//Invoice 2
"operation": {
"name": "add"
},
"fields": {
(Invoice JSON Object)
}
]
}
]
}
}
]
},
"creator": {
"executionDate": "2021-09-13",
"name": "extension_name.js",
"version": "1.0"
}
}
]
}
Assure that the JSon Invoice Object is valid
It is advisable to check that the JSon Invoice Object is valid. Therefore:
- Create a Json Invoice Object.
- Pass the object to the function Calculate invoice.
- Add the object to the document change
Example Change the Date with the Document Change API
Introduction
This page explains how to update the file properties, accounting dates with Banana Accounting Javascript Extension using DocumentChange API .
Before start, to understand part of the code it's useful to read all the documentation about the DocumentChange API functionalities .
It is very important to have all the dates that match in the accounting document, otherwise the script may not work properly.
This script allows you to change the file dates with the current year. for setting the current year has been created a Date type object, we use his method getFullYear() to recover the year of the current date.
The current date value can be changed in the in the method: initParam();.
In an Accounting file we can find three main places with references to the dates:
- OpeningDate/ClosureDate, which indicate the opening and closing dates of the accounts
- HeaderLeft/HeaderRight, that are the two opposite side headers of a page, where a user could decide to put the dates for example for print a report
- Date, referring to the date field in the table
the internal format is always composed as follows 'YYYYMMDD'.
References
- DocumentChange API
- Script's source: ch.banana.apps.documentchange.dates.js
- Build you first Extension
Function Exec()
The exec() function is the main function, which is called when the extension is executed. This function does the following:
- creates the initial command structure part which is the same for all documents
- calls the methods used to define the rest of the structure and parameters of the documents we want to change
- for every function called, checks that the parameters are not empty
- returns an object containing the whole commands structure
function exec(inData, options) {
var param = initParam();
Banana.console.debug(JSON.stringify(param));
var ischange = false;
var documentChange = { "format": "documentChange", "error": "", "data": [] };
if (param.newaccountingopeningdate.length > 0) {
var jsonDoc = setfileInfo("AccountingDataBase", "OpeningDate", param.newaccountingopeningdate);
documentChange["data"].push(jsonDoc);
ischange = true;
}
if (param.newaccountingclosuredate.length > 0) {
jsonDoc = setfileInfo("AccountingDataBase", "ClosureDate", param.newaccountingclosuredate);
documentChange["data"].push(jsonDoc);
ischange = true;
}
if (param.newaccountingheaderleft.length > 0) {
jsonDoc = setfileInfo("Base", "HeaderLeft", param.newaccountingheaderleft);
documentChange["data"].push(jsonDoc);
ischange = true;
}
if (param.newaccountingheaderright.length > 0) {
jsonDoc = setfileInfo("Base", "HeaderRight", param.newaccountingheaderright);
documentChange["data"].push(jsonDoc);
ischange = true;
}
jsonDoc = setTransactionsDate(param);
if (typeof(jsonDoc) == "object") {
documentChange["data"].push(jsonDoc);
ischange = true;
}
jsonDoc = setBudgetDate(param);
if (typeof(jsonDoc) == "object") {
documentChange["data"].push(jsonDoc);
ischange = true;
}
if (ischange) {
return documentChange;
}
}
Function initParam()
- This method initializes the parameters, which are dates taken from the file informations, and the crurrent date.
- checks if there is a date in the headers taking as reference the opening date of the file, if there is it updates it with the current one
- check that the year of opening and closing have the current year, if not, will update them with the current year
- return an object with al the parameters wee need.
function initParam() {
var param = {};
param.differenceyear = 0;
param.accountingyear = "";
param.newaccountingyear = new Date().getFullYear();
param.newaccountingopeningdate = "";
param.newaccountingclosuredate = "";
param.newaccountingheaderleft = "";
param.newaccountingheaderright = "";
var OpeningDate = Banana.document.info("AccountingDataBase", "OpeningDate");
if (OpeningDate && OpeningDate.length > 4) {
param.accountingyear = OpeningDate.toString().substr(0, 4);
var Headerleft = Banana.document.info("Base", "HeaderLeft");
if (Headerleft && Headerleft.indexOf(param.accountingyear) >= 0) {
Headerleft = Headerleft.replace(param.accountingyear, param.newaccountingyear);
param.newaccountingheaderleft = Headerleft;
}
var Headerright = Banana.document.info("Base", "HeaderRight");
if (Headerright && Headerright.indexOf(param.accountingyear) >= 0) {
Headerright = Headerright.replace(param.accountingyear, param.newaccountingyear);
param.newaccountingheaderright = Headerright;
}
var currentYearint = parseInt(param.newaccountingyear);
var fileYearint = parseInt(param.accountingyear);
param.differenceyear = Banana.SDecimal.subtract(currentYearint, fileYearint);
if (parseInt(param.differenceyear) != 0) {
param.newaccountingopeningdate = param.newaccountingyear.toString() + OpeningDate.toString().substr(4);
param.newaccountingopeningdate = param.newaccountingopeningdate.replace(/-/g, "");
var closureDate = Banana.document.info("AccountingDataBase", "ClosureDate");
if (closureDate && closureDate.length > 4) {
var year = closureDate.toString().substr(0, 4);
var newyear = parseInt(year) + parseInt(param.differenceyear);
param.newaccountingclosuredate = newyear.toString() + closureDate.toString().substr(4);
param.newaccountingclosuredate = param.newaccountingclosuredate.replace(/-/g, "");
}
}
}
return param;
}
Function changeYearInDate()
Calculate the new date for the opening date and the closure date taking as parameters the current opening and closure date and the difference between the current effective date and the current opening date reported in the file information.
conditions:
the opening and the closure date should not be empty.
return the date changed.
function changeYearInDate(differenceyear, OpeningClosureDate) {
if (OpeningClosureDate && OpeningClosureDate.length > 4) {
var Year = OpeningClosureDate.toString().substr(0, 4);
var newyear = Banana.SDecimal.add(parseInt(Year), parseInt(differenceyear));
var changedDate = newyear.toString() + OpeningClosureDate.toString().substr(4);
changedDate = changedDate.replace(/-/g, "");
return changedDate;
}
return "";
}
Function getNewRowDate()
Calculate the new date for the table rows taking as parameters the current date in the table and the difference between the current effective date and the current opening date reported in the file information.
Conditions:
- the current date must be longer than four characters, and should exist
- it must be a difference between the current effective date and the current opening date reported in the file information
Returns the new date.
function getNewRowDate(currentDate, param) {
if (!currentDate || currentDate.length < 4)
return "";
if (param.differenceyear == 0)
return "";
var currentyear = currentDate.substr(0, 4);
var newyear = parseInt(currentyear) + parseInt(param.differenceyear);
var newDate = newyear.toString() + currentDate.substr(4);
return newDate;
}
function setfileInfo()
- this function set the structure of the document wich modify the dates into the files information table
- requires three parameters which represent the identification values of the fields who can containing dates in the file information table
- returns an object with the document structure
function setfileInfo(key1, key2, value) {
//row operation
var row = {};
row.operation = {};
row.operation.name = "modify";
row.fields = {};
row.fields["SectionXml"] = key1;
row.fields["IdXml"] = key2;
row.fields["ValueXml"] = value;
var rows = [];
rows.push(row);
//table
var dataUnitTransactions = {};
dataUnitTransactions.nameXml = "FileInfo";
dataUnitTransactions.data = {};
dataUnitTransactions.data.rowLists = [];
dataUnitTransactions.data.rowLists.push({ "rows": rows });
//document
var jsonDoc = initDocument();
jsonDoc.document.dataUnits.push(dataUnitTransactions);
return jsonDoc;
}
functions setTransactionsDate()/setBudgetDate()
these two functions are very similar, they have the same code and the same document structure, which modifies the rows of the record tables, the only difference is that they are pointing to two different kind of tables:
- transactions
- budget
both call the function getNewRowDate(), wich change the current date founded in the row with the effectvie current one.
Returns an object with the document structure.
setTransactionsDate()
function setTransactionsDate(param) {
var table = Banana.document.table("Transactions");
if (!table) {
return;
}
var rows = [];
for (var i = 0; i < table.rowCount; i++) {
var tRow = table.row(i)
var TransDate = tRow.value('Date');
TransDate = getNewRowDate(TransDate, param);
if (TransDate.length <= 0) {
continue;
}
//row operation
var row = {};
row.operation = {};
row.operation.name = "modify";
row.operation.sequence = i.toString();
row.fields = {};
row.fields["Date"] = TransDate;
rows.push(row);
}
if (rows.length <= 0)
return;
//table
var dataUnitTransactions = {};
dataUnitTransactions.nameXml = "Transactions";
dataUnitTransactions.data = {};
dataUnitTransactions.data.rowLists = [];
dataUnitTransactions.data.rowLists.push({ "rows": rows });
//document
var jsonDoc = initDocument();
jsonDoc.document.dataUnits.push(dataUnitTransactions);
return jsonDoc;
}
setBudgetDate()
function setBudgetDate(param) {
var table = Banana.document.table("Budget");
if (!table) {
return;
}
var rows = [];
for (var i = 0; i < table.rowCount; i++) {
var tRow1 = table.row(i)
var TransDate = tRow1.value('Date');
var TransDateEnd = tRow1.value('DateEnd');
TransDate = getNewRowDate(TransDate, param);
TransDateEnd = getNewRowDate(TransDateEnd, param);
if (TransDate.length <= 0) {
continue;
}
//row operation
var row = {};
row.operation = {};
row.operation.name = "modify";
row.operation.sequence = i.toString();
row.fields = {};
row.fields["Date"] = TransDate;
row.fields["DateEnd"] = TransDateEnd;
rows.push(row);
}
if (rows.length <= 0)
return;
//table
var dataUnitTransactions = {};
dataUnitTransactions.nameXml = "Budget";
dataUnitTransactions.data = {};
dataUnitTransactions.data.rowLists = [];
dataUnitTransactions.data.rowLists.push({ "rows": rows });
//document
var jsonDoc = initDocument();
jsonDoc.document.dataUnits.push(dataUnitTransactions);
return jsonDoc;
}