Hello! I’m so happy to have found Glide! I love the program and all the help I’ve found here in the community. I’ve been trying to build this app but I think I’m getting tripped up with structuring the database setup. And before I get too far into the build, I want to make sure my foundation (database) is solid. Here’s what I’d like to do:
- The app is a stock/options tracker.
- Each user submits a form with the trade type (5 types) and multiple (different) details per trade.
- A screen showing all open trades and another screen for all closed trades.
- I’d like both screens to be filtered by trade type and ticker symbol.
- A user input for their starting monthly balance and their desired % goal for the month.
- A dashboard showing their current progress on their goal (both as a percentage and monetary amount).
- The dashboard would also have a selection for each previous month displaying their goals/progress.
- An annual chart showing the whole year’s profit with a menu to select past years.
Right now I have the Glide table for the trade log so I can capture and display all the details for the open/closed trades. So really all I have is points 1-3 done. To accomplish the rest, I’m not really sure how to set up the tables from here.
I have it all working in Google sheets, but every user has their own sheet so it’s not too difficult. A mobile app seems much more of a challenge to me. Is this even possible with Glide? It seems like it would be.
Any suggestions would be appreciated, thanks!
Does this mean each person can submit up to 5 types per trade? Or there are 5 types to choose from?
Glide may only let you use 1 in-app filter for each screen, so if you want to filter by 2 things then I would recommend creating an additional Glide table to do that.
Create a Glide Table, add a normal text column and add a row, add whatever value you want to “create” that row, then add 2 user-specific columns to house the “Trade type filter” and “Ticker symbol filter”.
Make sure your tab is using a details layout. On the screen, add a floating button that links to the table above, set it to details view as well.
Add two choice components writing to the two user-specific columns, allowing people to use multiple choices if needed.
Use two single value columns to cast those values back to the table where you house all “trades”.
Use a series of If Then Else column to “filter” what to show, like below.
Trade type filter boolean: If trade type filter is empty then true, If trade type is included in trade type filter then true, else false.
Ticker filter boolean: Do the same as above.
Filter the inline list of trades by trade type filter boolean is true AND ticker filter boolean is true.
Just use 2 user-specific columns to let users input their balance and desired goal, although I’m not sure how you will use this on the front end. I think this is for the dashboard you mention in #6?
This is a bit tricky, I would add a choice component that points to a list of unique months for the user to choose, then use the user’s choice (written to a user-specific column) to create a relation and provide that data for the dashboard.
Same for the year thing.
Thank you @ThinhDinh! To answer your question, there are 5 different types of trades a user can choose from.
I will try out your suggestion on the double-filter setup.
Yes, the starting balance and goal will be used in the dashboard. They will input these values each month.
So the tables I have currently are:
1. User input: for starting balances and goals per month
2. Trade type: for the user to select a type of trade
3. Trade log: holds all the user’s trades
4. Dashboard: to pull in all the data for each user by month and display on the front end
I’m thinking these are all the tables I’d need? Then it would just be a matter of relating them together?
So when I want to total the profits from all of the 5 trade types for each month, should this be in the same table that records all the trades for each user? Or should it be its own separate table such as “Monthly Totals”? If a separate table, would the months be the columns and each user is its own row? Or vice versa?
I think you only need the user input, trade types (where you should hold all choices for other choice components in the app, if you need them), and trade log.
I’m not sure why you need the dashboard table. What are you having in there? I assume you want to do charts and that can be inferred from the other tables.
I think you just need a table that houses unique months, let’s say a column for month, a column for year, then computed columns to rollup totals for 5 trade types for the signed-in user. Does that work?
Thank you. As I’m thinking more about it, your response makes much more sense. “I think” I know how to just use those three tables (user input, choice table, trade log). I was thinking the dashboard was to summarize all the data and used for display, but like you said, I’ll just pull from the trade log and user input tables.
I’m wondering if I can use the month from the user input column as the “selector” for the profit rollup from the trade log. If that makes sense. Otherwise if a user selects a month they never made a trade in, then it wouldn’t make sense. So they’d only be able to select a month they traded in.
I think this would’ve been the idea of the dashboard table. Otherwise, I can’ figure out how to total each trade’s profit, plus the overall total profit for each month for each user in the trade table. If there’s a way to do that, I’d like to. Otherwise like you said, I’ll need to make a summary/dashboard table for monthly totals.
You can just use the “Trades” relation from your user table to the Trades table as the source for that choice component, point it to the month column, users will not be able to choose months they did not make a trade in.
I have a relation made so it pulls in all the trade profits per type into my user table and sums it in another column which is great. However, this populates all months with the same values even though no trades were made. That is, it’s not pulling trades from only that month. I know I’m doing something wrong, but I can’t seem to figure out how to fix it? I’ve attached a screenshot of my user table. As you can see there’s totals for October and November even though no trades were made in those months. Is there a way to Rollup values for just specific timeframes? Thanks!
You need to build your relation so that it does that filtering for you. In this case, it looks like the best approach would be to use a template on both sides of the relation that includes the year/month.
Can you show/describe how your relation is currently constructed?
Thank you. This is the only relation I have. I admit, I’m not even really sure ho
w it works
How would I make this relation you’re suggesting?
In your earlier screen shot, there is a column named “Display Month” that shows the year/month.
Create a similar column in your Trades table such that it gives the year/month for each trade in that exact same format. Then use those two columns to form your relation.
Relations work by matching values from one table to another. So in this case, the row in your User table that has “September 2021” will match every row in your Trades table that has “September 2021” in the corresponding column. So when you rollup through the relation, that row will give you aggregated data just for September. And ditto for October and November.
Actually, scratch all that. I just took a closer look at your first screen shot. I think your existing relation may be fine, but your rollup column isn’t correctly setup. Can you show how that is configured? (it should be working through the relation)
Here’s what one of the rollup columns looks l
yeah, there is your problem.
You’re doing a rollup on the whole table, which means it’s returning aggregated data for all rows.
You need to change that “Summarise the values of:” and point it at your relation.
Make sure your relation is a multi-relation (it looks like it is).
Ah! Perfect! Thank you so much!