When you run an OData query, currently "order by" clause if not explicitly specified, Dell Boomi will not apply any default 'order by' clause into the query. Without 'order by' clause the query result in each page is not certain so you could get duplicate/Missing records in different query pages.
If no sorting criteria is used in then the result might not be unique in the pages and this leads to wrong results by the query. The root cause of this problems seems to be a unstable sorting during paging of the API.
- SOAP UI
- OData Query
Add $orderBy=code,effectiveStartDate to the OData query.
If duplicated/missing records/data are seen during pagination, 'order by' Clause should be the solution to resolve this issue.
You should use all business keys defined for an object in OrderBy clause to avoid duplicate/Missing record.
- Is this same issue likely to occur for any other SFOdata objects for which Boomi sends similar queries?
Answer : As a best practice it is always good to Sort the fields on "unique code" for that entity.
- Should we recommend to make changes in Boomi to add the sorting feature as work around into the query for every SFOdata object currently being queried for, even though the same problem has not been reported for any of these other objects?
Answer: As a best practice yes.
Duplicate, SFOdata Duplicates, Order By, Pagination , KBA , LOD-SF-INT , SF Integrations - EC Payroll, Boomi/ HCI, API , Problem