Recursive macros

[pablo_luna]

Hi,
I'm trying to get some data from Salesforce using the new Salesforce Reporting REST API. I managed to get it to work with the download connector, but the API returns a max of 2000 records and a flag field in true or false to indicate if all fields were returned.
Since I don't know the total number of records in the report, I wanted to build a recursive macro that keeps quering Salesforce until flag field was true. This could be easily achieve by having a macro calling itself with some parameter that it would change in every iteration. I try to do this, but I got an error message saying that a macro cannot be insterted into itself.
Is there any way of doing what I need to do?
Note: Salesforce connector is not an option because it can't see the objects that I want to use.

Thank you,
Pablo

[rahuls · Accepted solution]

Hi Pablo,

I am wondering why are you not using the Salesforce connector. The Salesforce connector wont display all the objects in the list but when you manually write the object name in the Table field and click on run, it will be able to access the object and all its fields.
The Salesforce connector can access all the objects in the salesforce if it has access from Salesforce end. The below image shows a similar case where OpportunityHistory is not available in the list but when we type it manually in the table field it will be able to access it.

Let me know if it helps.
 

[pablo_luna]

Hi Rahuls,

I had no idea about that and I can see how would that work. The main difference with both approaches is that SFDC Reports contains data from many objects, and the SFDC connector would return data from a sigle object (or table), so I would have to do more than one query and then the joins.
If the SFDC connector would allow to run SOQL queries (supported by SFDC API), then one would be able to return aggregated data. Maybe an improvement?
I have fixed my issue by implementing an incremental load type of approach, so I no longer need to retrieve more than a couple hundread records at a time.

Thank you for answering my question!

[benjamin_carley]

Hello Pablo,

I see you have already solved your issue but incase you still wanted to go down the macro API route I'd like to share a solution.

When using the download tool to get a large response for the SalesForce API, SalesForce will paginate your reponse. To get the rest of the data you can use the "rel='next'" link in the downloads header to find the next page. 

Using an iterative macro would be a fun approach for this, I've included a screenshot of how I would start off but unfortunately I'm just sorting out a license renewal so everything has locks on it.

Starting off, make the call to SalesForce using the API url using the download tool. With the downloaded data, use a combination of json parsing and the cross tab tool to get your data into a correct format. This data can be then filtered through the 'completed' output of the iterative macro.

To get the next page, parse the URL of the next page of the request from the "Download Headers" field. I've done this using regex parse with the following expressions.

1. <.*rel="next" - for the "Get link Line" tool
2. http.*page=d - for the "Get link URL" tool

This is almost definitely not the best way to regex the URL as I haven't used grouping so I'd have a play and see if you can get it into one tool. 

Next check if the URL is not null, as it would be if you've reached the end of the request and there are no more pages, and pass then pass the URL through the iterative output to be fed back in again. This macro can then be dropped into another workflow and give you what you're after.

Hope this was interesting,
-Ben

[pablo_luna]

Hi Benjamin,

That's very close to what I started doing, but I couldn't find a way of implementing the loop that would call this macro until the next URL is null.. Do you know how to do that?

Pablo

[benjamin_carley]

Hello Pablo,

I would have a filter before the iterative output, the one that usually sends a url back for another request. Inside this filter I would use the expression "[Next URL] == Null()" and take the output from the false branch. This means that when there are no more pages and the regex can't find another 'next' url, the output from that will be null. This means no data will be passed through the iteration output and the macro will finish.

If you like I can email you a work flow for you to look at?

-Ben

[Aguisande]

Hi Ben,

I think uploading the workflow here could help a lot of users. This case is a very frequent use case.

Thanks

[BCarley]

I know this is VERY late response, but we have recently uploaded a macro that makes use of recursive iteration to download data from an API, in this case, Squirro the context intelligence tool.

Squirro Download Macro

I will attempt to upload a simpler example later, in case there are still people who are interested in learning about iterative macros.

[jayalakshmi]

Hi Ben ,

Can you email me  the recursive workflow 

[shevshenko]

Hello Ben, regarding this response you posted a while ago, would you share the iterative macro / workflow you created to pull Data from salesforce? thanks!