List Single API for Large Advance List Inconsistency When Page Size / Page Filters Used on Large List

Lindsey Bryant
Lindsey Bryant
Third Anniversary Kudos 2 First Reply Name Dropper
in SKY Developer General Discussions

I have an advance list that has over 100,000+ rows. The advance list is used to pull back all grades for records in BBEM to populate an ODS table, our data warehouse, and then send to Tableau.

We've recently discovered that we are now either missing rows or that some of the rows are duplicated. I originally had the page size set to 1000 and when I reduced it down to 500, I got more rows indicating to me that the pagination may be an issue.

Has anyone else ran into this type of issue before and if so, how did you resolve it?

Answers

  • Nick Marchese
    Nick Marchese
    Kudos 1 Name Dropper Facilitator 1

    Hey Lindsey. I haven't run into this issue before, but kudos for syncing out an advanced list of this scale. Are you doing this with direct API calls or with Power Automate? Are you using List Single? Happy to dig in more offline, if you like.

  • Lindsey Bryant
    Lindsey Bryant
    Third Anniversary Kudos 2 First Reply Name Dropper

    @Nick Marchese yes we’re using the list single api where we pass in the list id and then return the values. We use it a lot due to the limitations of how to get data out of BBEM as we patiently wait for actual APIs to be built. Think this one has been on the list of wishes for a couple of years now.

    I have a ticket opened but I’m not getting far with anyone dmso I’m now working on creating to smaller lists to combine the results and load our table. I’m planning on testing this out tomorrow in our production system to see if this eliminates the issue. If you have any other suggestions we would live to hear it.

  • Nick Marchese
    Nick Marchese
    Kudos 1 Name Dropper Facilitator 1
    edited May 12

    @Lindsey Bryant I am beginning to roll out of my Google Sheets extension that uses some of the newer endpoints that may alleviate this. Would be happy to chat or meet offline, whether you are interested or I can just point you in the right/different direction.

  • Bryce Howard
    Bryce Howard
    Fourth Anniversary First Reply Kudos 1 Name Dropper

    This post is relevant.

  • Lindsey Bryant
    Lindsey Bryant
    Third Anniversary Kudos 2 First Reply Name Dropper

    @Bryce Howard it sounds similar. I'm using the original API under school: https://developer.sky.blackbaud.com/api#api=school&operation=V1ListsAdvancedByList_idGet verse the one in preview. Though reading through the post, it's mimicking the same behavior. The biggest difference I would highlight is that everything runs successfully, it's just that when we go to pull the next page of data, it appears that sometimes we are duplicating the last row from the previous page or we are skipping the first row of the new page. We haven't noticed an issue until we hit that 100K mark but that doesn't mean the issue wasn't there before.

    I'm doing further testing right now and when I pull the data down into excel and use a simple formulate to create a PIK and then check for duplicate rows, I can see it's always happening on the 1/2 row in order (i.e. 2001, 2002 or 13001, 13002 or 67501, 67502). This clearly points to the pagination being the issue.

    Because the token has to be refreshed every hour, we also have to ensure we get all of this data within a specific time frame.

  • Lindsey Bryant
    Lindsey Bryant
    Third Anniversary Kudos 2 First Reply Name Dropper

    @Nick Marchese if you would like to connect offline, feel free to email me at lkbryant@exeter.edu. Based off my findings, it's an issue with the pagination with the API.

    @Stephen Boyle and @Todd DeSchuiteneer I am tagging you both in case you want to see additional details in my support ticket #020877956.

    I'm not sure what options either of you can provide but this is causing some issues for us across multiple departments.

  • Todd DeSchuiteneer
    Todd DeSchuiteneer Blackbaud Employee
    Tenth Anniversary Kudos 3 Name Dropper Participant

    Thanks for tagging me in @Lindsey Bryant. I do currently have this issue reported to our dev team to address. I have attached the defect to your ticket and the associate will let you know when we have it resolved.

    @Nick Marchese I wasn't able to locate a support ticket for you. Do you have anything reported yet?

  • Nick Marchese
    Nick Marchese
    Kudos 1 Name Dropper Facilitator 1

    @Todd DeSchuiteneer Thanks. No. Unfortunately, my Live Support only lets me chat with Online Giving folks, soooooo, here we are. Would love to get updates though!

  • Todd DeSchuiteneer
    Todd DeSchuiteneer Blackbaud Employee
    Tenth Anniversary Kudos 3 Name Dropper Participant

    @Nick Marchese I'll get something on the books for you so you can get updates.

  • Lindsey Bryant
    Lindsey Bryant
    Third Anniversary Kudos 2 First Reply Name Dropper

    @Todd DeSchuiteneer thank you. I'm sorry to always tag you in these post but it does expediate things; especially when they are so impactful. We appreciate your quick response and support as always.

  • Todd DeSchuiteneer
    Todd DeSchuiteneer Blackbaud Employee
    Tenth Anniversary Kudos 3 Name Dropper Participant

    No need to apologize. Please continue to do so!

Categories