How to implement country-specific report in Manager

65

Yeah, it will be supported for tax codes and other items too.

2 Likes
66

Thanks. In the meantime is it possible to enable batch create/update for custom fields?

It would save us a lot of time.

67

Is It possible to switch from accrural to cash basis numbers under Report Transformation?

68

@lubos or anyone else who has worked it out.

For completeness, what is the proceedure to access lines in receiptsAndPayments or salesInvoices or purchaseInvoices

The fields are displayed and Lines can be listed but my attempts to access lines data have only given only errors

{{ receiptsAndPayments | first | fields }}
{% for e in receiptsAndPayments limit:3 %}
   {{ e.Lines | first | fields }}
   {% for f in e.Lines limit:3 %}
      f appears to contain data for a line of a receipt or payment
   {% endfor %}
{% endfor %}

The structure appears to be the same for invoice

{{ salesInvoices | first | fields }}

Maybe it is a permissions thing as the lines table is not listed in globals

Ps
If lines is intended to not be accessed directly, and instead be accessed via transactions, then that’s OK to, I will just stop trying to access it directly.

69

It doesn’t work for myself to. I think that it is an issue of exposing the variables.

70

Exploring the Report Transformations environment took me some time.
The following Manager business / Report Transformation, just displays the business’s data. Which I found useful when writing transformations.
Test Localisation (2020-10-01) - Display variables.manager (100 KB)

71

Thanks

72

I cannot find the localisation for Ghana anymore as it used to be. Can you please add it? @lubos. I will want to contribute by way of submissions. Thank you.

73

If you have a Manager business in which you have loaded the Ghana tax codes you can delete everything else and use that.

If you then create a localisation using those tax codes it will work with Businesses which used the old localisation. New businesses will be able to load your localisation (with a Report transformation and tax codes) which will include the tax codes.

74

I have been playing around with report transformations and am very excited about the ways that they’ll help me once I get a few set up. However, I’m having a little difficulty with my first attempt.

I need to generate a report that lists all purchase invoices within a specified period, showing the following columns:

  • Date
  • Supplier
  • Vat number
  • Invoice number
  • Cost excluding VAT
  • VAT amount
  • Amount including VAT
  • Description of goods/services

I have managed to access the data for all the columns, but not the right amounts. For each line item on each purchase invoice I am getting a row with the VAT amount appearing in all three amounts columns, followed by a row with the tax exclusive amount appearing in all three columns. I want the right amounts as per the headers, and I want to see only a single row for each purchase invoice, not for each line item. Is there a way to group by purchase invoice? Or should I be selecting something other than tax_inclusive_purchases from transactions?

image
image1228×793 28.1 KB

I will also need to create a similar report for the sales invoices, but I think that once I figure this one out that will be easy.

75

taxcode:
In custom report terminology provides a “Where …” search term
between:
provides “From Until” functionally (which is actually also a database search so analogous to a “Where …” search term)

Managers accounting is all done at the line item level. That is where rounding to smallest legal tender occurs. Invoices, receipts/payments and all other larger entities are all collections of line items.

Any calculations in Manager are done at the line item level, so could be done within your for loop.

To get sub totals use
group_by "salesInvoices"
Then a for loop to cycle through the sales invoices
And a for loop within it to cycle through the line items

Repeat the above for purchaseInvoices and receiptsAndPayments
I had posted an example of doing this with an explanation and figures but it got deleted.

I don’t believe it is currently possible to re assemble line items into invoices directly as while all the following are accessible, the lines database table is not

  • Sales invoices salesInvoices
  • Purchase invoices purchaseInvoices
  • Receipts/payments receiptsAndPayments

My attempts to display them are in the test system in this post above

That type of filter is really a data display filter with the prior filters determining what is displayed when drilling down.
The data display you want I think will require some minor calculations and is not a standard display.

1 Like
76

Thanks @Patch.

I’ll have a look at this. If it’s easy for you to write a simple example it might help me understand, and would be greatly appreciated.

I’ve had an idea for a crude method of grouping line items into purchase invoices: For each line item that the loop iterates over, it could check the key for the purchase invoice for that transaction, and if it matches that from the previous item then they’re added together into a running total. If the keys are different, then the previous total is entered as a line on the report as a purchase invoice and the variable is reset to start summing up the new purchase invoice.

Any thoughts on this method or any others are appreciated.

77

A basic core is

