Help

Re: List records where LInked Field contains Record ID

2376 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Travis_Delly
4 - Data Explorer
4 - Data Explorer

I’m trying to figure out a formula for listing records that have have a linked field with the Record

For Example if i have a Post Table with a Customer field that is an Array of customer Id’s such as [‘rec23jia’, ‘rec234i’], I would like to get this record back if the Customer Array field contains the value rec234i

Heres my thought

SEARCH(‘rec234’, ARRAYJOIN({Customer}, ’ '))

but this doesn’t seem to be working. When i do the opposite

SEARCH(ARRAYJOIN({Customer}, ’ '), ‘rec234’), i actually get undefined values, so it’s almost as if the ARRAYJOIN function isn’t doing what i think it’s doing.

3 Replies 3
Chinara_James
6 - Interface Innovator
6 - Interface Innovator

Hi, are you trying to do this using the API. Was a little confused since you are using a formula.
You don’t need to use a formula if I’m understanding your question correctly.

You want to list all posts that have a specific customer, correct?
Using the API you would use select or find method. This would return the fields data include the array of Ids in the linked column. In your case a field
“Customer”: [
“reciRjJKXikfJm52V”,
“reciscDZFa7v6SWQt”
]
Now if you know the Customer ID before hand, you can use a ES6 array function to see if the Known ID is in the array returned by the API select or find methods. Example post.customer.includes(“reciRjJKXikfJm52V”)

Here is a pen of javascript array functions in case you have another use case

Chinara_James
6 - Interface Innovator
6 - Interface Innovator

Hi, your question inspired to write a tutorial on this. I hope this can help you.
Here is link to the forum post in Show and Tell

Jesper_Oman
4 - Data Explorer
4 - Data Explorer

I managed to solve this by adding a formula field in the linked table which pulls out the record id using the function RECORD_ID().

So for your example:

  1. add another field in the Customer table called “record_id” which is of the type Formula, in the formula, you type RECORD_ID()
  2. In the Post table, you add a lookup field to the Customer field record_id.
  3. then you can search with the filterByFormula=SEARCH(‘rec234’, ARRAYJOIN({Customer}, ’ '))

Hope this helps, couldn’t find this answer anywhere else.