Find first Entry in Look Up Field

[Jason_Friedlan2]

I have a very predictable set of data across multiple tables.

There is one table where a person can request a room for a meeting.

If the room for the meeting is more than 30 minutes I run automation to make sure the next 30 minutes time period is assigned and link it back to the original request.

This results in a LookUp field called Timeslots that has two results 30 minute period 1 w/ Date & 30 minute period 2 with date.

For the next step I need to take 30 minute period 1 w/ date and use that info to Schedule the meeting in my cal. the result is always hh:mm mm/dd/yyyy

I am stuck trying to figure out how to extract just that first 30 minute entry from the look up datetime format it.

[Justin_Barrett]

Welcome to the community, @Jason_Friedlander2! :grinning_face_with_big_eyes: A lookup field is most likely going to return an array, which is simply a container for multiple items. While lookup fields use commas to separate items collected from multiple linked records, those commas are purely for display, and can’t be used in formulas to distinguish where one item ends and another begins. While you could switch the lookup field to a rollup field to insert separators (using the ARRAYJOIN() function) to help with later parsing, that may or may not actually help depending on your data.

What exactly is the field being targeted by the lookup field? You referred to the data as “30 minute period 1 w/ Date” and “30 minute period 2 w/ Date,” but I’m not sure if these are just date fields or some other field type. The more you can describe about your base design, the easier it will be to help.

[Jason_Friedlan2]

Justin - it is a formula field. I just need to extract the info from that. I already have what I need to do with it working.

[Jason_Friedlan2]

Even if I was able to pull everything before the first comma in a lookup it can solve my issue.

[ScottWorld]

@Jason_Friedlander2

I think you’re referring to the technique that I describe in the video below to isolate the latest linked record, although you are looking for the earliest date instead of the latest date — so you will use MIN() instead of MAX().

This will work as long as you always link the 2 linked records in chronological order. The first date will always need to be linked before the 2nd date.

[Justin_Barrett]

Jason_Friedlander2:

Justin - it is a formula field. I just need to extract the info from that.

What type of data does the formula return? A string? A date? Again, the more info that you can share, the quicker we can get to a solution.

The technique mentioned by @ScottWorld will work if the formula field that you mention outputs an actual date (more accurately referred to as a datetime when referring to the data type itself). However, if it outputs a string, some extra steps will need to be added to the process.