Skip to main content

It's more complicated than a simple filtered view

  • March 23, 2020
  • 7 replies
  • 47 views
H
Forum|alt.badge.img+4

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?

7 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • March 23, 2020

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.

    H
    Forum|alt.badge.img+4
    • Holli_Younger
    • Author
    • Inspiring
    • March 23, 2020

    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.


    Thank you for responding @kuovonne. Here is a snapshot of the area I am questioning.

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • March 23, 2020

    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.

      H
      Forum|alt.badge.img+4
      • Holli_Younger
      • Author
      • Inspiring
      • March 23, 2020

      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.


      Do you think having the WO#s as a linked record would be better? I am slowly trying to revamp our system for a better project workflow, but with the limitations available it can be a struggle and very difficult process.

      kuovonne
      Forum|alt.badge.img+29
      • kuovonne
      • Brainy
      • March 23, 2020

      Do you think having the WO#s as a linked record would be better? I am slowly trying to revamp our system for a better project workflow, but with the limitations available it can be a struggle and very difficult process.


      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.

        J
        Forum|alt.badge.img+18

        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.


        100% agree

        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.

          H
          Forum|alt.badge.img+4
          • Holli_Younger
          • Author
          • Inspiring
          • March 23, 2020

          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.

          Terms & ConditionsAccessibility statement