Find Arbitrary Item in Lookup(array) - like Vlookup
Simple version (so you can cut and paste, and put to use)
Link to demo base:TestLab Status
TestLab Status - Airtable
Explore the "TestLab Status" base on Airtable.
Table 1:
Latest Status
LEFT(
RIGHT(status_&"",
LEN(status_&"")
-FIND({Last Updated}&"",status_&"")-23
),
FIND({Last Updated}&"",
RIGHT(status_&"",LEN(status_&"")
-FIND({Last Updated}&"",status_&"")-23)
)-1
)
Last Updated is just pulling the latest date for the linked records in Status Update Records.
TheStatus_ is looking up all of the status-helpers for the linked records so they can be searched through by the Latest Status formula.
Table 2:
Helper-status field is simply
{Status Date}&Status&{Status Date}
Its easy enough to repeat for multiple fields, I recommend using notepadd++ to copy the Latest Status formula to find&replace old field name the new one.
.
.
.
.
Detailed Version
broken down version (forgive the slight differences in naming convnetions)
Scenario: At my work, we have a test lab (think 25 test machines, that test our product). Each morning, the technicians each write an email to the lab manager with status updates for their respective machines and he sorts through and adds status updates into up-time metrics for the lab.
My desired output: A summary table that requires no interaction on the part of the lab manager.
Structure: 2 tables, Summary table with list of the test machines, Status-Updates table with status update records that will get fed up to the summary such that only info from the most recent record is in the Summary table for a given machine.
The work-around steps:
- We need the Status for each record to be padded on both sides with a unique time code with a fixed character length time code.
(note, the Test Machine X is linked from the Summary table to the status-update table when a new record is added here)
Steps to implement:
in summary table,
- Lookup helpers
- Formula - convert helpers to text
- Rollup latest date from linked-records (linked records from Status-Updates)
- Formula - convert latest date to text so we get that nice, fixed character length time code
- Find first occurrence of that time code in our text-converted helpers list, return the position
- Remove Left() all characters before that position as well as the fixed number of character after that position that make up the time code
- find next (only other) occurrence of the time code, return the position
- Remove Right() all characters onward from the start of the time code
- Voila, you have the value of the status.
I welcome any feedback on how to improve this - as I wrote this I realized (depending on the contents of the status field) there may be a better way to do this with an auto-number field (or formula & auto-number) instead of using the date as the unique value if you have no numbers in the value you are trying to extract.
Obviously, the number of formula fields can be reduced, but its broken out for explanatory purposes.
I think I get it, but completely confused how to make this work for me.
Ive a really simple need.
Table 1 is members.
Table 2 is a linked to MEMBERS, and is them “checking in” with X number of guests.
- so there are multiple records of the same member in table 2.
I want to pull through the number of guesst they checked in, from the latest record.
I can show ALL the guests they have ever checked in easily…
In the image above, the first time they checked in they had 5 guests,
the second time (on 25th Feb) they had 31.
I just want to show the latest value, and I feel your solution can do that.
Maybe i’ve just been working on this too long and my heads gone,but if you can help that would be epic.
Thanks
Dee
