topic Re: Filtering SugarCRM Records in Connectivity & Data Prep

Filtering SugarCRM Records

fredlee33 — Fri, 02 Apr 2021 08:13:16 GMT

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'

);

Re: Filtering SugarCRM Records

scottduthie — Mon, 01 Feb 2021 23:05:04 GMT

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.

Re: Filtering SugarCRM Records

chrisbrain — Tue, 02 Feb 2021 08:27:38 GMT

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?

Re: Filtering SugarCRM Records

scottduthie — Tue, 02 Feb 2021 13:59:12 GMT

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=''
);

Re: Filtering SugarCRM Records

chrisbrain — Tue, 02 Feb 2021 15:37:59 GMT

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/Web_Services/REST_API/Endpoints/modulefilter_GET/index.html

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

Re: Filtering SugarCRM Records

scottduthie — Tue, 02 Feb 2021 16:35:06 GMT

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?

Re: Filtering SugarCRM Records

chrisbrain — Wed, 03 Feb 2021 06:58:05 GMT

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.

Re: Filtering SugarCRM Records

scottduthie — Wed, 03 Feb 2021 14:17:08 GMT

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=''
);

Re: Filtering SugarCRM Records

chrisbrain — Fri, 05 Feb 2021 07:37:44 GMT

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.

Re: Filtering SugarCRM Records

scottduthie — Mon, 08 Feb 2021 16:27:29 GMT

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.

Re: Filtering SugarCRM Records

chrisbrain — Fri, 05 Mar 2021 07:56:47 GMT

@Hi @scottduthie - It took us a little longer to get this release out but you should now see filter and fields parameters - as shown for example here:

Note there is a link in the description at the top to a page which will give some support on constructing the filter string.

Re: Filtering SugarCRM Records

scottduthie — Fri, 05 Mar 2021 13:52:14 GMT

Hi @chrisbrain ,

Thanks for pushing this update through. We'll go test it out.

Scott.

Re: Filtering SugarCRM Records

DGO — Sat, 10 Apr 2021 01:12:34 GMT

Will this be released on the on premise Web Connectors as well? My SugarCRM opportunity table has over 400,000 records and it would be great to filter by a date field.