Can fields reflect information from multiple records?

#1

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!

#2

Hey there! Check out this sample base :slight_smile:

1 Like
#3

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

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

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.

03%20AM

While finishing this post, I saw the reply from @Neads_Admin 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.

1 Like
#4

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.