How to store and keep corporate data up to date with low/no-code

Following significant headcount growth, our client needed a single, up-to-date employee database — one where the HR systems held current information about each person, and where a change of surname or payroll details caused problems for no one.

The absence of a data-sync mechanism and endless routine tasks

The company used several services to work with employee information. On top of that, there was no single ID for each person — across the various services, someone might be assigned their own variant of an identifier, or none at all.

This led to data-synchronisation problems across the company's departments. As a result, getting reliable information about a person was very difficult. Over time, this gave rise to errors in entirely everyday matters:

• Change of surname. Access to corporate services was granted by surname, and to change it you had to go to the developers, wait for new passwords and a profile change. On top of that, accounting and analytics reports had to be redone.

• Change of payment details. This data could change very often, and the volume of work grew proportionally once the company passed 700 employees: the accountant spent days manually changing details.

• 
  

Because the company is international, this problem was even more acute — people relocated, changed surnames, got married and switched banks, all across dozens of countries.

• Change of role. For example, a new employee agrees a particular position with their manager, who happens to forget to tell HR. HR looks at the start paperwork and assumes the person is where they were, while analysts award bonuses based on a mix of what managers or other staff told them and old data.

• 
  

Correctly identifying an employee to rule out bugs when updating and syncing databases (ID or not ID, that is the question)

It's a mistake to think an employee's email or full name is the key by which they can be identified in databases. In practice that's a poor solution, because an email goes out of date immediately. Someone joins the company, is given an email and a name, and the very next day they may already have a different name, role or department — and therefore a different email. On top of that, many people have one surname in their passport but want to display a different one in the work chat, and for them that's critical.

Not only our client but many other companies prefer to build their database and org structure in Google Sheets or plain Excel, where such tables grow to an incredible number of rows, columns and inaccuracies. And if you tie most of your processes to them, you'll of course run into a mass of errors when you try to automate.

You can't build bug-free databases on Excel-type services, because Google limits us on the number of requests, and the constantly changing composition of the data forces you to rework the system's logic. Bugs and confusion arise because you can't reference a column properly — whereas in a software-based database these problems are solved.

From the client's existing tables we assembled a single technical one in Airtable with the basic employee entities — the IDs assigned in the HR system.

For employees who preferred to use the original Excel table for their work and reports, we created a mirror of the Airtable database in Google Sheets. This mirror also serves as a backup file and is stored on the company's Google Drive.

Distributing and synchronising information across departmental databases

Connecting up the corporate services was achieved through integrators that reference the unique identifier of a field in the Airtable database.

Thanks to flexible tools like Zapier, Make (formerly Integromat) and Workato, you can feed information anywhere and use it as a database — a relational database in Airtable, for example. Or you can deploy a database on your own server, such as PostgreSQL, and query it directly.

The essence of the data flow being introduced was to:

• have the single database controlled from one management centre (department), while each other department sees only the data relevant to its work;

• automate the data-change process, granting that capability exclusively to authorised people rather than to all employees;

• free employees from the monotonous, repetitive task of filling in spreadsheets every day.

• 
  

Automatic data validation, a chatbot with correction hints, and a human decision-maker

The architecture includes automated data validation to eliminate the human factor as far as possible when drawing up contracts with candidates and employees, and when creating a user and granting them access to corporate services.

Automatic data validation, together with a chatbot and auto-emails, catches incorrect or inaccurate information. This rules out sending and signing a document drawn up with errors (for example, a recruiter slips and adds an extra zero to the salary) or granting excess access (for example, support granted more access than it should have).

Georgy Nesterenok — Low-code developer at Goprod.dev

For example, a completed contract must meet certain parameters that are checked against a template. If the automation finds deviations from the template, the document is returned for revision to whoever is responsible for that particular field.

The candidate receives a message in the work chat or by email listing exactly what needs to be corrected, added or specified. HR receives a duplicate of the message to stay aware of the stage the contract is at.

All the information for creating the contract is taken from the Airtable database. During creation, validation runs to rule out most errors, and only once it passes is the contract created in SignNow. The fully completed contract is then sent to the recruiter for review, after which they sign it. So the recruiter has nothing to fill in — they receive a ready contract with the data already in place.

Using a special button, any party signing the document can reject the data and send it back for revision. After the fix, the person responsible for checking and approving the data decides again whether it's correct and, if so, presses the confirm/sign button. For example, the accountant confirms that the payment details provided by the employee are correct, since salary payments must be made against them on time.

The main point of this process is to relieve people of everyday routine so they can take on more engaging, interesting work. For instance, it takes most of the load off recruiters, who would otherwise constantly have to enter masses of data into masses of tables, message everyone “Hi, can you redo or fix this…”, write to team leads about a new hire, and so on.

Nikolas Krestov — Low-code developer at Goprod.dev

HR services as the company's primary database

Information in all services is updated instantly based on data from the HR system. In practice this means that if someone tries to manually change information in any other service, it will revert to its original value the very next day.

If an employee needs to change some information about themselves, they fill in a special Google Form, after which the data goes to the finance manager for approval.

We chose a Google Form because it provides authentication via a Google account. Authentication via a Google account meets the high security requirements needed for working with personal data.

Georgy Nesterenok

