At ZangaBee, we enjoy turning complex limitations into efficient, scalable solutions. One of our recent integration projects involved exactly that.
The challenge
We were working with a legacy WMS system that could only export product data as a nightly flat file dump — thousands of lines, every time. There was no way to export only updated products.
To complicate matters further, this export was formatted as a fixed-width CSV file with a single column per line. Simply forwarding every product to the target system would have quickly exceeded API rate limits.
Our solution
We needed a way to identify which product records had actually changed since the last run. By combining Celigo’s new Lookup Cache feature with JavaScript scripting, we turned this full export into a smart, delta-style integration — only sending records that had changed.
Here’s how we did it
1. Splitting the line and generating a checksum
The export file is pulled from an FTP server and loaded as a pipe-delimited CSV. Each line initially appears as one long string.
To break this down and create a change identifier, we used a transform script hook that:
- Splits the line into usable fields.
- Generates a checksum (for example, using a hash function) based on the data.
If your data is already structured into columns, you can skip the splitting and generate the checksum directly from options.record.
2. Comparing with cached checksums
During the PreSave phase of the export, we:
- Fetch the known checksums from the Lookup Cache using a single API call per page (not per record).
- Compare the current record’s checksum with the one stored in the cache.
If the checksum has changed, the record is flagged for processing by setting a changed = true property, which is later used as a filter in the import step.
To keep things performant:
- The export page size was set below 1000 (the max supported by the Lookup Cache API).
- Lookup calls were made outside of any loop, ensuring just one cache call per page.
3. Updating the Lookup Cache
Any updated or new checksums are written back into the cache at the end of the process, so they’re available for the next nightly run.
The result
Instead of pushing thousands of unchanged records every night, the flow now detects and processes only the deltas — keeping the integration light, fast, and well within rate limits.
This approach not only solved a technical bottleneck but also gave our client a future-proof pattern for working with static exports from legacy systems.
