Building timesheets in Airtable (without pay rates being visible)

[Tim_Wo]

I’m using Airtable records for individual jobs in my business.
I’d like to incorporate timesheets into Airtable and have the timesheet data link to the job records.

I’m imagining a separate table for employees and their various pay rates which links to the main jobs table and calculates the cost of the job vs the budgeted amount, but I don’t want the pay rates and job costs to be visible to non-management employees.

Is this possible in Airtable? Is there perhaps another way of approaching this problem to achieve the same outcome? I’d love to hear any feedback!

[kuovonne]

Welcome to the Airtable community!

If someone has direct access to a base, that person can view all the data in the base. Thus you need a system where external users don’t have direct access to the base. Here are some options:

• Use a portal service (Stacker, MiniExtensions, Softr, Pory, Glide, UIBakery, etc.). External uses do not use the base directly. They only access the base through the portal. All of these portals have a monthly fee if you want a reasonable number of external users to have edit access
• Use a system of forms and shared views. External users add data using a form. External users can view existing data through shared forms. Although this method is free, it is a clunky interface and having external users edit existing data requires cumbersome workarounds with automations.
• Split the timesheets and pay rates into to different bases, then sync the timesheets to the pay rates table. You will need to have either an automation or workflow that creates the links from employees to their pay rates. This is also free.

[Tim_Wo]

Hi Kuovonne

Thanks for your reply - I’m liking the idea of your third suggestion.
Do you have an example of how I would link the employees to their pay rates via automation? I’m needing to report on the timesheet data together with the data from the jobs table.

[kuovonne]

Tim_Wo:

Do you have an example of how I would link the employees to their pay rates via automation?

1. In the private base have a people table where the primary field for each person is unique.
2. Create a shared view of that table that does not show any private info.
3. Sync that shared view to your timesheets base.
4. In the timesheets base, use a linked record field for people to pick the proper person.
5. Sync the timesheets table back to the original base.
6. Have an automation that triggers when there is a new record in the synced timesheet table in the private base. Have the action update the record by copying the original person field (which will be converted from a linked record field to a text field) to a new linked record field.

This sounds a bit more complicated than it is.

Also keep in mind that this system does have limitations.

• unless your timesheets are in a free workspace, you will need to pay for every user who can create or edit records directly in the base.
• you will not be able to prevent users from adding/editing/deleting records that belong to other people.
  These limitations are why many people go the portal route.