[C4C ODATA] Search Function on Expand for Another Collection

[tokgozatakan]

We have the following code where we are doing an expand. However when we try to do a search, system does not allow search (502 Bad Gateway). Is there a way to make a search in the expanded collection?

/sap/c4c/odata/v1/c4codataapi/ServiceRequestPartyCollection?$filter=PartyID eq '1234'&$expand=ServiceRequest&$search=Hello

[FabienHenique]

Hello, I wasn’t able to perform any filter for expanded collection with relation 1..n, only possible with 1..1.

I suggest you to start from ServiceRequest if you have a parent navigation in your odata service or to deal with 2 queries.

[tokgozatakan]

Hello Fabien,

The reason we're expanding with the party is that we'd like to see the tickets of a person with any involvement in the ticket. We wanted to utilize the search function for an easy way but I think this is not possible. And unfortunately we're limited with the substringof, to one collection.

This Works:

ServiceRequestPartyCollection?$filter=PartyID eq '10005217' and substringof('Wol', ServiceRequest/Name)&$expand=ServiceRequest,ServiceRequest/ServiceRequestTextCollection,ServiceRequest/ServiceRequestParty<br>

While this does not:

ServiceRequestPartyCollection?$filter=PartyID eq '10005217' and (substringof('Wol', ServiceRequest/ServiceRequestTextCollection/Text) or subtringof('Wol',ServiceRequest/Name))&$expand=ServiceRequest,ServiceRequest/ServiceRequestTextCollection,ServiceRequest/ServiceRequestParty

[former_member187491 · Accepted solution]

Hi Kemal Atakan,

As mentioned by Fabien, substringof operator only works for properties having 1..1 cardinality. Furthermore, please note that there is no restriction in the number of times the substringof operator can be used in a single query. It just needs to be used with the right property (i.e. 1..1 cardinality).

I see two issues in your question;

1. Usage of $search is returning 502 error which should not happen. Therefore, please consider reporting this to SAP support. The reason for the 502 error in this case, appears to be the fact that no properties are enabled for $search (under ServiceRequestPartyCollection).
   
   Therefore, you may want to create another incident for the Ticket (a.k.a. ServiceRequest) OData Service and request adding ServiceRequestCollection/Text to be marked as Search enabled. I am not sure if there might be technical issues with this though. But it is still worth creating the incident. If this is fixed, you can then use $expand just like the way you are doing with $filter queries.
2. Given the above restriction in the usage of substringof, you can execute two separate queries in a $batch call, and create a union of the results. If you choose to implement this approach you don't need to wait for any fix from SAP.

Furthermore, in C4C OData API, you can use '*' asterisks as wildcard character in queries containing $filter (with eq operator) instead of the substringof operator. For example;

...ServiceRequest/Name eq '*fries*'...

Even the above approach only works for the properties with 1..1 cardinality.

Regards,

Mustafa.

[tokgozatakan · Accepted solution]

Hi Mustafa,

Thanks for the answers. It's good to know the limits 🙂

The case in our hands is that we have a customer portal and we'd like to display the tickets to anyone involved (anyone in the ServiceRequestParty).

Can we limit the search to a specific set of data? Let's say that from our first original request, we get a list of ticket IDs (100,101,102). Can we make the search request, only in ticket IDs 100,101,102? At least this is what I understand from ODATAAPIDEVGUIDE:

• $search and $filter can be used together in the same request - the matching results must satisfy the conditions set in both $search and $filter i.e. logical AND.

PS: c4codataapi'yi çok seviyoruz, destekleriniz için teşekkürler 🙂

[nkn30 · Accepted solution]

Hi Mustafa,

In my testing it seems the wildcard operator only works when you use 'eq' not when you use 'ne', so for example

This filters the correct results:

...ServiceRequest/Name eq '*fries*'...

This does not (Entities with name 'french fries 1' would still show up):

...ServiceRequest/Name ne '*fries*'...

Can you maybe give me more info on that?

Kind regards,

Niklas