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)

Logged ticket IMAN-888

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

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.  

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.

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 [,].

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!

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)

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.

Closed due to inactivity.