Field groups, i.e. sub-fields

[Natalie_Bates]

I am convinced that Airtable is the only thing I will ever need for all the complex organization I do for basically everything in my life, but there is one thing that would help immensely: the ability to group fields, like in the sense that a particular field would be subdivided into two different fields that always occur together.

Here is an example for something frivolous for the sake of being easy to understand:

Airtable can be an effective wiki, so I’ve been using it to keep track of some stuff in a video game. In the video game, there are certain Actions that can result in items, which can vary, and those items come in varying quantities. Say there’s an Action that sometimes gives me 3 Knives, but other times it gives me 8 Flowers.

There’s not really any way for me to record that right now.

For one thing, you can’t have multiple values for a Number field. (I’ve already bumped the request to have multiple values for any field, not just linked records and multiple selects and attachments.) And I really do want it to be a number field, because I’d like to do calculations with it.

But let’s say I give up on the calculations capability and just enter numbers in a Multiple Select field called Item Quantity. And then I have another field where I link to the Item. So I enter the Action I described at the beginning – let’s say it’s “Give the old man a penny.” Sometimes when I give him a penny, he gives me 3 Knives, so I enter “3” in the Item Quantity, and link to the Item record Knives. But sometimes the old man gives me 8 Flowers instead, because he’s supposed to be batty and fickle and it’s luck-based or whatever; this is all made up, but tons of games have changing luck-based or other-based outcomes. Okay, so I enter “8” in the Item Quantity as another multiple select option, and also link to the Item record Flowers. Then I keep playing.

I hit a point in the game where I need to stockpile Flowers, so I go look up all the Actions that result in Flowers. After all, this is exactly why I bothered to keep a personal wiki. I find “Give the old man a penny.” Except… hmm… does he have a chance to give 3 Flowers, or 8 Flowers? It makes a difference because if it’s 3 Flowers, it’s not worth spending an Action point in the game; there are other actions in my Airtable that show I can get more than 3 Flowers per Action point, and 8 Flowers would be the best return on an Action point I could get. But I don’t remember which is which; I made the Airtable so I wouldn’t have to memorize stuff like that. I then have to waste time and points going around the game trying these things and then simply memorizing which are my best options, which defeats the point of recording the data in the first place.

Basically, if a field could have two or more components to it, I would not have this problem. I could have a field called Outcome with the subcomponents Item Quantity (which would be a number field) and Item (which would be a linked record, or a short text for some people’s uses, etc).

The only way to do this now is to have a separate table for each action, with separate records for each outcome, so they don’t share fields for Item Quantity and Item record links and it’s very clear what you get in what quantities. But there are thousands of actions in the game, and most only have two or three possible outcomes. Making that many tables is simply not feasible because it would become excessively difficult to find a table whenever I need to edit it, and I’m not even going to be editing the vast majority of the tables I’ve made in that case. All the tables would also have long names that would get truncated. I’d also have to keep a template table around to duplicate with all the necessary fields configured, and then constantly duplicate it, which is just annoying.

There are more complex ways I’d like to use this for less frivolous projects as well, this just took the least specialized knowledge to explain. I am working on a big biochem project where this functionality would be so helpful. I once tried to do this in Drupal, where people had programmed a handful of ways to do field groups, but Airtable is less buggy and much easier to use than Drupal and I can enter things lightning fast from my phone. I point this out because it shows there is demand for field groups for similar uses on another platform, plus I know Airtable can be used as a CMS and I really think it has the potential to blow Drupal out of the water for some kinds of uses, but the lack of field groups makes some uses infeasible or impossible.

[Mike_Eovino]

Natalie - Can you have a single child table for actions with an action type column, rather than having a separate child table for each type of action?

[Natalie_Bates]

Hmm, either I’m not understanding what you’re suggesting or you’ve misinterpreted something… I don’t understand what an “action type column” would do to address the sort of issue I laid out or what exactly you mean by that. I’m taking it to mean some column where I can label actions as some particular kind of action, but I don’t see how that would engage with any of the issues I just laid out, so maybe that’s not what you meant?

To be completely clear, what I have in place now, and what does not work for my purposes, is a single table of actions with columns for things that can be gained by them. The reason this does not work is because fields cannot be grouped, e.g. the quantity field in my example would contain the values 3 and 8, and the item field contains the values Flowers and Knives. I only ever get 3 Knives or 8 Flowers, never 8 Knives and 3 Flowers, but if I refer back to the data I have no way of knowing if I can expect to get 3 or 8 Flowers by performing the action, or 3 or 8 Knives, etc. The number must be linked to the item itself, and it should not link to the other item or allow for ambiguous interpretations.

