Symptom
When creating an integration in Integration Center you're adding fields that are 1:many (one-to-many) and the properly result are not appearing or are not the expected.
Other issue that you might facing is adding $filter in one navigated entity ($expand) and the filter is not returning your expected results.
Environment
- SAP SuccessFactors HCM Suite
- Integration Center (IC)
- OData API
Reproducing the Issue
- Go to Integration Center;
- Create a simple outbound integration;
- Add a new simple associated field;
- The message about 1:many will appear;
- The fields value will appear different of the needed.
Cause
This is a behavior of the system when using a 1:many (one-to-many) navigation properties, the order is not predicable, this behavior occur due the "OData Version 2.0 Uri Conventions".
Please note that $orderby does not sort the results of $expand in a 1:many association. Only the parent element is sorted.
Also regarding $filter, You can only filter top-level entities and child entities that have 1:1 relationships with their parents. When you apply $filter on an entity that has one-to-many (1:M) relationship with its children, the child entities are not filtered. The query returns all parents with full list of child entities even though only one child meets the condition in the filter.
Resolution
Check the KBA with one example of "calculated field" that explain how to select the unique information that will filter out your desired data. If you tried use <first> or <last> you may not have the expected value because the order is not predicable and can be different in preview mode and run now.
Please also check our OData Developer handbook page to read about the $filter note in the one-to-many navigations scenario.
See Also
Query with $expand and $orderby
2729221 - Integration Center: Non-Effective Dated to Effective Dated Entity Navigation
Keywords
one to many, one-to-many, 1:many, $orderby, $expand, $top, <first>, <2nd>, 1:m, Successfactors, Integration Center, OData, V2, <last> , KBA , LOD-SF-INT-INC , Integration Center , LOD-SF-INT , Integrations , LOD-SF-INT-ODATA , OData API Framework , LOD-SF-INT-EC , Employee Central SFAPI & OData Entities , How To