I need to make a record-like query for a 1 to N relationship, in which it only returns the values of the parent record if all the values in the N relationship meet a condition. Example :
I have a record called "Need" and it is related to another called "needchangestate" through a relationship 1 to N, I need it to return only the needs where all the needchangestate meet a condition, if all the elements of the relationship N do not meet the condition, the element Need should not appear
Currently it returns me an investment where the condition is met in at least one of the records of the relationship N but this is not what I am looking for.
Discussion posts and replies are publicly visible
Hello. As I understand it, to implement what you need, you can use the following code as an example. But it requires iterating the list to be able to filter the data, and if your case is to bring a large batch of data this can affect performance. Also, you can use Mathieu Drouin suggestion as a reference and query directly to the view. Regarding pagination, if you need to show it in an interface you could use pagination and pass it as a parameter to your rule and bring only what is necessary per page.
a!localVariables( local!data: a!queryRecordType( recordType: 'recordType!SB Need', fields: { 'recordType!SB Need.fields.name', 'recordType!SB Need.relationships.needDetail.fields.decision' }, pagingInfo: a!pagingInfo( startIndex: 1, batchSize: 10, sort: a!sortInfo( field: 'recordType!SB Need.fields.id', ascending: false ) ) ).data, local!filteredData: a!forEach( items: local!data, expression: a!localVariables( local!relatedData: fv!item['recordType!SB Need.relationships.needDetail.fields.decision'], if( and( a!isNotNullOrEmpty(local!relatedData), count(local!relatedData) = length(wherecontains(false, toboolean(local!relatedData))), ), fv!item, {} ) ) ), local!filteredData )
I've thought about your solution and I think I have another way to do it.