monthly projection table creation
Hi, I'm wondering the best approach to having entries in a table called "Billing Projections" created when a project is created under our project management (Projects have many Billing Projections)...
Forum Discussion
Andrew,
Quick Base is outstanding at managing workflows and process management. Creating arrays of information however is not one of its strong suits. I have had to solve this problem of needing to generate multiple child records for a single parent on numerous occasions. The core business cases tend to be around money. Establishing milestones for progress billing or paying out commissions on sales.
For an array of future payments I am using a custom PHP script that kicks off from a Formula URL button. This way you can pick a start date and interval for your array. The PHP calculates the values for all the child records and then uses the Quick Base API to write them to the app.
Alternatively you can create Formula Date fields in your Projects table and then have a series of Automations kick off and create the child records. This will eliminate the need to write any code, but it is not as elegant and harder to maintain.
Happy to discuss further if you would like.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
Quick Base is outstanding at managing workflows and process management. Creating arrays of information however is not one of its strong suits. I have had to solve this problem of needing to generate multiple child records for a single parent on numerous occasions. The core business cases tend to be around money. Establishing milestones for progress billing or paying out commissions on sales.
For an array of future payments I am using a custom PHP script that kicks off from a Formula URL button. This way you can pick a start date and interval for your array. The PHP calculates the values for all the child records and then uses the Quick Base API to write them to the app.
Alternatively you can create Formula Date fields in your Projects table and then have a series of Automations kick off and create the child records. This will eliminate the need to write any code, but it is not as elegant and harder to maintain.
Happy to discuss further if you would like.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
- This is what I have been trying to figure out using QB native functionality. Given the automations in place, I may give a go with some thoughts on this either copy records or import record from different tables. I will come back to you if that works.
------------------------------
Babi Panjikar
------------------------------ - Up to the original OP but unless he has equal payments the array or equal forecast wont be right. It would need to compare open billing / months left each month. That is why I went manual entry. My team could forecast with tighter accuracy. They just need to record the information.
------------------------------
Ivan Weiss
------------------------------ - Don,
I wound up having similar circumstances and agree completely. Growing into QB as a technology person used to writing code, I've tried to go native when all possible. With things like this, I wound up agreeing. About 30 lines in Python and I have, most likely, an identical solution to you. We project out 12 months by default, to which a PM can leave blank, if needed. The formula would work but for projects that exceeded said default duration, would get messy, as you said. Likewise, the dates that get brought over from Sales are pure estimates (guesses much of the time). The scope of the work can take 6 months to start. Most projects are completed within 12 months but with our complicated AIA billings, a number do last longer.
Thanks,
Andrew
------------------------------
Andrew Ryder
------------------------------
