How can I Join TranGLC to my APInvExp?

Yahoo Archive
1

Thank you guys for the replies.


The I can see making the join in the SSRS RDL file - however I'm not sure how to make the join in the RDD.


Epicor is now (in 10.1) giving us a table called APInvExpTGLC - I thought from Insights I heard them say this was to help make the join to TranGLC.


Unfortunately, I'm not sure how to make that join.


Any thoughts,


Again - thanks for the suggestions.


DaveO

2

I am trying to modify our AP Check to show the GL allocations on the check.

For any one invoice - we may have 3 or 4 GL Allocations.


I added the APInvExp table to the DataDef and joined using Company, VendorNum, and InvoiceNum and used an outer join.


That appears to be working - I can see and print my APInvExp amounts.


However, I would like to display the GLAccount Description next to each of the amounts. 


I thought I had this solved by joining the APInvExp to TranGLC and using Company, Key1 (VendorNum), Key2(APInvExp.InvoiceNum), and Key3(APInvExp.InvoiceNum)


However I get an error when I try to join those fields.


So - I now see that there is an APInvExpTGLC table (I was unable to find this in the SQL Schema tables)  and I thought I remembered that this table was added to help join to the TranGLC.


However - am at a loss to see how to make the joins.


Has anyone ever tacked the APInvExp to TranGLC?


Thanks,


DaveO

Ph: 651-246-3281

Feel free to call if the answer is lengthy.

3
I am not sure about report data definitions but in BAQs to join on the key1, key2, key3 fields I had to cast the joining field to nvarchar(50) to avoid type mismatch errors. eg CAST(PartTran.TranNum as nvarchar(50)). You can type this instead of selecting from the drop down options in the BAQ designer. 

Hope this helps.
Brett
4
Doing that in an RDD is a pain. You basically have to do the same thing Brett said since crystal can't automatically convert from an Integer to a string.  So you have to write a custom Command. Here's a part of one I have written for a AP/GL report. I'm not SQL coding expert, but this works for me. 

LEFT OUTER JOIN "Epicor905"."dbo"."APInvJob" "a" ON (CONVERT(char,"a"."VendorNum") = "TranGLC"."Key1") AND (CONVERT(char,"a"."InvoiceNum") = "TranGLC"."Key2") AND (CONVERT(char,"a"."InvoiceLine") = "TranGLC"."Key3") and "a"."JobNum" = "TranGLC"."Key4" and (Convert(char,"a"."AssemblySeq") = "TranGLC"."Key5") and (CONVERT(char,"a"."mtlseq") = "TranGLC"."Key6")

-Bobby