Some help getting started with using FilterAPI to create sudo reports in n8n please

Hi Adam Ososki , first of all for the N8N part:
You need to send POST request to the <module>/filter endpoint with the body


But I would agree with your data guys, that if you would like to reproduce complicated Report conditions, the effort to go through all the relationships and modules would be just too much.

Instead of recreating the Report conditions, you could just directly get Report data via Reports/:id/json.


Please let me know if that doesn't work for you for any reason.

 Hi Constantine Shpikat , firstly thank you for responding!

I am currently pulling the reports exactly that way - but the issue is - that means to run our report - someone needs to change the sugar report criteria every month manually just before the report is run again - and that relies on too much human-in-the-loop. 

What I want to be able to do is create expressions inside n8n that create the run time query changes programmatically. 

For example - one report brings back a ton of info from about 6 tables that only relates to items that were purchased in this month - in the last 4 years - and checks that it won't have expired (been returned) by the time the report is run. In the Sugar Report its 4 "OR" statements - with between dates for each. In n8n I can take the {{now}} date, extract the month, calculate this year, this year -1, this year -2 etc etc and build an expression that inserts the between parameters into the query. (Or at least that was the plan. 

My original question against the Dev Blog - was simply "can I send run time parameter queries in the url for a report api call" and the answer was - why are you not using FilterAPI to get the data?

Hi Adam Ososki ,

Sorry, I didn't have full context.

I believe what are you trying to achive is called BI tool :) and what companies usually do to have an extract of data for the BI tool is the integration or the daily backup of the database to feed into the local source (i.e. local MySQL, which will be the source of the data for BI).

But n8n sounds to me personally as a more flexible way of getting direct access to your data in the enterprise systems (i.e. CRM, ERP, etc).

I don't believe in SugarCRM we have REST API to create/update the Report. At least, I can't find it in the documentation.

But... (and now I step on a very dangerous path)...

Disclaimer: that is an undocumented endpoint. I don't see it in the official documentation. That is definitely not a "recommended way". You cannot rely on it as a permanent solution. "Not a financial advice" ( c )

... but, as a temporary workaround to have at least some way to update the Report filtering... There is a POST /Reports/{{$REPORT_ID}}/updateReportFilters endpoint, which works for me (I tested right now on 25.1).

Be sure that you have date condition as runtime filter, and please check in the browser->dev tools what runtimeFilterId Sugar is sending to update the Report's runtime filter (Network tab for developer tools in browser, filter by request "updateReportFilters"), so you can do the same from n8n:

After I updated the runtime filter and send the HTTP request to get JSON data based on Report conditions, I've got my one record I updated today (see the conditions I applied: date_modified -> between_dates -> yesterday/today).

I am sure that is a "cutting the corners" way, which I should not recommend. So I would recommend the right way, which is: you need to get the data clone and run the query you need. Of course, all the security concerns of accessing data clone should be taken care of. I wonder if that is a task for your IT department.

Hi Constantine Shpikat thank you for your continued patience with me...

The midterm plan is building a data warehouse which is 4-6 months out. I am trying to use n8n as an interim automation solution for them. 

When I load a report with runtime filters in UAT and apply them in UAT I only get 


I can't for the life of me find that updateRuntimeFilter (we are not on the sugar cloud - Enterprise 12 I think) 

I tried loading the report_def

but it ignores it and brings everything back regardless

sorry to be so dumb at this!

Hi Adam Ososki , no problem, I don't believe there is quick and easy way of learning things rather than just spending time on this or asking other people.

You are digging into the right direction, but you are investigating the HTTP request for saving the Report record itself.

I was referring the HTTP request, which you trigger when you RUN the Report and change the runtime filter:




I just want to repeat-repeat myself that this is a non-official way and you are taking full responsibility.

When I hit apply:

in docs I only get this:

in fetch/XR i just get this

If I choose ALL and filter for runtime I get nothing at all

First - I totally understand and acknowledge the non-officialness.

I had our Head of Web look at the DEV and he says its completely different to what you are showing - we are not on the sugarcloud - and using enterprise 12 - but where your screenshot shows api rest calls - we have that php instead - so I think this route can't work for our install?

12.... Auch.... Sorry to hear that. Do you have any plans to upgrade it to the latest version? I believe 12 is already retired and not supported.

way above my head.... but I would imagine we would have a data warehouse in place before an upgrade... I think 80% of our sugar instance is dev - so upgrading is never easy. 9 to 12 took a year I think

So going back to the Filter API lol - I have been able to make it do a basic filter on Accounts - but I can't seem to find anyway to pull in related fields.  

I have tried every different combination of  modulename.fieldname I can think of and nothing ever pulls across...

/rest/v11_16/Accounts/filter?max_num=50&order_by=id%3ADESC&fields=id,name,contacts.salutation