How to apply a filter on a single property of a data set using more than one value of this property

[Ludovic_MOOS]

Hi,

We need to filter a data entity using the filter conditions input of a "Get record collection" activity in Build Apps. For this, we use the following formula in the Formula Editor:

{type: "and", conditions: [{type: "eq", property: ["account.id"], value: appVars.collectedAccountTechnicalIds[0].accountTechnicalId}]}

appVars.collectedAccountTechnicalIds is the variable where we store the values needed to filter the data set. It is a list of objects with one property (accountTechnicalId).

The formula works well, but as you can see, we're filtering using the first occurrence of the array only ([0]), which results in showing the records filtered on this value only. We've made countless tests but they're always returning errors, for instance:

{type: "and", conditions: [{type: "eq", property: ["account.id"], value: appVars.collectedAccountTechnicalIds}]}

So the question is: using the Formula Editor, is there a simple way to apply the filter using all the values stored in the variable? Else, is there some sort of "for each" activity or should we create a loop or use a JS script in the logic canva? Basically, the goal here is to apply a filter on a single property of a data set using one or more values of this property.

Thanks a lot for your help on this, we're stuck on this problem for quite some time now and we need to implement this urgently for a demo to a client next Thursday...

Best regards,

LM. 

[Dan_Wroblewski]

I still do not understand why my suggestion would not work for you. Your IDs are stored in an App variable instead of my page variable.

[Dan_Wroblewski · Accepted solution]

I still would rework the data connection, and I think it more elegant to do this with OData IN $filter, but if you want to do the filtering in the Get Record Collection flow function, what about this.

Create a filter page variable like this.

Set the page variable with the fields you want. Here I hard-code the values but you could use a MAP function to parse your IDs. The property field is a list but you just put a single string, the name of the field on which to filter.

And then in the Get Record Collection filter, you set to formula and do something like this:

Would this work?

[Dan_Wroblewski · Accepted solution]

I made a little app/UI where I return values from our OData service for our CodeJam.

I'm able to create a list of strings and then use that to filter the list of emails.

I used a formula like this:

MAP(pageVars.strings,{type: "startswith", property: ["customer"], value: item.mystring})

Of course you will get the filter values from your app variable

[Ludovic_MOOS]

Hi @Dan_Wroblewski,

Forgot to drop you a message after our call on December 24th. What you suggested worked fine so again thank you very much for the help you provided.

Hope you enjoyed the festive season and my best wishes for the new year!

[Ludovic_MOOS]

Hi @Dan_Wroblewski,

Let me bring more details to clarify. Actually, the app variable where we store the account technical IDs has already been created:

Once we retrieve the account technical IDs, we set the above variable as follows:

Then, we set the filter condition of the Get Record Collection as follows:

The formula "as is" works fine but as you can see, we filter the data entity using only the first account technical ID of the array (see appVars.collectedAccountTechnicalIds[0].accountTechnicalId). The result of this is that only the cases of the data set whose account.id field is the first occurence of the collectedAccountTechnicalIds variable are displayed.

In this particular case, we have 3 account technical IDs stored in the app variable:

[{"accountTechnicalId":"11f01ec7-9077-32de-afdb-816548020a00"},

{"accountTechnicalId":"11f01ec7-908d-c81e-afdb-817b0a020a00"},

{"accountTechnicalId":"11f01ec7-8ea9-ecee-afdb-817b0a020a00"}]

So, our question is: using the above formula, is there a way to filter the data entity using all the values stored in the array?

We tried using the following formula too {type: "and", conditions: [{type: "eq", property: ["account.id"], value: appVars.collectedAccountTechnicalIds}]} as you can see below, but it returns the following error message {"code":"unknown","status":-1,"message":"TypeError: e.replace is not a function","rawError":{}}

Thanks,

LM.

[Ludovic_MOOS]

Hi @Dan_Wroblewski,

Let me bring more details to clarify. Actually, the app variable where we store the account technical IDs has already been created:

Once we retrieve the account technical IDs, we set the above variable​ as follows:

Then, we set the filter condition of the Get Record Collection as follows:​

The formula "as is" works fine but as you can see, we filter the data entity using only the first account technical ID of the array (see appVars.collectedAccountTechnicalIds[0].accountTechnicalId)​. The result of this is that only the cases of the data set whose account.id field is the first occurence of the collectedAccountTechnicalIds variable are displayed.

In this particular case, we have 3 account technical IDs stored in the app variable:

[{"accountTechnicalId":"11f01ec7-9077-32de-afdb-816548020a00"},

{"accountTechnicalId":"11f01ec7-908d-c81e-afdb-817b0a020a00"},

{"accountTechnicalId":"11f01ec7-8ea9-ecee-afdb-817b0a020a00"}]

So, our question is: using the above formula, is there a way to filter the data entity using all the values stored in the array?

We tried using the following formula too {type: "and", conditions: [{type: "eq", property: ["account.id"], value: appVars.collectedAccountTechnicalIds}]} as you can see below, but it returns the following error message {"code":"unknown","status":-1,"message":"TypeError: e.replace is not a function","rawError":{}}​

Thanks,

LM.

[Ludovic_MOOS]

Hi @Dan_Wroblewski

Thanks for your answer. Yes, that's exactly what we want: we need to return all items of the data set whose account.id field is in the accountTechnicalId variable.

The reason why we're using a GetRecordCollection is because we need to display the filtered list in our app via a list item component (logic flow behind this being GetRecordCollection>SetAppVariable) and the accountTechnicalIds are retrieved using the logged on user's email. Plus, the API (sap.crm.caseservice.api.case in our case) is linked to Sales Cloud and has already been set up using a Universal Rest API integration.

Note that we're going back over a project that was initially outsourced. So ideally we'd like to use what has already been implemented. That's why we wonder if there's a way to do this by just modifying the formula written in the editor...

Thanks.

LM.

[Ludovic_MOOS]

Hi @Dan_Wroblewski 

Would you have any idea how to do this please? We really need to move forward with this.

Many thanks,

Best regards,

LM.

[Dan_Wroblewski]

You are saying you have an array of IDs in the accountTechnicalId field, and you want your Get to return all items whose account.id field is in that list?

In general, I would not use that GetRecordCollection feature but try to construct a $filter that looks something like this:

GET /service/Products?$filter=Name in ('Milk', 'Cheese', 'Donut')

I wrote this about creating $filter in Build Apps, but that was before Build Apps supported Actions, which I think would be the better way: https://community.sap.com/t5/technology-blog-posts-by-sap/odata-filtering-in-sap-build-apps-could-th...

Let me know exactly what you need and I will try to see how this can be done.