Create tablequery with number of columns variable

Hello everyone, I have a problem that due to my inexperience in tryton I don’t know how to solve it and I didn’t find a similar module. I have to plan jobs. And it is required to be loaded as follows.

job; required_quantity; planned_quantity;month_1; month_2; .....

I created the following table to store the data:
job; month_number; quantity

I can think of two solutions:

  1. Have 12 columns of months and by means of a field pass the months offset value to it.The problem I have is that I don’t know how to pass this displacement value to my tablequery (I have implemented this, both the obtaining of the data by tablequery, as well as the write method to save the data).
  2. To have a tablequery with variable columns. The problem here I do not know if it is possible from tryton.

Thanks in advance.

You need to create a model with a column for each month.

Then using the table query you can use the filter attribute of the aggregate to filter the agregate for each month. We have an example on the spanish tax reporting

Thank you very much for your response. I will check what you sent me. The problem is that the number of months depends on the project, it can be from 5 months to 36 months for example, there is no limit of duration.

This part I don’t understand.

This rocks! It improved the performance a lot, I had done it with 12 joins.

I saw that with context_model I can filter the results, so in this case I can choose from which month I will start showing the results, but I saw that it applies to act_windows, I wanted to know if I can do the same for a view, since I want to show these results as a One2Many field, from another view.
I add a screenshot of how I want the interface to look like

With Tryton you can not have arbitrary/dynamic columns (for now). So I think month columns should be a list of month stored/displayed in a One2Many.

Thanks for the answer Ced. The problem is that the job list is also dynamic and the name is important, that’s why I chose to put the jobs as rows.

But you can have a One2Many in a One2Many. But you can just not display them in a list.

I don’t understand what you want to explain.

As a context, the idea of the module is to see all the tasks to be done and to load which ones are going to be done and how many of them are going to be done in a specific month. It is proposed in this way so that visually it is easy to plan the tasks for the user.

There I understood, I would place an O2M for quantities per month and the only columns I would have would be:

job; required_quantity; planned_quantity;quantities_per_month(O2M).

But I would have the problem that it would not be easy to contrast with the other tasks.

Another way would be to have just fixed number of columns and compute them relatively to today. Like current month, next month etc.

I’m trying to add an offset of where the first month starts as shown in the figure, but I can’t get the start_month field through the context.

So I’m looking at making it a window to use the context_model. It is not what I want, but I can’t solve it the other way.