Help please. Newbie here

Roman_Repic · ‎Feb 25, 2020

Hello Airtable community. Newbie here. I recently come across the Airtable app on android and I am hooked.
I am a beekeeper and I want to track progress of my bees using Airtable. I have multiple bee houses with multiple beehives in them. I think that Airtable will be great for that but it has some quirks that I hope I will be able to solve.
I will have multiple bases. One for each beehouse. And there will be approximately 60 tables in each base. One table per hive. Because I can’t share views between tables I will create a view in the first table and then I will duplicate this table 59 times.

I have a few quirks to solve. The biggest is. Is it possible to have filters for bases? For instance. If one or more hives have some problems to solve it will be easy to set the filter to the whole base instead of search for each problem in the table individually.

It will be awesome if I can export the whole base at once. Can it be done?

Have anyone had problems with my issues?

Geoff_Bernstein · ‎Feb 25, 2020

What is the data you want for each beehouse? Having one table for each beehouse seems excessive. Depending on the data you are trying to keep, you might be able to have each beehouse as a record.

kuovonne · ‎Feb 25, 2020

May I gently suggest that you look at some of the base templates to get a better feel for how database design works? It sounds like you are used to having multiple spreadsheets with multiple tabs per spreadsheet. But Airtable is a database, and it sometimes it is appropriate to structure data a bit differently. In general, relational database design tries to minimize duplicating information.

You may find that you will have one table listing beehouses, and another table for hives. Then you would have a one-to-many relationship between the beehouses and the beehives.

It will be a bit different from how you are used to seeing things because the information for all the hives will be in one giant table, with one record/row per hive. You can filter and group the records to view information on individual beehouses.

Airtable’s ability to filter, sort, and group records in a single table/tab is very powerful. You can also propagate information across the relationships in tables with lookups and rollups. For example, if there is a problem with an individual beehive, you can propagate that information up to the beehouse, and have a filtered view that shows only beehouses with problems.

However, all of this depends on how your data is structured, which depends on what the data is.

Roman_Repic · ‎Feb 25, 2020

I want to use Tables for some sort of report card. I agree I am a newbie with databases and I have an analog point of view.
I agree a lot of problems will be solved if I use just one base and one table. And then use filters when I need a report for each particular hive.
Thank you for answers. I will try this out.

victor_alumanah · ‎Feb 26, 2020

The response from @kuovonne describes your solution in a nutshell, and would be the ideal way to build it. As mentioned, if you are used to only a spreadsheet point of view, databases can be a bit confusing at first glance. There is a lot of info on the internet with regards to building databases that you can apply to your case. A couple of good pointers for you though.

Use one of the templates already on airtable, there are lots of really good ones.
If building yourself, maybe build it on paper first. sometimes pen and paper is a faster way to get a feel of what tables you really need before doing it on Airtable.
If you get stuck of things like formulas, filters e.t.c, Come and search on Airtable because chances are someone else had the same issue and a solution has been posted
Try and have it all in 1 Base, it will save you a ton of time going forward, the idea behind databases, is to store and manage information which in turn provides insight. If you build multiple database for this , you may find you spend a lot of your time managing databases… :slightly_smiling_face:

feel free to message me if you want any assistance with getting the design off the ground.

Roman_Repic · ‎Feb 26, 2020

You are absolutely right @victor_alumanah. I use a spreadsheet quite a lot so I am familiar with formulas and how things work. I will start to use Airtable in about a month so I have plenty of time to plan this project.

I will use this forum for debate and suggestions so anyone who will have similar problems can learn from it.

Just one question for now. I can set up a primary field as a barcode. Can I have multiple entries under same barcode or should the primary field be unique for each row?

Geoff_Bernstein · ‎Feb 26, 2020

Each primary field has to be unique. Any reason why you want the barcode? I’m not saying its a bad idea to use a barcode in some fashion but why do you think you need one?
Also when you say you want a report card, what does that report card show? What is the data you want to be displayed and recorded?

victor_alumanah · ‎Feb 26, 2020

As @Geoff_Bernstein mentioned, each primary field should be unique, otherwise things can get messy pretty quickly. The main reason it needs to be unique is because of relationships to other tables, you need to be able to identify each record uniquely, other wise you get duplicate data appearing in places. If you are going to enter a barcode multiple time then I would suggest a normal field. one way round it would be to combine the Barcode with another attribute, this would allow you to use it multiple times while still being unique due to the other attribute.

kuovonne · ‎Feb 26, 2020

In general, each row in table should be unique, and thus we expect each primary field to be unique. However, sometimes what makes a row unique is actually a combination of fields.

For example, suppose you are collecting daily data for a set of beehives, and each beehive has its own barcode. You want to keep historic records for all the data you collect. The identifying features of each record is a combination of both the barcode and the date.

In a case like this, the primary field should be a formula field that combines the information for both the barcode and the date.

{barcode} & " on " & DATETIME_FORMAT({date}, 'YYYY-MM-DD')

Notice that I suggest formatting the date with the year first, then the month, then the day. That is to make sorting the records in sequential order easier.

Zollie · ‎Feb 26, 2020

Here’s the documentation for filters. And an article on exporting to CSV. Searching the rest of the guide will likely provide answers for any future ‘basic functionality’ questions.

Quick thoughts on your architecture, unless your data is vastly different from one hive to the next, you might be better off just creating one table that contains a couple columns that dictate which hive a given row refers to. Here are a couple examples:

Table Name: Bee Hives

Bee House (integer)
2
Bee Hive (integer)
3

Then you could create views with the properly filtered bee house or group the list by Bee House number.

Or you could leverage linked records and separate the concept of house data and hive data.

Bee Houses
Name (single line text)
House1
Hives (linked records)
Hive1, Hive2, Hive3 …

Bee Hives
Name (single line text)
Hive1
Hives (linked records)
House1