Hi, sorry if this was addressed already but I’m having trouble finding it. I’m experimenting with Glide by trying to recreate an app I have on another platform. I’m trying to figure out the following.
Basic Background:
App replicates a simple employee to company directory
I have two tables, Company and Employee
I made a relations field on Employee table to find Company Name in Company Table.
Basic page would be a company detail page that has a button or inline list to show employees that work there.
Secondary page would be a contacts page that lists the companies the employee is affiliated with.
Questions:
I have the Employee to Company relation working for one-to-one but if I set that to select multiple, I can’t seem to get it. I have employees that can do work with multiple companies. How do I make this a one to many from the employee side? Same with going from Company to many employees. Looks like the tables put commas between them but I don’t see the relation column work in this case. Also breaks grouping in employee list. The employee who works for multiple companies doesn’t show up in multiple companies, it creates a group of “Company 1,Company 5, Company 7”
I was exploring Row Owners. Can a Company Owner Role, have the ability to add employees but only to their own company. On my form now, they can pick any company. I guess I’m looking for a filter or a hidden field that would set company to logged in user’s company or similar.
you have the wrong data structure…
you need 2 sheets to accomplish that:
user’s profiles sheet - list all users, and give them a function column (don’t use roles) that would be admin, or company, or employee (if you set filters to included in… each user can have multiple functions)
contracts sheet - here you list all contracts between companies and employees that would have a column for company email and a column for employee email.
now you can easily set visibilities, and relations, filter data, and get additional info from the user’s sheet, also very easy to store some parameters in the profile sheet that you can access from any place in your App
in this structure, you won’t need multiple relations.
also, you might need sheets for:
employee’s time card
pay-slips
Incentives
expenses
day-off requests
internal chat and messaging
payments integration log
tax brackets
overtimes (if they are not calculated from time card)
It sounds like you may be listing companies in the employee table as a comma delimited list? If so, use a Split Text column to split that comma delimited list into an array. This will allow you to establish a multiple relation from the employee table array to the company table, and you can establish a multiple relation from the company table to the array in the employee table. This will allow you to add an inline list on both the employee details screen and the company details screen.
How important is data security in your use case? When applying row owners, that means unowned data is not downloaded at all. If you are just attempting to control which company employees are added to, then there are other ways. First I’d like to ask what is your procedure for adding employees to a company? I’m thinking you may want to flip around what I mention in item number one. Instead of an array of companies in the employee table, it might make more sense to have an array of employees in the company table. Then when you are viewing company details, you could put a condition on the edit button so only someone that belongs to that company can edit the details. In the edit screen, you could then have a multi-select choice component where you can select all of the employees that belong to that company. This will generate a comma delimited list of employees in the company table, which you can use to create both of your relations linking employee to company, and company to employee.
In the end, this wouldn’t require a form to add employees to a company, and this wouldn’t require a third table to link companies and employees. You would only need the two tables. The employee table could function as your user profile table. And the edit condition when viewing a specific company would check if the comma delimited list of employees (emails) includes the signed in user email. There are a few other different ways that could be done too, but start with what I told you and then we can address authority over who can add what where.
Also, I don’t think it would apply in this case if you choose to go my route and not use a form, but when using a form, you can pass values from the parent table row where the form button is located. There are several additional components, such as Value components, Special Value components, User Profile components, etc. Just something to keep in mind for the future.
he will have a hard time managing the data if he keeps the company and employees sheets separately, I’m writing software for small businesses for over 35 years, trust me…
Thank you for the quick reply. I neglected to send this reply until I had more play time, but you understand where I’m going with this.
So I tried what you suggested and please tell me if I went about it wrong.
Company Table
Column for Employees comma separated. (I just switched this is use employee rowID, that’s best practice correct?) In app, I use a multi-choice field off the employee table.
Column split text by ,
Column Relation for split text array to employee table matching rowID
Employee table
Relation field for RowID matches employee column in Company table
Now I have working the related employees showing in companies and the companies in each employee correctly.
To answer your questions:
2. In this use case, security by row owner is not important. Adding employees at the present is done by any member with edit rights to the the company. When they add employees, the selection of the multi-choice box for company is filtered by company permission contains logged in user’s company. I’d like to try to replicate this functionality.
I currently have a Company permission column in the user table so the company edit is allowed (by visibility filtering, not row owner download block) where company is found in user>company permission
I was trying to add the Add button to the employee tab then use a choice field for company that would filter to only company that is in the logged in use profile company but I can’t choose that company relation field. I can pick other relation fields. Any idea why?
Also, I may make a new post regarding but some best practice advice:
When going about relations, is it best to use rowID>split by text>Relation via RowID or just use name?
Looking for a way to create database backups and long term, a change log so I can see who made what edits. I considered moving the data to Airtable or sheets so at least there’s a history and way to rollback. Is this the only option? Any links already discussing a audit/change log? I’m also currently looking. Thought about doing this with Airtable automations to just add rows to a new table but maybe there’s a cleaner way
Airtable vs Glide Tables. I was trying to keep it in Glide for performance but does Glide poll the airtable live or does it create a local copy in glide? I did notice relations are cleaner. A linked table in Airtable seems to just be a relation table without doing the text field>split by text>relation. Am I doing that wrong? Do I need all 3 columns?
So to be clear, you are wanting to add an employee to a company by viewing the employee first, and then add them to a company from the employee details screen. I’m trying to decide if I want to revert on my advice to switch which table that delimited list is in. I think it could go either way, but I’m trying to understand how you want your app to flow. If you have any screenshots or a loom video, that would help a lot. I guess I was imagining a multi-select choice component on the company screen, that would allow you to select from the entire list of employees, but I may need to know more before I can give adequate advice. I mean, there are a thousand ways to skin a cat, so anything is possible, but it all depends on what you want in the end.
I think how you answer the above questions would determine how this works, but ultimately, it sounds like you are trying to compare the user profile company directly to the relation? First of all, a relation is a connection to an entire row or set of rows. A relation does not return a single column, even though it may appear that way. If you are looking for a single value from a single relation or an array of multiple values from a multiple relation, then you would use a Lookup column in conjunction with that relation. In your case however, you could use a Joined List column against that relation. A Joined List column is like a Lookup column. It returns values from a specific column in the relation, but instead of returning an array, it returns a delimited text string. With that you could filter your list by comparing where the user profile company ‘is included in’ the joined list, or the joined list ‘includes’ the user profile company. Again, I’m not sure if this is the best route, depending on how you have your app flow set up and how you want it to work. Screenshots and/or video will help me a lot to visualize what you have and what you want.
Yes. RowID is good practice because that allows you to alter details about the employee, such as name, email, etc. and it won’t break the relation. RowID’s will never change, but other details about an employee could change. You want to ensure that you are using a value, such as RowID, that will never change.
So, change history can be as simple or as complicated as you want to make it. There’s a lot to consider as far as row counts and update usage. Personally, in my app, I don’t keep an edit log of any changes. I think it would add a lot of overhead. But, you could probably do something like using custom actions that would not only update a row, but also add the same data to a log table. Or, you can use special value components in forms, which can log the date/time and user email of the last change to a row. I guess it all depends on what kind of history you want to keep and how extensive you want it to be. I guess it would be easier to answer if you had a pretty good idea of what you want for history or change logs.
As far as backups of data, you have a couple of options. One thing that Glide provides is an Export function. This will allow you to export your data as a CSV file. It’s not automated, so you would have to run that export any time you wanted a backup of date. For my primary app, I have all of my data in a google sheet. One advantage to that is that I was able to create a google script that runs weekly and will automatically create a duplicate backup of my data with a timestamp attached to the file name.
You can see the script here: Backup My Data In My Spreadsheet - #2 by Jeff_Hager
In my google drive, I have a folder with copies of my database generated every week, going back 3 years. If I ever need to restore any data that was lost, I have a reasonable chance of restoring it between these backup copies and google’s own revision history.
I think another alternative would be to use the Glide API to retrieve your data, but I think you may need an Enterprise subscription to get full API access to do a mass Get of all data.
This comes up every once in awhile. As long as your Airtable or Google Sheet aren’t performing their own calculations and you keep all computed logic in glide, then I really don’t think there is any sort of performance difference between Glide Tables and any other external data source. If you use Google Sheets for example, Glide still keeps a copy of that data on their servers. Glide acts as a middle man between Google and the end user. Your app will never know the existence of any third party database. The app only syncs data with glide, and glide does the syncing with the google sheet. So yes, glide does keep a local copy.
I don’t have any experience with Airtable myself. From what I’ve read, I think it has it’s own quirks. I think this is especially the case if you are using some airtable functionality that is exclusive to airtable. To some degree, Glide can interpret it, but I think people have had mixed results with linked tables for example. I think if you used airtable purely as a database source, and didn’t use the eye candy that’s exclusive to airtable, then it would work just as well as google sheets, glide tables, and excel. One thing to note about airtable…I think the current connection that glide has to airtable forces them to poll airtable regularly, instead of airtable sending updates to glide. Glide has mentioned that someday, they will get access to a better airtable API, but I’m not sure when. In my opinion, I would at least avoid Airtable if you can. If you already have data in airtable, are comfortable with it, and it works with glide how you want, then go for it, but I would be hesitant based on what I’ve read.
Short answer, no. But, this isn’t necessarily the standard way to create a relation. Glide can’t create a many to many or many to one relation using a comma delimited list. Glide sees it as a single text string and tries to find a literal match…commas and all. That’s why you need to convert it into an array with a Split Text column. However, you could have cases where you have a single value that can be used to create a relation. You also could have an existing array, such as the array form of a Lookup column, an array from a Make Array column, an array generated from sequentially numbering columns in a google sheet, or any other way you end up with an array. Airtable probably performs some magic on their end to convert a comma delimited list into an array…or maybe they don’t even store it as a comma delimited list. Point being, you don’t have to use the text field>split by text>relation method every time you need to create a relation. You only do that if you have a text field with multiple values in a single value text delimited form.
So to be clear, you are wanting to add an employee to a company by viewing the employee first, and then add them to a company from the employee details screen.
No, new employees can be added by anyone but only to the same company the user has edit access to. I think I can make the choice box to fill the company and filter to where company contains user’s company
I will see if I can copy the database and fill it with dummy data tomorrow. I can’t show screenshots or a loom due to the private data in the tables. In essence I’ll try to explain the final product and the flow:
App is for an industry where several companies are all working on a similar function but in different exclusive territories
App serves as a listing of all companies, listing of key contacts within each company, and a list of special tools, resources, or vendors available to each company as well as a listing of small files that are either company specific or viewable in all companies.
Main functions:
company list. Detail view with basic company info, address, phone etc. Also Inline list of Key contacts for the company. Owner, CTO, CFO, etc. Detail view of that has email, phone, title. List relation to resources owned by company. Detail view opens resource or vendor
contact list, grouped by company. Detail view same as above. Inline list to associated company
resource/tool/vendor list grouped by type and inline filters to locate particular item. Detail as above. Inline list to associated company.
File Datastore with files associated per company, listed in a relation list on each company
Some files are marked that they appear in all companies relation list, other files only show on company they belong to. I didn’t tackle that yet, but I imagine I will make filter say files include company OR files include All or something like that.
Also want to add a boolean to mark file as private so only employees of a particular company can see it. In this area, row owner security is preferred.
Key Points:
All users can read all data except for private files controlled by row owner
Edits can only be made to company(ies) the user belongs to as defined by a column in the user table. So edits to the company direct, employees that belong to that company or resources/files also linked to company. Basically all permissions are at company level view or edit
New employees can be added by anyone but only to the same company the user has edit access to. I think I can make the choice box to fill the company and filter to where company contains user’s company. Or special value. I haven’t read about that too much yet.
if you are not far with the app development, I strongly suggest changing your data structure. I did many similar Apps to yours… and I’m pretty sure, your app will not only be hard to do but impossible. you must create a contracts sheet, not assigning companies to employees by relations.
I think the part that I’m trying to wrap my head around is how an employee can belong to multiple companies.
Someone in a company can only add an employee to the same company. So, based on the fact that you want to limit which company an employee can be added to, I’m unclear how they could ever belong to multiple…at least in the add phase. I could maybe see it as a possibility in the edit phase, or if the user adding a new user already belongs to multiple companies.
So, UserA belongs to CompanyA. UserA has authority to add NewUser to CompanyA. But, NewUser also needs to belong to CompanyB? My question is how or who adds NewUser to CompanyB? What’s to stop UserB from CompanyB from adding NewUser again, but now associated with CompanyB? Now you have duplicate NewUser employee records. One added by UserA to CompanyA and one added by UserB to CompanyB. What’s the process to associate NewUser with multiple companies? I think that’s the key element bro how things will need to be set up.
Do you mean contacts? I’m not talking about contracts. Current App is based on Airtable. Main tables are Company and Employee. Company has relation to Employee but all Employee data is in the Employee table. I’m trying to understand how what you’re suggesting is different other than terminology used
I was unclear. When adding, you can only add you your own companies. Yes there is the possibility of two companies adding the same user. I think the occurrence would be rare based on how this would be used.
To understand better. UserA belongs to CompanyA and UserB belongs to CompanyB. CompanyA and CompanyB have a regional manager UserC. UserC would like edit rights to both CompanyA and CompanyB. In current form, UserC is normally added by an Admin or Me, who have rights to all companies.
But if UserA wants to add new user UserC, technically speaking, they can only be added to CompanyA. Now say there is an existing UserD who’s also a regional manager who has rights to CompanyA and CompanyB already, granted by an admin. UserD can add UserC and relate them to both CompanyA and CompanyB or Edit UserC if it was previously created by UserA and add CompanyB.
In most cases, a regional manager would be added by an Admin or an existing regional Manager only.
In order for your app to be functional, you need to merge company and employees sheets, into one. Next, create a sheet with contracts between companies and employees, each employment separate row. This is how you will know who works for whom, not by relations… then any event that will happen in that contract, you will record with the contract row ID, when you have data structured like this… is easy to manage, and you can have any number of employee working, they can have multiple contracts with same company, they can work for other companies, also companies can work for other companies or even for employees it will be irrelevant
I’m not familiar doing it this way for my experience with related database tables. So you suggest I have a table of Company/Employee. Say Name Column is
CompanyA
CompanyB
CompanyC
UserA
UserB
UserC
Then a table with two columns to marry them?
Company A | UserA
Company B | UserB
Company C | UserC
Then an Events table?
Event | Contract RowID ?
Seems like this would have a lot of data duplication, would it not?
absolutely not, there will be no duplications at all.
one table for all users, no matter if they are a company or employee or admin… or whatever users you have in your app.
second table is for contracts, every time you connect someone with someone you create a row, and you specify who is a company and who is an employee, by putting user email into the company email column, or employee email column.
then you add all columns that you need for that contract, like start date and end date, salary, job position, tax, days of work in a week, hours per day, day offs allowed… contract terms…
now every time event happens, like a payslip, a day off, the time card clocks in or out… you create a record for that with the contract ID
there is no other way to do that… your concept is only good for showing who works for whom, you will get stuck doing anything with your data, and it will not work… I guarantee it
So I whipped together a quick example app that you can copy. It’s loosely based off of the Glide Employee Directory template.
I reverted my thoughts and put the delimited list of companies in the Employees table instead of employees in the Companies table.
If you look in the Employees table, I have a CEO listed with all three companies. I have two Regional Managers that are both part of two companies, where one of the companies overlaps between the two of them, and I have a Manager which is part of one company. All other employees are part of one company at the moment.
If you preview as the CEO, you can add a new employee and assign them to one or all three companies. If you preview as a Regional Manager, then you can only add an employee to 1 or 2 companies. If you preview as a Manager, then you will only be able to add an employee to one company. All other employees can add a new employee as well, but that could be locked down if you wanted.
For the CEO, Regional Manager, and Manager, they also have a checkbox in the table, which is used to grant them edit authority over existing employees. (Not sure if you needed this functionality or not, but I added it anyway.) This will determine if the edit pencil is shown or not when viewing a specific employee. When editing an employee, there is a choice component that shows which companies they belong to, and any that they could be added to based on the signed in user’s access to that company. This is a muli-select choice component, and it’s filtered by the signed in users company list. There is one peculiar part of the multi-select choice component for your particular use case. If an Employee is assigned to 3 companies, but the person editing their record only had access to one company, then the choice component would only show one company due to the filter, and there is a risk of losing other associated companies when modifying the selected companies. For this reason, I added an OR condition on the filter, so it will also show any companies that are previously selected for the user. But, if someone unselects a company that they don’t have access to themselves, then that company is removed from the list of choices. It’s a little weird, but maybe it’s something you wouldn’t have to worry about??? There are much more complex ways to handle it, such as custom edit forms, but that becomes a lot more complicated to explain and maintain. Just something to think about though, if you think it will be a problem
So, In the end, I think this is pretty close to what you want. Make a copy, play around with it, and let me know if something doesn’t make sense, or something isn’t working how you expected.
I’ll add that I did do one thing a little different. I set up my tabs as Detail Style layouts instead of List Style. It gives me a little more flexibility in my screen design. In the Contacts tab, for example, you could keep it as a list layout and use the the built in Add Form instead. I chose you use a Floating Form button, but they would both work the same.
Thank you immensely for this. This cleared up a lot of confusion for me. Partly because I was building two versions of the app. One with Airtable as the base since the existing app is built upon that, and another with Glide Tables. Some of the confusion was with how AT handles related (linked) fields. As it turns out, the recordID (or RowID) is used, just hidden on GT side. Some other gotchas with AT as well in terms of usable fields but I overcame that with formula columns and some joined list columns in GT direct. I’d like to get away from AT but until I have time to convert the automations and design a backup in GT, I’ll stick with that or Google Sheets.
Is it known how many syncs happen when using Google Sheets or Airtable? Other than when updates are made in Glide? If it’s constantly syncing, I assume it’ll use up the updates limit
