Pages

▼

Monday, June 13, 2016

Episode 487: Collaboration with Cloud Accounting (Part 2)

How do I make "Slip data" federated?

In the Latest post, we succeeded to auto-generate some sheets of "Transfer slip" upon reporting orders. Yet, it doesn't mean a thing if they are not entered to "Accounting software".

Well, should I rather say "Cloud-based accounting software" than "Accounting software", for the coming age?

However, unfortunately there are only few Cloud-based accounting software which allows "API access from external" as of 2016. Therefore, I would like to consider to utilize "File import" feature which is supported in every software efficiently.

(Though, REST API over OAuth will be supported in any software service after a year or two.)

Incidentally, although this Business Process is almost the same as the one in the latest post, it has been added a Step of "Superior's approval" for the sake of more practical use.

[Sales Report-CSV Download]



[Sales Report-CSV Download: "3. Bookkeeping" screen]

This is not something complicated.

What you need to prepare first is just a template file which is supported in each of "Cloud-based accounting software". An accounting personnel is able to add data neatly to it by copying data on slip to clipboard with Ctrl-c or so, and pasting it (to e.g. B2 column). Then, "import" the template file to the accounting software "daily", "weekly", or "once in 10 days".

"MF cloud accounting" (a Japanese accounting software service), for example, although it takes the time and effort to assign the "Transaction No.", you can collectively input all the slip data. The only point you should note is that you must assign "the same Transaction No.", if slip data is over multiple lines.

<Excel capture>

By the way, there are some companies that are utilizing ERP-system as the mission-critical system. In such a case, you retrieve each of "slip data" as a CSV file.

For those cases, (although some skill of JavaScript will be required,) you arrange a "CSV file generation button" at the part where the slip data are indicated. Indeed, it requires complicated script configuration if you wanted to support browsers of older versions. But, as long as browsers of recent are used in your organization, you will be able to output those files with a very few lines of script. (HTML5,download attribute)

It is merely arrangement of a "tiny button", but it actually could be a "significant business efficiency".


P.S. Excel-friendly CSV file

In accounting work, the presence of "MS Excel" is great. Many workers would affirm that "Never be a day without it."

However, a "genuine CSV file" often results in a trouble, such as cannot open in Excel/ or if could, garbled/ or Not located in a cell. You should be aware of the following points when you generate an "Excel-compatible CSV file" by JavaScript. (For technical details, see other technology books and technology site.)
  • If it is assumed only Windows browser, Comma-Separated Values UTF-8 with BOM is good enough.
  • If you want to include Mac browser, it should be Tab-Separated Values UTF-16(LE) with BOM.
  • To put the Byte Order Mark (BOM), you will need Binary operation (even though only a few bytes).
  • If the memory byte order (endian) is suspicious, confirm the generated file with a binary editor.

<Binary editor capture>

<Open "Comma-Separated Values UTF-8 with BOM" with Excel on Mac>

<Open "Tab-Separated Values UTF-16(LE) with BOM" with Excel on Mac>


= Setting sample of [Input hint] of :TSV data" (HTML/JavaScript) 
<button type="button" id="myFile_4">generate UTF8-bom-CSV</button><span id="myMsg"> (URL here) </span> 
<button type="button" id="myFile_4b">generate UTF16-bom-CSV</button><span id="myMsgB"> (URL here) </span> 

<script type="text/javascript"> 
// Not support Internet Explorer Safari 

jQuery('#myFile_4').on('click',function(){  
  var tsvStr = jQuery('textarea[name="data\\[4\\].input"]').val();  

  //File name 
  date = new Date(); 
  var filename = date.toISOString().replace( /-|T|:/g, "" ).substring(0, 14) + ".csv"; 

  //BOM (Byte Order Mark) generation 
  var bom = new Uint8Array([0xEF, 0xBB, 0xBF]); 

  //File generation (Comma-Separated Values UTF-8 with order mark) 
  var myCsvStr = tsvStr.replace( /\t/g, ", " ); 
  var blob = new Blob( [ bom, myCsvStr ] , { type: "text/csv" } ); 

  //Download 
  jQuery( '#myMsg' ).html(  
    "<a href=" + window.URL.createObjectURL(blob) 
    + " target=_Blank download=\"" + filename 
    + "\"> Get_CSV </a>" ); 
});  

jQuery('#myFile_4b').on('click',function(){  
  var tsvStr = jQuery('textarea[name="data\\[4\\].input"]').val();  

  //File name 
  date = new Date(); 
  var filename = date.toISOString().replace( /-|T|:/g, "" ).substring(0, 14) + ".csv"; 

  //BOM (Byte Order Mark) generation 
  var bom = new ArrayBuffer( 2 ); // 2 byte (16 bit) memory 
  // edited via TypedArray or DataView 
  var bomAccess = new DataView( bom ); 
  bomAccess.setUint16( 0, 0xfeff, true ); // Little Endian order 

  //File generation(Tab-Separated Values UTF-16 with order mark) 
  var textEncoder = new TextEncoder( "utf-16" ); 
  var aryU16 = textEncoder.encode(tsvStr);  
  var blob = new Blob( [ bom, aryU16 ] , { type: "text/csv" } ); 

  //Download 
  jQuery( '#myMsgB' ).html(  
    "<a href=" + window.URL.createObjectURL(blob) 
    + " target=_Blank download=\"" + filename 
    + "\"> Get_CSV </a>" ); 
});  

// To support Internet Explorer, use msSaveBlob  
</script>

[Data Items list]


[Free Download]
<Similar Models>
<<Related Articles>>

[Japanese Entry (ε’Œζ–‡θ¨˜δΊ‹)]