There is a script that aims to retrieve up to 200 records from the Data Extension, perform some manipulation with retrieved values and lastly delete processed records from the Source Data Extension.
If there are only 200 records - it works fine but then an additional requirement appears that Data Extension might have more than 200 records thus we decided to iterate the flow in 200 records batches and here is when the problem occurs.
Let's say, the Data Extension has 300 records, the first batch of 200 records is processed and then deleted but when it comes to the second batch the script views the Data Extension records as they were initially presented, which means as there are 300 records still rather than actual 100 left after deletion in the first iteration. So that, it looks like that when the script is executed it snapshots the Data Extension and you can only work with that version of the Data Extension within that particular script execution.
How can we dynamically retrieve and delete retrieved records from the same Data Extension?
%%[ FOR @a = 1 TO 2 DO SET @LookupOrderedRows = LookupOrderedRows("Data_Extension", 200, "Field1 asc", "All", 1) IF RowCount(@LookupOrderedRows) > 0 THEN FOR @i = 1 TO RowCount(@LookupOrderedRows) DO SET @Field1 = Field(Row(@LookupOrderedRows, @i), "Field1") /* Skipped retrieval of more fields */ DeleteData("Data_Extension", "Field1", @Field1) NEXT @i ENDIF NEXT @a ]%%
- Cannot retrieve more than 200 records per batch
- Tried to simply have 2 LookupOrderedRows one after another (not within the loop) and the result is the same
- Checked amount of records of the Data Extension with DataExtensionRowCount AMPscript function and it always shows 300 records at any given position within the script
- Tried doing the same with SSJS Platform functions, which resulted in the same outcome
Adam's suggestion about Data Extension caching sounds exactly what we are experiencing but there is no relation to Email Sends. We are running the script in the CloudPage and will run it later in the Script Activity. Also, tried running it with 2400 records, which is what should have solved based on the Knowledge Article.
The main problem is still there - when we are doing a Lookup, Data Extension records got cached and because of that you cannot "mark" with UpdateData or DeleteData functions which records have been processed in a batch, so that the next batch will pick other records. This leads to the same outcome - only the first 200 records are processed.