Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Find Arbitrary Item in Lookup(array) - like Vlookup

1685 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Mike_McLaughlin
8 - Airtable Astronomer
8 - Airtable Astronomer

Simple version (so you can cut and paste, and put to use)

Link to demo base:TestLab Status

0d41b7239ab01c5f5e8f76a7f7d714f76ba86a36.png

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.

image.png

Table 2:
Helper-status field is simply

{Status Date}&Status&{Status Date}

image.png

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:

  1. 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)

image.png

Steps to implement:
in summary table,

  1. Lookup helpers
  2. Formula - convert helpers to text
  3. Rollup latest date from linked-records (linked records from Status-Updates)
  4. Formula - convert latest date to text so we get that nice, fixed character length time code
  5. Find first occurrence of that time code in our text-converted helpers list, return the position

image.png

  1. Remove Left() all characters before that position as well as the fixed number of character after that position that make up the time code
  2. find next (only other) occurrence of the time code, return the position
  3. Remove Right() all characters onward from the start of the time code
  4. Voila, you have the value of the status.

image.png

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.

3 Replies 3
Daniel_Halenko
4 - Data Explorer
4 - Data Explorer

hey there, i “think” this might be able to help me.
but the test lab link is private, any chance you can repost of make public?
thanks

Daniel_Halenko
4 - Data Explorer
4 - Data Explorer

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…
image

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

SOLVED

So i got my solution :slightly_smiling_face:

On this thread

Thanks