Continuing my SAP CI Pagination blog series, this time with the Coupa Adapter!
When integrating with Coupa, handling large datasets efficiently requires a proper pagination strategy. Unlike Salesforce and Microsoft Dynamics CRM, which rely on next page links, Coupa uses an offset-based pagination approach. Here, data is fetched in chunks based on the offset and limit values set in the adapter.
In this blog, I’ll cover:
How pagination works in the Coupa adapter using offset & limitBest practices for optimizing API calls and handling large datasetsA step-by-step implementation to ensure seamless data retrieval
If you missed the last blogs in this series on Salesforce and Microsoft Dynamic CRM adapters, below are the links:
Seamless Integration with SAP CI: Mastering Pagination for Salesforce Adapter
Seamless Integration with SAP CI: Mastering Pagination for Microsoft Dynamics CRM Adapter
Coupa Adapter – Handling Large Data Sets with Pagination
In this use case, we focus on retrieving records from the invoices resource in Coupa, which contains 50+ records. To achieve this, we use Query Resource operation to retrieve data. While the adapter provides an Auto Pagination feature that retrieves all pages in a single call, this approach can lead to significant performance issues when dealing with large datasets.
Fetching data page by page, processing each batch, and then moving to the next page is the best approach for handling large datasets. In this blog, I will explain the end-to-end batching process, ensuring seamless data synchronization while optimizing API performance.
Design Approach:
The scenario involves sending invoices resource from Coupa to a target system that has a batch limit of 10 records per request. To accommodate this constraint, we implement a custom pagination approach using Coupa receiver adapter call.
The call fetches the page of records within a looping process to retrieve the pages one after the other. Each fetched page is passed through message mapping, ensuring seamless transformation before being delivered to the target system. This cycle continues—fetching a page, processing it, and sending it to the target—until all records are transferred, ensuring an efficient full end to end Batching processing and controlled data flow from Coupa to the target system.
Design components elaboration:
Timer : This is a timer based flow which be a scheduled as per the user’s input
Content Modifier : “set properties” – This step is used to declare propeties such as CoupaResource, fieldnames, Coupa_limit, Coupa_offset, StartDate.
Property NameUseCoupaResourceName of the resource that has to be queriedfieldnamesfieldnames of the resource that has to be queriedCoupa_limitNumber of records per pageCoupa_offsetoffset value, is set as “0” as we intend to fetch all the pages without missing a single recordStartDateDate from which the records to be queried, refers to updated-at in Coupa resource
Understanding Offset and Limit in Coupa Pagination
In this design, the two key parameters to control data retrieval are Offset and Limit. Unlike cursor-based pagination (as seen in Salesforce and MS Dynamics), Coupa follows an offset-based pagination approach, where:
Limit – Defines the number of records per page (e.g., if set to 10, each API call fetches 10 records).
Offset – Determines the starting point for the next batch of records. In our design after each request, the offset is incremented by the limit value (10) to fetch the next set of data.
The initial request starts with Offset = 0 and Limit = 10.The next request will have Offset = 10, Limit = 10, fetching records from 11 to 20.This process continues in a loop until the last page is reached.
Looping Process Call : “Initiate Coupa Call”
This Looping Process Call continuously runs until the field is absent from the response payload. The condition for looping is: ${property.RecordsPresent} = ‘true’
I have used a Groovy script to help us determine when to stop fetching records.
After each Coupa API call, the response is checked for the following error:
<errors>
<error>No results match your search criteria.</error>
</errors>
If this error is found, it means there are no more pages left, and the script sets:
RecordsPresent = false (End of pagination)
Otherwise, if valid records are present, the script sets:
RecordsPresent = true (Continue fetching the next page)
The Looping Process Call continues until RecordsPresent = ‘false’, ensuring controlled pagination.
Coupa receiver adapter:
Theadapter is placed inside the Looping Process Call to fetch records page by page.
Processing Tab
— Operation : Query Resource
— Resource : Resource dynamically passed from ${property.CoupaResource}
— Return Object Fields : Fields dynamically passed from ${property.fieldnames}
— Limit : Defines the number of records per page (e.g., 10 records per page). This value cannot be passed dynamically and only accepts numeric values. To control it, we declare “Coupa.limit” as a property.
— Offset : Determines the starting point for data retrieval in pagination. It must always start at 0 and cannot be passed dynamically, as it only accepts numeric values. To handle this limitation, we declare “Coupa.offset” as a property and increment its value after every API call to fetch the next set of records.
TheAdapter supports setting this via the properties “Coupa.limit”and “Coupa.offset” allowing better control over pagination.
We are not digging into the connection tab in this blog, this blog focuses only on pagination concept.
Groovy Script : Set Coupa Offset Parameter
Below script control the offset incrementation as well as last page identification
import com.sap.gateway.ip.core.customdev.util.Message
def processData(Message message) {
// Get the property value from the message and cast it to an integer
def propertyValueString = message.getProperty(“Coupa_offset”)
def propertyValue = propertyValueString ? propertyValueString.toInteger() : 0 // Convert to integer, default to 0 if null or empty
def Coupa_limit = message.getProperty(“Coupa_limit”) as Integer
// Add 50 to the Coupa_offset
propertyValue += Coupa_limit
// Set the modified offset value back to the message
message.setProperty(“Coupa_offset”, propertyValue.toString()) // Convert back to string for setting property
// Parse the body of the message
def body = message.getBody(java.lang.String)
def slurper = new groovy.util.XmlSlurper().parseText(body)
// Check if the specific error message is present in the body
def errorPresent = slurper.depthFirst().find { it.name() == ‘error’ && it.text() == ‘No results match your search criteria.’ } != null
// Set the CountObjects property based on the presence of the error message
message.setProperty(“RecordsPresent”, !errorPresent)
return message
}
Router : “Data?” –
A router is used to determine whether the fetched page contains any records. If records are present, the integration flow proceeds to process to next steps. However, if the page is empty, the flow terminates immediately, preventing unnecessary processing.
This check ensures optimized execution by stopping the pagination loop when there are no more records to fetch, improving efficiency and reducing unnecessary API calls.
Process Call : “Message Mapping” –
After fetching each page, the data is passed to Message Mapping, where it is processed before being sent to the target system. This step is target-specific and is not covered in this blog.
For demonstration purposes, I have included this step to mimic the “send to target” operation after message mapping. The key focus of this blog remains on pagination handling in the Coupa adapter.
Output / offset and limit explaintion:
For a total of 54 records, the pagination process spans 6 pages:
First 5 pages contain 10 records each.Page 6 contains the remaining 4 records.
When the 7th page is fetched, the response contains as below, which means end of records/pages.
<errors> <error>No results match your search criteria.</error> </errors>
Page 1 : Response from the Coupa system contains 10 records as per the set limit and with offset = 0
Page 2 : Offset = Offset (10)+ limit(10) = 20, the adapter skips the first 10 records and responds with next 10 records in this call.
Page 3 : Offset = Offset (20)+ limit(10) = 30, the adapter skips the first 20 records and responds with next 10 records in this call.
Page 4 : Offset = Offset (30)+ limit(10) = 40, the adapter skips the first 30 records and responds with next 10 records in this call.
Page 5 : Offset = Offset (40)+ limit(10) = 50, the adapter skips the first 40 records and responds with next 10 records in this call.
Page 6 : Offset = Offset (50)+ limit(10) = 60, the adapter skips the first 50 records and responds with next 10 records in this call. This is the last page containing 4 records.
Page 7 : Offset = Offset (60)+ limit(10) = 70, the adapter skips the first 50 records and responds with next 10 records in this call. <error> tag in the response which means end of records/pages
Full run in TRACE
Wrapping Up
With limit and offset handling in place, we achieve a structured and efficient pagination mechanism in Coupa Adapter. By leveraging properties like Coupa.limit and Coupa.offset, along with a controlled looping process, we ensure seamless data retrieval without unnecessary API calls.
This approach not only optimizes performance but also ensures that large datasets are processed efficiently and accurately. Stay tuned for the next blog in this series, where we continue exploring pagination strategies across different adapters!
I hope this helps!
Cheers,
Punith
Continuing my SAP CI Pagination blog series, this time with the Coupa Adapter! When integrating with Coupa, handling large datasets efficiently requires a proper pagination strategy. Unlike Salesforce and Microsoft Dynamics CRM, which rely on next page links, Coupa uses an offset-based pagination approach. Here, data is fetched in chunks based on the offset and limit values set in the adapter.In this blog, I’ll cover:How pagination works in the Coupa adapter using offset & limitBest practices for optimizing API calls and handling large datasetsA step-by-step implementation to ensure seamless data retrievalIf you missed the last blogs in this series on Salesforce and Microsoft Dynamic CRM adapters, below are the links:Seamless Integration with SAP CI: Mastering Pagination for Salesforce AdapterSeamless Integration with SAP CI: Mastering Pagination for Microsoft Dynamics CRM Adapter Coupa Adapter – Handling Large Data Sets with PaginationIn this use case, we focus on retrieving records from the invoices resource in Coupa, which contains 50+ records. To achieve this, we use Query Resource operation to retrieve data. While the adapter provides an Auto Pagination feature that retrieves all pages in a single call, this approach can lead to significant performance issues when dealing with large datasets.Fetching data page by page, processing each batch, and then moving to the next page is the best approach for handling large datasets. In this blog, I will explain the end-to-end batching process, ensuring seamless data synchronization while optimizing API performance.Design Approach: The scenario involves sending invoices resource from Coupa to a target system that has a batch limit of 10 records per request. To accommodate this constraint, we implement a custom pagination approach using Coupa receiver adapter call.The call fetches the page of records within a looping process to retrieve the pages one after the other. Each fetched page is passed through message mapping, ensuring seamless transformation before being delivered to the target system. This cycle continues—fetching a page, processing it, and sending it to the target—until all records are transferred, ensuring an efficient full end to end Batching processing and controlled data flow from Coupa to the target system.Design components elaboration:Timer : This is a timer based flow which be a scheduled as per the user’s inputContent Modifier : “set properties” – This step is used to declare propeties such as CoupaResource, fieldnames, Coupa_limit, Coupa_offset, StartDate.Property NameUseCoupaResourceName of the resource that has to be queriedfieldnamesfieldnames of the resource that has to be queriedCoupa_limitNumber of records per pageCoupa_offsetoffset value, is set as “0” as we intend to fetch all the pages without missing a single recordStartDateDate from which the records to be queried, refers to updated-at in Coupa resourceUnderstanding Offset and Limit in Coupa PaginationIn this design, the two key parameters to control data retrieval are Offset and Limit. Unlike cursor-based pagination (as seen in Salesforce and MS Dynamics), Coupa follows an offset-based pagination approach, where:Limit – Defines the number of records per page (e.g., if set to 10, each API call fetches 10 records).Offset – Determines the starting point for the next batch of records. In our design after each request, the offset is incremented by the limit value (10) to fetch the next set of data.The initial request starts with Offset = 0 and Limit = 10.The next request will have Offset = 10, Limit = 10, fetching records from 11 to 20.This process continues in a loop until the last page is reached.Looping Process Call : “Initiate Coupa Call”This Looping Process Call continuously runs until the field is absent from the response payload. The condition for looping is: ${property.RecordsPresent} = ‘true’I have used a Groovy script to help us determine when to stop fetching records.After each Coupa API call, the response is checked for the following error:<errors>
<error>No results match your search criteria.</error>
</errors>If this error is found, it means there are no more pages left, and the script sets: RecordsPresent = false (End of pagination)Otherwise, if valid records are present, the script sets:RecordsPresent = true (Continue fetching the next page)The Looping Process Call continues until RecordsPresent = ‘false’, ensuring controlled pagination.Coupa receiver adapter:Theadapter is placed inside the Looping Process Call to fetch records page by page.Processing Tab– Operation : Query Resource– Resource : Resource dynamically passed from ${property.CoupaResource}– Return Object Fields : Fields dynamically passed from ${property.fieldnames}– Limit : Defines the number of records per page (e.g., 10 records per page). This value cannot be passed dynamically and only accepts numeric values. To control it, we declare “Coupa.limit” as a property.– Offset : Determines the starting point for data retrieval in pagination. It must always start at 0 and cannot be passed dynamically, as it only accepts numeric values. To handle this limitation, we declare “Coupa.offset” as a property and increment its value after every API call to fetch the next set of records.TheAdapter supports setting this via the properties “Coupa.limit”and “Coupa.offset” allowing better control over pagination.We are not digging into the connection tab in this blog, this blog focuses only on pagination concept.Groovy Script : Set Coupa Offset ParameterBelow script control the offset incrementation as well as last page identification import com.sap.gateway.ip.core.customdev.util.Message
def processData(Message message) {
// Get the property value from the message and cast it to an integer
def propertyValueString = message.getProperty(“Coupa_offset”)
def propertyValue = propertyValueString ? propertyValueString.toInteger() : 0 // Convert to integer, default to 0 if null or empty
def Coupa_limit = message.getProperty(“Coupa_limit”) as Integer
// Add 50 to the Coupa_offset
propertyValue += Coupa_limit
// Set the modified offset value back to the message
message.setProperty(“Coupa_offset”, propertyValue.toString()) // Convert back to string for setting property
// Parse the body of the message
def body = message.getBody(java.lang.String)
def slurper = new groovy.util.XmlSlurper().parseText(body)
// Check if the specific error message is present in the body
def errorPresent = slurper.depthFirst().find { it.name() == ‘error’ && it.text() == ‘No results match your search criteria.’ } != null
// Set the CountObjects property based on the presence of the error message
message.setProperty(“RecordsPresent”, !errorPresent)
return message
} Router : “Data?” -A router is used to determine whether the fetched page contains any records. If records are present, the integration flow proceeds to process to next steps. However, if the page is empty, the flow terminates immediately, preventing unnecessary processing.This check ensures optimized execution by stopping the pagination loop when there are no more records to fetch, improving efficiency and reducing unnecessary API calls.Process Call : “Message Mapping” – After fetching each page, the data is passed to Message Mapping, where it is processed before being sent to the target system. This step is target-specific and is not covered in this blog.For demonstration purposes, I have included this step to mimic the “send to target” operation after message mapping. The key focus of this blog remains on pagination handling in the Coupa adapter.Output / offset and limit explaintion:For a total of 54 records, the pagination process spans 6 pages:First 5 pages contain 10 records each.Page 6 contains the remaining 4 records.When the 7th page is fetched, the response contains as below, which means end of records/pages.<errors> <error>No results match your search criteria.</error> </errors>Page 1 : Response from the Coupa system contains 10 records as per the set limit and with offset = 0 Page 2 : Offset = Offset (10)+ limit(10) = 20, the adapter skips the first 10 records and responds with next 10 records in this call.Page 3 : Offset = Offset (20)+ limit(10) = 30, the adapter skips the first 20 records and responds with next 10 records in this call.Page 4 : Offset = Offset (30)+ limit(10) = 40, the adapter skips the first 30 records and responds with next 10 records in this call.Page 5 : Offset = Offset (40)+ limit(10) = 50, the adapter skips the first 40 records and responds with next 10 records in this call.Page 6 : Offset = Offset (50)+ limit(10) = 60, the adapter skips the first 50 records and responds with next 10 records in this call. This is the last page containing 4 records.Page 7 : Offset = Offset (60)+ limit(10) = 70, the adapter skips the first 50 records and responds with next 10 records in this call. <error> tag in the response which means end of records/pagesFull run in TRACEWrapping UpWith limit and offset handling in place, we achieve a structured and efficient pagination mechanism in Coupa Adapter. By leveraging properties like Coupa.limit and Coupa.offset, along with a controlled looping process, we ensure seamless data retrieval without unnecessary API calls.This approach not only optimizes performance but also ensures that large datasets are processed efficiently and accurately. Stay tuned for the next blog in this series, where we continue exploring pagination strategies across different adapters! I hope this helps!Cheers,Punith Read More Technology Blogs by Members articles
#SAP
#SAPTechnologyblog
