Counting records in a view that match a certain criteria

#1

I am using airtable for a somewhat unlikely use case, namely as a tool to visually merchandise catalog pages on a webshop.

Basically, I am using a table called SHOP in the catalog view to drag-and-drop images directly from our webshop onto a record / attachment field and am using a formula to extract our product code from the attachment field, as the attachment name literally holds the unique product identifier.
Each product code is consisting of a prefix and a code, indicating supplier and product and I have both split as two separate fields by formula.

First, I would need to establish how many times the code prefix field and the code field are listed in a view, in google sheets a basic COUNTIF Formula, which I understand is not as basic in AirTable. The product code field is actually linked, but the prefix field at the moment, is not.

Additionally, I am pulling data from another table based calles SALES on the code, such as sales figures, engagement data, etc, which is being pulled via a lookup field onto the main table.
Currently, this requires me to manually duplicate the entire column that holds the product code and paste it into the linked field column to establish the relation to the sales data records in the SALES table. This does work but is a bit cumbersome, is there any way to dynamically link a formula-calculated field to a related record in another table?

Any help would be greatly appreciated!

#2

note that the issue is that we have over 20k records (and growing) in the SALES table, and a ever-changing number of user-entered records in the SHOP table, so the solution would need to be scalable and not rely on too much manual labor.

There also is a multitude of different SHOP tables with identical setup in terms of the field structure and requirements, but different records populating each table.

All the records in the SHOP table however do have a matching record in SALES.

#3

Unfortunately — or fortunately, since you evidently discovered the trick early enough to have made it to 20k+ records without [presumably] tearing your hair out — your copy-and-paste of an entire column is probably the least manually intensive method of accomplishing these tasks. Since that is essentially a 2-click, 2-keystroke process (select the source column header, press Ctrl-C, select the destination column header, and press Ctrl-V), it seems about as low-impact as possible — bar a fully automated solution, which isn’t currently available.

Since {Product Code} is already a linked record, in your [Product Code] table a simple count field or a rollup using COUNTA() will give you a count of product codes. To count prefixes, you’ll have to do the column copy-and-paste into a linked-record field pointing to a [Prefix] table. From there, use a count or rollup field to sum.

As far as I know, there is currently no way in ‘pure’ Airtable to create the link to [Sales] dynamically. It is possible using an integration service (Zapier, Integromat, or the like), but you’re essentially doing the same copy/paste on a record-by-record basis you currently do for the entire table at once.

1 Like
#4

Thanks a ton for this. I am already quite short on hair, so this is a relief to know :wink:
I will give the COUNTA another try, i think i may have attempted counting the non-linked/computed field, if I understand you correctly, this won’t do the trick.
Thanks, I will hopefully figure this out on my own or else post back in here!

#5

Sorry if this is a stupid question, but to set up the Counter for the Product Code, I am still struggling to understand how this is meant to be…

I am attaching a screenshot, as you can see the counter shows the value ‘1’ for both the design that’s there once but also the design that’s in that table twice, so it counts but I am not sure what it counts.

Is there any hint you can give me - I literally would need that field to count all the occurances of the linked field in the current table and view.

#6

To count the number of instances of a Design ID, you’d go to the Design ID table and run COUNTA() against the links back to [#New].

1 Like
#7

I don’t see any Degings table (I was to suggest that). Also, you could just use the Count field type.

#8

Thanks, that’s working, I now included a lookup field on the main table to run the counter from the design id table back onto the main table.

The only problem with that is that we keep adding “curation tables” quite frequently, resulting in the need of someone (me) creating and setting up these fields each time a new table is created

Can you think of a work around for that?
Also, where are you located, can I send you a thank-you on behalf of JUNIQE???

#9

Oh I see, there is a field called Design ID (but I don’t know to what table it links haha). Yes, you add a Count field on that linked table, and later you add a Lookup to get that number back to #New.

What are those tables? Could you have all of them in just one table? I see several ‘Collections’ table, and I think you maybe should have them together, with a Single Select field for the topic (Female Icons, Boho, and so on), so you could them filter it, group, create views, etc. But you’d need just 1 table.

#10

Well, these tables are visual curations for an online art shop - later we export this view/design selection with sorting as an .csv file with all the design codes, which is then being uploaded and turned into a curated shop like this https://www.juniqe.com/wall-art/curators-picks

So i do see the benefit of a single-table set up with the shops being represented by views and toggled fields - that’s also somewhat the setup of the entire 20k webshop catalog and it’s smaller curated shops.

Would that significantly ease things up though in regards of the counter??
I’d still need to configure it to conditionally count the instances of the design id in the same table, but according to conditional rules that would need to be set up?

-> the counter is so important because we need to ensure not to double designs and represent artist and aesthetics in some balanced way.

#11

Hi Elias,
I’ve just been exploring this option a little and while it does have it’s advantages, I am also seeing some issues with that operationally. Hear me out, and please change my mind if you can :slight_smile:

I am now using a rollup field with arrayjoin on the curation table to pull the linked records.
Since they’re the same value, it doesn’t let me count them directly but I have created a separate field counting the commas in the rollup, which gives me the amount of (identical) values in there.

  1. So for one, the manual steps would still be there when setting up a new curation via a view/single-select field - i’d need to duplicate the view instead of the table but also configure the counter to listen to a new condition (the single select field, like Female Icons, Boho - btw kudos on your understanding of our customers) and have users create a new value for the single select field
    This seems a little more error-prone than just letting user pick another linked field when duplicating their table

  2. We will inevitably end up creating multiple instances of the same record in the main table - certain bestsellers will be featured in numerous collections and curations, because the process of picking them is quite intuitive and spontaneous and involves a lot of different options being tried out, I would oftentimes see a scenario where people enter a new record that’s already in that table but hidden in this new view, and will need to be featured in different positions in different views.
    This could mean that we could either use multiple select fields, which would still mean running risks described in point 1) but also, in the way we curate these shops, it would be a lot easier just dragging another shop image onto a fresh record, hence creating a dupe - which would be easy when working with multiple tables but is a bit harder when talking about views.

#12

I have an idea as how to handle curation tables — but it’s pretty much orthogonal to traditional Airtable development, and whether it is suitable depends heavily upon your work flow. (It’s a byproduct of some work I did for a client about six months ago. It wasn’t quite right for him — his use of Airtable had been dictated to him by a client, so they could commingle bases, and any gains in efficiency on his end would have been lost in the additional work needed to pound the data back into a shape his client expected — and ever since I’ve been stubbornly looking for a problem for this solution.)

Unfortunately, I’m currently even harder to get hold of than usual, as at some point over the next few days I’ll be relocating from the West Coast to the East, for a period of somewhere between one month and the rest of my life. (Presumably those aren’t synonymous.) Before I fly off, I’ll try to send you a PM here describing the approach; if it sounds feasible, we can discuss it in more detail when I resurface, which I hope will be later next week…