One of my workflow views is filtered to show jobs that have received a quote, telling me to process. However, some jobs have multiple quotes coming in from different vendors. If I filter to show the status “waiting on quote” and “received quote” I end up multiple waiting on statuses bogging down the view needed in order to process.
Each project has a job number so one job number could have been assigned to multiple vendors, all required to submit a quote. If the quotes I am waiting on do not show up somehow in the received quote filtered view they could be submitted without receiving all the data.
Is there a formula I can use to tell us “2 of 5 have been submitted” for instance?
How are your quotes stored? A screen shot would be helpful.
If each quote is a record in a linked field for the project, you can use a
COUNTALL rollup to determine the total number of quotes, and a conditional rollup to count the number of submitted/received quotes. Then you can use another formula field to compare the two numbers.
Thanks for the screen shot. It really helps to understand your situation.
It looks like you are grouping records by WO#, where WO# is the job number. It looks like each row represents a single quote, and each quote has its own status.
Notice that the WO# is simply a value that is shared by multiple records, but there is no single record that is responsible for the WO# as a whole. Thus, the method I described in my previous reply would require changes to your base design in order to work.
As the WO# does not have a unique record of its own in a different table, there are limited things that you can do, as filters are based on individual records, not groups. One thing you can try is to edit the summary bar for the “Status: Quote” field so that it shows how many unique values exist for the group. However, that will have very limited usefulness. Sorry.
Yes, I think having a new table with one WO# per record would be better. Then have each WO# have a linked records field that links to all of the related quotes.
I recommend making a copy of your base and testing out the design on the copy.
It can mean a lot more work if you have to manually redo a lot of linked records, lookups, formulas, etc. in your original base… but peace of mind that you aren’t ruining everything in your original base while you tinker is worth the extra effort.
Thank you both! I am trying to get away from re-creating the bases everytime a new scenario comes up. I do have a duplicate base that I test run a some ideas and suggestions I’ve learned over the year with Airtable. Hopefully I will be able to implement an entire base with all upgrades soon.
I ran into this issue today - on the base in question and now I’m scared after a little research there is no way to prevent this in airtable. Do you have any experience with views? A collaborator created a personal view and has opened up all sensitive info that was once hidden.