Hello all,

I am currently trying to retrieve all Google Reviews for multiple locations for one of my clients, who operates 35 locations. Some of these locations have over 50 reviews, and since Google Reviews uses pagination through a nextPageToken for more than 50 reviews, I need to handle this pagination effectively.

The client is running TimeXtender version 20.10.52.64, and I am using the CData connector version 24.0.8999.0. I have reviewed the documentation on the TimeXtender support pages about nesting an API call and utilizing pagination. I am attempting to set up the Query Slicer to loop through the locations and retrieve all reviews per location, but I am encountering difficulties.

Here’s what I have set up so far:

I created two .rsd files:

1. locations.rsd: Retrieves a list of all location IDs. This returns 35 locations, as expected.
2. locationsReviews.rsd: Retrieves all reviews for a given location. This works correctly when I test it, returning 397 reviews for one location ID.

(I've included these in this message as .txt files)

Based on my understanding, I should be able to combine these two files using the Query Slicer to retrieve all reviews for all locations. However, I am getting stuck when trying to do this. Here is the SQL code I’m using in the Query Slicer:

SELECT *

FROM locationsReviews

WHERE LocationId IN (

    SELECT SUBSTRING(Bname], CHARINDEX('locations/', iname]) + 1 + LEN('locations/'), LEN('name]))

    FROM locations)

Note: The substring function is extracting the LocationId from a longer text field that also includes an account ID and pathname.

When I run this code, it doesn’t return any results. However, if I test the Query Slicer by selecting just one location, it works as expected. For example:

SELECT *

FROM locationsReviews

WHERE locationId = '69328694563260864'

This returns the 397 reviews I was expecting, and similarly, using a different ID returns the correct number of reviews (152) for that location. But when I try to combine multiple location IDs, I get no results:

SELECT *

FROM locationsReviews

WHERE locationId IN ('69328694563260864', '15487095234204958953')

It seems like the query is not accepting more than one location ID as a parameter. Could you help me understand why this is happening, or guide me on how to correctly set up the Query Slicer to loop through all location IDs and retrieve reviews accordingly?

Thank you in advance.

Rob