I wonder if someone could point me towards the right way to do this?
I have made a custom version of the POForm SSRS and I have managed to get most items to display how I want but I want to add an item, in this case Buyers Phone Number.
I have found the field I want, SysUserFile.Phone but am struggling to create a label and field in my SSRS poform.rdl as the training I had was not the best (and it was months ago), before I had been anywhere near SSRS or BAQ’s at that time.
Each time I think I have it added and try to upload I get an error saying Undefined.
Can anyone help?
Cheers.
There is a method to the madness and I don’t know of a clean and easy way to explain it so I will instead try to help you find the easy button. Many times there are Calculated Fields already in existence that bring in common fields that are used/referenced. I would check there for it. If not there, I’d check the linked tables. In this case, you should be able to link to the PerConID from the BuyerID and then you could get the Phone Number from the PerCon table.
Hi Dan, based on the training I got at the beginning of the year I opened Dataset Properties for POHeader, went into the query and and tried to make a left outer join from POHeader to SysUserFile to add the Phone field so I think I was in the right place but I am missing something in the execution
I’m not sure if you have already done it but the first step is to find the system Report Data Definition (RDD) in Epicor/Kinetic (assuming no other mods have already been made, if they have then you need to modify the custom one that your report style is calling) and duplicate that, then you setup the tables & fields you need in the report there. Once that is done you can then edit the report itself to include those.
I’d highly recommend starting with @DaveOlender 's video. He actually is the consultant that helped our company with SSRS and I owe him a tremendous amount of gratitude for the time he took with me personally.
We should clarify one thing. Are you looking for the record on the Person / Contact field of the Buyer? Or are you looking for the Default Authorized User of the Buyer ID? Depending on which you are planning to go, you will need to go two slightly different paths. The Person / Contact would be a little easier, in my opinion.
Thanks for the replies, I was in training all day Friday on this very subject but am still very much just finding my feet.
Right now my sticking point is trying to create the link between EmpBasic.phone and the PO in some way in the RDD, and I am just missing, the only common field is Company.
I think we are getting a customized field soon for this as part of our Project (we are not actually live yet) but as a training exercise I thought I should have been able to use that EmpBasic.phone number.
I think we need to go back to what @dr_dan was asking, what are you really trying to link?
Off the top of my head, yes I think you will struggle with linking the EmpBasic table to the POHead table since there is not really a natural link I can think of, at least not directly. You may have more success linking POHead to the Workforce table (and setting up a workforce record for each applicable employee), then linking that to EmpBasic or just stopping at the workforce level and having the information there as well as EmpBasic.
I think for me though I would be looking to the Buyer record and then if you need to have multiple employees buy things and it show the proper information setup each as a buyer.
Are you trying to get the phone number of the Buyer in the report? If that is what you want, you will have to join the PerConID from the BuyerID linked table.
Thanks Leonard, yes I think what I am trying is the issue, I should have picked a more do-able join as a learning exercise.
I have been looking for a more appropriate table but in any case, Epicor are going to create a bespoke custom field
Table: Buyer
Field: Telephone Number
Type: Text (x20)
And this should enable me to add the Buyers Phone number into the custom POForm I have been working on
Thanks yes, I have been looking at this table just before lunch.
PerCon seems to have Name and PhoneNum. Unfortunately when I display these in a BAQ to test them I get more than one entry for a buyer’s name, one all in Caps and one correctly formatted and only one has the phone number against it.
What are you joining on? Are you using the PerConID to link to the other table?
Did you create your Buyer records through the Person/Contact screen? That is the best way to do it as it will maintain the link through Buyer, Employee, and Workforce.
Based on your posts thus far you would need to add PurAgent to the RDD and join on BuyerID then add PerCon to RDD and join on PerConID and company for both tables. You will also need to unexclude the appropriate columns to get the information you require.
