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

I am using n8n - I can pull a very basic field restricted list from accounts (for example) using this 
/rest/v11_16/Accounts/filter?max_num=50&order_by=id%3ADESC&fields=id,name

But I haven't been able to work out where to build more complex filters spanning multiple related modules.

I'm not able to work out where to put the Filter expressions like you show in examples - query params, header, body etc 

filter=[{"name":"Nelson Inc"}] going in Header / Body

what type is it expecting

would you do a new field for every filter or build them into one huge expression.

So far I haven't found a place that i can enter even a single name filter and return on that. 



Any help or pointers you can give to get started would be great - as I imagine I will have to try and recreate the massive / complex reports that the data guys are building in Sugar in this filterapi...

We need to replicate a pretty complex report with lots and lots of joins across main modules / custom modules etc - my data guys here are saying this approach won't work at all because there are so many joins - and something about there being hidden helper tables that connect for example accounts to contacts - with over 400k rows - so not sure if we need to reference the hidden helpers or direct accounts to contacts relationship. Also if You are using for example Opportunities as the starting module, then you want to get the contacts who can be linked to multiple accounts  - so its 2 or 3 layers deep effectively - how does that work with field relationship naming....?

(I am advanced Sugar user - wrote most of the original reports used in the business - but newish to api's)

Parents
  • Hi  , 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  , 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  ,

    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  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  , 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

Reply Children
No Data