Account Description Formula

[rossallen]

Started using Crystal in SQL database info as follows-
SQL Database - Crystal Version 9 One Table named GM

Output Expected is as follows

GLAccount AccountName TotalAccount TotalDesc
300.1005.00 Petty Cash 300.1025.00 Cash Assets

This information is stored in the GM table as follows
String String String String
Field Field Field Field
GM_ACCT GM_NAME GM_TOTAL_ACCT GM_NAME


I am using GM_NAME in the TotalDesc field and it is returning the description associated with the GL Account and not the TotalAccount Essentially I want Crystal to lookup the GM_TOTAL_ACCT that is associated with this line and then display the correct description associated with this TotalAccount.

Any help would be much appreciated.

R Allen
CR Pro Version 9.2

 

[lbass]

It would depend on the design of your database, but if you are sure there is a result in the GM Name field that is specific to the total account, you might want to try adding the table again as an alias GM_1. Then link {GM.GM_Total_Acct} to {GM_1.GM_Acct} and then add {GM_1.GM_Name} to the report for your total description field.

-LB

 

[rossallen]

Thanks LB Unfortunately tried this with no success. The way this information is stored in the SQL database is that for every account {GM_ACCT} it will have a Total account that it refers to {GM_TOTAL_ACCOUNT}. The {GM_NAME} field is associated with the {GM_ACCT} field and the the problem is that the system does not store the description associated with the {GM_TOTAL_ACCOUNT} in a seperate field as this would be duplicating information you just have to "lookup" the description by matching the {GM_TOTAL_ACCOUNT} with the {GM_ACCT} and finding the description associated with it {GM_NAME} for this {GM_ACCT}. Hope I haven't confused you

Ross Allen
CR Pro Version 9.2

 

[lbass]

I think we need to see some sample data with multiple rows, so we can see how the description appears for the total account. What is returned if you place the following in your details section? Is GM_Acct null when the the GM_Name relates to the Total Account?

GM_ACCT GM_NAME GM_TOTAL_ACCT

-LB