All,

I’ve had some great help on previous issues with this report from “/users/1734722/milletsoftware”.

However, as is nearly always the case, as you get past one issue the machine throughs up another.

Well here is the latest issue : Please see code below :

SELECT
“OINV”.”DocNum”,
“OINV”.”DocDate”,
“OINV”.”CardName”,
“INV1″.”Dscription”,
“OINV”.”CardCode”,
“OINV”.”BPLName”,
“INV1″.”LineTotal”,
“INV1″.”GTotal”,
“INV1″.”InvQty”,
“OINV”.”VatSum”,
“INV1″.”LineVat”,
“INV1″.”Rate”,
“INV1″.”Quantity”,
“INV1″.”VatPrcnt”,
“INV1″.”Price”,
“OINV”.”DocEntry”,
“INV1″.”PriceAfVAT”,
“INV1”.”Width1″,
“INV1”.”Width2″,
“OACT”.”AcctCode”,
“OACT”.”AcctName”,
“OINV”.”LicTradNum”,
“OINV”.”Pindicator”,
“INV1″.”Itemcode”,
“INV1″.”ItemType”,
“INV1″.”AcctCode”,
“OINV”.”BPLId”
FROM (“DATABASE”.”dbo”.”OACT” “OACT” INNER JOIN “DATABASE”.”dbo”.”INV1″ “INV1” ON “OACT”.”AcctCode”=”INV1″.”AcctCode”) INNER JOIN “DATABASE”.”dbo”.”OINV” “OINV” ON “INV1″.”DocEntry”=”OINV”.”DocEntry”

UNION

SELECT
“OCSI”.”DocNum”,
“OCSI”.”DocDate”,
“OCSI”.”CardName”,
“CSI1″.”Dscription”,
“OCSI”.”CardCode”,
“OCSI”.”BPLName”,
“CSI1″.”LineTotal”,
“CSI1″.”GTotal”,
“CSI1″.”InvQty”,
“OCSI”.”VatSum”,
“CSI1″.”LineVat”,
“CSI1″.”Rate”,
“CSI1″.”Quantity”,
“CSI1″.”VatPrcnt”,
“CSI1″.”Price”,
“OCSI”.”DocEntry”,
“CSI1″.”PriceAfVAT”,
“CSI1”.”Width1″,
“CSI1”.”Width2″,
“OACT”.”AcctCode”,
“OACT”.”AcctName”,
“OCSI”.”LicTradNum”,
“OCSI”.”Pindicator”,
“CSI1″.”Itemcode”,
“CSI1″.”ItemType”,
“CSI1″.”AcctCode”,
“OCSI”.”BPLId”

FROM (“DATABASE”.”dbo”.”OCSI” “OCSI” INNER JOIN “DATABASE”.”dbo”.”CSI1″ “CSI1” ON “OCSI”.”DocEntry”=”CSI1″.”DocEntry”) INNER JOIN “DATABASE”.”dbo”.”OACT” “OACT” ON “CSI1″.”AcctCode”=”OACT”.”AcctCode”

UNION

SELECT
“ORIN”.”DocNum”,
“ORIN”.”DocDate”,
“ORIN”.”CardName”,
“RIN1″.”Dscription”,
“ORIN”.”CardCode”,
“ORIN”.”BPLName”,
“RIN1”.”LineTotal”*-1,
“RIN1″.”GTotal”,
“RIN1″.”InvQty”,
“ORIN”.”VatSum”,
“RIN1″.”LineVat”,
“RIN1″.”Rate”,
“RIN1″.”Quantity”,
“RIN1″.”VatPrcnt”,
“RIN1″.”Price”,
“ORIN”.”DocEntry”,
“RIN1″.”PriceAfVAT”,
“RIN1”.”Width1″,
“RIN1”.”Width2″,
“OACT”.”AcctCode”,
“OACT”.”AcctName”,
“ORIN”.”LicTradNum”,
“ORIN”.”Pindicator”,
“RIN1″.”Itemcode”,
“RIN1″.”ItemType”,
“RIN1″.”AcctCode”,
“ORIN”.”BPLId”

FROM (“DATABASE”.”dbo”.”ORIN” “ORIN” INNER JOIN “DATABASE”.”dbo”.”RIN1″ “RIN1” ON “ORIN”.”DocEntry”=”RIN1″.”DocEntry”) INNER JOIN “DATABASE”.”dbo”.”OACT” “OACT” ON “RIN1″.”AcctCode”=”OACT”.”AcctCode”

UNION ALL

SELECT
“_PM_BGT_CNT”.”LineId”[Budget],
“_PM_BGT_CNT”.”U_EndDate”,
“_PM_BGT_CNT”.”U_CardName”,
“_PM_BGT_CNT”.”U_ItemCode”,
“_PM_BGT_CNT”.”U_CardCode”,
“_PM_BGT_CNT”.”U_Comments”,
“_PM_BGT_CNT”.”U_M01Amnt”,
“_PM_BGT_CNT”.”U_M02Amnt”,
“_PM_BGT_CNT”.”U_M03Amnt”,
“_PM_BGT_CNT”.”U_M04Amnt”,
“_PM_BGT_CNT”.”U_M05Amnt”,
“_PM_BGT_CNT”.”U_M06Amnt”,
“_PM_BGT_CNT”.”U_M07Amnt”,
“_PM_BGT_CNT”.”U_M08Amnt”,
“_PM_BGT_CNT”.”U_M09Amnt”,
“_PM_BGT_CNT”.”U_M10Amnt”,
“_PM_BGT_CNT”.”U_M11Amnt”,
“_PM_BGT_CNT”.”U_M12Amnt”,
“_PM_BGT_CNT”.”U_YearAmnt”,
“_PM_BGT_CNT”.”U_CntId”,
“_PM_BGT_CNT”.”U_ItemType”,
“_PM_BGT_CNT”.”U_Location”,
“_PM_BGT_CNT”.”U_LocId”,
“_PM_BGT_CNT”.”U_SalesAcc”,
“_PM_OBGT”.”U_Year”,
“_PM_OBGT”.”U_Type”,
“_PM_OBGT”.”U_BPLId”
FROM (“DATABASE”.”dbo”.”@PM_BGT_CNT” “_PM_BGT_CNT” INNER JOIN “DATABASE”.”dbo”.”OACT” “OACT” ON “_PM_BGT_CNT”.”U_SalesAcc”=”OACT”.”AcctCode”) INNER JOIN “DATABASE”.”dbo”.”@PM_OBGT” “_PM_OBGT” ON “_PM_BGT_CNT”.”DocEntry”=”_PM_OBGT”.”DocEntry”

THE ISSUE

In Crystal Reports the “Field Explorer – Database Fields – Command Field List” dose not show the fields for table nest 4 (in italics above) and as can be seen in the screenshot below:

Crystal Report Screenshot

May be a join issue! May be something else!

HELP! PLEASE!

Many thanks in advance.

Simon.

Tried some different joins but the database crashed with some of them, kind off worked with others but watching paint dry is faster!