UI Builder - How to find record reference in another table (Look up records)

[User303823]

I am prototyping a cutom 'app' using workspace and am looking at ways to build page(s) using UIB to assist with search and selection of various records. I have several tables working to accomplish this, but to simplify for my question there are two main tables to consider: a 'templates' table and an m2m table which associates the selected template with a specific design. 

In UIB, I have configured a Look up records data resource for the templates table and am displaying these on the page using a repeater > card based container design. What I'm looking to achieve is to find a match between the template record result card (sys_id) and a record in my m2m table (if it exists). If found, I'd like to do something on my page (hide the card, display a highlighted value, etc). 

Outside of UIB, I'd just query the m2m table for the sys_id I'm looking for. However, within UIB I cannot seem to accomplish this task. I've looked through the formula builder and scripting API but have not had success yet. I thought I was close with the formula WHERE_ONEOF(array, path, options), but either it doesn't do what I think it does or I'm just unable to form the options correctly. 

Another way to ask this might be, how can we work with the records returned by a Look up records data resource outside of binding to a component?

Any examples or links that help me get closer to figuring out how to query if a sys_id returned in the results of one data resource exists in another set of results is appreciated.

[IronPotato]

Hi @User303823 ,

1. data_1 (look up all templates) - set it to fetch data immediately or on page load.

2. data_2 (m2m table; selected templates) do not invoke this lookup yet set it to only when invoked

3. create client script name logEvent and inside add two lines of code:

console.log('event', event);

console.log('m2m table result', api.data.>your_m2m_DR>.<your schema variable>);

4. make sure that your repeater is getting data from DR from point 1.

5. create client STATE currentTemplateId

6. create and add client script on click event to your card component and extract the sys_id of the template from the event and set the currentTemplateId STATE to that sys_id. NOTE: Make sure that result of DR1 is array of objects containing sys_ids of the templates.

7. add filter condition into your M2M lookup to find records matching the template sys_id to currentTemplateId STATE

8. add logEvent client script to each stage of m2m DR ("Data Fetch Initiated", "Data Fetch Succeeded", "Data Fetch Failed").

9. save UI builder, refresh the page in browser and open developers console

10. test the setup, click on card and observe whether M2M DR was triggered...you should see 2 logs (initiate, succeed OR initiate, failed);

11. on failed debug, on success react on that returned payload