How to query on local data?

Question

I have an array of of CDT which have local data stored in it. Now I have to fetch data from the array based on some certain condition.
For example, I have a CDT named "Employee" with fields firstName, lastName and employeeId. Now I want to get the firstName of the employee whose employeeId is 12345.
How to achieve that?

Robert Shankin · Answer

Hi - 
 Perhaps this pattern will help : 
 index(pv!employees.firstName, wherecontains("Finance", pv!employees.department)) returns the first names of all employees with their department field set to Finance or an empty array if none have it set to Finance 
 In your case, you want change: PV -> local, "Finance" -> "12345", .department -> .employeeId

shwetad7106 · Answer

Hi, 
 
You can make use of wherecontains() and index() functions, 
 
load( 
local!employee:{ 
 {employeeId:12345,firstname:"abc"}, 
 {employeeId:1345,firstname:"ghj"}, 
 {employeeId:12345,firstname:"xyz"} 
}, 
local!indexes : wherecontains(12345,tointeger(local!employee.employeeId)), /*this will return the index of the row which has this employeeId.*/ 
local!data:index(local!employee,local!indexes,{}), /* this will return data at that indexes */ 
local!data 
) 
 
Try above code. 
 
Thank you.

Brazil Vibanco · Answer

Hi Sudipta Biswas 
 You can also achive this using displayvalue() function. Is another alternative, I don't know if better performance, but I like it. 
 load(
 local!employee:{
 {id:23456,firstName:"xyz23456",lastName:"def"};
 {id:12345,firstName:"abc12345",lastName:"def"}
 },
 displayvalue(
 23456,
 local!employee.id,
 local!employee.firstName,
 null
 )
)

niteshtwri · Answer

Hi, 
 
Please run the below code in expression rule: 
 
load( 
 local!employee:{{id:23456,firstName:"xyz23456",lastName:"def"};{id:12345,firstName:"abc12345",lastName:"def"}}, 
 local!result:a!forEach(items:local!employee, 
 expression: if((tointeger(fv!item.id)=12345),fv!item.firstName,{})), 
 local!result 
 ) 
 
It should return "abc12345" which is first name for the employee having id as 12345. 
 
Thanks, 
Nitesh

Shubham Aware · Answer

Hello, 
 
There are multiple ways by which you can retrieve data from local! variable like 
1) filter() function 
2) Use array function like whereContains, index 
 
 whereContains("12345",index(ri!Employee, "firstName")) 
By this you will gate index of particular data in cdt. 
 
Thanks, 
Shubham

chetany · Answer

You can use the filter() function. First you need to create a rule which takes A .one Employee CDT as input B. the id/name to filter on. This rule should return true if the EmployeeCDT.name is equal to given givenName or EmployeeCDT.id equal to id. Example: 
 local!filteredEmployee: filter( rule!returnTrueIfEmployeeHasId( _, 12345) , ri!arrayOfEmployeeCDT ). 
 Then just access the name using local!filteredEmployee.name 
 Here the rule returnTrueIfEmployeeHasId is assumed to be created already. It has some condition like this: ri!cdtEmployee.id = ri!inputId Note: you could extend this pattern and create child rules for each of the filters possible. (say filter by name, filter by id etc.)

How to query on local data?

Top Replies