Back to connectors… Back to Sage CRM...

SageCRMCustomWhere - little help with this lookup? (10 replies)

Arline Welty
3 years ago
Arline Welty 3 years ago

Can I get your take on this? I think it's a similar issue to this one: https://www.realisable.co.uk/sage-crm/merge-variables-into-a-where-statement/

I'm trying to do something similar, trying to return CompanyId but filter out null or deleted records. The idea is to find a match on "comp_bvcustnodq" in CRM with the CSV import file column CUS_NO:

CustomWhere:

SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq" = "&%CUS_NO &" and (comp_deleted is null and comp_status 'Inactive'), "comp_companyid", false)

Error:

Line=1;Pos=66; Unexpected identifier [WYNUFNNEDTBX] expected [,].

Support
3 years ago
Support 3 years ago

Hi Arline,

That error message isn't very helpful. I'll log a ticket to improve it.

Your query is malformed however, please see corrected query below:

SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq = " & %CUS_NO & " and (comp_deleted is null and comp_status  'Inactive')", "comp_companyid", false)
SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq = " & %CUS_NO & " and (comp_deleted is null and comp_status  'Inactive')", "comp_companyid", false)
Support
3 years ago
Support 3 years ago

Logged ticket IMAN-888

Arline Welty
3 years ago
Arline Welty 3 years ago

Excellent! So I just needed the spaces in  my query then? This works!

Arline Welty
3 years ago
Arline Welty 3 years ago

Actually, hold up, when I try to run this within the IMAN job I'm getting this error during the mapping stage.

Any ideas on how I can fix this?

Error whilst evaluating field [CompanyId] on transaction type [Record].
Resolved Function - SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq = " & "000092" & " and (comp_deleted is null and comp_status 'Inactive')", "comp_companyid", false)
Error - Description: Index was outside the bounds of the array.  

Support
3 years ago
Support 3 years ago

Arline, try this:

SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq = '" & "000092" & "' and (comp_deleted is null and comp_status  'Inactive')", "comp_companyid", False)
SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq = '" & "000092" & "' and (comp_deleted is null and comp_status  'Inactive')", "comp_companyid", False)

There are two changes:

  1. I have placed single quotes around the query string
  2. I have altered false to False.
Arline Welty
3 years ago
Arline Welty 3 years ago

Hm, now I get an error when I try to check the formula. I've tried the following:SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq = '" & "%CUS_NO" & "' and (comp_deleted is null and comp_status 'Inactive')", "comp_companyid", False)SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq = '" & "%CUS_NO" & "' and (comp_deleted is null and comp_status 'Inactive'), "comp_companyid", False)Error for both of these is:Line=1;Pos=69; Unexpected identifier [OUCVZNTZVVOW] expected [,].

Arline Welty
3 years ago
Arline Welty 3 years ago

Couple more attempts:

SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq = '" & "%CUS_NO" & " and (comp_deleted is null and comp_status 'Inactive'), "comp_companyid"', False)

New - 04

SageCRMLookupCustomWhere("CRM", "Company", '"comp_bvcustnodq = " & "%CUS_NO" & " and (comp_deleted is null and comp_status 'Inactive')', "comp_companyid", False)

Line=1;Pos=171; Unexpected identifier [NewLine] expected [)].

New - 05

SageCRMLookupCustomWhere("CRM", "Company", '"comp_bvcustnodq = " & "%CUS_NO"' & " and (comp_deleted is null and comp_status 'Inactive'), "comp_companyid", False)

Line=1;Pos=171; Unexpected identifier [NewLine] expected [)].

New - 06

SageCRMLookupCustomWhere("CRM", "Company", '"comp_bvcustnodq = " & "%CUS_NO" & " and (comp_deleted is null and comp_status 'Inactive')', "comp_companyid", False)

Line=1;Pos=171; Unexpected identifier [NewLine] expected [)].

Any insight would be helpful. Thank you!

Support
3 years ago
Support 3 years ago

Arline, you're missing a closing double quote after the close bracket.

Try this...if it still doesn't work, we can setup a GotoMeeting.

SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq = '" & %[CUS_NO] & "' and (comp_deleted is null and comp_status  'Inactive')", "comp_companyid", False)
SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq = '" & %[CUS_NO] & "' and (comp_deleted is null and comp_status  'Inactive')", "comp_companyid", False)
Arline Welty
3 years ago
Arline Welty 3 years ago

Thanks for that- now I get "No errors found" when I check the script itself. But when I try to actually do the transform with data, I'm seeing this error during the job:

Error whilst evaluating field [CompanyId] on transaction type [Record].
Resolved Function - SageCRMLookupCustomWhere("CRM", "Company", "comp_bvcustnodq = '" & "000092" & "' and (comp_deleted is null and comp_status 'Inactive')", "comp_companyid", False)

Error - Description: Index was outside the bounds of the array.

2 years ago
Arline Welty 2 years ago

Closed due to inactivity.

Contact

Realisable Software Ltd provides code-free, cost-effective applications integration solutions for SMEs. Our core IMan product is designed to integrate almost any application with a number of Sage solutions and online payment processors.

Looking to purchase IMan, please see our resellers here.

Realisable Software
Ph: +44 (0) 208 123 1017

Copyright © Realisable. All rights reserved.
Realisable is a registered trademark

Close

Request Demo

Realisable Software Ltd provides code-free, cost-effective applications integration solutions for SMEs. Our core IMan product is designed to integrate almost any application with a number of Sage solutions and online payment processors.

Looking to purchase IMan, please see our resellers here.

Realisable Software
Ph: +44 (0) 208 123 1017

Copyright © Realisable. All rights reserved.
Realisable is a registered trademark

Close

Access Downloads

Realisable Software Ltd provides code-free, cost-effective applications integration solutions for SMEs. Our core IMan product is designed to integrate almost any application with a number of Sage solutions and online payment processors.

Looking to purchase IMan, please see our resellers here.

Realisable Software
Ph: +44 (0) 208 123 1017

Copyright © Realisable. All rights reserved.
Realisable is a registered trademark

Close