There’s your problem, right there. :winking_face:
Without breaking into my ‘Airtable Is Not a Spreadsheet’ sermon, let me note that, um, Airtable is not a spreadsheet; while that metaphor may be an excellent way to introduce someone to Airtable’s look and feel, it often leads to confusion for new users, as it suggests false equivalences. Let me try to explain using an analogy that no doubt is equally as far off the mark…
The obvious — and erroneous — way to visualize the comparison between Airtable and a spreadsheet is:
| Airtable |
spreadsheet |
| row |
row |
| table |
spreadsheet |
| base |
workbook |
Based on data visibility, though, the reality is more like this:
| Airtable |
spreadsheet |
| row |
spreadsheet |
| table |
workbook |
| base |
workbooks(?) |
As I said, the analogy isn’t perfect. The thing to remember, though, is that every item in a spreadsheet has visibility of any other item in its two-dimensional plane. In Airtable, though, an item can see only those items along the same row. There are ways around this, but they all involve somehow getting data to surface on the necessary row.
If one were to implement your sample screenshot literally within Airtable, the result would be five records containing such fields as {Date}, {Hours}, {Rate}, and so forth, along with a sixth record containing only the {Hourly Rate}. Since none of the records has any visibility into any other record, as you discovered, you cannot use {Hourly Rate} in a calculation.
The easiest solution, then, is to add {Hourly Rate} as a seventh column of your table; then, the formula
{Hours}*{Rate}*{Hourly Rate}
will work.
To keep employees (or you!) from having to enter {Hourly Rate} for each record, there are options:
- You can create
{Hourly Rate} as a numeric field with a default value; collaborators with edit ability can change the default value to reflect their own hourly rates before submitting an overtime request,
- Similarly, you could create a table per employee, with that employee’s hourly rate ‘baked’ into the table.
- You could create a third table,
[Employees], which includes (at least) the employee’s name or number and his or her hourly rate. By using a field linked to the former, you could then access the latter for calculations. (Of course, then you’d face the problem of having to enter the employee’s name or number into each record… :winking_face: )
- There are ways, involving linking every record in one table to a single record in another table and getting tricky with passing data back and forth between tables, to share common data across multiple records — but, frankly, they all are probably overkill for this solution. If none of the other four options are acceptable, reply to this response, and I’ll toss together an approach.
(Hmmm… I see that all of my replies today should come with an embedded TL;DR tag…)