The Google Form is linked to Airtable via automations. In our case, data goes into the form using an Airtable Automation, and the data filled in by the user is sent to Airtable via Apps Script. Using the form together with Airtable this way provides reliable information protection.

Recruiters no longer need to spend time filling in employee data, since in most cases the user is notified automatically at the validation stage about what to fill in and where.

Even if the data still doesn't fit, the HR specialist doesn't have to change it manually. They simply write a comment in Airtable about the problem and reject the personal data received (set the status to “declined”). The employee automatically receives the comment and a link to the form to enter the correct data. The accountant, in turn, receives the new data for approval through an interface that's intuitive for them.

There is also a reverse flow of data from the integrators back into the HR system — for example, to update a name. For the automations to work correctly, the employee data entered must be in Latin characters, so it is validated in the integrator, where a probation task is then created.

Suppose a candidate entered their full name in a non-Latin script in the Google Form. When the candidate card is created in the HR system, the automation fills in and saves the name in its original script. A process (a module in Zapier) then runs to check that the name meets company standards, after which the automation rewrites it in Latin characters and refills the corresponding fields in the HR system.

You can also enter other characters in the Google Form — Arabic, Hebrew or pseudo-Latin, for instance. Here, responsibility for the accuracy of the data lies mainly with the recruiter, since only certain scripts are transliterated automatically.

Visualising the org structure online

With a headcount of more than 700, building a clear, informative org chart is a hard task, since many paid tools render the diagram practically unreadable. They sprawl horizontally, and at normal zoom you can see one person, while at a wider view the picture is far from complete.

We settled on the same service we'd used to build the technical database — Airtable. Using its built-in apps, we created a clear company org structure: you can see departments and who works in them, who reports to whom, and divisions either as a list or as the whole company.

Now everyone can see which department they're in and what other departments exist; this makes communication easier. After all, if you know who's responsible for what and who to message, that's already half the battle.

Clicking on a department reveals its divisions as a list; clicking on a division shows more detail — the list of employees, their roles, the department head and the department budget (visible to that department's lead and those above them).

It's populated and managed from a single point — the recruiters' database. Every change to an employee's role and status is reflected there immediately. For example, when a decision is made to hire a candidate and a contract is signed, the automation enters the new employee's information into the technical database and shows them in the relevant branch of the org structure.

Asker Ragimov — Certified Zapier expert, Low-code developer at Goprod.dev

Managing work profiles and access in Google Workspace and the corporate chat

Once the technical database is populated with a new employee's data, the automation creates a work profile for them in Google Workspace and the messenger. In Google Workspace, the employee's access to the services, materials, groups and so on that they need for their work is configured.

We set up a user-authentication system that lets you disable access to various services from one place. If you disable access to the Google account, access to other third-party services that depend on Google credentials is automatically blocked.

We configured all the important entities — in terms of structure, dynamic groups, roles and access — from scratch.

Improving internal communication

Work interaction at the client happens either in services tied to the corporate Google email or in the Slack work chat, so up-to-date employee information needs to live there. Not knowing your colleagues can lead to conflict and misunderstanding — discussing bonus calculations with the accountant, say, or demanding overdue figures for a report from another department's head.

Setting up corporate Google contacts

Employee profiles are organised in Google Contacts, where you can find out who's writing to you, their role, who their manager is and more. The information populating the profile is pulled from the Airtable database.

If a new employee's account wasn't created, the responsible party — whose erroneous data or actions meant the account wasn't created — receives a notification about what to fix.

Setting up corporate Slack profiles

Employee information in the Slack profile is now filled in automatically from the Airtable database using Zapier automations. This means employees who leave, whom the automation removes from Google Workspace, are also automatically removed from Slack (available among the app's paid features).

Calculating bonus pay correctly

Most employees' bonuses at the client depend on the number of calls. They're accrued in different ways and calculated by analysts based on data from BigQuery, visualised in Metabase. Every employee has access to the analytics dashboards to view their personal bonuses.

Google BigQuery is convenient to work with because it lets you write SQL queries and, compared with a Google spreadsheet, can be viewed in full.

BigQuery can provide a data slice for both analysts and IT staff — for example, the IDs mentioned above. The databases of the analytics services are automatically populated from the Airtable database and from Google Workspace data.

Results of implementing the data-flow and storage automation

• Order in resources and an up-to-date org structure — everyone sees the real picture of the company's headcount.

• Up to 20% of working time saved for employees across different departments by reducing routine work. For example, the accountant's job used to involve tracking payment details and filling in a huge table covering hundreds of employees and dozens of fields. On average she processed 20 people a day, doing this day in, day out on top of her core accounting work. Now it's enough to open the convenient Airtable, where all the information lives.

• Minimising false data, and therefore human error. This affects financial processes and team communication. Previously there were errors in employee bonuses due to outdated role information, and people could change their own details in their Slack profile, which could mislead others about an employee's role or department.

To synchronise access we used SSO. So, after a termination request is created in the database (the employee is given “dismissed” status in the HR systems), access to that single sign-on point — the Google account — is automatically removed. The information then updates in BigQuery, the employee disappears from the current org structure in Airtable, their termination date appears in Google Contacts, and access to Slack and other systems is closed.

Questions, or need a consultation on a project? Get in touch — we'll find the most convenient and effective solution for management, employees and developers.

P.S. We wrote about how we set up seamless onboarding and offboarding at this company in a separate article.