How to implement country-specific report in Manager

[Davide]

@Patch

The suggestion is that report transformation have filters

• Where:
• Group:
• Select:

To be complete there should be:

SELECT
FROM List of tables with JOIN and ON
WHERE
ORDER BY
GROUP BY

since SQL is an international standard that works so well I think that we can simply follow it.

[MarkLL]

Thanks for the response and clarification.

@lubos

Normally you wouldn’t make straight edits using text editor. You’d import JSON into Manager, make changes, then export JSON out of Manager. So you’d use Manager as your “JSON editor” for these files.

Yes I agree completely, for the average Manager user you have created a very easy workflow and kudos to you! Then, if you’re already familiar with the normal GitHub workflow and are familiar with .json structure (and it’s a definite improvement over xml) I find text editors are quicker and easier.

Anyway you have confirmed what I wanted to know and I will be mindful of that fact or just use phpStorm as it appears to works correctly in that (VSCode and Notepad++ are troublesome in windows).

[lubos]

@Davide

I was trying to extract a whole list of all the inventory kits with all the attributes (including all custom fields) that were not sold (so not linked to a transaction).

Here is simple code which will give you list of inventory kits not used by any transaction.

{% assign inventoryKitsInUse = transactions | map: "InventoryKit" | uniq | compact | map: "Key" %}
{% for e in inventoryKits %}
{% if inventoryKitsInUse contains e.Key %}{% continue %}{% endif %}
{{ e.Name }}<br />
{% endfor %}

The first line will get list of inventory kits used by transactions. Then we loop through all inventory kits and skip those which are used by transactions.

There is no point to add SQL if you can already do everything using Liquid. Liquid can handle data querying and presentation while SQL can only handle data querying. You still need scripting language for presentation so SQL cannot really replace Liquid but Liquid can replace SQL.

For that reason SQL is redundant.

[Davide]

@lubos

There is no point to add SQL if you can already do everything using Liquid. Liquid can handle data querying and presentation while SQL can only handle data querying. You still need scripting language for presentation so SQL cannot really replace Liquid but Liquid can replace SQL.

So I will have to learn liquid. Thanks a lot.

[Ealfardan]

It’s amazing how liquid can do so much with so little.

[Davide]

@lubos

{% assign inventoryKitsInUse = transactions | map: "InventoryKit" | uniq | compact | map: "Key" %}
{% for e in inventoryKits %}
{% if inventoryKitsInUse contains e.Key %}{% continue %}{% endif %}
{{ e.Name }}<br />
{% endfor %}

Let me see if I understand it correctly (looking at Liquid online manual):

1. query transactions which are unique (uniq), eliminate duplicates (compact), save into an array all columns from “Inventory Kit” (map) and also map “Key” (to get only lines that have actually an UUID, ie that contains a real transaction)
2. start enumerate all the inventory kits
3. if the query at point 1 returns a Key Liquid will skip the Inventory Kit Item otherwise it will go into it
4. print data from Inventory Kit

It seems quite verbose and, so, not so difficult.

A further question. How do you manage Group By a field. Sorry for the silly questions but it is something completely new.

[lubos]

@Davide

How do you manage Group By a field. Sorry for the silly questions but it is something completely new.

I just added new group_by filter to the latest version. So you can something like:

{% assign inventoryItemsPerUnitName = inventoryItems | group_by: "UnitName" %}

<ul>
    {% for e in inventoryItemsPerUnitName %}
    <li>{{ e.Key }}
    <ul>
        {% for inventoryItem in e.Value %}
        <li>{{ inventoryItem.Name }}</li>
        {% endfor %}
    </ul>
    {% endfor %}
</ul>

This will group inventory items by their UnitName.

[Davide]

@lubos

{% assign inventoryKitsInUse = transactions | map: "InventoryKit" | uniq | compact | map: "Key" %}
{% for e in inventoryKits %}
{% if inventoryKitsInUse contains e.Key %}{% continue %}{% endif %}
{{ e.Name }}<br />
{% endfor %}

That’s fantastic! Thank a lot. So… group_by is the equivalent of GROUP BY under Custom Reports and sort is the equivalent of ORDER BY?

[Mark]

@MarkLL

Anyway you have confirmed what I wanted to know and I will be mindful of that fact or just use phpStorm as it appears to works correctly in that (VSCode and Notepad++ are troublesome in windows).

I recommend Brackets

[dalacor]

@lubos I would suggest merging the report transformations and custom reports modules into custom reports on the reports page if they are going to use the same coding methods etc. I also agree with another user who stated that the report transformations needs to be in the reports tab not the settings tab.

