- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 02, 2019 10:46 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 07, 2019 01:33 PM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 09, 2019 04:43 PM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 18, 2022 01:21 AM
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:
- add another field in the Customer table called “record_id” which is of the type Formula, in the formula, you type
RECORD_ID()
- In the Post table, you add a lookup field to the Customer field
record_id
. - then you can search with the filterByFormula=
SEARCH(‘rec234’, ARRAYJOIN({Customer}, ’ '))
Hope this helps, couldn’t find this answer anywhere else.