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
date_start=22/01/01
date_end=23/01/01
repeat=weekly
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!
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?
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
Click on any contact in the Contacts table.
Then add a new interaction in the Interactions widget on the bottom right.
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
start_date=2022-01-01
end_date=2030-12-31
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.