Ascot Covid-19 Resource Directories
Introduction
In March, the UK issued mitigation measures to slow down the spread of the novel coronavirus, Covid-19. Social-distancing soon changed to isolating at home in a lock-down.
Paola noticed that members of a local Facebook group, Ascot People, were regularly asking about about food deliveries because supermarket delivery slots were filling ip quickly.
She saw that there was a need for directories of information to resources to answer regularly asked questions.
Paola looked for a framework on which directories with different data structures could be built.
She used Airtable - a spreadsheet-database hybrid designed for cloud collaboration by businesses - to create a set of directories.
The directories were created in a Workspace, a base for each. The public View of a directory was a specific View of a Table. These Views were then presented in a dedicated web site.
Credits
Thank you to:
- Jamie K, for moral support and gathering information to test data structures in Google Sheets
- Francois Jordaan, for CSS expertise to style the directory panels on the web site
- Helen Kirkby, for the quick design of logos and a cover image
- Honor Abell, Ascot People admin, who championed the directories
Directories
The directories include (at the time of writing):
- Food & meals - where people can get food (fresh, frozen, ready meals) or take-aways by shopping, collection or delivery
- I need help - local and national services to help those in need
- Help for NHS staff - products and services available to local key workers for free or at a discount
- I'd like to help - local and national organisations who need volunteers or donations
- Health & wellbeing - offline services now offered online to keep people health in body and mind
- Gardening supplies - sources of gardening products, such as seeds and compost
- Useful services - other local companies offering remote services useful during lock-down
- Activities - links and suggestions of things to do alone or as a family
Paola later added a Bin collection zones directory.
Anatomy of the directory
Admin
The Views and Forms in bold were what the public see.
- Airtable workspace
- Food & Meals Base
- Table: Food & Meals
- View: Ascot Food & Meals
- form: Food & Meals
- View: Moderator
- View: Moderator (oldest first)
- Table: Types
- Table: Towns
- Table: Status
- Table: Food & Meals
- Food & Meals Base
- Airtable workspace
- Food & Meals Base
- I need help Base
- Table: I need help
- View: I need help
- form: Help available for those in need
- View: Moderator
- View: Moderator (oldest first)
- Table: Types
- Table: Who for
- Table: Regions
- Table Status
- Table: I need help
+ similar structures for the other 7 Bases (directories)
Public
- home page
- 9 x directory web page
- embedded public Table View (in a Base)
- embedded form (of the same Table)
- 9 x directory web page
Sharing
Airtable allows Workspaces to be shared, or specific Table Views, or Table Forms.
It is not possible to designate some Views within Bases as public and then share a Workspace with a single link.
In Airtable, each Base (directory) View and Form has its own shareable link or embed code.
Paola therefore created a simple web site in Amazon S3 so all that the directories were at one shareable location.
Facebook comment with web site preview
Directory web page
A unique colour and icon was chosen for each directory. These were used on the Airtable Views, web page directory headings and site navigation.
Each directory was embedded on a web page along with the submission form.
When visitors sort and filter an Airtable Table, it does not affect what other people see.
Table Views
The first Table in every Base was the main directory data. The other Tables were for reference data.
For the main Table, Paola set up different views:
1) The public View it doesn't include the status or message fields. The last column shows the date-time when the record was last updated. The Table was filtered to only show records where the a) status is published and b) non-empty name and category fields.
2) The submission form for the Table includes a message field where people can write a note to the moderator. The form doesn't include the status or updated fields.
3) The moderator View includes records of every status, and grouped by status (which was why there were numbered). The status and message field were shown near the start of rows. New entries have a blank status field and so appear at the top of this View.
4) The second moderator View shows records be age, oldest first. This shows all published records in descending order of age by days (a calculated field based on the updated field). This View was for going through older records to recheck the details. Especially in the food sector, some companies suspend their services.
Moderator View
When someone uses the form to submit a new entry for the directory, it appears in the moderator View without a status and at the top because the View was sorted by the status field.
If the person who submitted the form wrote a message to the moderator, it will appear near the start of the row. For example, if they want to suggest a new category.
Unpublished records
Some records were unpublished because they were out of date or under review.
There was a Table just for the possible status values. Directory records link to these status values.
The status values were prefixed with a number so that they could be sorted in a logical order.
Types and towns
There were also Tables for types and towns in the food Base.
These extra Tables differ depending on the directory.
Records by age
The moderator View showing records by age only shows records that were published or under review.
There was a calculated field showing the updated date by the number of days as well as the message field.
Whereas the message field was used by people submitting new entries. After a record was published or while it was under review, Paola uses the message field to make notes about the record, or to force a change to the updated field if a record has been rechecked with no other fields updated.
Suggestion form
Each directory has its own form to suggest new entries.
Forms were embedded on the directory's web page.
Airtable has given Pro accounts for a year to projects related to Covid-19.
For these directories, this has meant Paola could add coloured select lists and graphics to forms. The embedded form does not use graphics, though.
Admin View of the form
The submission forms were set up to mail responses to a dedicated gmail address which forwards mail to Paola. The form redisplays in case the person wanted to submit more than one entry.
Responsive
The web site has been styled to be responsive to smaller screens.
On small mobile screens, the header graphic and heading icons don't display.
Text sizes were also adjusted so that text isn't very big or very small.
Bin collections
During Covid-19 lock-down, the recycling and waste bins in the borough were being collected alternate weeks. The borough published information on the official web site with a downloadable calendar.
The calendar refers to orange and purple zones. To find out your zone, people click on a link depending on the usual collection day and find their street in a list.
Paola created a directory by aggregating the street lists.
Airtables come with a search tool and so it is easy to find values.
Airtable Universe
Paola published the Food directory on Airtable Universe so that people could copy the Base to use as a template.