Help

Fetching info from within a linked record

Topic Labels: Formulas
Solved
Jump to Solution
1056 5
cancel
Showing results for 
Search instead for 
Did you mean: 
olausenn
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello, 

I am pretty new to airtable and have a slight issue with fetching info from a record within a record. 

With an automation i create a number of records within a record to be able to choose multiple products for the same order number. (Not a lot of context here, but it would be a way to long post if I provide all the context)

All I want to do is separate the info, in the following case, for the two elements.

Looks kind of like this:

Amount & product (linked records)Weight (lookup)

Weight, first element

(help here)

Weight, second element

(help here)

120 Coarse,

100 Fine

4.5, 3.04.53.0

 

Is there any way to fetch certain info depending on the position of the lookup element? 

 

Thank you! 

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Screenshot 2023-01-30 at 9.40.09 PM.png
Try this out:

First:

LEFT(
  ARRAYJOIN({Number (from Table 2)}),
  FIND(
    ',',
    ARRAYJOIN({Number (from Table 2)})
  ) - 1 
)

Second:

SUBSTITUTE(
  ARRAYJOIN({Number (from Table 2)}),
  First & ",",
  ""
)

 Link to base

See Solution in Thread

5 Replies 5
TheTimeSavingCo
18 - Pluto
18 - Pluto

Screenshot 2023-01-30 at 9.40.09 PM.png
Try this out:

First:

LEFT(
  ARRAYJOIN({Number (from Table 2)}),
  FIND(
    ',',
    ARRAYJOIN({Number (from Table 2)})
  ) - 1 
)

Second:

SUBSTITUTE(
  ARRAYJOIN({Number (from Table 2)}),
  First & ",",
  ""
)

 Link to base

This worked like a charm! Thank you! 

I'm not sure I see a pattern here though. If there were a third and fourth linked record with a "number" each, how would this formula expand? 

You'd keep nesting them, so something like this:

SUBSTITUTE(
  SUBSTITUTE(
    ARRAYJOIN({Number (from Table 2)}),
    Second & ",",
    ""
  ),
  First & ",",
  ""
)

That didn't really work, it actually fetched all elements except the first one. 

olausenn_0-1675155568868.png

Are you able to see where this goes wrong? 

I copied your code and substituted the names "number from table 2", "second", and "first". 

 

olausenn
5 - Automation Enthusiast
5 - Automation Enthusiast

If someone ever reads this and is wondering, I made it work by brute force. 

The code above had a few flaws when the number of elements exceeded 2. 

To get around this I had to create a field for every time en element was grabbed since the code for "second" above retrieved all elements except the first one. Then I used the code for "first" above on that new field and so on. Works fine, but you have to add "," at the end for the "find"-formula to work. 

olausenn_0-1675406836226.png