Anything that you try after reading my instructions is at your own risk. They
work well for us, but you always need to initially check the results until you
gain confidence.
So in the first step, we 'hopefully' managed to print preview our new report
(which looks no different).
Now you need to open your new .rpt file in Crystal (possibly saved with Auto as
the suffix).
Go to Database > Set Datasource Location > Create New Connection > ADO.NET
(XML)Â Â ********
Locate the recently created .xml (probably located in your EpicorData > Reports
Go to Database > Database Expert
Highlight 'CustCnt' on the left and click the right arrow to move it to the
'Selected Tables'.
Go to the Links tab.
CustCnt will not be connected to any other tables - Even though we created a
relationship in Epicor, they dont actually join when they come to Crystal.
You need to create the following links, both as Inner Joins.
CUSTOMER | CustNum > CUSTCNT | CustNum
CUSTOMER | PrimBCon > CUSTCNT | ConNum
Some people may get an issue where it tries to force the links on you -
Unfortunately I can't remember the fix for this, but the fix can be easily found
on google if you search for the error that you receive. If I remember correctly
it was something to do with the registry.
You should now be able to preview your report in Crystal and ti will look no
different.
Now you need to insert a sub-report. You dont actually HAVE to do this in a
sub-report, but in the past when it wasnt in a sub-report, 1 in 50 invoices
would display multiple repeats of the same line throughout the invoice so the
totals would be wrong. I dont know why. Epicor dont know why. Crystal dont know
why.
Enter a name for the sub-report > click Report Wizard > Select the previous
created xml file (see ******** above).
Select the following tables ...
CustCnt
Customer
InvcHead
In the sub-report, go to Database > Database Expert.
The tables will be unlinked, but they require linking as follows ...
INVCHEAD CustNum > CUSTOMER CustNum > INNER JOIN
INVCHEAD CustNum > CUSTCNT CustNum > LEFT OUTER JOIN
CUSTOMER PrimBCon > CUSTCNT ConNum > LEFT OUTER JOIN
Click OK.
In the sub-report, create the following formulas.
Call this formula: By Mail
What it does: Checks if the customer requires the docs by mail or not
if ({InvcHead.Customer_LangNameID} = "eng")
AND ({INVCHEAD.Customer_CheckBox01} = TRUE)
then "Invoice Address" else
if ({InvcHead.Customer_LangNameID} = "fra")
AND ({INVCHEAD.Customer_CheckBox01} = TRUE)
then "Adresse de Facturation" else
if ({InvcHead.Customer_LangNameID} = "deu")
AND ({INVCHEAD.Customer_CheckBox01} = TRUE)
then "Rechnungsadresse" else
if ({InvcHead.Customer_LangNameID} = "enu")
AND ({INVCHEAD.Customer_CheckBox01} = TRUE)
then "Invoice Address" else
{Check 1}
((You might not need this if you aren't going to use the checkbox01 as mentioned
in Part 1 of the instructions))
Call this formula: Check 1
What it does: Checks if the the Primary Bill To Contact Email exists
If isnull({CustCnt.EMailAddress}) then {Check 2}
else {CustCnt.EMailAddress}
Call this formula: Check 2
What it does: Checks if the Primary Bill To Contact Fax exists
if isnull({CustCnt.FaxNum}) then {Check 3}
else {CustCnt.FaxNum}
Call this formula: Check 3
What it does: Checks if the Bill To Fax exists
if isnull({CUSTOMER.BTFaxNum}) then {Check 4}
else {CUSTOMER.BTFaxNum}
Call this formula: Check 4
What it does: Checks if the Customer Email Address exists
if isnull({CUSTOMER.EMailAddress}) then {Check 5}
else {CUSTOMER.EMailAddress}
Call this formula: Check 5
What it does: Checks if the Customer Fax exists - if it doesnt then print 'not
on file'
if isnull({CUSTOMER.FaxNum}) then 'Not on file'
else {CUSTOMER.FaxNum}
These formulas could be compacted into one single formula, but its not
necessary. It is much easy to understand if you do them bit by bit.
In Report Header on the sub-report, include the formula (that you have just
created) called 'By Mail'.
Go back to the main report and make sure the sub-report is not in a supressed
section.
Preview the report.
Providing all has gone to plan, you should now see 'something' on your preview
where the sub-report is.
You can check this is working correctly based on the hierarchy of checks that I
mentioned in Part 1.
Once you get this working, in Part 3 we will integrate it with Altec.
[Non-text portions of this message have been removed]
work well for us, but you always need to initially check the results until you
gain confidence.
So in the first step, we 'hopefully' managed to print preview our new report
(which looks no different).
Now you need to open your new .rpt file in Crystal (possibly saved with Auto as
the suffix).
Go to Database > Set Datasource Location > Create New Connection > ADO.NET
(XML)Â Â ********
Locate the recently created .xml (probably located in your EpicorData > Reports
> YourName folder).Click 'Update' > Close.
Go to Database > Database Expert
Highlight 'CustCnt' on the left and click the right arrow to move it to the
'Selected Tables'.
Go to the Links tab.
CustCnt will not be connected to any other tables - Even though we created a
relationship in Epicor, they dont actually join when they come to Crystal.
You need to create the following links, both as Inner Joins.
CUSTOMER | CustNum > CUSTCNT | CustNum
CUSTOMER | PrimBCon > CUSTCNT | ConNum
Some people may get an issue where it tries to force the links on you -
Unfortunately I can't remember the fix for this, but the fix can be easily found
on google if you search for the error that you receive. If I remember correctly
it was something to do with the registry.
You should now be able to preview your report in Crystal and ti will look no
different.
Now you need to insert a sub-report. You dont actually HAVE to do this in a
sub-report, but in the past when it wasnt in a sub-report, 1 in 50 invoices
would display multiple repeats of the same line throughout the invoice so the
totals would be wrong. I dont know why. Epicor dont know why. Crystal dont know
why.
Enter a name for the sub-report > click Report Wizard > Select the previous
created xml file (see ******** above).
Select the following tables ...
CustCnt
Customer
InvcHead
In the sub-report, go to Database > Database Expert.
The tables will be unlinked, but they require linking as follows ...
INVCHEAD CustNum > CUSTOMER CustNum > INNER JOIN
INVCHEAD CustNum > CUSTCNT CustNum > LEFT OUTER JOIN
CUSTOMER PrimBCon > CUSTCNT ConNum > LEFT OUTER JOIN
Click OK.
In the sub-report, create the following formulas.
Call this formula: By Mail
What it does: Checks if the customer requires the docs by mail or not
if ({InvcHead.Customer_LangNameID} = "eng")
AND ({INVCHEAD.Customer_CheckBox01} = TRUE)
then "Invoice Address" else
if ({InvcHead.Customer_LangNameID} = "fra")
AND ({INVCHEAD.Customer_CheckBox01} = TRUE)
then "Adresse de Facturation" else
if ({InvcHead.Customer_LangNameID} = "deu")
AND ({INVCHEAD.Customer_CheckBox01} = TRUE)
then "Rechnungsadresse" else
if ({InvcHead.Customer_LangNameID} = "enu")
AND ({INVCHEAD.Customer_CheckBox01} = TRUE)
then "Invoice Address" else
{Check 1}
((You might not need this if you aren't going to use the checkbox01 as mentioned
in Part 1 of the instructions))
Call this formula: Check 1
What it does: Checks if the the Primary Bill To Contact Email exists
If isnull({CustCnt.EMailAddress}) then {Check 2}
else {CustCnt.EMailAddress}
Call this formula: Check 2
What it does: Checks if the Primary Bill To Contact Fax exists
if isnull({CustCnt.FaxNum}) then {Check 3}
else {CustCnt.FaxNum}
Call this formula: Check 3
What it does: Checks if the Bill To Fax exists
if isnull({CUSTOMER.BTFaxNum}) then {Check 4}
else {CUSTOMER.BTFaxNum}
Call this formula: Check 4
What it does: Checks if the Customer Email Address exists
if isnull({CUSTOMER.EMailAddress}) then {Check 5}
else {CUSTOMER.EMailAddress}
Call this formula: Check 5
What it does: Checks if the Customer Fax exists - if it doesnt then print 'not
on file'
if isnull({CUSTOMER.FaxNum}) then 'Not on file'
else {CUSTOMER.FaxNum}
These formulas could be compacted into one single formula, but its not
necessary. It is much easy to understand if you do them bit by bit.
In Report Header on the sub-report, include the formula (that you have just
created) called 'By Mail'.
Go back to the main report and make sure the sub-report is not in a supressed
section.
Preview the report.
Providing all has gone to plan, you should now see 'something' on your preview
where the sub-report is.
You can check this is working correctly based on the hierarchy of checks that I
mentioned in Part 1.
Once you get this working, in Part 3 we will integrate it with Altec.
[Non-text portions of this message have been removed]