x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 4 replies
  • Subscribers 74 subscribers
  • Views 362 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
MichalPoncak

How to retrieve a list of records for company id.

Posted By

Hello,

I am trying to retrieve a list of projects with associated company. (for example: i am trying to do the very same thing where when you find your company and click on 'People' tab then youll get all people associated with this company.)

So far i created my custom entity Project which holds a list of project records. I created a list for company called 'CompanyProjects' along with tab called 'Projects'.

Below is the code of my custom page:

//var screen = CRM.GetBlock("CompanyProjects");
var resultgrid = CRM.GetBlock("Company Projects");
var myBlock = CRM.GetBlock("Container");

CompanyId=CRM.GetContextInfo("Company","Comp_CompanyId");

record=CRM.FindRecord("Project","project_companyid="+CompanyId);

with(myBlock)
{
AddBlock(resultgrid);
}

resultgrid.ArgObj = record;
CRM.AddContent(myBlock.Execute());
Response.Write(CRM.GetPage());

The problem is that i am getting an error message saying "SQL Error".

Below is the SQL log:

Nov 25 2013 16:36:42.795 4344 7056 1 fselectsql,time,sql,errormsg 141 SELECT * FROM ( select * , ROW_NUMBER() over(ORDER BY project_name, Comp_CompanyId) AS rowranking from Project WITH (NOLOCK) WHERE project_companyid=1226 ) as A WHERE rowranking > 0 and rowranking Invalid column name 'Comp_CompanyId'

Any comments or suggestion are welcome. Thank you.

  • 0

    Hi Jeff,

    I changed the code as you suggested but i am still getting the same error message.

    I tested 'comp_companyid' and 'airproject_companyid' fields by standard response.write method and i got the same ids.

    So if i understand this correctly, line1. get the list/grid with fields/columns 2. get current company id 3. this is translated into WHERE clause meaning select * from tblProjects where projectid = companyid 4. and 5. will produce the output.

    So, everything seems fine but its not.

    I also tried to replace inRecordId variable with raw number and SQL log reported the exact same thing (i even performed metadata refresh).

    Page Source Code:

    var myBlock = CRM.GetBlock("CompanyProjects");

    var intRecordId = CRM.GetContextInfo("Company","comp_companyid");

    var Arg = "airproject_companyid="+intRecordId;

    CRM.AddContent(myBlock.Execute(Arg));

    Response.Write(CRM.GetPage());

    SQL Log:

    Nov 26 2013 10:18:50.060 3612 4756 1 fselectsql,time,sql,errormsg 16 SELECT * FROM ( select * , ROW_NUMBER() over(ORDER BY airproject_name, Comp_CompanyId) AS rowranking from AirProject WITH (NOLOCK) WHERE airproject_companyid=1226 ) as A WHERE rowranking > 0 and rowranking 'Comp_CompanyId'

  • 0

    Is the Block 'CompanyProjects' based on a view or a direct reference to the table?

    Does the table 'airproject' contain the column 'Comp_companyid'?

  • 0

    'CompanyProjects' is a list in Lists tab, in Company entity. This 'CompanyProjects' list is referring to 'AirProject' table/entity.

    This 'CompanyProjects' list contains the following fields : airproject_name, airproject_description, airproject_personid and airproject_companyid.

    The second question makes me feel like i am doing something wrong here because my 'AirProject' table/entity does NOT contain ''Comp_companyid' however it does contain 'airproject_companyid' which is of type Adv Search Select.

*Community Hub is the new name for Sage City