Create date rows between two dates with conditions


I have originally planned to lay out some custom json structure and write a big python script. Having read about grist I am currently running a local docker instance for what I call a “multi-project calculation tool”. I have always new ideas for projects which have different asset investments with depreciation, fixed costs and per-event costs. With the help of grist I’d like to have a quick way of calulating these.

As I would like to have a simple yet flexible interface I’d like to have the chance to enter not every month yet have the chance to create a list of costs|revenues|working hours which - to me seemed the most logical approach in grist concepts.

So for revenues I’d basically have a table that has following columns, it is mostly a boilerplate for all other things to come, only assets has additional columns for depreciation

  • Project - select from project list
  • Title - custom text describing the revenue type
  • Repeat - linked to helper table that has year|month|day|week-integers and perEvent-toggle, last is actually the most reason why I need an event helper table
  • Start_Date
  • End_Date - to eg. represent different project phases with different offers
  • Months - Linked MultiSelection with Month-Names<>Numbers, should used to enable seasonal offers without having to enter any single year
  • Amount - the price
  • VAT - for future cash-flow

Now I’d like to summarize the amounts with the help of the bold fields. I first thought I could do some python code to do a rather big calculation which I think might break down the developement of additional charts and timelined statistics. So I am thinking of another helper table that lists all regular events with the bold parameters.

So eg. a row with

months=June, July

should create 8 (when date_end=24/01/01 > 16) rows that I can use to calculate all the costs that have the perEvent-Toggle and the other tables accordingly.

I hope I described my idea well enough and am open to different approaches, just not really able to sort this out.

Last not least - even not having finished my first grist-project - I find this an amazing tool and have already recommended other people to look at it. I am well sure that this so powerful I might use this in a commercial context one day and it is amazing that this is an opensource thingy I can try out so easily. Thank you!

1 Like

Ok, I got some things to work with the help of python. Will post the solution as soon as I’m on my office machine again. I wonder if it was possible to create rows and assign as Reference List programmatically?

1 Like

I’d love to see the solution. I’m not yet clear on the details of the use case, but it sounds interesting!

I’m not sure what you mean by “create rows and assign as reference list programmatically.”

In Grist, if you have a dashboard with two widgets that are linked by a reference column, then enter a new record in the referencing table, Grist will automatically fill in the correct reference in the reference column.

You can see an example of this in our lightweight CRM. Grist

  1. Click on any contact in the Contacts table.
  2. Then add a new interaction in the Interactions widget on the bottom right.
  3. Navigate to the “Interactions” page on the left-side menu. You’ll see the new interaction at the bottom of the table. It has been assigned to the correct contact. This makes data entry easier in dashboards.

Ok I’ll try to describe it differently, I understand that I might have described it strangely. Basically the idea is to create a concept to evaluate a variety of projects that might eg. share assets (eg. some tech) or people (two hours for one project, three in the other) and play around with the factors to evaluate business ideas to decisions based on numbers. This way I’d like to see the ROI of any single project or that I need to find another person (or upgrade ones regular hours).

For this I create a project and have according costs, assets, activities tables. There are some things I haven’t really worried about yet (depreciation, cash flow, etc) but for the question I’d like to limit it to costs. Costs appear on a regular base, yet for some ideas I have on my mind these costs are not monthly but appear eg. twice a month during the summer. So I have a table that allows me to express that:

Location cleaning
repeat=every two weeks > linked to another table that expresses “every two weeks” with a column weeks=2
months=june,july > linked to another table that expresses these terms as month-numbers

So how I understood the process I’d now need to generate a helper table that creates rows for every two weeks in june,july for the time between 1st January '22 and 31st December 2030. I’ve already created some python code that delivers me the dates. To be able to work with these I’d need add a row to my helper table that has the date and the according “cost”, now I could summarize all costs for project x in a monthly manner and generate charts.

So I’d like to have a chance to append a row with the help of python and include that rows ID within the ‘cleaning’-row as part of the reference list.

I hope this describes it better?