I am working on a simple stock tracker app where the user can enter their trades and it would show the summary of their position. When the user enters the first transaction of a given stock (Apple) then I need to create a row in a positions table for that stock (Apple) where I am going to summarize all of the trades. Is there a way to do this in glide or I need to use something like Zapier?
If you want to create a related record then I can advise using a custom form, but I want to know more about your data structure here.
Do you have a Users table, a Stocks table, a Trades table and a Positions table? How are the Trades and Positions table related?
@ThinhDinh thanks for the reply.
I have one or more APPL trades in the transactions table:
stock_symbol, type, count, transaction_price, transaction_date, total_cost, current_value
APPL, BUY, 10, $60, 2022-01-07, $600, $700
AAPL, BUY 20, $65, 2022-01-08. $1300, $1400
Then in the positions table the AAPL line would be auto-created when the first AAPL transaction entered and it would be basically sum up your AAPL trades:
stock_symbol, total_count, total_cost, current_value
AAPL, 30, $1900, $2100
I am not sure if this needs an extra table or somehow I can create it from the transactions table though. I am open to all suggestions
Looks like you need to record the user’s email/ID here as well, if you’re having multiple users using the app.
Let’s say it will look like this:
email, stock_symbol, type, count, transaction_price, transaction_date, total_cost, current_value
thinh@example.com, APPL, BUY, 10, $60, 2022-01-07, $600, $700
thinh@example.com, AAPL, BUY, 20, $65, 2022-01-08. $1300, $1400
Then you create a template column in the same sheet joining the email and the stock_symbol. It should look like:
thinh@example.com - APPL.
Then create a multiple relation using that template column to itself, so you get all the transactions from the same email and the same stock.
Finally, you can use 3 sum rollups to calculate the total count, cost and current value.
3 Likes
Thanks for the reply. So it seems like I will not need the Positions table at all because the template string with the thinh@example.com-APPL and the relation will be able to compute the total cost, total count and current value.
I have one last question though. How do I display this data to the user so each stock position is shown only once?
I assume you have a rowID for each transaction.
Then, from the relation I mentioned here.
You add a single value > return first rowID from the relation, you will have the rowID of the first transaction that matches.
Then filter if rowID is first rowID from the single value column above.
1 Like
This is gold! 
Thank you for your help.
1 Like
That is one clean way to have a “unique” list without creating another table 
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.