Hi everyone, I am looking for some assistance with the logic behind defining a report relationship. I have a request from one of my epicor users to add the memo text from our Purchase Order entry for each entry into our purchase order report. I have added a datasource with the memo table that has MemoText as a field under exclusions that I was sure to include. I then created a report relationship between po header as the parent (since It is the parent for all other relationships) and Memo.
I was told by a technician with epicor that I need to relate the PONum parent field to Key1 so that the query can access the memo dataset. I got the Key1 to PONum correlation by looking at the BAQ that would be generated from PONum I believe? I am a noob at epicor so I am not 100% sure how the logic there works but I have a recording of it so if anyone needs more details regarding that step let me know. I then went into microsoft report builder and created the correct query field and added a calculated field MemoText. When I run the report however I do not get an output for MemoText but the report does run.
I am not 100% sure the issue lies here but I believe there needs to be a relationship between Memo text child field and some sort of parent field in POHeader but I am not sure which field to put there. I understand this post may be pretty vague because I dont understand epicor very well yet but if anyone has any tips on how report relationships should be designed that would be a huge help. Thanks!
Adding tables and fields to a built in report can be really tricky. For what it’s worth, Memo table needs the Related to Schema Name fields = Erp, and the Related To File field = POHeader. Key 1 is your PONum. So you are on the right track.
I tried copying the RDD and updating it to include the Memo table. Then I copied the report style to make a new report and pointed to the new RDD. However, I couldn’t get the Memo fields to show up in the RDL dataset. I think someone else here can fill in the blanks, but you are very close.
Also keep in mind that you can store memos at virtually any level. Hopefully all the memos you need to include on your report are on the POHeader, otherwise you may need to look deeper into the POLine to find your memo. A few open-ended BAQs on the memo table can tell you a lot about how it is setup.
Good luck!
Whenever adding something to a system report, always check to make sure Epicor did not already do the work for you. If they added it as a Linked Table, you can just expose the fields you need.
I would double check the RDD for you, but I am stuck on 8 at the moment.
okay just to make sure I did that first step right, how do I make sure the related to schema name fields is equal to the erp? Is that done by the PONum to Key1 relationship or does some other setting need to be entered. Also thankfully all the memo’s my company puts in are only located on the poheader so I wont have to worry about that. Thanks for the tips though I feel a lot more confident knowing im at least on the right track!
thanks for the suggestion though