Thread Details
  • State Not Answered
  • +1 person also asked this people also asked this
  • Replies 12 replies
  • Subscribers 373 subscribers
  • Views 305 views
  • Users 0 members are here
Forum Actions
Related

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

Adam Ososki

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 Reply Children
  • 0 in reply to Constantine Shpikat

     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?