PowerBI and UD Fields

powerbi-ud-feilds

(MIGUEL S.) #1

We are trying to use PowerBi and usually we can create the query in EPICOR BAQ and copy paste the syntax into the Power Query. We have some UD fields that were created on the OrderRel table, and it is giving us some errors.

Details: “Microsoft SQL: The multi-part identifier “OrderRel_UD.CustReqDate_c” could not be bound.
Invalid column name ‘CustReqDate_c’.
Invalid column name ‘CustReqDate_c’.
Invalid column name ‘PromiseDate_c’.”

I thought if we put the _UD in front of the table that would help, now we get a different error.

Details: “Microsoft SQL: The multi-part identifier “OrderRel_UD.CustReqDate_c” could not be bound.
The multi-part identifier “OrderRel_UD.CustReqDate_c” could not be bound.
The multi-part identifier “OrderRel_UD.CustReqDate_c” could not be bound.
The multi-part identifier “OrderRel_UD.PromiseDate_c” could not be bound.”

Anyone have this issue or might have a solution.


(Brandon Anderson) #2

The UD fields on E10 are actually separate tables in the database. When using Epicor, it automatically pulls this table and links it. Outside of Epicor you will have to make the link yourself. I haven’t had to do that yet, I just know that’s how it works. You will have modify your SQL to join to that table.


(brett manners) #3

If you query dbo schema instead of ERP you may find the tables have been joined for you in a view. Eg select * from dbo.OrderRel.

Brett


(Paul Morgan) #4

Join OrderRel and OrderRel_UD (OrderRel.SysRowID = OrderRel_UD.ForeignSysRowID)


(Dan Edwards) #5

Just curious - why are you not using REST to retrieve the data from Epicor to PowerBI?


(MIGUEL S.) #6

I heard about REST and recently saw some EPICOR info on this. So in short, I have no knowledge of REST, just that it is some sort of API. Plus, I dont know anything about API’s. +:slight_smile:


(MIGUEL S.) #7

Thank you all for the info. I, we, didnt think about linking the two tables.


(Dan Edwards) #9

If you do use direct SQL there are views created that do this join for you. The tables used by the application are in the erp and ice schema but the views are in the dbo schema. So to get the equivalent of joining erp.orderhed to erp.orderhed_ud you can use dbo.OrderHed (view)
image