Fetching info from within a linked record

[olausenn]

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.0	4.5	3.0

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

Thank you! 

[TheTimeSavingCo · Accepted solution]

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

[olausenn]

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? 

[TheTimeSavingCo]

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

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

[olausenn]

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

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]

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.