Question about specific use of User-specific Columns

Ask for Help
1

My app is for a camp that involves a relatively simple simulated economy. Each student using the app will have $1000 of virtual currency to exchange. Currently, I have a complicated set of columns in the User table that exchange data with another couple tables to run this. Things like giving money to someone else, purchasing items, paying fees/fines, etc go between the various tables.

I’ve been thinking that this might be easier to handle on just one “Money” table that treats the User’s bank balance as a User-specific Column, with the other Columns also being User-specific in that table and calculating between them what the User is doing. The thing is, we will have around 1000 users split into 16 groups and need to coordinate all of this, so it feels unintuitive to me that one table with user-specific columns can actually do this…even if that’s what the point is supposed to be.

Will this work?

2

Should work. User specific columns provide a unique experience for each individual user that is signed in with their own account.

User specific column values are technically stored is a hidden table that has separate rows for each user.

Should be fine.

3

Great! I wanted to verify this before I tried to implement it.

Do you know if Glide will have any issues with hundreds of people accessing/updating these columns all at the same time?

4

I wouldn’t think it would be a problem.

1 Like
5

Follow-up question:

I want to create a list of all the transactions that a particular individual made, then combine that into a master list that can be sorted. For example, if I have 50 students and I’d like a list of every student and the debits/credits/etc that they made from their accounts, would I be able to somehow do that with user-specific columns? Somehow pull together ALL the data into a report?

6

No, you can’t aggregate user specific data from all users, but maybe I’m misunderstanding your goal.

I am assuming that your Money table would only be used for temporary transaction calculations. The current balance would still be stored in the user table and then brought into the money table for further calculations. Then data from the money table would be written to a separate transaction log table. One row for each individual transaction. The transaction log table shouldn’t have any user specific columns. With that, it would be easy to get your cumulative data from that transaction log file.

But reading your post, I feel like I’m missing something both how you currently have this set up.

2 Likes
7

I agree with Jeff here. I think all you need for your virtual money feature is the users table (of course) and a transactions table (a sort of ledger). Optionally, you could set up a “System settings” or “Global settings” or “Transaction settings” table where you could specify the starting balance of each student, minimum spend and maximum spend.

I don’t really understand why you would need user-specific columns here. For me user-specific columns are useful when you want a given cell in a table (an item’s attribute defined as the intersection of a row and a column) to have as many values as you have users. If you want to use user-specific columns, what attribute (for what item) do you have in mind?

An approach of how you could set up your tables:

Users table

• User ID
• Name, Email, Image, Role
• Created Date
• Virtual Currency Balance (Math: Starting + Received - Spent)
• Total Sent (Rollup)
• Total Received (Rollup)
• Transaction Count (Rollup)

Transactions table

• Transaction ID
• Sender ID
• Recipient ID
• Amount
• Description
• Category (optional, reason for payment, for example services at the camp)
• Status (optional, pending / completed / cancelled)
• Transaction Date
• Sender Balance Before (lookup)
• Sender Balance After (lookup+math)
• Recipient Balance Before (lookup)
• Recipient Balance After (lookup+math)

Transaction Settings table

• Row ID
• Starting balance
• Minimum spend
• Maximum spend

You probably would need to add at least on Choices table for transaction categories and statuses. 1 or 2 tables depending on how you like setting up helper tables for choices.

This is how I would approach it. I think. It looks kind of fun to set up!

1 Like
8

I like that you think it’s fun…because it really is!

To help you understand this more, let me explain the camp a bit. It’s a government simulation where the students are sorted into groups that are meant to be fictional cities and counties in a state. They have to elect leaders and run campaigns, so there’s a lot of donating money to campaigns and buying campaign supplies. I want to accomplish a few things:

1 - student donates money to a campaign (sometimes its a group, sometimes its an individual)
2 - student spends donated money for campaign supplies
3 - groups pay money as taxes/fines/salaries/etc to other groups or students

I have a solution right now that works but is overly complicated. My thought for a solution is to create a “Money” table that has user-specific columns for each person and does all of the math inside of itself. It could even be as simple as one column with a couple dozen rows. But since I want to have some sort of ledger (but not in the form of several thousand rows of data), I’m imagining just having each transaction be added as text to a “Ledger” cell.

For example, if Student A donates $50 to a Student B, Student A’s “Donation” cell would get the value:

[User/Donation Cell] & “Student A donated $50 to Student B”

While Student B would get:

[User/Received Cell] & “Student A donated $50 to Student B”

This way, each individual student’s owned cell for their donated and received values would maintain the ledger for themselves, allowing them to personally look it up. I would also have the dollar values stored in another cell and increase/decrease appropriately.

So my question is: can I as an admin create some sort of way to store all of this data in a single row of a “Money” table using user-specific columns, then view all of this data at once in a report? Or is the data in a user-specific column essentially invisible to everyone else, no matter what?

I can easily just have that data report back into the Users table and store it in a column there, but my Users table is currently over 200 columns and 1000 rows so I’m trying to minimize it.

9

I truly think it depends who you ask. What kind of builder you are.

The creative builder will find ways to make the tool do unexpected things. I’m in awe of the people here who are technically creative.

View the data inside user-specific columns inside a report? My initial response would be “No, it can’t be done”. It’s not meant to be done (this is precisely a phrase you might want to ignore). It’s having one column and each user has his own version of the column. Like parallel universes. Separate columns really.

10

I’m not sure I understand what you are trying to do. You are saving the ledger entry as text?

If you want to implement virtual money in your app, I would try what I suggested above. I just reread my suggestion and I feel it’s worth a try.

There are a lot of bullet points, but really it’s straightforward to implement.

11

My thought is that I’d like to be able to run a general report (see all the transactions that students in one group made) while also seeing a more specific report (see all donations to one specific student). The easiest way to do this is for each student to have a “money I gave” text ledger as well as a “people who gave money to me” ledger as separate text columns. The math would still be done in the Money table but the column it writes to would refer back to the Users table.

The goal is to have a record similar to what you would have in real life. But it only needs to exist for a week.

1 Like
12

Great idea. I think you could achieve this quite easily from the transactions table I suggested. As long as you have the basic data, you can then compute it and create roll-ups and templates as need be.