Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 28, 2022 08:03 AM
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!!
Solved! Go to Solution.
Jul 28, 2022 09:33 AM
@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.
Jul 28, 2022 08:15 AM
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
Jul 28, 2022 08:24 AM
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?
Jul 28, 2022 09:26 AM
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.
Jul 28, 2022 09:30 AM
wow - thanks!!!
really, really appreciate it!
Jul 28, 2022 09:33 AM
@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.
Jul 28, 2022 10:32 AM
super, got it now Russel, thanks!!
your explanation is really clear
Jul 28, 2022 01:30 PM
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: