Can fields reflect information from multiple records?

[michael_frankha]

Hi!
I am trying to figure out how to do two things that are kinda related. and I am getting no where.

if I have for instance.
3 1 8 2
5 4 4 1
3 7 8 2
2 5 14 2
2 9 14 2

where the first two fields are supplied and the second 2 fields are returned.

column 3 Is the sum of column 2 of all the records what share the same column 1 value.

column 4 is the number of records that share the same value in column 1

and the records would have to be updated as new records are added. so if another row was added with a 3 in in the first column. both record 1 and three would be updated.

can this be done in air table?
any help is really appreciated i’m new to all this.
thanks!

[AlliAlosa]

Hey there! Check out this sample base :slightly_smiling_face:

Airtable

Untitled Base - Airtable

Explore the "Untitled Base" base on Airtable.

[Justin_Barrett]

Welcome to the community, Michael! :slightly_smiling_face: You’ve definitely got an interesting problem here.

michael_frankhauser:

column 4 is the number of records that share the same value in column 1

This one can be done with a minor tweak to a solution I came up with a little while ago:

Automatic Sequential Numbering of Non-Sequential Items (READ JUN 9 2021 FOLLOW-UP) Show and tell

Summary This setup allows Airtable to automatically number collections of items that may not be in sequential order. Item length can be either fixed or variable, with only minor changes needed to the core system to accommodate the latter. Example To borrow from the thread that inspired this endeavor, say you’re building product codes that have a variant sub-code, and want that sub-code to automatically increment each time an item of a given product code is added in a new record. However, it n…

In your case, you want the count to be persistent on all relevant records, rather than increasing with each new instance of a given number/item. To do that, you would skip the third step in my bullet-point breakdown of the process, and use this formula in the next step (the {Substitute} field:

SUBSTITUTE({Lookup A} & "", A & "", "")

In my test, I simply named the first two fields A and B, so this formula replaces all instances of the A number with a blank string. Then the fourth column in your example becomes this formula:

LEN({Lookup A} & "") - LEN(Substitute)

Here’s what I’ve got so far with that in place. The {B Sum} field is where your Column 3 solution will eventually go if I can work it out. It’s more complex than Column 4.

While finishing this post, I saw the reply from @AlliAlosa come through. It uses a similar approach for Column 4, and a clever way to get Column 3 figured out via links. However, I get the impression from your original post that the data in Column 1 would be entered directly, not linked. Please let us know if the link solution will work, or if you need another option.

[michael_frankha]

thank you so much! I just got done with work and just got to look at this, I will mess with it tonight and let you know. this makes a lot of sense though.