Filtering SugarCRM Records

[fredlee33]

How does one specify selection criteria when using the Beta Web Connector for SugarCRM in Qlik Sense Cloud?  Is there an additional parameter that must be added to the SELECT statement PROPERTIES?

SELECT

     id,

     name,

     date_entered,

     date_modified

FROM CustomModule

WITH PROPERTIES (

     moduleName='MyCustomModule'

     ,max_num='10000'

     ,order_by='date_modified:DESC'

);

[scottduthie]

Hi fredlee33,

Did you ever make progress on this?

There's still very minimal content on the community about using the Sugar connector - I'm struggling to work out the syntax to filter the API requests.

[chrisbrain]

Hi - Currently the connector does not have a parameter to supply an additional filter, it just extracts all the records (although it does have order by and max results parameters to limit the results).

Do you need an additional filter because it is just too slow to extract everything and then do the filtering in the Qlik app?

[scottduthie]

Yes that's exactly the issue - the extract process is incredibly slow and often falls over and fails, or hangs and then fails. (We are using a Sugar administrator account).

Ideally we'd like to filter the extract by something like 'date_entered' (for Contacts) - this way we can set up an incremental process on the Qlik side and just grab the little bit of new data we need each day. Is filtering the extract possible? The documentation suggests using the 'CustomRequest' table for filtering.....but I can't work out the syntax.

There is this example in the documentation: 'Contacts?filter0date_entered$lt=2015-01-01&fields=id,name'. But I'm not sure where in the footer of the query you'd write this?

This is what the default 'footer' of the query looks like - how do you add a filter in here?

FROM Contacts
WITH PROPERTIES (
max_num='',
order_by=''
);

[chrisbrain]

It looks like we would have to add this as a separate input. Looking at the docs:
https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_10.2/Integration/We...

Would it be OK to add an additional 'filter' parameter to the tables where you could enter something like:
[{"probability": {"$gt": "50"}}]

And a fields parameter where you could enter something like:
id,probability

?

[scottduthie]

I had started to work my way through the Sugar API documentation - however what I was hoping/assuming was that as Qlik was offering the Sugar web connector as an out of the box solution in the SaaS platform there would be some relatively easy mechanism to filter records.

It's actually a client of ours using the Sugar connector - hand encoding a REST URL based on their filter criteria is a little less 'self-service' than desired.

Do you know if you can squeeze parameters like you mentioned above '[{"probability": {"$gt": "50"}}]' into the default script generated by the connector, or do you think it's best to just ditch the Sugar connector and use the default REST connector and hand encode the URL based on the Sugar API documentation?

[chrisbrain]

Hi @scottduthie - I don't have a good suggestion for squeezing the parameters in - I think it's best if we add these parameters as per my suggestion above as it seems like they will be useful to all SugarCRM Connector users.

[scottduthie]

Hi @chrisbrain - thanks for the speedy replies here - I think adding an additional 'filter' parameter to the tables as you suggested would be extremely helpful! How long does a change like this normally take to make its way through dev cycles and into a release?

In lieu of having that filter parameter within the data preview GUI, can you confirm whether within the current functionality of the connector you can filter records by hand-coding the parameters into the query? The documentation says to use the 'CustomRequest' table for filtering. There is this example in the documentation to filter contacts based on date entered: 'Contacts?filter0date_entered$lt=2015-01-01&fields=id,name'. But where in the 'footer' of the auto-generated query would you write this?

FYI - by auto-generated footer I mean this bit:

FROM Contacts
WITH PROPERTIES (
max_num='',
order_by=''
);

[chrisbrain]

Hi @scottduthie - We have added those two filters but it still needs some additional testing. I think it's possible this might get into a new release next week week but this is definitely a personal view and not a commitment etc. 🙂

Sorry - but I don't have a suggestion for a workaround using the existing version.

[scottduthie]

Thanks for the update @chrisbrain , looking forward to having this available!

We have been experiencing a lot of 'socket closed' errors when trying to load more than about 10,000 rows from a table - so we're hoping the ability to filter might make the data extracts smaller and more stable. FYI - we have an open ticket regarding the socket closed errors.