Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 15, 2019 08:40 AM
Hello,
I have a table of People. Each person is linked to a Property via a lookup field. I want to add a field to the People table that assigns them a value based on which Property they’re linked to and when their Date Created is. So for example: for all the People in the table who are linked to 123 Main St, I want the first (by Date Created) 100 records to have the value of 1, the next 100 People records to have the value of 2, and so on.
Does anyone have any ideas for how to go about doing this?
Thanks so much!
Solved! Go to Solution.
Aug 17, 2019 11:37 AM
I got something that works, but making the system dependent upon a date is potentially tricky. If your {Date Created}
field is Airtable’s created time type (or you plan on using the CREATED_TIME
function), then you need to be careful not to create several records really quickly; i.e. pre-making a bunch of “blanks” that you plan to fill in later. This would only be an issue around each of the 100-record thresholds, but it’s something to be aware of at any rate.
Here’s the starting point. (FWIW, I made a formula for the name field using pieces of the record ID, just to get something unique in there).
I then added a field named {Date Created}
, using this formula:
DATETIME_FORMAT(CREATED_TIME(), "x") & "|"
…which gave me this:
Notice that several of the timestamps are the same. That’s because of the quick record creation issue I mentioned above. If Airtable supported storing dates with millisecond accuracy, this wouldn’t be an issue. As it stands, it’s only accurate to the second. The bar on the end helps to keep individual timestamps separate in later stages.
In the [Properties]
table, I rolled up this new field using ARRAYJOIN(values, "")
as the aggregation function.
Back in the [People]
table, I added a lookup field to pull in this rollup:
These fields are hidden from here on out. An {Index}
formula field is added next to find the position of the {Date Created}
string inside the rollup, then add 100 to it and turn it into a string.
((FIND({Date Created}, {Rollup} & "") - 1) / LEN({Date Created})) + 100 & ""
Again, notice the duplicate numbers due to matching creation times.
If you won’t have more than 999 people tied to a given property, you can just wrap a LEFT function around that formula to grab the leftmost digit, and you’re done. However, if you think there might be more than 999, then this next formula takes that index, zero-pads the front of it if necessary, and grabs the value of the left two digits:
VALUE(LEFT(REPT("0", 4-LEN({Index})) & {Index}, 2))
These final screenshots show the transition from the first hundred to the second, and the second to the third, using the base system (max 999 people per property) with the extra fields hidden for clarity. I’ve also grouped the records by property, but grouping doesn’t affect the numbering sequence. It just makes the transition more clear for this first property
In addition to a reminder about record limit issues tied to your account type (1200 per base for free, 5000 for Plus, etc), there are a couple things to keep in mind regarding this setup:
[People]
table increases, the responsiveness of the table will slow down due to the size of that massive rollup string growing ever larger.Is this the most efficient solution? Not likely, but it can be done completely within Airtable. If you need something more hardy, you might consider hiring someone who can code an API-driven solution.
Aug 17, 2019 11:37 AM
I got something that works, but making the system dependent upon a date is potentially tricky. If your {Date Created}
field is Airtable’s created time type (or you plan on using the CREATED_TIME
function), then you need to be careful not to create several records really quickly; i.e. pre-making a bunch of “blanks” that you plan to fill in later. This would only be an issue around each of the 100-record thresholds, but it’s something to be aware of at any rate.
Here’s the starting point. (FWIW, I made a formula for the name field using pieces of the record ID, just to get something unique in there).
I then added a field named {Date Created}
, using this formula:
DATETIME_FORMAT(CREATED_TIME(), "x") & "|"
…which gave me this:
Notice that several of the timestamps are the same. That’s because of the quick record creation issue I mentioned above. If Airtable supported storing dates with millisecond accuracy, this wouldn’t be an issue. As it stands, it’s only accurate to the second. The bar on the end helps to keep individual timestamps separate in later stages.
In the [Properties]
table, I rolled up this new field using ARRAYJOIN(values, "")
as the aggregation function.
Back in the [People]
table, I added a lookup field to pull in this rollup:
These fields are hidden from here on out. An {Index}
formula field is added next to find the position of the {Date Created}
string inside the rollup, then add 100 to it and turn it into a string.
((FIND({Date Created}, {Rollup} & "") - 1) / LEN({Date Created})) + 100 & ""
Again, notice the duplicate numbers due to matching creation times.
If you won’t have more than 999 people tied to a given property, you can just wrap a LEFT function around that formula to grab the leftmost digit, and you’re done. However, if you think there might be more than 999, then this next formula takes that index, zero-pads the front of it if necessary, and grabs the value of the left two digits:
VALUE(LEFT(REPT("0", 4-LEN({Index})) & {Index}, 2))
These final screenshots show the transition from the first hundred to the second, and the second to the third, using the base system (max 999 people per property) with the extra fields hidden for clarity. I’ve also grouped the records by property, but grouping doesn’t affect the numbering sequence. It just makes the transition more clear for this first property
In addition to a reminder about record limit issues tied to your account type (1200 per base for free, 5000 for Plus, etc), there are a couple things to keep in mind regarding this setup:
[People]
table increases, the responsiveness of the table will slow down due to the size of that massive rollup string growing ever larger.Is this the most efficient solution? Not likely, but it can be done completely within Airtable. If you need something more hardy, you might consider hiring someone who can code an API-driven solution.
Aug 19, 2019 11:28 AM
Thank you so much! This really helps.