I'm trying to display the Department of the salesperson in my SO summary report.
I joined SOOrder and EPEmployee with salespersonid. There is no error that tells me there wasn't that table so I assume I ain't wrong in schema. But there's no data display from EPEmployee table. What should I do? I appreciate any help, thanks!
Best answer by aaghaei
View originalHey
1. try the joins in SQL and see if you are able to see the required output
2. use the same in report Relationships and check
This is a better way, please try and let me know if you need any further help.
+10
Hi Paula,
If you are on a version of Acumatica with the DAC Schema Browser, you can use that to see what the primary and foreign keys are in order to make sure you are doing the join properly or whether there would be a better DAC to join with.
Best,
David Eichner, CPA
Kensium
Hey
1. try the joins in SQL and see if you are able to see the required output
2. use the same in report Relationships and check
This is a better way, please try and let me know if you need any further help.
Thank you so much, and where or how do I do this?
+10
Hi
Here’s a sample GI for what you want to accomplish.
Best,
David Eichner, CPA
Sr Solutions Architect
Kensium
Hi Paula,
If you are on a version of Acumatica with the DAC Schema Browser, you can use that to see what the primary and foreign keys are in order to make sure you are doing the join properly or whether there would be a better DAC to join with.
Best,
David Eichner, CPA
Kensium
Thanks sir, I don’t have that, I have DAC Relations, is that the same thing? I looked up there, saw the key and tried but to no avail. Still no data, so I’m thinking isn’t there really any connection in EPEmployee and SOOrders? I saw a report that displays Department but in CFM screen which I also think doesn’t have any relation with my SOs, can you extend any more help? Thanks again!
+10
Here’s a sample generic inquiry for what you want
+8
On the Sales Order Screen Header, Hold Ctrl + Alt and Click on any field. A small window will popup. Click on the DataClass Hyperlink, which will take you to the DAC Schema. There you can see all fields and incoming and outgoing relations and keys. You will need Customizer Right to be able to perform this.
If these all look mumbo jumbo to you then I suggest starting to read the Acumatica basic customization materials on openuni.acumatica.com to learn the basics.
+8
late response I guess
Thanks! Sir, you mean I’ll make a GI? Cause I’m really not familiar, I just started using acumatica and just using Report designer. I’m lost I guess. Where do I need what you sent me sir?
On the Sales Order Screen Header, Hold Ctrl + Alt and Click on any field. A small window will popup. Click on the DataClass Hyperlink, which will take you to the DAC Schema. There you can see all fields and incoming and outgoing relations and keys. You will need Customizer Right to be able to perform this.
If these all look mumbo jumbo to you then I suggest starting to read the Acumatica basic customization materials on openuni.acumatica.com to learn the basics.
Thanks sir, I looked at this and applied. Anyway, this fixed my total amt.! Now I’m sure I’m generating correctly. Somehow, still doesn’t give me any data from EPEmployee. There’s not any error though, so I suppose they got related correctly. Here’s my tables, is it possible that I have redundant connections that caused this? I mean, of all these tables, I’m only using the highlighted ones. Just copied schema of another report because I needed some of its details.
+8
Not sure how your total amount is fixed when you do not get any data in your report. If your report is not retrieving any data but you expect data, then you have an incorrect join(s). If you have access to SQL, grab the query from the profiler and remove the joins one by one to see which one is causing the issue.
RDs are a little bit annoying but alternatively, to test your report, I suggest starting a GI and adding the above joins one by one to see which one causes the issue.
Also, I suggest you change your last join from full to the left to return the, possible null owners, as well.
Not sure how your total amount is fixed when you do not get any data in your report. If your report is not retrieving any data but you expect data, then you have an incorrect join(s).
I am getting my total amount and almost everything from ARTran, and what doesn’t give me any data is only EPEmployee. Okay, I will try removing some joins to check. Thanks sir!
+8
Ok, when you export your SOOrder, is OwnerID field filled?
Yes sir!
+8
Ok, Do you have the same BAccountIDs in EPEmployee when you export the list of employees? If yes, then it should return employee name.
Ok, Do you have the same BAccountIDs in EPEmployee when you export the list of employees? If yes, then it should return employee name.
No Sir, I really don’t have any output field from EPEmployee, neither BAccountID nor employee name nor anything.
Anyway, our acumatica project manager said sth like it’s a ‘stand-alone’ so I really cannot retrieve data from there and just settled to not including the fields I need from there for now. I’ll try to study GI mentioned anyway. Thanks a lot!
+8
Not sure what being Standalone has to do with the report. Do you have access to employee screen from inside Acumatica? My guess is you do not have the required permission.
Not sure what being Standalone has to do with the report. Do you have access to employee screen from inside Acumatica? My guess is you do not have the required permission.
Yes, I can access the Employee screen sir. Someone guess it could be because I have some tables which are not supposed to be included, and that’s why some won’t display because of too many relationships, is that possible? I have other tables that won’t give me any data, ARInvoice and SOShipment.
This is my list of tables. Can u help me find out? Thanks!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
