Keeping Links to Archived Customers

[James_Green]

We are using Airtable as a Storage Company.

This means that we have a set number of units with different information and another customer table too. If a customer is in unit 1 then we will show that relationship. However when a customer moves out they become an archived customer.

We need a way of the customers being sent to an archive whilst still keeping the unit number and unit price. Its almost like taking a snapshot of that field and its relations and saving them, is that possible at all?

[Elias_Gomez_Sai]

Could you create an Archived field (a Checkbox for instance), and the filter it out?

[James_Green]

The only issue then is that the links that they have to the units will remain.
So if they move out of unit 1 and I mark them as archived, the archived customer will still show in the units table as a link

[Elias_Gomez_Sai]

Count only the “not archived” ones :stuck_out_tongue_closed_eyes:

[Attaboy]

You could insert start/end rental date fields into your customer records to filter non-current customers out of any given view. This would also allow you to create “Timeline views” for particular units or customers.

IE: Show me records for which ‘this link’ exists and ‘end date’ is after ‘now’.

[W_Vann_Hall]

You can find code that does essentially what @Attaboy suggests in the [Out] table of my Wardrobe Manager base.[1] I wanted to be able to have an item referred multiple times to different responsible parties without having to rely upon the manual ticking or unticking of a checkbox. Instead, I declared by fiat there could only be one valid referral (‘out’) for an item at a time: Therefore, the one with the latest referred-out date must always be the most recent valid one. I could easily see a similar algorithm being used to determine who was the current tenant of a storage space.

Something to keep in mind, as well, is a little-known Airtable secret: Rollup fields can have aggregation formulas as well as aggregation functions. This greatly enhances the usefulness of rollup fields — especially in comparison with such ‘one-note’ fields as the count field. For instance, here’s what’s configured as the aggregation function for the {Dupe} rollup field in version 2.0 of my De-duplication demo base:

IF(
    FIND(
        MatchName,
        values,
        FIND(
            MatchName,
            values,
            1
            )+1
        )>0,
    "Y",
    ""
    )

Without using this ‘enhanced’ aggregation function, my de-duplication base required the field to be de-duplicated for every record in the main base to be rolled up into one massive field — which then was replicated back to the main table via lookup.

In other words, every record in the main table included a field containing the target field values from every other record in the table.[2] For a 1,000-name base, this works out to a field 13,000 characters long — that’s 13 million characters, or roughly four War and Peaces for the base as a whole. (The 18,000-name base I tested dragged around nearly 235,000 characters per record; that’s like appending a copy of C.S. Lewis’s The Lion, the Witch, and the Wardrobe — in an academic edition, containing an introductory essay and a biographical note on the author — to every record.) With the aggregation formula, in contrast, while the mega-field still must be created, it no longer must be replicated throughout the table.

I mention this because, although I can’t give you an example, this just feels like a place where an aggregation formula might come in handy.
. __________

1. On second thought, it might be easier simply to write your own routine than to adapt one of mine. Still, it’s there, should you need a reference.
2. As I know nothing of Airtable’s internal workings, I don’t know whether the value of a lookup field is actually duplicated to the record doing the lookup or merely accessed by reference. I do know, however, the revised version of the de-duplication base seems to operate noticeably faster and more responsively than did the original.