Localisation: GST/VAT worksheet programming guide

[Patch]

Philippine BIR Reports Templates

I’m actually working on new way to do these report transformations, the current implementation comes short for quite a few use-cases so that’s why there hasn’t been guidance on how to do it because it will be obsolete. Once new implementation is in the program, there is going to be a lot more activity in country-specific implementations.

As a result of this post it is probably best to not put too much effort into localisation at the moment. The following thread describes what can currently be done. I assume the future implementation will enable a larger range of use cases to be addressed and hopefully maintains relative ease of implementation for those with a good understanding of local tax requirements such as local accountants.

Background information

Development environment

The localisation development environment is accessed by Setting → Report Transformations. It consists of

Name: A text sting the final user sees when running the localisation

Type: Used to determine both where the localisation appears in the report tab and the content of the “objects” variable (information) passed to the localisation when it runs. Current options are:

Localisation type	Application
Tax Summary	GST/VAT Worksheet. “Objects” contains totals for all tax codes used separating sales, purchases, and tax component.
Taxable Purchases by Supplier	Taxation report summarising supplier transactions typically during the last financial year
Payslip summary	Reports for the current pay period. “objects” contains information about each employee as well as their payslip earnings, deduction, and contribution items.
Employee Summary	Taxation report for a specific employee summarising payments during the last financial year.

Description: A free text field which I believe is used to build the installation help file

Script: Liquid code to implement the data manipulation and calculation performed by the localisation.

Layout: A spread sheet type graphical editor with menu controlled formatting functions. It also support liquid code.

During development it is efficient to have one browser tab opened on the editor for your localisation and another tab opened on the view of your localisations output.

[Patch]

Liquid

Liquid is an open source template language. The basic feature are described here Introduction – Liquid template language or in more detail here Liquid for Designers · Shopify/liquid Wiki · GitHub . The implemantation in Manager actually uses dotLiquid the differences are summarized here DotLiquid for Designers · dotliquid/dotliquid Wiki · GitHub in particular Manager uses .Net date formatting. The language is extendible (by updates to Manager). The version used in Manager support additional filter including array filters useful in localisation

Filter	Function performed
where:	Select all the objects in an array where a given attribute has a given value.
select:	Selects the value of a given attribute for each item in an array, and returns these values as an array.
map:	Standard liquid array operator. Creates an array of values by extracting the values of a named property from another object
uniq	Standard liquid array operator which removes any duplicate elements in an array.

Code examples

{% assign NewVariable = objects | where: array_field, IndexValue %}
{% assign NewVariable = objects | select: array_field, IndexValue %}
{% assign NewVariable = objects | map: IndexValue | uniq %}

[Patch]

Importing / Exporting localisations

Localisation examples are available on the main web site https://www.manager.io/localizations/countries Many countries have multiple localisations which can be simultaneously imported into a Manager business as described here Add localized settings and features | Manager

Manager localisations in business can also be exported, doing so exports all that businesses localisation. As a result when developing a localisation it is best done by creating a new business and adding only the features you want in your final localisation. The export can also by used as an interim backup when testing development ideas.

[Patch]

Business information access

Only information passed by Manager to the localisation is accessible from within the localisation (due to Liquids design philosophy). Most of the localisation specific information is passed in a variable called “objects” the contents of which varies with the localisation type. “business” and “report” specific information is also visible when the localisation is run.

Variables are of dynamic size. So if the your business has not used or defined some thing, variables referencing it will be empty.

Variable definition and behaviour is actually harder to describe than they are to use. So if you find this overview confusing, create a test localisation, leave the calculation section blank and experiment displaying the content of the variables. The content will change as you enter transactions and define values else where in the Manager business.

Data is mostly stored in variables consisting of an array of elements of the format [dataLable, dataContent]. The dot “.” operator can be used to select a particular value of a array. For example assuming
MyVariable = [apples, 5.5][oranges, 10.2]
Then

{{ MyVariable.oranges }}

would print 10.2

Data is mostly accessed via a pointer or UUID. Complex data structures are build by having a pointer (UUID) in the dataContent. When a structure is displayed containing a UUID, then “DotLiquid.Hash” rather than the pointer value or referenced structure is displayed. However if a specific UUID is specified, the referenced data structure will be displayed.

To illustrate, in a test business with several tax codes defined and two transactions entered
Receipt into Sales account, amount 100, with tax code “GST 15%”
Receipt into Sales account, amount 1000, with tax code “GST 0%”