I have not actually made thousands of tables, one per each action, and to be clear I have no intention of doing so. I’m saying that if I wanted this functionality, the only way to accomplish it would be with an entire table for each action, which is infeasible.

Anyway, I don’t want to get too caught up in the specifics of this specific game-based usage because like I said, it was a frivolous example that I used so it would be simpler to follow, and the real thing I want to use it for it a biochem thing that would be confusing for people here to follow because it requires specialized knowledge to understand why alternatives (probably) won’t work. For what it’s worth, I can’t think of any way a single table could do for that project either, which is the point of this request. I feel like I must have been very unclear?

[Katherine_Duh]

Hi Natalie,

I believe what Mike is suggesting is that there is a many-to-many relationship between actions and items: each action can potentially result in a variety of items, and each item can be obtained through a variety of actions. In most databases, many-to-many relationships are represented through a junction table, which is a table that exists at the intersection of two tables and holds information about the relationships between two lists of entities.

In Airtable, you don’t necessarily have to form a junction table to represent a many-to-many relationship if you don’t need to store metadata about the relationship between the two entities. But in your hypothetical video game example, the quantity of the item produced by an action is a type of metadata about the relationship between the item and the action. Since you need to store this metadata, you would need to create a junction table in order to represent all possible outcomes with different quantities.

Let me show you what I mean. You need a table of potential actions/quests:

And a table of item types:

The junction table between the Actions and Items table gives each possible outcome for an action its own record. A Quantity field stores the metadata for the relationship between each item and each action (the number of items produced by each action). Each record is named according to a formula which incorporates the action and item types, and the quantity.

If you wanted to find the most efficient actions for maximizing the amount of flowers, you could look at this Outputs table and apply some filters.

If you were feeling really fancy, you could even incorporate the percentage chance of each outcome into the Outputs table (since that would also be a form of metadata about the relationship between an action and an item). Well, it looks like even though delivering the troll sandwiches will give me more flowers, it also seems like there’s a low percentage chance of that happening, and giving the old man a bunch of pennies would be more efficient for resource grinding.

Now, as you’ve said, this is a hypothetical example, but the basic principle of creating a junction table may still be applicable to whatever your real-life situation is. If you have more specific questions on how to craft a junction table in a many-to-many relationship, I’d be happy to help.

[Natalie_Bates]

Hello, thank you so much for such a helpful and detailed response! I had constructed something sort of similar for the science project I’m working on, but your example made me realize I could change some things about it. I will mess with it for a while and if it seems like a junction table is unworkable I will come back and explain the issues I’m having, but for now I have an inkling that I could make it work. I really appreciate your advice!

[Mike_Eovino]

@Katherine_Duh - The junction (or cross-reference - what I’ve always called it) table is exactly what I was thinking. However, you have created an amazing explanation of a many-to-many relationship. What you wrote should be a tutorial on many-to-many relationships here on Airtable.com.

[Natalie_Bates]

I agree with Mike! It was very helpful. I think a lot of people who are coming to Airtable without a background in databases would find it quite useful because it’s not entirely intuitive to come up with the idea on one’s own. I had something similar but didn’t take it all the way because it hadn’t occurred to me; when a person is trying to organize many things that reference each other like I am for my science project, it’s easy to get things jumbled in one’s mind trying to think of what would work best and never think of something like a junction table. But I had read the Airtable guides and it would be a great thing to include in them!

I think it could also convince people to try Airtable if they happen across it in the guide and it suits their needs. I read the Airtable guides to see if I wanted to even fuss with it because I was so used to database software being too rigid for my needs. I got the inkling I could make it work from the guides and knew I wanted to at least use Airtable for a bunch of other stuff anyway. But if I’d seen a junction table guide I would have gotten very excited because it would have stood out to me as exactly the solution I needed. I think some people are probably like me and look over the guides with a particular use in mind before trying Airtable.

Thanks so much for writing it up!

[Katherine_Duh]

I’ve actually been working on writing up a tutorial for many-to-many relationships—based on your feedback it sounds like it’d be better to put it out sooner rather than later!

[Hashim_Warren]

I run a coding bootcamp and I’m evaluating Airtable to help manage student records.

Oiginally I considered having two tables - students and sessions. But each session is one of two classes in one of two locations. Now that I’ve read about junction tables it makes more sense for.me to have:

Students, sessions, classes/locations

Every session has a unique date, different teachers, and even a slightly.different curriculum. I think it’s a good candidate for a junction table.