API call to match Record_IDs on Linked columns

Topic Labels: API
3903 2
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

I have CarTable with a linked field to EngineTypeTable and another linked field to ManufacturerTable. I have the record_id from a record in EngineTypeTable and a second record_id from a record in ManufacturerTable. I want to create an API call on CarTable that returns all the CarTable records with linked field columns that respectively match the record_ids that I have. How can I do this? Is it possible with filterByFormula?

2 Replies 2
6 - Interface Innovator
6 - Interface Innovator

filterByFormula allows you to filter records based on the values that you see in the table. So in this case, you could use filterByFormula using the values from the primary field from the records you want to search by.

For example, if the primary field from the ManufacturerTable is “Manufacturer A,” and the linked field column in the CarTable is called “Manufacturer,” you could then use the following formula to get all CarTable records where the Manufacturer is “Manufacturer A”:

filterByFormula=SEARCH("Manufacturer A", {Manufacturer})

You can then chain these searches together using the AND/OR functions. For example, if I want to find all records in the CarTable that link to both Manufacturer A and Manufacturer B and Manufacturer C:

filterByFormula=AND(SEARCH("Manufacturer A", {Manufacturer}), SEARCH("Manufacturer B", {Manufacturer}), SEARCH("Manufacturer C", {Manufacturer}))

If you don’t have the primary field names for the records for the ManufacturerTable on hand, you will need to look those up first using the record_ids you do have on hand.

If you absolutely want to use the record IDs to search, then you can:

  1. Add a formula field (call it record_id) to your ManufacturerTable which runs the formula RECORD_ID()
  2. Add a rollup field (call it manufacturer_record_ids) to your CarTable which uses the linked field to the ManufacturerTable, and takes the “record_id” field from the previous step and does ARRAYJOIN(values)

For each record in your CarTable, you will now have a field that is a string containing all of the record_ids of the Manufacturer’s it is linked to. You can still use the filterByFormula=SEARCH(...) paradigm, just now you will search for a record_id within manufacturer_record_ids.

Ok. This was my suspicion. I wanted to search by record ID since this is what I get by default for Manufacturer when the API returns CarTable records with a linked field to the ManufacturerTable. I will do one of these work-arounds. Thank you @Giovanni_Briggs !