Then in a localisation of type “Tax Summary” and nothing entered in the Script section. The following code in the Layout

{{ objects }}

Will display

DotLiquid.HashDotLiquid.Hash

Consistent with the variable “objects” containing information about the two used tax codes.

The contents of the “objects” array can be displayed by

{% for objectRow in objects  %}
  {{ objectRow }}	
{% endfor %}

Which will display

[TaxCode, DotLiquid.Hash][netSales, 1000.00][taxOnSales, 0][totalSales, 1000.00][netPurchases, 0][taxOnPurchases, 0][totalPurchases, 0][taxOnSalesMinusTaxOnPurchases, 0] 
[TaxCode, DotLiquid.Hash][netSales, 86.96][taxOnSales, 13.04][totalSales, 100.00][netPurchases, 0][taxOnPurchases, 0][totalPurchases, 0][taxOnSalesMinusTaxOnPurchases, 13.04]

Which is the actual data Manager calculated from the current business and passed to the localisation.

The data above contains the UUID of each of the tax codes used in the business, labelled “TaxCode” and displayed as “DotLiquid.Hash”. That data element can be referenced using the dot operator and specifying the “TaxCode” data label.

{% for objectRow in objects  %}
  {{ objectRow }}
  {{ objectRow.TaxCode }}
{% endfor %}

Which displays:

[TaxCode, DotLiquid.Hash][netSales, 1000.00][taxOnSales, 0][totalSales, 1000.00][netPurchases, 0][taxOnPurchases, 0][totalPurchases, 0][taxOnSalesMinusTaxOnPurchases, 0] 
[Name, GST 0%][Notes, ][Rate, DotLiquid.Hash][TaxRate, ZeroRate][TaxRateType, SingleRate][HasFlatRate, DotLiquid.Hash][FlatRate, DotLiquid.Hash][Archived, DotLiquid.Hash][IsReverseCharged, DotLiquid.Hash][ReverseChargedRate, DotLiquid.Hash][CustomSalesInvoiceTitle, DotLiquid.Hash][SalesInvoiceTitle, Tax Invoice][CustomCreditNoteTitle, DotLiquid.Hash][CreditNoteTitle, ][Key, 1a88fd08-a595-4e12-97d3-85fc165eecdc]     
   
[TaxCode, DotLiquid.Hash][netSales, 86.96][taxOnSales, 13.04][totalSales, 100.00][netPurchases, 0][taxOnPurchases, 0][totalPurchases, 0][taxOnSalesMinusTaxOnPurchases, 13.04] 
[Name, GST 15%][Notes, ][Rate, DotLiquid.Hash][TaxRate, CustomRate][TaxRateType, SingleRate][HasFlatRate, DotLiquid.Hash][FlatRate, DotLiquid.Hash][Archived, DotLiquid.Hash][IsReverseCharged, DotLiquid.Hash][ReverseChargedRate, DotLiquid.Hash][CustomSalesInvoiceTitle, DotLiquid.Hash][SalesInvoiceTitle, Tax Invoice][CustomCreditNoteTitle, DotLiquid.Hash][CreditNoteTitle, ][Key, ee8cacde-58da-48ec-8aa9-aa6acba9c32f] 

From which more useful data can be selected, for example

{% for objectRow in objects  %}
  Tax Code name  {{ objectRow.TaxCode.name }}
  Tax Code UUID  {{ objectRow.TaxCode.key }}
  Localisation data {{ objectRow }}
{% endfor %}

Entered into a localisation layout

Which when viewing the localisation displays

Similarly the contents of the variables “report” and “business” can be displayed by

{{ report }}
{{ business }}

An example of the printout from this for “report” is

[From, 01/01/0001 ][To, 15/08/2019 ][Description, ][Key, 69394879-fd35-4143-bf4b-33ea6de86443]

And “business” where a custom field has been added but accounting Basis not defined

