Overview | Contents

cBiz Error Diagnosis 1 Links Table Reflection

Issue of Reflecting the Links Table in the cBiz Dynamic View

Issue:
Reflecting the link table fields in the dynamic view and x-tabs, even on update the SQL server latest service pack.

Analysis:
When the SQL code is consisting of the Links table, cBiz is reflecting Links table also, provided to resolve this, there should be a UNION command in the code.

Error SQL Code:

<View>
<ID>uuCopyOfOpen1</ID>
<Caption>Open+Links</Caption>
<Properties>54261</Properties>
<SQL>SELECT Requirements.ReqIntID,Requirements.ReqID as Requirement,Requirements.JobTitle,Requirements.Location,
Contacts.DisplayName as Contact,Contacts.CompanyName as Company,
Consultants.DisplayName as Consultant, "Submit" as Type,"Manual" as SubType,
MLinkInfo.DateTime as DateAndTime,MLinkInfo.UserIDs
FROM Contacts, Links b, Links c, MLinkInfo, Requirements, Consultants
WHERE (Consultants.Private=False OR Consultants.UserIDs LIKE [LoggedInUser]) AND
(Requirements.Private=False OR Requirements.UserIDs LIKE [LoggedInUser]) AND
Requirements.Archived = False AND
(Requirements.ClosedDate &gt; Now() OR IsDate(Requirements.ClosedDate)=False) AND
Contacts.ContactIntID=b.ToID AND (b.FromID=c.ToID AND c.ToID=Requirements.ReqIntID) AND
c.FromID=Consultants.ConsIntID AND c.Type=1 AND c.SubType=1 AND
c.FromID=MLinkInfo.FromID AND Requirements.ReqIntID=MLinkInfo.ToID AND
MLinkInfo.Type=1 AND MLinkInfo.SubType=1</SQL>
</View>

• The following image reflecting the links table in the view.

Fig 1.1

 

• The cBiz is connected to the database with DSN name "cBizFinal1-Nat".

Fig 1.2

• The DSN name "cBizFinal1-Nat" is created with Native Client (ODBC) .

Fig 1.3

• The cBiz DSN is created with SQL Server default driver name "cBizFinal1".

Fig 1.4

• The cBiz connected with DSN is created with SQL Server default driver name "cBizFinal1". Now links table is not getting reflecting in the dynamick view.

Fig 1.5

• The following fig illustrates that by adding the union in the SQL Query, it is not reflecting the links table.

Corrected SQL Code:

<View>
<ID>uuCopyOfOpen</ID>
<Caption>Open+EmptyLine</Caption>
<Properties>54261</Properties>
<SQL>SELECT Requirements.ReqIntID,Requirements.ReqID as Requirement,Requirements.JobTitle
FROM Contacts, Links b, Links c, MLinkInfo, Requirements, Consultants
WHERE (Consultants.Private=False OR Consultants.UserIDs LIKE [LoggedInUser]) AND
(Requirements.Private=False OR Requirements.UserIDs LIKE [LoggedInUser]) AND
Requirements.Archived = False AND
(Requirements.ClosedDate &gt; Now() OR IsDate(Requirements.ClosedDate)=False) AND
Contacts.ContactIntID=b.ToID AND (b.FromID=c.ToID AND c.ToID=Requirements.ReqIntID) AND
c.FromID=Consultants.ConsIntID AND c.Type=1 AND c.SubType=1 AND
c.FromID=MLinkInfo.FromID AND Requirements.ReqIntID=MLinkInfo.ToID AND
MLinkInfo.Type=1 AND MLinkInfo.SubType=1

UNION

SELECT '' AS ReqIntID,'' AS Requirement,'' AS JobTitle</SQL>
</View>

Fig 1.6

Solution:

There is a bug in the SQL server Native client. For that we have to update latest hot fixes.