Use Lookup Tool Within A Specific View Only

[Chais_Meyer]

I’m trying to use a lookup function to help me avoid running a zap within zapier. But it seems I’m limited to do a lookup (count sum function) within a specific table, not within a specific view within that table.

Here’s the deal…
I currently keep track of my customers that have requested quotes from our shirt shop (but don’t have an actual order going yet) in a specific view within my ‘order’ table. When they convert their quote into an actual order, we just change the ‘order status’ within that specific record.

In order to help each quote customer remember to reply to us (if they want to move forward or not), we automatically send them a ‘1 week reminder message’ and if they still don’t reply a ‘2 week reminder message’ - These messages are zaps running within zapier. These reminder messages are great and help us convert a lot of customers into actual orders, but sometimes we have an issue where certain customers call, email or physically come into our shop to start their order, versus going through our suggested route of converting their quote into an order. The problem that arises from them starting a new order while also having a quote with us is, even though they now have an active order record with us (displayed in our ‘all active orders’ view), they also still have an active quote record with us (displayed in our ‘all quotes’ view). Because they have two records for the same order, specifically the one in the quote view, we don’t have a way to check for conflicting records automatically and their ‘1 week’ and ‘2 week’ quote reminder messages will still be sent out (which is awkward when they have an active order at the same time).

If I could do a ‘lookup’ function, and have it be limited to a specific view, I could do a lookup within our ‘all active orders’ view and have it count how many records exist, displaying a number. If this field displayed a number greater than 0, I could have our ‘quote’ order status records (that also have a number greater than 1 in the look field) show up in their own view. This view would then trigger an integration within zapier to auto-create a todo for our team to manually look into this, verifying if we need to delete the quote record or to see if it is, in fact, a unique quote.

---

So, does this feature already exist and I just can’t find it?
If not, I’d love to add it as a feature request. I can think of a dozen uses for this functionality.

Thanks again!

[Elias_Gomez_Sai]

Some ideas:

• Could simply ask the Customer if s/he has a quote? Or even just the name to check if they have quotes?
• Also, your could count how many Quotes a Customer has, and show an emoji when creating a new one if there is an existing quote. Check this thread: Counting Records that Meet a Criterion

A Lookup works on a Record and a Field, not on Table/View. Just can’t limit it because a View is just a way to change what you see.

[Chais_Meyer]

Thanks for the reply @Elias_Gomez_Sainz!

Yes, Idea #1 (asking the customer if they have a quote) is a great idea in theory. :slightly_smiling_face: This doesn’t take into account human error (staff forgetting to ask). This is why we use automation (like this request), to fix as many issues as we can, specifically related to human error.

For your Idea #2, we can’t actually count how many quotes someone has as ‘quotes’ are in the same table as ‘orders’ - they’re just separated by a different view (based on an ‘order status’).

Yes, I agree that I’ve found a lookup to work that way as well, but I’d love for the code to be modified so that it could allow Airtable users to do a lookup within a specific view (only) so that my specific use case could be performed with ease. If zapier allows me to trigger off of a new record in a specific view, meaning that within my account’s code each view is broken up within it’s own subset, then I would assume I should be able to do something like a lookup within a view as well (if airtable’s code allowed for it).

If not currently possible (which it doesn’t appear so), I’d love for this to be considered a feature request.

Thanks for giving some suggestions.

[Elias_Gomez_Sai]

Chais_Meyer:

we can’t actually count how many quotes someone has

Yes, you can. Did you check the link I posted? There is the trick.

Here you have: https://www.youtube.com/watch?v=RJmxBlbB2xA

[Chais_Meyer]

Hey there @Elias_Gomez_Sainz, I owe you an apology!

First off, I did not watch the video you made for me the first time. Secondly, it took me forever to reply back.

I JUST watched your video and I’m grateful for your creative use of formulas, this helped me solve my issue!

Thanks again for your help, I very much appreciate it!
-Chais

[Elias_Gomez_Sai]

No problem! Happy to help :slightly_smiling_face: