© 2018 Capita Business Services Ltd. All rights reserved.

Capita Education Software Solutions is a trading name of Capita Business Services Ltd. Our Registered office is 30 Berners Street, London, W1T 3LR and our registered number is 02299747. Further information about Capita plc can be found in our legal statement.

SIMS 8 - Lookups (Potential Issue)

It has been brought to our attention that staff lookups return continuation URLS to enable Technical Integrators (TIs) to complete the extraction of lookups because there are more than 1000 rows.

Lookups are a union of a number of 'tables' such as gender, title, ethnicity, ... making a single call being more efficient that making the 28 separate calls required to get all the lookups for staff from their own individual endpoints, however that option is not currently available.

The clue as to how lookups work can be found by making the call /Staff/V3/StaffLookups?$top=1 where a response of zero or one rows returned would be the norm.  However in this case we get 28 rows, the top one of each LookupType (or underlying table). Hence when we make the call /Staff/V3/StaffLookups?$top=1000&skip=1000 then we get no records back because none of the contributing tables had more than 1000 rows.  In our test data there were 500+ entries for 'TeachingSubject'  and 300+ for 'StaffEthnicity'.

Checking for errors

In all cases if a request for lookups does not return a continuation URL ( "@odata.nextLink":  "https://seli00apm01.azure-api.net/ssli01/Staff/v3/StaffLookups?$skip=1000") then all will be well.  However, if a continuation URL is provided then it will be incorrect.

Fix

We are investigating fix options but have no ETA for this at present.

Work Around

If the continuation URL appears then TIs need to ask different questions until it goes away with one exception.

It is unlikely that TIs will want all of the 28 lookup types for staff and $filter LookupType eq <value> would work e.g:

  • $filter= LookupType eq 'TeachingSubject' 
  • $filter= LookupType eq 'Gender'

NB: This would also work correctly if a continuation URL is returned (because it would return the next 1000 rows from the underlying table). 

A second option is:

  • $filter= LookupType eq 'TeachingSubject' and a second call
  • $filter= LookupType ne 'TeachingSubject' 

which works so long as the not equals call returns without a continuation URL.

A third option is (numbers are from our data set):

  • ?$top=200                                                                     => returns 960 records
  • ?$top=200&$skip=200                                                => returns 317 records
  • ?$top=200&$skip=400                                                 => returns 129 records
  • ?$top=200&$skip=600                                                => returns 0 records

However this will again fail if any of the calls return a continuation URL which in our case could occur if 41 more lookups are added to lists with fewer than 200 rows.

The common feature is that as soon as the continuation URL is returned, there is no obvious way to get the additional record and an alternative route will be needed.