Hi Siri,

Is this query a report that is emailed to you periodically, or is it from some other function?

It is the email form on the My Assistant task (pre-existing, was called "Contracts that have not been billed in specified days" and I modified it with date specific to 90 days).  It auto-generates an email with a table of information in it.  I can select various fields but don't know how to properly tie them so that they actually populate.

It can depend on the specific fields and tables you are trying to add - what fields are you adding to the report?

MyAssistant use the Job Summary field on the Contract Derived record to get from the Contract to the Job.  But Sage has never populated the the field.  As a result, it is blank and the direct relationship from the Contract to the Job always returns blanks.

You have to use a formula to get the value off the job record.  For example, Let's say you wanted to retrieve the Cost_Account_Prefix from the job record.  Create a formula like:

Select j.cost_account_prefix from JCM_MASTER__Job j where j.job = "CNC_ACTIVE__CONTRACT"."CONTRACT"  Then add the formula to your email format. 

The j is an alias for JCM_MASTER__Job.  Where I had Cost_Account_Prefix, you can substitute the job field that you want.  

Hope that helps  

Thank you.  I see what you mean, but I am still a bit technologically restricted. I found the area where you Define the Formula on the email section.  I copied your formula but came up with an error.  I then went and checked and MA sees Job as 10 characters and Contract as 11 characters.  Both job and contract would appear as five digits, followed by a dash, followed by a letter.  (for example, 19025-C).  I thought maybe I could use the substring function to limit what the system was looking for in order to make a match, but I am not a programmer and I am sure I have some sort of syntax not right, because when I test it, it doesn't work.

Here's what I've got:

Select "JCM_MASTER__JOB"."Pending_CL" where SUBSTRING("JCM_MASTER__JOB"."Job",1,7) = SUBSTRING("CNC_ACTIVE__CONTRACT"."Contract",1,7)

The Pending CL field is a custom field (and yes I have synced those in MA) that tells me if a job is pending closure.  For this report, it would be really helpful to know if a job/contract hadn't been billed in 90 days but was pending closure (then I wouldn't need to investigate).

What did I do wrong?

THANKS!

I realized I didn't include the "From table name" before the where clause but I am still not having luck.  Have tried a variety of options - currently I've got:

Select Pending_CL from JCM_MASTER__JOB where
"JCM_MASTER__JOB"."Job" = "CNC_ACTIVE__CONTRACT"."Contract"