Administration, Core Financials, and Project Accounting

[nkrogmann]

I'm running 7.2, currently our AR Department puts what they call an Invoice Method (Fax, Email or Reg Mail) in the Primary Contact's Title Field for each Customer.  I'm wanting to modify our Invoice to include this Title Field so when Invoices are printed or previewed they can "at a glance" see the Invoice Method.  However I cannot find this column it in the default database tables the report has available.   I see where this column is in tcicontact but knowing just enough to be dangerous I'm not sure how to proceed given the report mostly has Wrk tables.  Anyone have any advice on the best way to achieve what I am wanting?

I appreciate any input!   Norbert

[JohnHanr]

Norbert,

You can link to tarInvoice (in Crystal) via the InvcKey then link to tciContact and use the ConfirmToCntctKey (assuming that's the primary).  Then you can print it out.  If not then you will have to go through tarInvoice to tarCustomer (via CustKey) then using PrimaryCntctKey link to tciContact.

Just to plug for my company though - We have an enhancement that might be able to do your email and faxing (can't fold, stuff envelopes and send Invoices in the mail through).  You can check with your reseller if interested.

Thx.

John Hanrahan

The Macabe Associates

[nkrogmann]

John,

Thank you for the quick reply.  I'm using a copy of the default arztc001 form to work with - the only Database tables available to pull fields from are (tarPrintInvcDtlWrk, tarPrintInvcHdrWrk, tciAddressWrk, tciAddressWrk_1, tciAddressWrk_2, tsmCompany).  Now when I go into Database Expert to add additional tables these tables I just mentioned are under a Database called MAS 500, not the name of my production database.  Now I can add a new connection to my production database & link the tables as you mention but I'm not sure this is the right thing to do.  I guess I'm stuck at how to pull the information from the tables you mentioned.  Sorry for my lack of knowledge on this.

Norbert

[JohnHanr · Accepted solution]

Norbert,

tarPrintInvcHdrWrk has a key to tciContact via ConfirmToCntctKey.  If you are unsure how to add tables to the form I would just copy the existing form to a new name then give it a try and then test it out.  If you're not comfortable doing this I would get a copy of a Crystal book and check it out or give my reseller a call and have them walk me through it.

It's somewhat hard to describe how to do it since it's all point/click and drag.

Best of luck.

John

[nkrogmann]

I added the tciContact table from my production DB & linked it as suggested.  It appears to work fine - doing what I want.  I do get the following message however:

"More than one datasource or a stored procedure has been used in this report.  Please make sure that no SQL Expression is added and no server-side group-by is performed."

Should this worry me?  So now when I go into Database expert I see both a database named "MAS 500" & my production database listed.  I guess I figured that I could add this table to the "MAS 500" database listed here but could not seem to drop the table under it.

Norbert

[JohnHanr]

I believe you will be fine.

J

[nkrogmann]

John,

Thanks again for your help on this.

Norbert

[oyama]

In Crystal Reports you can right click on "Database Fields" in the "Field Explorer" at the right.

Choose "Set datasource location...". 

Then you can make sure you report use only one database. It could create undesired result if you query from two database that out of sync.

[JohnHanr]

Oyama,

The databases are not out of sync.  MAS 500 updates the work table connection information on the fly.  If he does what you suggest it will take away the warning message though.

John

[nkrogmann]

Oyama,

Thank you for your message.  So before I start: I have this database named MAS 500 & my production database named best_app.  The MAS 500 one was where all fields were pulling from to build the report, as suggested above I added my production database in the mix so I could pull the tciContact field from it because I couldn't see a way to add tciContact to the MAS 500 database.

In a way your message was kind of what I was looking for - i.e. where could I edit or change where the tables were being sourced from.  So I enter the Set Datasource location & replace all fields sourced from the MAS 500 database to source from my best_app database.  However after doing this I received row set errors when attempting to view the report.  So I attempted to Verify the Database to fix this but this results in each table name popping up "The Database table "..." cannot be found.  Proceed to remove this table from the report".  Note this message appears even though the table I source from exist.

So long story short I'm a bit baffled by this, I guess I'm better off pulling just the tcicontact field as I'm doing and having two databases.

Norbert