[Name, My Test Business name][Address, My test Businesses Address line 1 Address line 2][CustomFields, System.Collections.Generic.Dictionary`2[System.String,System.String]][Key, 38cf4712-6e95-4ce1-b53a-bff03edad273]

[Patch]

Developing GST/VAT Worksheet: Step 1 Create Manager businesses

Create a new Manager business, Name it something like “My VAT Worksheet business”

Import the VAT/GST tax codes for your country into “My VAT Worksheet business”. This ensures the UUID of your tax codes is the same as all other Manager businesses in your country. Avoid recreating tax codes as that would force anyone who uses your report transformation to change the tax codes on all their transactions.

Similarly, it there are custom fields shared with other report transformations, import that report transformation then delete all items not needed in your new report transformation (such as the report itself).

Look through the VAT/GST worksheets already published. Choose one with similar tax rules to your country or other feature you like. Create another manager business “Country X VAT worksheet business” and import this localisation worksheet into it.

[Patch]

Developing GST/VAT Worksheet: Step 2 Copy Report Transformation Script

Open “Country X VAT worksheet business”, go to Settings → Report transformation → open the GST/VAT worksheet. Select all of the “Script” section and copy it.

Open “My VAT Worksheet business”, Go to go to Settings → Report transformation. Select “New Report Transformation”.

Name it “VAT Calculation worksheet” , Choose type: “Tax Summary”. Paste in the Script from “Country X VAT worksheet business”

At the bottom of the screen click the create button to create your new (incomplete) report transformation.

On subsequent occasions and “Update” button will show here.

[Patch]

Developing GST/VAT Worksheet: Step 3 Correct the Tax codes UUID

Open a Manger window in your browser (so it shows the page address bar). How to do this varies with operating system. In windows right click on a link and choose open in a new window)

Go to the tax code page in Manger in you business

Edit the page address to add “.json” (at the end of the address but before the parameters)

For each or your countries tax code the UUID and name are then displayed

In your localisation, for each tax code create a variable named identical to each tax code (spaces replace by under score) and assigned the appropriate UUID.

[Patch]

Developing GST/VAT Worksheet: Step 4 Create arrays of all your tax code totals

This is done by the following code which is likely to be the same for all VAT calculation work sheets, so you can leave it unchanged (or delete the lines your worksheet never uses).

{% assign netSales = objects | select: 'TaxCode.Key', 'netSales' %}
{% assign taxOnSales = objects | select: 'TaxCode.Key', 'taxOnSales' %}
{% assign totalSales = objects | select: 'TaxCode.Key', 'totalSales' %}
{% assign netPurchases = objects | select: 'TaxCode.Key', 'netPurchases' %}
{% assign taxOnPurchases = objects | select: 'TaxCode.Key', 'taxOnPurchases' %}
{% assign totalPurchases = objects | select: 'TaxCode.Key', 'totalPurchases' %}
{% assign taxOnSalesMinusTaxOnPurchases = objects | select: 'TaxCode.Key', 'taxOnSalesMinusTaxOnPurchases' %}

What it does, from the “objects” passed to the localisation, it creates variables containing an array which use each tax codes UUID as an data label and the relevant total as a value. Array elements being of the format [TaxCodeUUID, amountInDollars]

These arrays can then be used to easily access specific totals for each tax code.

For example the following code will now display the net sales for the tax code GST 15%

{{ netSales[GST_15] }}

[Patch]

Developing GST/VAT Worksheet: Step 5 Perform your countries VAT calculation

This mostly requires an understanding of your countries VAT legislation. It is country specific. It maps the tax codes used in a Manager business to your countries tax reporting requirements. The calculations done here should essentially be the same as you are currently doing manually for each VAT return. Examples from other countries VAT calculation worksheet may provided some guidance particularly if similar tax regimes apply, however the details are likely to be different for each country.

[Patch]

Developing GST/VAT Worksheet: Step 6 Place the calculation results in a layout similar to your tax departments standard form

• Obtain standard stationary for your countries VAT submission.

• Edit the localisation “Layout” section to approximate your standard VAT submission form.

• Cells can be modified by double clicking on them

• Flow control statements are typically entered in a merged row (column A) where they are highlighted red and facilitate clear layout control.

• Table cell formatting options are available via a mouse menu

[Patch]

Developing GST/VAT Worksheet: Step 7 Testing the calculation worksheet

This is most rapidly done by printing out all input and intermediate values.
A rough way of printing the input data is

{% for objectRow in objects %}
  {{ objectRow.TaxCode.name }}
  {{ objectRow }}
{% endfor %}

This displays the tax code name then the tax code information lubos has provided to the localisation. Most of which is readable except the UUID (the array entry showing as [taxCode, DotLiquid.Hash])

Intermediate values common to most Tax Summary localisations can be displayed as follows (but you will probably find this unnecessary as the above rough listing is relativity easy to read when see what it is displaying)

{% for objectRow in objects %}
  {{ objectRow.TaxCode.name }}
  {{ netSales[objectRow.TaxCode.Key] }}
  {{ taxOnSales[objectRow.TaxCode.Key] }}
  {{ totalSales[objectRow.TaxCode.Key] }}
  {{ netPurchases[objectRow.TaxCode.Key] }}
  {{ taxOnPurchases[objectRow.TaxCode.Key] }}
  {{ totalPurchases[objectRow.TaxCode.Key] }}
  {{ taxOnSalesMinusTaxOnPurchases[objectRow.TaxCode.Key] }}
{% endfor %}

Which can be entered into the layout section

Other intermediate calculation variables will vary for each countries worksheet. Displaying them would be useful if the results are not as you expect.

Then enter a single transaction and check the worksheet results, comparing it to what you manually calculate. Test cases should be for legitimate transaction covering the use of each of the tax codes for

• Sale
• Purchase
• Sale return (ie negative value)
• Purchase return (ie negative value)

[Patch]

Developing GST/VAT Worksheet: Step 8 Create a localisation for general use

• Transformation description field; In your report add text to guide other users in the correct use of your report transformation. Information to assist other users may include; what it is designed to be used for, when it should be run, and assumptions on how tax codes are used for particular situations in the Manager business. This text will appear at the top of your report transformations user guide.

• Backup the “My VAT Worksheet business” and/or export the report transformation.

• Import the report transformation into an new blank business or restore your “My VAT Worksheet business” back up.

• From this interim Manager business, delete all development / debug code. Delete any other customisations not required for this report transformation such as custom fields or tax codes not used or not appropriate.

• Export your final report transformation.

• Contact Lubos (or other designated moderator) to discuss hosting it on the main manager site for general use / distribution.

[novica]

So, I’ve followed Patch’s instructions to create a new localization, but my report fails with

DotLiquid.Exceptions.SyntaxException: Syntax Error in 'assign' tag - Valid syntax: assign [var] = [source]
   at DotLiquid.Tags.Assign.Initialize(String tagName, String markup, List`1 tokens)
   at DotLiquid.Block.Parse(List`1 tokens)
   at DotLiquid.Template.ParseInternal(String source)
   at DotLiquid.Template.Parse(String source)
   at ManagerServer.HttpHandlers.Businesses.Business.Reports.Report2.Get()
   at ManagerServer.HttpApplication.ProcessRequest(HttpRequest request, HttpResponse response)

I cannot see where my error is. Anyone willing to review the .json file I made?

[Patch]

The interpreter syntax error reporting in a little cryptic.
It is telling you your liquid code is not valid.

• Simplify the search for the error by cutting the later half of your code and saving it in a text document.
• Run it again (which can be rapidly done by having 2 manager browser windows open, one on your localisation, the other viewing the report)
• Increase or decrease the running code till you can see your syntax error.

I got a similar error with miss matched control structures. A accidentality wrote

{% for objectRow in objects %}
  {{ objectRow }}
{% endif %}

A syntax error as the for endfor, if endif balance is wrong

PS
Variable and debugging sections above have been updated

[novica]

Thanks Patch.

Found it.

Now I can see the report. It’s empty as I haven’t entered any data yet. However on top of the report I get a bunch of lines saying:

Liquid error: Value cannot be null. Parameter name: key

Is this because I’m just generating an empty report or this is another error?

UPDATE: Found this one too.

[Patch]

Enter a transaction which uses a tax code.
The data structures only contain things you have used

2019/08/17
Guide updated (variables section rewritten, Step 4 added net values, Step 6 added double click, Step 7 updated debug display code examples)

2019/08/18
Updated step1 with import tax code & custom field logic
Added Step 8 Create a localisation for general use

[novica]

How to I get {{ report.From | date: 'dd MMMM yyyy' }} to show the translated language of the month? Right now it prints in English for me.

[Patch]

Some date routines have a language parameter. I haven’t seen it in liquid.
Other localisations just use a numeric date format which ovoids the problem.
A filter could be used to replace each English month with it’s translation but there is probably a better way.

[novica]

OK. So I have a completed localized report.

I export it to get the .json file.

I try to import in in another empty business.

I get an error: Unexpected character encountered while parsing value: . Path '', line 0, position 0.

[Patch]

If you look at the .json file with a text editor, does it look similar to other localisation files (ie not an empty file or otherwise grossly wrong). Note I have found saving the .json file with a text editor has stopped it from being usable in Manager (I assume the file uses non printable characters do define the document parts).

If it looks reasonable @lubos may need to help. I suspect he will need to be involved soon to consider adding it to the main web site.