AndrewRyder
Qrew Cadet
Joined 6 years ago
User Profile
User Widgets
Contributions
Pipeline for Sales Forecasting and Projections
Hi, I was dabbling around in Pipelines, trying to setup an automation that will do some forecasting for future quarters. Sales has Proposals and Sales Goals. Proposals have a text field with start and end quarter, in the format YYYYQQ (202101, etc.). Both have to be 'current' and start cannot be < end. Some fancy formulas keep all this working and Sales happy. In a hidden formula field, I do a multi-text/text that calculates out the "span" for work. This is used for production capacity planning, along with our Sales team knowing what quarters are lacking in the future. Ex: Start is 202102 and end is 202201. My "Quarter Span" field has the values 202102 ; 202103 ; 202104 ; 202201 inside of it. Then, another field takes the Proposal Grand Total and splits based on the number of quarters. Billings aren't always equal but this gives us valuable insight as to what can be sold/when and when things are lacking. Sales Goals has a field with YYYYQQ. I need to do projections based on the TOTAL of these "Projected Amounts" that are divided up based on what I mentioned above. So, if I'm projecting for 202201 and there are 50 proposals that are not Lost, I add all these amounts together and get a sum. We'll also be using an identical method, since each line item has estimates per department, for capacity and workforce planning. I have a table report that partially represents this but I need to put this into a "summary" table. I can easily write this in Python and have it triggered via a nightly cron/Automation/webhook, but wanted to give this a shot in Pipelines. Is there "addition" or "math" in Pipelines that can be performed? The Bucket Channel is, unfortunately, vague as to documentation. And, I didn't see any tutorials doing something like this. I figured the steps would go something like this: Fetch CSV -> report of these proposals in the pipeline. Quickbase->Query Sales Goals and get all YYYYQQ. Iterate over each line of CSV. If Quarter Span (the field with all quarters covered between Start/End) contains the YYYYQQ variable, grab the Projected Amount and add into a running total. Once done iterating over all lines in the CSV, take only the sum for the YYYYQQ and inject back into Sales Goals. Run nightly or every other night to keep the total fresh. Likewise, I am not sure how to best iterate, without also tearing up my step count. Trying this out yesterday, I tore through 1000 steps without knowing it. Thanks! ------------------------------ Andrew Ryder ------------------------------Re: Upcoming Changes to JavaScript in Quickbase
@Evan Martinez - I'd counter that at least allowing would allow us to figure some stuff out, with an option to turn it on. That's part of the job/game (fun for me, too). Could this possibly be considered? I'd rather be able to try and get something and get small stuff, like a simple JS refresh. It's easy enough to debug and I don't think folks like me would care if, ultimately, future QB changes wouldn't allow for something. I wish you could put up a poll or something like that since a number of us really like the ability to at least try.âRe: conditional dropdown
Blank. I thought about it more, and the problem is that when the Production Task is created, there are no "Related" PT Chains created, even though Grid Edit is allowing me to enter some. It's a chicken-egg thing. Unless I'm overthinking this? If I could use Dynamic Form Rules to populate something on PT Chains (displayed via Grid Edit), that would work. I don't think you can, though. I created an Automation that when a Prod Task is created, it created 10 blank PT Chains. This works. There's then 10 entries where someone can associate a PT to a ST. It's messy but I cannot think of any other way. A nightly Cron can clean up the "blank" entries to limit the junk. It makes sense but I wish QB would allow for a non-IOL solution here. I couldn't think how to do this with Pipelines, either. Unless there is some way to use a multi-select here? I tried that before but that didn't work. I was trying to do a summary combined text into one using some Formulas, but its not limited to the scope of the specific project, so all Sign Types were showing. Any other ideas? ------------------------------ Andrew Ryder ------------------------------conditional dropdown
Hi, I'm trying to utilize conditional dropdowns and running into an issue that I can't figure out. Here's the relationships: Project Management App has Projects. Each Project has many Sign Types (think of these as Products associated with a Project). Production App syncs Projects. Each Project (sync table) has many Production Tasks. Production Managers Task via Production Tasks. They click on a button on the Project sync table in Production and it associates the Production Task with the Project (in the sync table) but I lookup the Record ID#2 (the original in PM app). Nothing unusual here. What we're looking to do is gain further granularity into how long each Production Task associated with a specific Product (each Project has anywhere from 1 to sometimes 100). So, I created a join table "PT Chains" in Productions to help with this many-to-many relationship. Production Tasks -> PT Chains <- Products. Utilized lookups to bring the related "project" from Products and Production Tasks has the "Record ID#2" which is the same ID in the Production App. I've tried to use Grid Edit and directly adding a new record into "PT Chains" but inside that table, if I enable "Conditional Values" and try to line-up the Record ID#2 and "Related Project" from Sign Types (first attachment) but it seems like this is entering into a circular relationship, where I think it should work. Production Manager creates a Production Task off of the related project. The "Add PT" is a normal Formula URL with the associated fields already filled, so it knows of the Record ID #2 field, which is the record id of the project. Then, as they're creating the task, they should be able to "add in" via grid edit, the specific sign types (products) that the task is related to, since you could be working on more than one, and there's thousands of products, so I'm trying to limit the selection to ONLY the sign types related to that specific project. I'd think this works but it doesn'tâ. It's like a chicken-and-egg or something isn't working right. I can't select any STs (products) because PT Chains (join table) seems to not be inheriting the associated lookup field from the task. Any clues? Thanks! ------------------------------ Andrew Ryder ------------------------------Re: Automation selecting option in Multi-Select Text
Mark, That worked. I was trying something and was close but nice to see it didn't take anything complex. I'll post my screenshots to hopefully help others that wind up in this situation. I needed to create a "cheater" or "dummy" field that was a Formula - Text with that option appended. Then, I just copy via the automation. ------------------------------ Andrew Ryder ------------------------------Automation selecting option in Multi-Select Text
Hi, Wondering if an automation can select an additional option, when triggered (modified)? Ex: Multi-select field has choices A B C D E - A and C are selected in Record - Action occurs that triggers Automation and also selects "E" when Automation conditions are met Now that record has A, C and E selected. I'm not seeing this ability in automations and can't think of a way without an API call or Pipelines. Thanks ------------------------------ Andrew Ryder ------------------------------Re: monthly projection table creation
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 ------------------------------Re: monthly projection table creation
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. 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 ------------------------------Re: monthly projection table creation
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 ------------------------------
