Expand all | Collapse all

monthly projection table creation

  • 1.  monthly projection table creation

    Posted 10-26-2019 13:58

    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:
      StartMonth = ToMonth(Project Start Date)
      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 2019
    Nov 2019
    Dec 2019
    Jan 2020
    Feb 2020
    Mar 2020

    Would 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.
    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. 


    Andrew Ryder

  • 2.  RE: monthly projection table creation

    Posted 10-27-2019 06:18

    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. 

    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. 

    i also enter paid information etc so my sales team can monitor without needing access to the accounting software. 

    Ivan Weiss

  • 3.  RE: monthly projection table creation

    Posted 10-27-2019 08:36

    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

  • 4.  RE: monthly projection table creation

    Posted 10-27-2019 20:32

    Yes we do create placeholders.  We have a few tables and here is the structure:

    Projects has Many Opportunities
    Opportunities has many orders
    Orders has many Invoices

    By 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.  I can pass all that data up to the root project.

    Our Project Coordinators create the "expected" billing schedule and update as we go.  That allows us to have somewhat accurate projections.  A bit too early to test though as we joined Quick Base 4 months ago and just launched the app last week.  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.  But since it is schedule of values that would never be accurate.

    Ivan Weiss

  • 5.  RE: monthly projection table creation

    Posted 10-28-2019 07:45

    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. 

    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. 

    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

  • 6.  RE: monthly projection table creation

    Posted 10-28-2019 09:46
    I would love to integrate with Quick Books.  However, I have heard it is not the best vs the online version.  We sync our travel expense software to Quick Books and it is a nightmare.  It goes down every other day etc.
    I am actually hoping I can build enough functionality over time into the Quick Base app where perhaps we can just go to the online version of Quick Books and eliminate the desktop version.  If we get the reporting and data into Quick Base I really just need the pure accounting functions.
    But that is prob Phase 3 or 4 of this project lol.  It has been a beast to build it.  This was a self created position on top of all of my daily duties.  But as a labor of love it is really exciting how it is comng together.

    And since it launched last week I have not seen my picture on a dart board so that is good :)

    Ivan Weiss

  • 7.  RE: monthly projection table creation

    Posted 10-27-2019 15:41
    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

  • 8.  RE: monthly projection table creation

    Posted 10-28-2019 23:18

    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
    Westlake OH

  • 9.  RE: monthly projection table creation

    Posted 10-28-2019 23:52
    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

  • 10.  RE: monthly projection table creation

    Posted 10-29-2019 07:05
    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

  • 11.  RE: monthly projection table creation

    Posted 11-14-2019 14:16

    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.


    Andrew Ryder