Forum Discussion

AndrewRyder's avatar
AndrewRyder
Qrew Cadet
5 years ago

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

Thanks!

------------------------------
Andrew Ryder
------------------------------
Quickbase Automations
Tables and fields

10 Replies

Sort By
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    5 years ago

    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
    ------------------------------
    • AndrewRyder's avatar
      AndrewRyder
      Qrew Cadet
      5 years ago
      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
      ------------------------------
      • IvanWeiss's avatar
        IvanWeiss
        Qrew Captain
        5 years ago
        Andrew,

        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
        ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    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
    ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Commander
    5 years ago
    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
    ------------------------------
    • BabiPanjikar's avatar
      BabiPanjikar
      Qrew Assistant Captain
      5 years ago
      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
      ------------------------------
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      5 years ago
      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
      ------------------------------
    • AndrewRyder's avatar
      AndrewRyder
      Qrew Cadet
      5 years ago
      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
      ------------------------------