{% assign gst_10 = "8cf9d117-3142-4d9c-82ee-b57a0e22c809" %}
{% assign gst_free = "14f63584-be71-40ca-9028-1a60e2e2cc90" %}
{% assign period = transactions | between: report.From, report.To %}
{% assign GST_Purchases = period | taxcode: gst_10, gst_free %}
{% assign GST_PurchaseInvoices = GST_Purchases | group_by: "PurchaseInvoice" %}
{% for e in GST_PurchaseInvoices %}
   Purchase invoice level information (Note the Key in this group_by: references a PurchaseInvoice)
   {{ e.Key.IssueDate }}  {{ e.Key.Description }}
   (Assign subtotal counters = 0 here)
   Purchase invoice line item information 
      {% for f in e.Value %}
      {{ f.GeneralLedgerAccount.Name }} {{ f.AccountAmount }} {{ f.TaxComponent }}
      (add line items to subtotals here)
      {% endfor %}
{% endfor %}

A test business for which is Test Localisation (2020-10-15) Group_by.manager (164 KB)

1 Like
78

Thanks very much. That looks like it’ll be helpful. I’ll spend some more time on it this evening, I hope, and will report back.

79

I have had moderate success.

image
image1126×919 55 KB

I used a few crude methods to achieve what I wanted. Any suggestions for improving these would be appreciated:

  1. When trying to get the supplier name, I couldn’t find a way to pick it up directly at the transaction level. The From field gives the GUID for the supplier, but trying From.Name and a few other things didn’t work to give me the supplier’s name. So I created a loop (starting in row 9 in the table) to iterate through all my suppliers and check their GUIDs against the From field of the transaction, and when there was a match I could use the properties of that supplier for some of the columns in my report, such as the supplier name and supplier VAT number (a custom field).
  2. At the transaction level I couldn’t find properties for the tax exclusive amount and tax amount, so I calculated these from TransactionAmount. (I used hard-coded values for the tax rates. I’d like to change this to retrieve the rates from the tax code definitions.)
  3. I tried an alternative approach of starting with purchaseInvoices instead of transactions, but I got stuck very quickly when I couldn’t figure out how to apply something like the between: filter for selecting a date range. So I went back to using all transactions and filtering those that I didn’t want by checking to see if transactions had a key after being grouped by purchase invoice (line 17 in the table). Those that don’t are not purchase invoices and so are omitted.

Thanks @Patch for your help above and for your test localisation, which I spent a lot of time referencing.

This is what my report creates so far, which is exactly what I want for this section:
image
Now I need to test with more data and expand it to include other currencies, and do the equivalent for sales invoices.

80

Liquid does have a weird command set in my opinion. I suspect because it is designed to create a walled garden. Providing easy access to some functionality but blocking other access. Lubos has given widespread access to users data, which when combined with liquid inherent design creates weird hurdles and requires lots of trial and error design. It is however ridiculous easy when used as it was designed to do the things it was designed to do.

Getting back to your question. Liquid uses GUID keys or pointers to access its data structure or objects. The GUID is a 128 bit pointer. The pointers can be converted relatively easily into a hexadecimal string and displayed by using the “.Key” specifier. Unfortunately liquid doesn’t appear to have the inverse function ie conversation of a hexadecimal string to a 128 bit point which can reference objects.
I think the work around is to get liquid to replicate your explicit search by doing by something like

{% assign mySupplier = suppliers | where: "Key", e.Key.From | first %}

Unfortunately I could not get any liquid where: filter to do anything using Manager v20.10.29

That requires the same function, but this time matching on “Name”.

{% assign ThisTaxcode = taxCodes | where: "Name",  g.TaxComponent | first %}
{{ ThisTaxcode.Rate }}

Alternatively you could sum the tax separately within the inner for loop.

Edit
Deleted my erroneous suggestion to use map: and instead suggest where:

1 Like
81

2 posts were split to a new topic: Questions about reports

82

@Patch Do you know if in the meantime where-statements work in Manager?

I´ve tried to filter transactions by the accounts but there seems to be no effect. Either this koind of filter doesn´t work in Manager or my statement is false:

{% assign einnahmen = transactions|tax_amount|where: "GeneralLedgerAccount.Name", "Einkommen" %}
{{einnahmen}}
83

Liquid is an extendable programming environment

I suggested earlier in this thread

The advantage being; that having some commonality between Managers custom reports and localisations would help Manager’s ease of use.

There are other variants of liquid such as Liquid Filters | Jekyll • Simple, blog-aware, static sites which does have the following filters

where:
where_exp:
group_by:
group_by_exp:

Manager does now have the group_by filter

However, to my knowledge the other filters have not been implemented in Manager or at least hadn’t last time I looked.

More importantly
With the newer versions of Manager, I’m not able to understand how flow control is used to control table output. Perhaps it’s just my ignorance on how to read html, or perhaps it’s a limitation of the current editor implementation in Manager.

84