Creating records in a related table

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, APPL, BUY, 10, $60, 2022-01-07, $600, $700, 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: - 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.


Thanks for the reply. So it seems like I will not need the Positions table at all because the template string with the 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! :slight_smile: Thank you for your help.

1 Like

That is one clean way to have a “unique” list without creating another table :wink:

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.