Crystal Reports:need help to link GL trans history and AP trans hist 1

[JUrban]

Hello,

I need to create a set of Crystal reports which would show GL transaction history and supplement it with the Vendor name/number and item description where applies.
My problem is that i cannot find join fields to acomplish this (tried GENTRX_SQL.Id_No to APDISFIL_SQL>Id_No, but GENTRXSQL table is empty, also tried to link by reference field in both GLTRXHST and APOPNHS)
Can somebody help me please. Any ideas, experiences to share?
My firm uses Macola Progression v7.6 and Crystal V8.5.

Regards,
J Urban

 

[dgillz]

There is no such table as GENTRX_SQL, I am assumming you mean GLTRXFIL_SQL.

Right now the vendor number or name itself should be in the reference field of the GLTRXFIL_SQL. Which of these are present is dependant on how you post from subledger. If you check the "use name in reference" checkbox when you do this, the first 12 characters of the vedor name will appear in the reference field. If you do not check it, the vendor number will appear here instead.

If the 12 characters are enough, extract them from the reference field using the instr() function in crystal.

If you need the full name, then extract the 12 characters of the vendor number using the instr() function, then write a subreport linking to the APVENFIL_SQL to get the vendor name. (There is no need to use the APDISFIL_SQL at all to do this).

If I have misunderstood your intentions, or if you have any questions, please let me know.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[JUrban]

Thank you for your prompt and accurate answer.
Yes, the vendor name is in the reference field, and I will try to use the InStr function to extract it.
Any ideas on how to incorporate Item Description in my GL report?
When done, I would like users to filter on cost center, vendor, and item number or description.
PS: GENTRX is GLGENTRX. I obtained the table name from the Developer's Reference Manual - SQL DDF Primary Table joins 7.5.103. Apperently, this table as you pointed out does not exist, since my queries come back empty.

Again, thank you very much for your help.
Regards,
JUrban

 

[dgillz]

GLGENTRX should be empty unless there are unposted journal entries. Is this what you want to do?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[JUrban]

Due to your tip, I extracted the vendor name from the reference field and conditined it for AP source data.

My ultimate goal is to create a gl transaction report which would include vendor name (done), and item description (should I use APTRXDST_SQL table, and if yes, how to link it to GLTRXFIL_SQL table?). The indended users are cost center managers who need an "easy to read" report which inform them how they are doing against their budgets.

Thanks for your help,
Regards,
JUrban

 

[dgillz]

When you say "and item description", are you talking about item description of AP transactions, or of inventory transactions?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[JUrban]

I am referring to item descriptions of AP transactions.
I extracted item numbers for IM transactions from the reference field (took your tip regarding the vendorm name extract further)
Regards,
JUrban

 

[dgillz]

AP transactions don't have items, so there cannot be an item description at this level. However there is an item in some cases at the PO receiver level. Is this the item you are referring to?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[JUrban]

Sorry for the delay in my response.
I needed to ask for more clarification in order to better answer your question.
For transactions comming from the IM module, the reference field available in GLTRXFIL_SQL table shows the PO and item numbers. I need the vendor name number associated with these PO's. In other words, how to I link the data available in the PO order view by order number session to the GLTRXFIL_SQL table?

Regards,
JUrban

 

[dgillz]

A subreport. Create a formula in the main report that extracts the vendor number from the reference field. Then write a very simple report against the APVENFIL_SQL table that shows the vendr name only. Save this as VendorNameSub.rpt.

Then on the main report, insert a subreport, point to the new report you have just written, and link from the formula field to the vend_no field.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[JUrban]

Dgillz,
Thank you for your feedback. I'll follow your advice.
Regards,
JUrban