SugarCRM Filtering Records

[jpacker]

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

[diegozecchini · Accepted solution]

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') & "'";

[diegozecchini]

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.

[jpacker]

Tried that thanks, runs beautifully without the filter

[jpacker]

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

[diegozecchini]

cool, great. Glad to be helpful