Secondly, if you are planning to changing custom reports to use liquid and not sql, when will this be done. I am intending to link Manager up via api with my CRM system and one of the things that we want to do is effectively run custom reports (on demand) to only show invoices for client A and so on. This project has been delayed as we are still working on something else. I just need to know whether we need to delay the api project if you are planning to change custom reports as we will need custom reports to filter invoices by client.

[Davide]

@dalacor

I would suggest merging the report transformations and custom reports modules into custom reports on the reports page if they are going to use the same coding methods etc.

@dalacor

Secondly, if you are planning to changing custom reports to use liquid and not sql, when will this be done.

@lubos is saying exactly the opposite. They will remain distinct since one is a way to get data quickly without programming (Custom Reports) and the other one is a way to program a report from scratch (you can define variables, pivot data and so on).

A part from that, Custom Reports is not SQL but it is a SQL look alike via graphical interface.

@dalacor

I also agree with another user who stated that the report transformations needs to be in the reports tab not the settings tab.

I totally disagree. Report transformation is like an Inventory Kit. Once rigidly defined, users will not need to edit Report Transformations like they don’t edit current standard Reports’ source code.

I would say instead that in the future (like it happened in the past) @lubos should add the possibility to move single Report Transformation to existing reports groups and even to define custom report groups to put them in.

[MarkLL]

@Mark

I recommend Brackets

Thanks, unfortunately being based on NodeJS/ECMAScript it has the same behaviour as VSCode.

[MarkLL]

@dalacor

I just need to know whether we need to delay the api project if you are planning to change custom reports as we will need custom reports to filter invoices by client.

This is already available. Try the following url swapping out the Customer uuid and FileID as relevant:
/sales-invoices.json?Customer=cdf47fed-9fa1-4cab-a8dc-e3f0539e351f&FileID=Tm9ydGh3aW5k

Basically it’s the link from the Invoice count in the Customers tab

[dalacor]

@Davide I have just re-read the topic and I think that I misread one post to mean that the custom reports would be changed to Liquid. It actually makes sense to have the transformation report as Liquid and the custom reports as sql. But as they are both essentially coding, it may make sense to have them unified at some point in the future.

Thinking about it, it would probably make more sense to keep it where it is.

[dalacor]

@MarkLL I am probably not copying the right things.

what I tried is this: https://mywebsite.com/api/business identifier/sales-invoices.json?Customer= and I put in the first customer id and the number next to it. I assume that the number next to the customer id is the fileID?

I just get a Sql Server error message. I am running Server version 20.7.31 if that helps as maybe there was an update after this date that fixed the issue.

It was Lubos himself that said that I would need custom reports to do what I wanted to do. Should I be using Rest API to sync certain data

[Ealfardan]

@Davide

I totally disagree. Report transformation is like an Inventory Kit. Once rigidly defined, users will not need to edit Report Transformations like they don’t edit current standard Reports’ source code.

You missed the point.

Suppose I created a new transformation called “VAT return.”

This transformation will appear in Settings >> Report Transformations as well as in Reports >> Report Transformations.

Currently if I want a restricted user to view “VAT return” report, I should give him access to Settings >> Report Transformations.

It would be better if there is a way to give access to Report Transformations section within Reports tab for users who have no access to Settings >> Report Transformations or whatever section we can put them in as you suggested. In any way, the report user shouldn’t have access to the settings.

[Davide]

@Ealfardan

You missed the point.

We were talking about a completely different thing. About Users Permissions I agree that we should see all the reports we created under Report Transformation listed also under Report User Permission.

I think that we are missing this functionality since they are still WIP.

[Davide]

I’m getting into Liquid. A question about “sort”.

@lubos

{% assign inventoryKitsInUse = transactions | map: "InventoryKit" | uniq | compact | map: "Key" %}
{% for e in inventoryKits %}
{% if inventoryKitsInUse contains e.Key %}{% continue %}{% endif %}
{{ e.Name }}<br />
{% endfor %}

I added sort in various point to get Inventory Kits sorted by Name but without success. For example:

{% assign inventoryKitsInUse = transactions | sort | map: “InventoryKit” | uniq | compact | map: “Key” %}

How does it work?

[lubos]

Think about what you want to sort. You want to sort inventoryKits by Name.

So your code would look like this:

{% assign inventoryKitsInUse = transactions | map: "InventoryKit" | uniq | compact | map: "Key" %}
{% assign sortedInventoryKits = inventoryKits | sort: "Name" %}
{% for e in sortedInventoryKits %}
    {% if inventoryKitsInUse contains e.Key %}{% continue %}{% endif %}
    {{ e.Name }}
{% endfor %}

This will work in the latest version (20.9.63) because sort filter didn’t work properly before.

[Davide]

How do Custom Fields work? I cannot understand how to display them.