Jul 28, 2020 11:46 AM
Hi everyone,
We do assessments for our clients, and currently use Google Sheets to track the assessments. We might evaluate 150 things for a client, and we rate each of them with a “pass” or a “fail.”
The typical sheet has the following columns:
ID, Category, Description, Pass/Fail, Notes
This works fine on a client-by-client basis. Where we run into trouble, though, is that the list of 150 requirements changes.
What we’re hoping to find a way to do (and it would be great if Airtable is that solution) is to have a master list of the 150 requirements, and then have the client-by-client evaluations stored in separate Bases. Then, if the master list changes, say, from 150 to 155 requirements, we’d love to have any easy way to look across all of the past assessments to see which ones are missing the 5 new ones.
Is there a way to do this in Airtable? It seems like the templates might not be a fit, as it seems they lose the logical connection to the original template when an instance is created.
Thanks in advance!
Jul 29, 2020 06:41 AM
I am new to AT and not into scripting so what I am about to say might not be much if any use.
It is not possible to link bases. From what you say, I wouldn’t think that necessary in any event. One base with 2 tables and different views should do it. Different tables have different fields, Different views can each have their own parameters, including the hiding of fields.
Table 1 would be the master list of the 150 requirements. Use the primary field for the requirement description. to the right of the primary field, add one or more fields depending upon whether each requirement keyword should have its own field or ideally could manage with just one field or maybe a few with keywords (customise field type, choose multiple select, then create pre-defined options or add the options as you go - options can be sorted abc, also renamed whenever); a field for each requirement would need some thought. if you are going to have several fields I have found it is best to have separate keywords appropriate to each of the separate fields.
How many different keywords in a field comes into their own with group. If you have more than one keyword in a field then when you group the keywords for the selection to be grouped all the keywords would appear. I have found that by limiting the keyword in a field to one or two or three keywords at most the group layout is easier on the eye. Used with a filter the group becomes presentable. In my law library, for example, I have separate fields (all containing keywords) for the year of the case, keywords for the case details, the court, my view and sorting ref. For example, to add James v Smith [2020] CA , I enter James v Smith [2020] in the case description field, CA keyword in the Court field, C in field I use for a view, J, JA for a Group, and some other fields for specific elements. In the view I have for cases, I use filter to exclude anything in the field that is not C - hence Act is excluded - , in the group layout all J cases display sorted alphabetically J JA contains all J JA, then J JI, then J JO. Where a case name starts with British. I could enter keywords B and British and group would display all cases with that ref. Although It might be possible to extract sort parameters from the case name I find that separate fields enables me to control the case name.
As and when the requirements change to 155, per your example, it depends upon the requirement whether the extra 5 could added to the one field or a pre-existing field or would need new fields.
in table 2, use the primary field for the client’s name or your client id. The primary field can be sorted. Add a new field to the right of the primary field and in customise type select Lookup. In the Lookup dialogue, link to that new field in table 1. Then when you add a new record in table 2, you can allocate select a keyword from that field in Table 1.
In different tables and views, fields can be hidden. So you could isolate Current and at a glance check the Past.
Using your example, the primary field in Table 1 could be the description of the requirement.
Next field name creation date (customise to create date), next field name ‘when’ customise multiple select, pre-defined options: current, past. Next field modification date. next field: notes, long text.
In Table 2, primary field client id, next field look up to the desired field in table 1, next field ditto and so on.
Alternatively you could put everything in one table with however many field you want and have separate views that use filters and keywords to organise the information. The snag with one table is that each record in the primary field must have a unique description otherwise it would be difficult to find anything easily.
I daresay my convoluted approach to organising information might not suit everyone but I’d much rather organise info to suit my way of working than learn how to save time or automate only to find I’d probably still have to check each record manually because the save time / automate process cannot cope with non-standard entries.
Jul 29, 2020 10:28 AM
The simple way…
Have a table with Client and Assessment… The Assessment table has a column for each assessment. Include a “complete” flag and a link to the client table.
If you later added 5 more assessment columns it would be very easy to create a view or filter to see which assessments are marked complete where the new 5 columns are empty.
For workflow you could create an airtable form view where staff simply goes to the web form and clicks yes and no and complete at the end and then submits the form to get new data into your base.