Creating Records from Fields (where more than one field is linked to same record in source)

[JonahM]

Hi all

Here is the challenge I need to solve -

I track some small time investments for an investor club in companies.
i.e. records are the company names (Apple, Netflix, etc.)
Fields are James $ Invested and Adam $ Invested
James invested $100 in Apple
Adam invested $50 in Apple and Adam also invested $50 in Netflix

I have a formula field that sums up the total amount invested in each company (e.g. Apple is $150 ($100+$50) and Netflix is just $50.

What I would also like to do is link this to another table and have James and Adam as the Records and then show the total amount they each invested in aggregate (i.e. fields would be Total Invested, and for James it would be $100 and for Adam it would be $100)

I saw this link below but it does not work for me since I have more than one field (James and Adam) linked to the same record (Apple)

Airtable Support

Find the sum of an entire field

A core difference between Airtable and a traditional spreadsheet is that Airtable is more than just a spreadsheet - it's also a database. At times, that creates the need for a different approach th...

I would be so grateful if someone has a solution for me

Thanks!!

[Mohamed_Swella1]

Hey @JonahM ,

Welcome Airtable Community !

The way to do so is exactly like you described it. Have another table with the names, you shouldn’t have different fields for every name. Instead. you can link that to a 3rd table, and in this 3rd table you link the company and the name and the amount.

Let me know if this helps.

Best Regards,
Mohamed Swellam

[JonahM]

thanks Mohamed!
I don’t exactly follow though.
First table will be Apple and Netflix in records. What will the fields be there?
What should be in table 2 and 3?

[Mohamed_Swella1]

The first table is Netflix and Apple

2nd table is Investors, technically just a name (or you can add the info you need of course).

3rd table is Investments, this will be linked to the first table and the 2nd table.

Here is a draft of the tables.

Airtable

Airtable | Everyone's app platform

Airtable is a low-code platform for building collaborative apps. Customize your workflow, collaborate, and achieve ambitious outcomes. Get started for free.

this 3rd table is what we call a Line Items table.

[JonahM]

wow - thanks!!!
really, really appreciate it!

[Russell_Findlay · Accepted solution]

@JonahM - the advice given is exactly what you need to do … possibly a transactions table so every new purchase or sale of a stock is recorded and linked to 1) the stock and 2) the investor.

You will then find it easier to mark date, stock price etc and therefore to understand the portfolio value etc with formalae.

So record 1 could be

Name: Transaction 1
Investor : linked record to Investor 1
Stock: linked record field to stock 1 (eg apple)
Transaction type: buy / sell
Price : currency field
Volume : number field

The price will be a static field when the transaction was processed

You will then have stocks which can have a life price field (possibly pulled in via an API) and linked to every transaction that is included

You will also have individuals with the data on them - inc email

Because you have linked fields you will be able to see the quantity of each stock held by each person and the purchase cost / sale price, current stock, current value , profit / loss etc.

[JonahM]

super, got it now Russel, thanks!!
your explanation is really clear

[Hannah_Wiginton]

The term for what you need is a Junction table as the others have mentioned

Here’s where Airtable explains more about how and when they’re used:

Airtable Support

Airtable's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...