How can I implement user filter like this

Question

SELECT * FROM TABLE_A A JOIN TABLE_B B ON A.ID=B.A_ID WHERE B.ROLE IN ('ROLE1','ROLE2') and B.IS_ACTIVE=1 
 The relationship between A and B is one-to-many, two conditions should be matched in one row 
 I tried to do 
 { 
 queryfilter(Conditon1), 
 queryfilter(Condition2) 
 } 
 But it returned record A with two record B that match one of the conditions each

Kumar Agniwesh · Answer

Please use https://docs.appian.com/suite/help/24.3/fnc_system_relatedrecorddata.html and your problem should get solved

Soma · Answer

Create a relationship between recordA and recordB as one to many. 
 Use the recordB field in the filter queryFilter(

field: recordA.relationships.recordB.fields.A_ID,

operator: "IN".

value: {"ROLE1","ROLE2"}

)

Shubham Aware · Answer

If you want to apply filter on TABLE_B would recommend use relatedRecordData Have a look at this example: https://docs.appian.com/suite/help/24.3/fnc_system_relatedrecorddata.html#only-return-customer-cases-that-have-a-status-of-%22critical%22