Skip to main content
Solved

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

  • July 28, 2022
  • 7 replies
  • 96 views
J
Forum|alt.badge.img+4

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)

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

Thanks!!

Best answer by Russell_Findlay

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.

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


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

7 replies

Mohamed_Swella1
Forum|alt.badge.img+17

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

J
Forum|alt.badge.img+4
  • JonahM
  • Author
  • Known Participant
  • July 28, 2022

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


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
Forum|alt.badge.img+17

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?


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.

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

Hannah_Wiginton
J
Forum|alt.badge.img+4
  • JonahM
  • Author
  • Known Participant
  • July 28, 2022

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.

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


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

R
Forum|alt.badge.img+16

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.

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


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

Hannah_Wiginton
J
J
Forum|alt.badge.img+4
  • JonahM
  • Author
  • Known Participant
  • July 28, 2022

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


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

Hannah_Wiginton
Forum|alt.badge.img+19

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:

---Hannah - On2Air.com - Automated Backups for Airtable
Terms & ConditionsAccessibility statement