Structuring My Data

Ask for Help
1

My app is a voter contact app being built for a nonprofit. they have a large Data sheet of voters they wish to contact, and my app provides their volunteers with a map of voters they are assigned to, and a survey to ask those voters.

I am struggling to wrap my head around the best way to structure my data in the app. I am currently building based off of a small data sheet example, but I have come to realize that the data sheets they use are essentially going to be switched out for each campaign. Every State they are working in has its own spreadsheet of data, and they may be working in multiple states across the country at any given time. The company would desire to have reporting based upon how many surveys each team of volunteers contacts within each campaign, and have their answers to the survey appended onto the data.They don’t necessarily need stats for every volunteer, but rather, stats for each group of volunteers.

So there needs to be some kind of master spread sheet I think, but I am not sure how to accomplish that structure. They purchase individual sets of data for each location their volunteers will work in from another company. Maybe I can have a master template they upload that sheet into, but I am not sure how to have multiple sheets going at the same time if they have multiple states with their own sheets working in the app at the same time, if that makes sense. Another option may be to simply make a copy of the app with the new spreadsheet and distribute it to that Campaign’s teams? However, I cant delete the original spreadsheet when I make a copy without breaking the app to replace it with the new one.

I currently have a business plan free trial. at any given time I may need 10 different master spreadsheets with around 5 teams or users in each.

I am also looking for resources to better understand how I can assign each team their section of the spreadsheet to work on. so rather than having the app load 1000 voters in KY for example, the team will log in and blue team has 50 voters to contact, red team has 50, ect. I think the answer lies in something like row owners but when I watch related videos I cant quite understand how to make that work.

Any Insight would be much appreciated. I have attached a chart to show the flow of my app. it does not include details on the analytics portion as of yet. The company would like reporting to have its own dashboard that can send them periodic PDF’s on a schedule to their email. The reporting needs would be things like, “How many voters did we contact in Indiana” and “How many voted yes or no to question 1” and “how many conversations with voters did team Blue have in Tennessee on Saturday?”. So, with the Form function as a survey, time stamps will need to be recorded that they can filter through for reporting

Finally, I want to check that I am thinking about real time updates correctly. Each user login is for a team of 2-10 people. they individually will complete surveys on their device, but under the same login. if multiple users are using the blue team login to complete separate surveys, will they all update in real time or will this cause weird interferance issues?

image
image1183Ă—713 33.1 KB

2

This will be a problem. Is there a way for you to convince them to just use one data sheet, or consolidate the existing data into Glide Tables?

Continuing with the problem above, this looks like your best bet, but would spreadsheets still be used in tandem with Glide? I would suggest trying to get people to use only the Glide app, with a consolidated, standardized data source in Glide Tables.

This looks like a setup for roles & row owners.

I haven’t had a setup like this before, but is there a reason why they can’t have different accounts?

1 Like
3

Hi everyone, i would like to show you “Hey Glide staging AI generator”, which helps to easily creates data templates for apps

Just add your description and it will creates basically data structure and simple screens

It is good start point to thinking about data structure for the application.

Link to Hey Glide staging

2 Likes
4

If I were to keep one master spreadsheet in glide for them to add to as they purchase more data sets, how can I upload them to glide and remove old data without breaking anything?

For further detail, they are going to be purchasing data for each location they have volunteers work in. the data comes to them in the form of excel spreadsheet, and each sheet is full of data for that location. would you advise a way to maybe import those spreadsheets and have them assigned to different groups of volunteers?

5

the only reason not to give each user an account is that there will be too many users and we only need to track their progress by team, not by individual. we could easily have 40 volunteers in one location working at the same time as 100 in another, but they are in groups of 4-6 so that helps cut down on how many users we need taking up space in the 40 user quota

6

I think if i were going to do row owners, it wont work, because i actually need it to be ROWS that are owned by each user. the sheet has 1 contact per row, and each user needs to be assigned about 100 rows each to work on.

Roles looks like it could work, but in the video he is only grouoping within the same users table. I would need to apply a Role like Team Blue, and team blue can access maybe 100 rows of the KY spreadsheet of data. so it would be a role sorting another sheet into sections, assigning the role some portions of that data sheet

7

I think you can use master table for multiply location, just use unique key for each uploaded set of data.

8

In my opinion it is very bad idea to multiple users using same email to login!

9

If you have hundreds of users and tons southands of data each month, so dont try for economy, think about how co cunsruct the data structure anp apps flow well.

10

So you need to remove old data each time? Do you ever need to access historical data, or run on multiple “datasets” at once?

As long as you have the same column names, you should be able to just import that into the right table.

Yeah, I can understand this.

If you have only 1 user account per team, wouldn’t row owners work?

11

We need the master data to be updated with the answers to the survey being performed in the app. That Data wont need to be accessed again within the app after the team completes that trip, but the data does need to be stored for later use outside the app after having been updated with the survey answers. The next time a team goes out, it would be with a new fresh spreadsheet of data contacts to go out and do surveys for

I think row owners is actually going to be the answer for separating the data out, I am just trying to figure out how to get it setup to function properly.

12

Is there a way to apply 2 layers of row owners? So, If I need to sort first by the Campaign Name, and then within each of those, a team name?

13

Why do you need two layers here though? Would there ever be a campaign with 2+ teams?

14

Yes, so every spreadsheet is 1 campaign, and 3 to 6 teams will work that campaign at once. There are many campaigns throughout the next few months, some of them in the same day but in different states. The data for the campaign is a list of voters who the teams contact to ask a survey about the upcoming election

15
  1. Each user must login with their unique Email - it’s exactly and clear.
  2. You need a team table, ROW-ID of its rows - it is the role for the users.
  3. Master table contains:
    PK_Campaign_id|voter_data|FK_Admin_role(row owner) |FK_Team_Role(row owner) |Set_of_survey’s_columns|
  4. You need external service which will prepare the content for Master table with necessary columns
    Every Admin_role column must set to admin role value
    Team_Role column must be set according to assigned teams access.
    a. You can use google sheet as DB and AppsScript to process Role set. There is you process data in a not connected sheet and then copy it to a connected google sheet you will have some economy for updates. But then when users will fill out survey you will reach lot of updates. The workaround may be creating a new survey row in native Glide table with a foreign key sourced primary’s key of the master table. Then you can easily generate csv for exporting.
    b. You can construct an array column in Google sheet to provide several teams to access same campaign.
  5. In another hand you said you need teams to access several campaigns at same time, so it is possible in the above-described way.

If you need to clear the DB after the survey is finished, it is also possible (delete by multiple relation or by AS).
I think.

16

I still feel like this is an approach that will make it super hard to maintain the app later. Is there a reason why you don’t just keep them all in the same table?

17

I can explore that with them, I think they are going to want to export data from the separate locations to create a pdf report. So they want to be able to get a pdf email saying, here are how many surveys completed in Texas Saturday. Their longterm storage of the list can be one I think, especially if I can query the list somehow

18

It sounds like it would be best to keep them all in one table. Then you could use a query or relation to filter the data for export/ pdf.

19

Thank you, do you have an example of relation or query on the data I can look at?

20

Given your example how many surveys completed in Texas on Saturday. I would create a one row table with some user specific columns to hold the State and the Day of the week. Then I would create a query from the one row table to the data. Finally add a rollup column via the query for the count.