User ID in submissions API call off by 1

[afuenmayor]

I am working on a project where I am matching student submissions back to our local database.  To crosswalk this data I am using the sis_user_id field in the Pseudonym_Dim table downloaded out of Canvas Data. 

Now to my issue.  Lets say for example my api call includes submissions for two students with Canvas IDs = 62198 and 62315.  In Pseudonym_Dim table 62315 matches to our local ID number of 912581 who bears the correct Identity (I can verify that this student was enrolled in the relevant course and has the same biodemo data).  However, student 62198 does not crosswalk to the correct student in Pseudonym_Dim; 62199 does.  For about half the submissions I am looking at, the Canvas ID on the submission matches correctly.  On the other half the correct ID is the api result offset by 1. 

I can't figure out why on earth this might be happening.  The only general pattern I've noticed is the offset IDs are lower values than the correctly matched IDs.  Any insights or explanations are greatly appreciated.

[afuenmayor]

After a bit more searching i noticed that some of the IDs are off by more than 1.  Some are offset by 2-5.  When I pull the sis_user_id directly in the API call I get the right ID.  But why is the crosswalk in canvas data broken?

[James]

 @afuenmayor  

What program are you using to look at / analyze / store your data?

The IDs in Canvas Data are bigInt and some systems cannot handle those as numbers, but will work properly if treated as strings. In particular, Excel has this problem (Number Format on Data Portal Exports). Tableau had this problem as well (Use of Tableau Prep and ID fields ) -- it seems I read something about this being fixed in a recent release notes, but I cannot find it now and I haven't tried using Tableau with Canvas data in a long time. Even JavaScript has issues with it (Long ID's in Javascript )

Another possibility might be if you declared your fields as unsigned when you import them. They are definitely signed big integers.

Even though you may not have super-large IDs, the primary and foreign keys in Canvas Data use them