Report Filter by Current Job

Kadie Butterfield

Hi All:

I have been working furiously for the last few months completely redesigning how we use Smartsheet since we purchased Control Centre. The automation is great but I have come across a load of roadblocks trying to manipulate Smartsheet and Control Centre to do what I need it to do.

With that being said, I am looking for any solution for the following issue:

We have a production order list for each job, because I can't have webforms automatically generate new links for each provisioned job through Control Centre I've had to make this production order list one sheet that everyone will use their webform link to add products for their jobs (the link I provide on everyone's dashboard is generated with a formula and a URL query string so that no one has to manually select which job the product is for).

Here is my dilemma, when a new job is provisioned I have a Production Order List report that looks to this one sheet but I can't have it automatically filter based on the job for that provisioned project.

Any ideas on how I can have this automatically look for the job without having to manually go into each and every provisioned job and manually select the job as a filter would be AMAZING!

C'mon Smartsheet Community! Don't let me down!! 😃

Brian_Richardson

One simple, but slightly manual, way to approach this would be to have CC provision a "This Job's Products" report as part of the job's template set. That report would use the consolidated Production Order List as a source, filtered for the job. You would need to manually adjust that filter 1 time once the job is provisioned. Then that report can be used for any given job to show the related products.

Or, you can adjust the process a little bit. If you have a Production Order sheet for each job as a template that's provisioned by CC, and have the submission form on that sheet, then each job will have a unique form URL. You would need to quickly copy/paste that into some kind of reference sheet of links so people would know which form to use for which job, but then you'd have your orders submitted into the correct job sheet and visible for that job with no further filtering needed. If you wanted to see everything across jobs, that's a simple report.

Kadie Butterfield

Hi @Brian_Richardson,

Thank you for taking the time to reach out! Unfortunately what you've described in your first paragraph is exactly what I am doing at this time. I was hoping for a way to do this without manually having to adjust the report for each provisioned project.

The second option you provided unfortunately would be even more work as I have multiple webforms for the one sheet that are needed depending on the type of order being placed. (Multiple formulas running in hidden rows help make the information look uniform across the order sheet but serve different departments needs behind the scenes).

Thank you again for your feedback!

Brian_Richardson

My usual approach would be to work with Bridge to automate the process. However, that won't help you here because the Smartsheet API does not have any methods to adjust report filters or do anything with forms.

So I'm afraid you're out of luck with this one. The best you can do is reduce the process to as few manual steps as possible, which you've already done.

Would it be possible to collapse your forms into 1 to reduce the workload and take the second option I describe? You can show/hide sections on a form based on the values in other fields, so you could potentially just have someone select the type of order at the top and expose the appropriate fields for that order.

Both options still involve a manual step though, whether you're copying a form URL somewhere or you're adjusting a report filter. So it's up to you to decide which you think is more painful.