A friend recommended this tool to me because of its adaptability to create data collections without the need for a custom tool.
I have the following doubts, after I checked the airtable help tool
create formula in the primary field.
I took the sales crm template and just want to simplify it, only the relationships it brings are not what I need and after reviewing the help I still have problems.
the idea is the following from the contact table to relate it to accounts just for one id (i don’t know if the autonumber function will work)
The idea that if you can’t use autonumber, you could use email as your unique identifier.
Then, the connection to the account table (which I will convert into services) will link it to the users’ email (one-to-one relationship) (as unique identifier) (the idea is that for each user you can have a one-to-many relationship; so, for each user by x amount of services).
and finally, the table of opportunities (I’m going to rename it as transactions to register every transaction that every client makes on every service). (the relation would be from several to several).
2)I have the following questions you can make conversions of specific objects for example in the section of registration of services for each client each service and each plan is in “Single select field” and I do not know if it is possible with some formula to convert from a selected value to numerical to add or subtract according to other format fields "Single select field
I don’t know about operators and functions in formulas, so I’m asking what I should use.
After I make the change of relations, I will use zapier to integrate it with type forms and wave. so I can link a form with the adapted crm and then generate an invoice on each new registered client.
Thanks in advance for any suggestion on how to use the formulas.
@faiberts: Welcome to the Airtable community. I think I understand what you’re trying to do, but I’m not sure. Forgive me if I got it wrong. But if I’m understanding you correctly, this ought to do the trick for you.
.
Getting unique value for each record/row
If you’re just trying to come up with a unique ID value for every record in a table, that’s not too hard. Try using the RECORD_ID() function as the formula for a Formula field. That should do the trick. If you would like different type of unique value, let me know and I can suggest other formulas that should do the trick but will require a little more work.
Now one of Airtable’s special features is that it makes it unnecessary for users to worry about primary and foreign keys. In Airtable, if you want to link line items for the Acme account to the Acme record in the Accounts table, you just click on the linked record field in the LineItems table and select “Acme”. Airtable takes care of everything else. Of course the key values are there but you don’t have to see them.
.
How to get and use Airtable’s hidden key values in related tables
But sometimes, some of us do need to see those values, because we need to take our relational data outside of Airtable and for that, we need those primary and foreign key values, so we can relate records properly in another system. Once again this is easy. Remember, under the hood in Airtable, the key values are there. So all you need to do is reveal them, using the function I mentioned above. So, say you have two tables: Projects and Tasks. Relationship between them is one-to-many (one Project can have many tasks, but a task can be linked to only one project). Do this:
In Tasks, create a field/column named Record_ID. Make it a formula field and use the formula I mentioned above: RECORD_ID(). Be sure to leave the space between the parentheses blank.
Go over to Projects and do the very same thing: Create a formula-type field named Record_ID using the RECORD_ID() formula.
Finally, go back to Tasks and create a new column that could be called “Project_Record_ID”. This should be a lookup column that gets the value of the Record_ID column over in the Projects table. Like this:
When you save that, you’ll see that every row in the Tasks table has a distinct value in the table’s own Record_ID column, but if you filter the tasks on a single project, you’ll see they all have the same value in Project_Record_ID:
.
Using these values outside Airtable
Finally, if you need to take your data outside Airtable, into another relational database management system (like, say, FileMaker or AppSheet), just export each table to CSV. Make sure when you export Projects (sticking with example above) you export the Record_ID column; and when you export Tasks, be sure to export both Record_ID and Project_Record_ID.
@faiberts: I have one caveat that I thought it would be better to put in a separate message.
The technique I described above works without any extra effort only if you use it for one-way exports from Airtable. That is, if you need to dump data from Airtable into another system for some kind of external processing but you do not need to bring updated records back into Airtable.
But if you wish to create new records outside Airtable and bring them back in, you may run into a problem with your ID values. I don’t know how Airtable calculates a result for the Record_ID() function and so I haven’t been able to recreate it outside Airtable. Instead, my external systems (mostly in FileMaker) create primary keys using a long UUID function.
I might also mention another problem: how to bring updated values back into Airtable and update the correct records. Fortunately, for this, there’s a block: the CSV Import block. Looks like that would do the trick.
I strongly urge you not to use email addresses as key values. If you absolutely have to, then you have to. But you probably–almost certainly–don’t have to, even if you think you do, so don’t.
A key value in a relational database should always be
unique within its table
immutable
That second requirement–immutability–is as important as the first. To make sure that ID values are immutable they need to be persistent and meaningless.
Persistent means that the value originally assigned to a row (record) stays with it no matter where it goes, when it goes in the database, out of the database, back into the database.
Immutable means that a key value must have absolutely no meaning other than its use as key value.
If you use email addresses or government ID values as keys, and you discover that you entered somebody’s government ID wrong or you misspelled their email, or the person changes their email address, well, you’ve now got an ugly problem on your hands.
Thanks for your well documented help, it works perfectly but then I saw what you said about updating the variant registry (tasks following the example you shared with me).
the idea in my case is to have a table of only clients and for each client X amount of services (relation of one to several) in the table of services those don’t change; that’s why the idea of creating a table that calls transactions (serious relation of a service to several transactions; from one to several). This table is where updates (prices, status, payments, etc) occur.
If I share your recommendation of not using email, I imagine that you can have a column with some of them working to generate a code based on a format (I don’t know if it’s possible in airtable, through formulas).
I have reviewed crm as a basic zoho and it is too much for just what I need and I do not want to invest time developing something I am with little time to get this done.
The idea I have is to capture the data through a typeforms form and through zapier connect it to airtable to create or update the records.
@faiberts: The short answer to your question is Yes. I’m not entirely sure what you mean by “code”, but yes, you can create a formula column in Airtable and populate it using a formula that will reliably generate a unique value from other values.
Beyond that I’m not able to suggest the design of your system for you, in any detail. But I can address a couple of general questions, and perhaps these comments will suggest to you a way to solve your problem. I apologize in advance for the length of the response below. It’s an important topic, might be useful to somebody else using Airtable, and I had some selfish reasons for writing it.
Good luck,
William
p.s. If you’d like help with the formula, feel free to send me a direct message.
.
“Match columns” vs Key columns
In relational database systems, data in different tables is always linked when a value in a column in one table precisely matches a corresponding value in a column in the other table. In some platforms these are called “match fields” (“match columns”). Most of the time database developers like me call these “key” fields. But there may be a useful distinction between “match columns” and “key columns”.
As I said, keys should be unique (within their table) and immutable; and I went on to say that one of the ways you make a key value immutable is by making it meaningless outside the database. Properly generated keys tend to look like this: “UmNygMQcY1twgeD9r”.
The problem is, precisely because good key values are meaningless to us humans, it’s rather awkward for us to use them. We don’t like to look at values like UmNygMQcY1twgeD9r, we find it extremely hard to pick UmNygMQcY1twgeD9r out of a list that also includes rMqkwlLC5eMx77Hsp etc. Your eyes are probably getting blurry reading this paragraph. :slightly_smiling_face:
This is why Airtable hides these values from us. Instead, in Airtable we get what I would call match fields. When a new order comes in, we switch to a view of the Orders table, click into the Accounts column (which is a Link to Another Table column) and Airtable shows us a list of known accounts, by name, so it’s easy for us to pick the right one. Behind the scenes, of course, meaningless key values are being used to link the order to the account, but we don’t see any of that.
So the match field is the meaningful token that allows us human beings to use the base; but invisible, meaningless key values are used to make the actual links between tables. And because that’s the case, if we change the name of the Acme account from Acme Inc to Acme Industries LLP, none of the links between Acme and its orders get broken. The invisible, meaningless IDs did not change.
.
Match values not always unique!
Now, here’s where it gets tricky. Primary keys (i.e. the key values in the One tables) must be unique. But the column we most naturally use as a match column sometimes will include duplicates, or more precisely, not “duplicates” but rather, values that are not unique. (This is an important distinction. The result of 2 + 5 is the same as the result of 4 + 3, but the first 7 is not a “duplicate” of the second 7. They’re just both 7s.)
I build systems in which the top level table in the hierarchy is almost always a list of people–typically, hundreds or thousands of people. I see a lot of this sort of thing:
Candace Moreno
Carlos Garcia
Carlos Garcia
Catherine Lawrence
Sometimes we can determine that the first Carlos Garcia is actually Carlos Garcia, Sr., and the second one is Carlos Garcia, Jr. That helps. But we can’t always do this. Or perhaps we have middle names–but often we don’t. Sometimes you just have two or more records with identical names.
Once we are able to figure out that it’s the first Carlos Garcia that we want, then the link between him and (say) the rows in the Orders table that belong to him will be made using the invisible key values, and everything is fine from that point on. But Which Carlos Garcia? is a question that occurs a lot, in Airtable and in other database systems as well. It’s a big deal for example when you Google a name like “John Adams”, or when you look for a name in the huge database known as Wikipedia. See for example this page which attempts to “disambiguate” the name John Adams.
So we tend to create meaningful distinguishers to help us keep things apart. In my systems I’ll often write a calc that puts the first name, middle name, last name, name suffix and birth date into a single column. So users can see something like this:
Carlos M Garcia (4/1/1980)
Carlos Garcia, Sr. (8/18/1968)
These are easy calcs to write. In Airtable, we’ll often write a calc like this and use it as the primary column for the table. If you’re dealing with a table where every record = a person, and the name and birthdate are not reliably distinct, you might have to add some other column like Zip Code or drivers license number or whatever you’ve got. Then when you link to that person’s record from a Many table, in Airtable the “Link to Another Table” will use that calculated column as the match column.
I suspect this may be exactly what you need to do in your system. Create a primary column that is a formula field containing several values that give you a unique result.
.
But the key values are always the most important thing!
Still, it’s always the hidden key values that do the real linking. So if your base includes, say, three or four or five tables (not just two), and you’re implementing the suggestions I gave you earlier about using the RECORD_ID() function to get primary key values out into the open, then you just need to do the same thing for all the tables in your base. In other words, in every table, a field named Record_ID that gets that row’s primary key value, and also in every Many or “Child” table, a field named something like Parent_Record_ID that looks up the Record_ID value from the linked parent record.
Then if you export your multi-table base’s data to another system, you make sure to export those key columns as well.
.
Too much work?
Yeah, it’s a little bit of work. But you don’t have to worry about the key columns at all if your system is never going to leave Airtable. And while we often do have to create those “disambiguated” primary column calculations, they not too hard.
The truth is, Airtable isn’t really all that easy, once you go beyond grocery lists. But the same can be said of every database system I know, and I’m familar with quite a few.
Thank you for your kind and didactic help, especially for someone who does not handle the tool very much. @WilliamPorterTech
After thinking about what I recommend, I will tell you a couple of things that I put in my initial message.
When I mentioned creating a code, I meant a field (column) with a value that I manually set, regardless of the value that results in the row inside the default primary field column (regardless of the value resulting from using record_ID() ).
I thought, initially to create a non-primary field, which would be that field “code” to make a unique code to try to group the 2 tables that I plan to create only to relate clients with another service table.
However, now after seeing the integrations with other elements using zapier, maybe the idea becomes exponentially more complicated.
i just want airtable to allow me to do the collection of clients and services so i don’t have to dedicate myself to the task of adapting a crm for such a simple activity.
I’m a system developer so I don’t want to spend as much time creating something from scratch as integrating various services for something so simple.
