Field that Assigns Value Based on Date Created and a Lookup Field

[Mallory_Price]

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!

[Justin_Barrett · Accepted solution]

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:

1. If you delete the record for a person linked to a given property, it will shift the value assigned to all later records. For example, say you have 150 people linked to 123 Main St., so the first 100 have a value of 1, and the last 50 a value of 2. In the course of your work, you delete 25 of those with a value of 1. The other 75 that were 1 will stay at 1, 25 of those that formerly had a value of 2 will become 1, with the rest staying at 2.
2. As the record count in the [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.

[Mallory_Price]

Thank you so much! This really helps.