I have a looong list of clients*.
Each client has several unique envelopes.
In each envelope there can be multiple unique items.
So the only relation I can see is [one envelope] <- [item A] [item B] [item C].
Please advice me what structure should I build to make it compact, and so I don’t have to create new table for each new client.
*) it’s not bragging 
It’s a great time to have a lot clients. I’d use some combination of a multi-select and a linked record. Here’s the schema I think you’re describing:
Clients (table)
Client Name (single line text)
Google
Apple
Envelopes (linked record)
[Envelope 1] [Envelope 2]
[Envelope 3] [Envelope 1]
Envelope Items (lookup) <- not required, just thought it’d be nice to know
Item A, Item B, Item B, Item C
Item A, Item C, Item A, Item B
Envelopes (table)
Envelope Name (single line text)
Envelope 1
Envelope 2
Envelope 3
Items (multi-select)
[Item A] [Item B]
[Item B] [Item C]
[Item A] [Item C]
Thanks, but the while point is - clients never have the same envelopes or items - they’re unique for every client.
Might not have the right schema for you then. Linked records should point you in the right direction. Best of luck.
Welcome to the Airtable Community!
I’m not 100% sure that I understand you correctly. I don’t quite know what you mean by “envelopes”. But it sounds to me like what you’re trying to do is pretty similar to what I do in the base that I use to track my own clients, projects (= your “envelopes”?) and my activity on each project.
Like your scenario (I think), my system has a number of unique clients, each of whom has unique projects, and each project is then linked to a number of unique activity records. That is, if I spend say 1 hour tomorrow working on something, it can only be linked to one project (even if as often happens the work I do for that one project might accomplish something for another project) and each project is the unique property of one client (even though some projects have the same name).
.
So my CLIENTS < PROJECTS structure looks something like this. My clients are all law firms, so I’ll stick to what I know in my examples.
CLIENT: ABC LLP
PROJECTS: Smith lawsuit, Johnson lawsuit, In re: Wallace, etc.
CLIENT: Xenophon Yang & Zebulon
PROJECTS: Airbags lawsuit, Lawn Poisons, High-Speed Rail
I trust you can visualize that relationship map so far.
.
Now the tricky thing is working in Activity, and this is where my situation might differ from yours. I don’t add clients constantly. A single project for a single law firm can keep me fairly busy for a couple of years. I spend the most time in the base in the Activity table, which I switch to whenever I have spent 30 minutes or 2 hours or whatever working on a particular project and need to record the date, what I did and the time I spent.
When I first created the base in Airtable, I wanted it to work the way I’d built it years ago in FileMaker: I wanted a new Activity record to ask me first for the client name and then to show me a filtered list of projects for that client. Unfortunately, this isn’t easily possible in Airtable.
So what I did was create a formula field in Projects that concatenates the Client name with the Project, and use that field as the Name (primary) field for the table. So for the example data above, I end up with these records in Projects.
ABC LLP/Smith Lawsuit
ABC LLP/Johnson Lawsuit
ABC LLP/In re: Wallace
Xenophon Yang & Zebulon/Airbags lawsuit
Xenophon Yang & Zebulon/Lawn Poisons
Xenophon Yang & Zebulon/High-Speed Rail
Now, I linked Activity records (your “items”) to Projects (your “envelopes”). So when I create a new activity record, I click into the Client/Project field (the one that links to Projects) and see a list like the one immediately above. If I type “x” the list filters immediately to show only the three projects for Xenophon Yang & Zebulon; if I type part of the project name (say, “rail”) it filters to that particular project; and then I can hit enter and create the link.
Here’s a screenshot taken in an example base. This shows the selection list for client-projects. Not same data that I’ve used above but same idea:
And here’s the grid view in Projects, showing (a small portion of) linked Activity records.
This view of the activity is obviously NOT very useful, and in reality, if I want to see activity for a project, I switch to Activity and either switch to a view filtering by that client (or that project) or I create a temporary filter.
.
In that screenshot I also included the linked Rates values. Although I do much of my work on my own, I have colleagues who help me sometimes. They don’t necesssarily charge same rate I charge. I myself don’t change the same rate for all projects or even for the same project all the time. My middle-of-the-night rate is higher than my normal-working-hours rate, for example.
So I have to use a similar approach to handle the complexity of Rates. Here’s a screenshot of grid view in Rates:
So when I create an activity record, after picking the Client/Project value (to link the activity rec to Projects), I click into the next field and select a value that represents the client/project/agent/hourly rate value. Again I can usually just type a letter or two from the project name to narrow down the selection list to one or maybe two pertinent options.
Does this help at all?
William
In many relational database management systems (RDBMSes) it’s possible to link two tables on more than one key. In the FileMaker system that I used to use to track my activity and time and to create my invoices, a record in Activity got linked to a record in Rates when there was a match on both AgentID and ProjectID.
Similar results can be achieved in Airtable, but when a link between two tables has more than one factor or dimension, then you have to combine these fields in a single formula field, and then link (say) the child field to the parent on that formula field.
.
Here’s a diagram showing how the relationship is created in an RDBMS like FileMaker (and many others). Note that the fields here are traditional key or ID fields (which we don’t see directly in Airtable).
The yellow highlighting shows that you handle the multiple factors in the definition of the relationship, that is, in the logical space between the two tables.
.
And here’s how you do this in Airtable:
Again the yellow highlighting shows where the multiple factors are handled–not in the relationship itself, but in field in one of the tables you are relating.
.
Having worked in more traditional RDBMSes than Airtable for decades, it took me a little while to get used to this. Now it seems fairly natural.
William
