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.

[Kevin83]

I don't fully grasp your question, but perhaps you want to post process the data in a client script, something like this:

and  then call this script when the data broker succeeds:

 

you can then use the state variable and bind that to your repeater, rather than doing what I think you are doing, directly binding the repeater to the output of the data broker.

[User303823]

Thank you Kevin. Your script suggestion helps me understand how I might work with the returned records. Here is a little more context that might help.

Let's say I have a new 'build' in my app. That has it's own table and gets number BLD0001165. The user is going to select a template(s) to associate with that build. Those available templates are presented in my page via the repeater component and a straightforward look up records resource to the template table which is bound to the repeater/cards. My m2m table (associates the Build with selected templates) is empty. This table also has a look up records resource conditionally filtering for the build and the selected state.

Now the user clicks on a card and 'selects' the template. Now my m2m table has a record (build | template | selected state). I'd like to now reflect on the card that that template has been selected. I was thinking of a highlighted value indicating 'Selected'. To do this, I was going to hide/display the highlighted value if the sys_id of the template card record matches the sys_id now in my m2m table. 

My challenge is working out how to check the m2m table data look up to see if the card row data unique value (sys_id) appears. 

Your script gives me an idea of how to loop through the data resources and check for matching sys_id's. 

I was also exploring using a record watcher resource on the m2m table. Not sure which would be better but am still looking at both. I feel like I'm a little closer. Thank you! 

I appreciate any other feedback and suggestions and will report back with any successes.

[User303823]

I've had some success adapting the suggested scripting but am not able to complete storing the data into the object and passing to my state variable. Here is what I've got...

I have two data sources that I refresh when the page loads. 

• data_1 (look up all templates)
• data_2 (m2m table; selected templates)

I have adjusted the suggested scripting below to match my needs. I first loop through the records in data_1 (all templates) and then cycle through data_2 to see if I have a match, if so, I push the data I need to display on my cards into the object/array.

function handler({api, event, helpers, imports}) {
    
    let all_templates = [];  //stores records from data resource for all templates
    let sel_templates= []; //stores records from data resource for m2m table for selected templates
 
    for (const template of api.data.look_up_templates.results){ //data_1 'all templates'
            for (const selected of api.data.look_up_selected_templates.results){ data_2 'selected templates'
               //if the sys_id in all templates matches the reference field in selected table, push values into the array
               if (template.sys_id.value == selected.u_topology_record.value){
                    all_templates.push({
                         id: template.u_template_id.value,
                         type: template.u_template_type.displayValue,
                         use: template.u_template_use.displayValue,
                         selected: false
                    });
                }
            }
    }
    api.setState("selectedTemplates", all_templates);
}

I've been able to test successfully down to my 'if (template.sys_id.value...)' line. The 'all_templates.push' does not seem to work however and I can never retrieve values from the object/array. 

I do have my state parameter (selectedTemplates) defined as JSON and I have built it the same way with id, type, use and selected properties. 

I just have been unable to work out why the creation of the all_templates object fails. If I can get this to work, then I would be able to bind the state parameter to my repeater and, I believe, get this all to work. 

If anyone see what I cannot and can offer any suggestions, it would be appreciated.  

[IronPotato]

@User303823 ,

since you are working with repeater you have access to currently selected template/card. If you have card component or button component or whatever component that have click event on that card or inside card, you can access individual card data via client script which you need to add into click event.

In your client script add this code to see the payload of clicked card:

console.log('card data', api.item.value);

EDIT: it might be that api.item.value will return undefined, if so change it to event.payload

in that payload there should be context key where you will find your data.

that log will show you currently selected card data as an "Object "from "Array" that you add into repeater, so make sure you include sys_id of your template.

In that client script you can then store that id as an client state

const templateId = api.item.value.yourTemplateId

if(templateId) api.setState('currentTemplateId', 'templateId');

IF YOUR M2M DATA RESOURCE WILL HAVE currentTemplateId state in the filter conditition, M2M DR SHOULD REFRESH AUTOMATICALLY SINCE STATE CHANGES AUTOMATICALLY RETRIGGERS DRs WITHOUT INVOKING THEM.

If my answer helped you in any way, please then mark it as helpful or correct. This will help others finding a solution.