monthly projection table creation

Question

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). I could think of some Javascript and APIs, but wondering if an automation is possible? The goal is that PMs fill out billing projections once a project gets going so that we can get an idea of projected billings for that month across all active projects.What I'd like to do is automatically create entries 6 months out but base it on a specific date field. Pseudo-code wise:when project is created:&nbsp; StartMonth = ToMonth(Project Start Date)&nbsp; EndMonth = StartMonth+Months(6)Iterate over this to create. Obviously, this could span a calendar year.Example: Current Month is October 2019. I'd want table entries in Billing Projections:Oct 2019Nov 2019Dec 2019Jan 2020Feb 2020Mar 2020Would an automation handle this? Would it be easier to make a URL Button with Javascript?PMs could then add or delete months based on this, as every project is different.&nbsp;&nbsp;Also, is there an ideal structure for Billing Projections to handle this? I currently use dropdowns for both months (has all 12) and year I defined 2019 through 2023.&nbsp;Thanks!------------------------------Andrew Ryder------------------------------

ivanweiss · Answer

Good morning, I have something similar in my app. We made a billing feature so I have table labeled invoices. My project coordinators enter the dates of invoices to go out and amount and it shows what is left to bill. Each Monday my Project Accountant gets a list of what to invoice. The benefit of that is the billing is automated as long as amounts and dates are kept up to date.&nbsp;
i can use that for a monthly projection and I color code it via formula for invoices that were submitted so I know what went out.&nbsp;
i also enter paid information etc so my sales team can monitor without needing access to the accounting software.&nbsp;------------------------------Ivan Weiss------------------------------

andrewryder · Answer

Ivan,Do you automatically create placeholders for future invoices by month when a project is created? That's what I'm trying to do. I'd like to placeholder six months out, even though every project might be different.The projections are purely that. What we bill for is another thing and quite complex, due to the range of contracts (AIA for example). The intent of where this is going is to match these up in a report for KPI purposes and budgetary. Currently, this is all done in a spreadsheet during the project management process.------------------------------Andrew Ryder------------------------------

markshnier__you · Answer

If you just want to create 6 records "once" then you can set up an Automation to do this. For any values that you need such as the date fields, you would have fields on your project record to calculate those values. That way you can have those fields available to you when writing the 6 steps of the Automation.

You will need to give some thought as to what you want to use for the Trigger of the Automation.

It was not clear to me form your post if you need to auto create addition rolling months forward as time progresses.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------

ivanweiss · Answer

Andrew,Yes we do create placeholders.&nbsp; We have a few tables and here is the structure:Projects has Many OpportunitiesOpportunities has many ordersOrders has many InvoicesBy doing this (since it sounds like we are both in the construction world based on your AIA comment) I can capture change orders etc as trackable opportunities and separate orders.&nbsp; I can pass all that data up to the root project.Our Project Coordinators create the "expected" billing schedule and update as we go.&nbsp; That allows us to have somewhat accurate projections.&nbsp; A bit too early to test though as we joined Quick Base 4 months ago and just launched the app last week.&nbsp; It was in development up to this point.I thought about going with a target opening date and dividing the amount over several months leading up to that via formula.&nbsp; But since it is schedule of values that would never be accurate.------------------------------Ivan Weiss------------------------------

andrewryder · Answer

Ivan,We are a custom fabricator, so often we have AIAs that come from GCs. Other projects are directly with the client, which is a lot easier when it comes to legal and billing. I'm sure we've both had fun trying to deal with the complexities that AIAs can have. My job at the company is my first exposure to them and when I work with our head of billing on structure, I get popsicle headaches. However, our more experienced PMs are aware of this and should be able to semi-accurately project out for budgeting.&nbsp;We're also new to QB. I've been designing for the past few months around our existing system w/many, many improvements and needed features, which QB has allowed. I'm not sure if I'll be able to tackle all the AIA complexities. It sounds like we're in similar boats on all of this.&nbsp;Do you have plans to integrate one of the QuickBooks plugins? We use desktop QB, so my intent was to design Billing around the needed structure after looking at the few plugins out there.------------------------------Andrew Ryder------------------------------

Forum Discussion

monthly projection table creation

Related Content

Project Names/ #s

Document Template/Creation (PDF)

Monthly Projected Revenue

Document creation - Save PDF to field in record

Document Creation - Report Totals