Fetching info from within a linked record

Topic Labels: Formulas
Solved
1056 5
cancel
Showing results for
Did you mean:
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.0 4.5 3.0

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

Thank you!

1 Solution

Accepted Solutions
18 - Pluto

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 & ",",
""
)``````

5 Replies 5
18 - Pluto

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 & ",",
""
)``````

5 - Automation Enthusiast

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?

18 - Pluto

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

``````SUBSTITUTE(
SUBSTITUTE(
ARRAYJOIN({Number (from Table 2)}),
Second & ",",
""
),
First & ",",
""
)``````
5 - Automation Enthusiast

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".

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.