Localisation: GST/VAT worksheet programming guide

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)
NZ%20new%20window

Go to the tax code page in Manger in you business
NZ%20tax%20codes

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.
NZ%20GST%20GGID%20assign

1 Like

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] }}

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.

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

NZ%20menu

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)

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.

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?

:slight_smile:
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

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. :slight_smile:

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

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.

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.

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.

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.

Yes. it looks like it should I guess :slight_smile: It’s working in the one demo company that I set up.

Another question, any idea how to achieve date calculation? I was following liquid documentation, but cannot get the wanted date. I want to be able to calculate the due date for submission of the report which is 25 days after the report.To date.

So I’m trying to calculate %s - Number of seconds since 1970-01-01 00:00:00 UTC. as per liquid/standardfilters.rb at 3-0-stable · Shopify/liquid · GitHub

{% assign period.End = report.To | date: '%s' %}

Calculate seconds for 25 days:

{% assign days = 25 | times: 86400 %}

And then add the two together:

{% assign due.Date = period.End | plus: days %}

{{ due.Date }} is a text sting not a single number unlike a date in a spreadsheet program.
So to do date arithmetic you have to

  • Convert the date string to a single number
  • Do the arithmetic
  • Convert the number back to a date string

Yes this is what I’m trying to achieve with {% assign period.End = report.To | date: '%s' %}

As far as I understand this is supposed to convert the report.To date to Number of seconds since 1970-01-01, but the result I get is 0.

Sorry I didn’t look closely enough.
Your code looks fine.
Tried myself and very limited date: filters work. (no month print out but “now” does work)
Perhaps date: time zone and language need to be set in Manger localisation.

{{ report.to  }}
{{ report.to | date: "%d %m %y" }}
{{ report.to | date: "%+" }}
{{ report.to | date: "%F" }}
{{ report.to | date: "%v" }}
{{ report.to | date: "%s" }}

Prints

15/08/2019
15 0 19
+

v
0

Thinking about it, I suspect lubos is already aware the date: filter is non standard. That would explain why financial year period as well as report period is separately entered in all localisation. Liquid date: filter appears to not support languages other than English, so Lubos may have a custom modification to circumvent this limitation.

Probably should look to see what date localisation data is stored in the “business” variable.

I am trying another approach, but I don’t know it seems things don’t work as documented.

{% assign month = report.To | date: '%M' %}
{{ month }}
{% assign month = month | plus: 0 %}
{{ month }}

If month is 3 I get 30 at the end. But liquid documentation says that plus: 0 should convert the string to number.

The idea was to use increment afterwards to get to the desired date.

OK. This is solved with opening the .json in a plain text editor, copy/pasting it to another file, and saving that to .json, as explained on SO.