topic Re: SugarCRM Filtering Records in Data Movement & Streaming

SugarCRM Filtering Records

jpacker — Wed, 19 Mar 2025 21:46:28 GMT

Hello All,

I'm just trying to do a fairly basic filter of the Accounts module from SugarCRM. I've been following the guidance (SugarCRM | Qlik Connectors Help) along with Sugar's own (/Filters GET - Sugar Support) which has lead me to the below, but I keep getting a less than helpful error with the filter.

LET vToday = Date(Today(), 'YYYY-MM-DD');

SELECT *
FROM Accounts
WITH PROPERTIES (
max_num='200',
order_by='date_modified:DESC',
filter='date_modified$gte=$(vToday)',
fields=''
)

Appreciate any thoughts

Cheers

Re: SugarCRM Filtering Records

diegozecchini — Fri, 14 Feb 2025 10:21:14 GMT

Hi!

try this

SELECT *
FROM Accounts
WITH PROPERTIES (
max_num='200',
order_by='date_modified:DESC',
filter='[{"date_modified": {"$gte": "$(vToday)"}}]',
fields=''
)

then check if vToday is formatted correctly. In Qlik, Date(Today(), 'YYYY-MM-DD') should work, but you might need to convert it explicitly to a string:

LET vToday = Date(Today(), 'YYYY-MM-DD');
However, try explicitly defining it as a string:
LET vToday = "'" & Date(Today(), 'YYYY-MM-DD') & "'";

Re: SugarCRM Filtering Records

diegozecchini — Fri, 14 Feb 2025 10:23:13 GMT

my idea also is reviewing the filter. Try running the query without the filter parameter to confirm that data is being returned correctly. If the base query works, then you know the issue is with filtering.

Re: SugarCRM Filtering Records

jpacker — Fri, 14 Feb 2025 10:38:45 GMT

Tried that thanks, runs beautifully without the filter

Re: SugarCRM Filtering Records

jpacker — Fri, 14 Feb 2025 10:40:02 GMT

Sir, you are a gent and a scholar and I greatly appreciate your assistance

Re: SugarCRM Filtering Records

diegozecchini — Fri, 14 Feb 2025 10:40:29 GMT

cool, great. Glad to be helpful