Counting records in a view that match a certain criteria

[Curation_Juniqe]

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!

[Curation_Juniqe]

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.

[W_Vann_Hall]

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.

[Curation_Juniqe]

Thanks a ton for this. I am already quite short on hair, so this is a relief to know :winking_face:
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!

[Curation_Juniqe]

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.

[W_Vann_Hall]

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].

[Elias_Gomez_Sai]

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

[Curation_Juniqe]

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???

[Elias_Gomez_Sai]

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.

Curation_Juniqe:

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

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.

[Curation_Juniqe]

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.