Apr 09, 2018 09:13 PM
Hello! I have a table like:
Name | Project | Labor | Days |
---|---|---|---|
Donald | Name1 | $150 | Sun, Mon, Tue |
Joe | Name2 | $130 | Sun, Mon, Tue |
Alex | Name1 | $130 | Sun, Mon, Tue, Wed, Thu |
Cookie | Name1 | $130 | Sun, Mon, Tue, Wed, Thu |
Alicia | Name2 | $130 | Mon, Tue, Wed, Thu, Fri |
Doris | Name1 | $130 | Wed, Thu, Fri |
Now in Excel I can easily do this in a separate table worksheet:
Sun | Mon | Tue | Wed | Thu | Fri | Weekly | |
---|---|---|---|---|---|---|---|
Name1 | $410 | $410 | $410 | $260 | $260 | $130 | $1,880 |
Name2 | $130 | $260 | $260 | $130 | $130 | $130 | $1,040 |
Thus, I can do math based upon value. I want to be able to draw from my schedule column (Sun, Mon, Tue, Wed, Thu, Fri) and get the amount of labor (predefined amount of $#, consistent among everyone so it doesn’t need to be different per line) for I can print out the total it’d cost me on each day of the week, and a grandsum of the weekly total for each project, depending upon what’s in the project field.
I am not sure how to do this, as AirTable doesn’t make calling from other sheets/table easy like Excel does.
Apr 10, 2018 01:07 AM
Ah, the spreadsheet metaphor: Airtable’s strength and weakness.
A good rule of thumb is that when you look at a table in a grid view — the one that looks most like a spreadsheet — the only calculations you can perform must use values along a horizontal line. To perform any math or comparisons against values arrayed vertically, you must link the records (the horizontally oriented values) to a record in another table and use this linked record as a point from which to gather and consolidate values from other records.
For instance, in your example above, you might easily have individual tables named [Name]
, [Project]
, [Labor]
, and———
Wait: Take a look here. I copy-and-pasted the four columns of data (Donald through Doris through ‘Wed, Thu, Fri’) into an Airtable table, converted {Project}
and {DoW}
from single-line text to linked-record fields (creating the linked tables and automatically generating the linked records), and tossed together a few rollup fields to perform some of the consolidating math. (I didn’t do a cost per DoW per project, because I’d need to change the base structure a bit, and I don’t really wantto get neck deep in an illustration.)
That’s a bare-boned base, but you should be able to build upon it to achieve the sort of functionality you outline above. Will it be as easy as working in Excel’s two-dimensional matrix? No. Will you be able to accomplish things you can’t do in Excel? Yes. Do you need to accomplish such things? I don’t know; only you can answer that question. Airtable, like any tool, isn’t the right choice for every application; this may be one of them. However, if it is the right choice, Excel isn’t. :winking_face:
Apr 10, 2018 07:34 AM
Your explanation and example helped me tremendously. While it would require a reconstruct of my entire base with a ton of data in it, to reflect those. But, you made me realize, I could simply link to the records in another table. Hide the column if I didn’t want the data to be viewed, but, by linking, I can pull the data through an @rollup and through LENS. Thank you!
I need to stop thinking of this like a spreadsheet. Ten years of Excel will do that to you, right? I just managed to throw together a basic version of my ultimate idea, and if I make any changes, AirTable will automatically adjust any and all values. This is something that used to take me 10 hours a week to do, now, once set up, will be less than five minutes.