Hi. I am making a project management app where admins can create new projects, and potential employees can sign up for them through a form (many to many relationship). Now what I have in mind is a glide table titled project management which holds all of the projects as rows (project name, description, and roles (if you’ve got a cool idea for this let me know because I plan on making like 25 columns for all possible roles)). Now the part I’m stuck on is how to hold the information for which projects an employee applies to. So to simplify things, I also want a new table for every project to hold employee information. Glide and zapier don’t have actions that automate making new tables. I know Zapier allows you to automate making a new google sheet which is what I will probably end up doing, but I was hoping to keep everything on glide.
I don’t think you want to create a new table for every project. It’s not good database design, and for every new table, you would have to go into the builder and create a whole new set of screens and app flow to work with each and every new table. It’s not a scalable solution and a nightmare to maintain.
Instead, you should only need one single Project Employee table. If you design your logic correctly, then each row in that table should have an ID for the project along with all of the employee information. Then you simply need a relation between the project ID in the project table and the ID for that project, that was written to the employee table.
Thanks for the heads up, im still new to all this. I’ll see what I can do with what I know. I just get very confused on how to establish the relationships.
For instance, based on what I understand from your comment, I will make a new table that is fed employee information through a form, along with the project ID from the projects table. Build a relationship between the two tables based on Project ID. That way from the project detail screen I can have a list relation of all the employees that applied to that project with a detail screen on of their information and an option to approve them (which would just go into another filtered list relation).
Now to show employees the projects they are approved for, I will have their “email-status” as a template and relate that to a similar template in the project employee table, then display an inline list of the relation on a separate tab called Assigned projects.
Now the only problems are that employees can apply for more than one role in the same project and the admin can choose to approve certain roles, I still have 26 unique role columns per project incase they are needed.
Sorry for the long post, just thinking out loud.
I think you have a pretty good grasp of what you need to do.
So when an employee applies for a project, can they only apply for one role per submission, or can they apply for several roles at one time?
Also, when an admin approves an employee for a role, so you intend to populate the respective role column in the project table with an employee ID or email?
Just trying to get a better idea of your app flow.
Well i think it would be a bad user experience to have them apply several times for each project. So I’m thinking of making 5 role columns on the assumption that they won’t want to apply for more than 5 roles.
The 26 role columns in the project table are for the admin to choose what roles they require out of a master list of 26 roles that I have (my manager told me this was the largest project they ever had with 26 unique roles). Now I intend on making a button that can add new roles to the master list and consequently the options to choose from, but just hope they never want to actually employ more than 26 unique roles.
As for handling the assigned employee roles, I’m thinking of making 5 additional columns in the project employee table, so that the admin can assign roles to them. then use visibility rules so that employees can only see the roles assigned to them.
I appreciate the support!
oh and as for employee ID or Email, does it matter? the way I see it is that they are both unique identifiers
OK, so here’s some things to consider.
First, have a separate master role table like you mentioned. One row for each possible role.
Next, In your Project table, you should only need one column which will be used to hold a comma delimited list of available roles for each project. When a project is created, the admin could use a choice component with multiple select enabled, so they can select from the master list all of the available roles for the project. This will write the comma delimited list into a single column.
Next, create a Split Text column in the Project table and point it to the roles column. This will convert the comma delimited list of roles into an array. Using that Split Text array, you can then create a relation to your Master Role table.
In the form that the employee fills out, I believe you can then create a choice component that is sourced from the Relation you created above, in the Project table. This should show them a list of available roles for that project to choose from. If you turn on multiple select, then an employee can choose multiple items from that list of roles and it will save them in a comma delimited list in the project employee table. That way you only need 1 column instead of the 5 columns.
Finally, you can create a Split Text column in the project employee table, so an employees choices will be converted into an array, which you can further use for relations.
No, it shouldn’t matter. Email would be fine.
woah that’s crazy, I had no idea comma-delimited lists were a thing, that would clean up my work a lot. I’m almost done with my current implementation. I’ll probably go back and clean it up into that when I’m done. If you don’t mind I’ll make a quick video showing you the back end of stuff with the current implementation sometime tomorrow, and you could let me know what you think before I try converting it into the comma delimited format
Yeah, that’s fine. I have a long weekend, so I’ll have limited access, but I’ll take a look at your video when I can.
Im still working, but just felt like sharing an amusing and frustrating situation. So my role choices although not nearly as clean functions very similarly to what you mentioned. I spent the last hour almost trying display the relation from the array as the choice component, turns out I just had to scroll down in the list of sources
Hehe, yeah I think that will trick people sometimes and they’ll try picking the table instead of picking the relation.
lol caught me red handed