Cannot see new InvNum_c custom field that was added to POHeader table when I run a SQL query or SSRS report

ERP 10
1

Cannot see my new InvNum_c custom field that was added to the POHeader table when I run a SQL query or SSRS report. When I run the sql query and try to select the InvNum_c field from the POHeader table, sql gives me an error: Invalid column name “InvNum_c”. Is there a way to add it to the SQL server without having to do something after hours?
Please help!
Thank you,
Richard

2

Did you do a data model regeneration after adding the field?

1 Like
3

Thank you for your reply Aaron.

I did not run the data model regeneration. Can I do that while users on using Epicor?
I am afraid to do it because it may harm the Epicor database if users are on?
I have never done the data model regeneration before. It is difficult to run and how long does it usually take?

Thanks,
Richard

4

Actually, the data model regeneration is run on the epicor server correct?
I need to run something that allows the sql server to see the new custom fields.

Thanks,
Richard

5

That’s a necessary step as it adds the new field to the schema. Without it, your field does not exist yet.
Whenever I regen the data model, I make sure to do it after hours and I go as far as stopping the app pool to make sure no one tries to log in during the regen. I’ve heard of people doing it during production hours, but that seems too risky in my mind.

It’s not difficult at all and the time depends on your DB size.
Open up the admin console on the server, stop the app pool, click the DB, then click regen.
It takes ours about 20 minutes with a 120GB database.

1 Like
6

In addition to Aaron’s astute advice about Model regeneration, it’s important to know that the field will not be added to the POHeader table directly. Epicor creates a POHeader_UD with a link between the two tables for you (via a VIEW I believe). But you never need to know that if you’re using the product through the Business Objects, BAQs, etc. I personally stay out of the SQL except for rare cases and even then, only for viewing.

2 Likes
7

Yes, it is best practice to regen the data model during a off-hours maintenance cycle.

1 Like
8

Ah, yes.
If you’re looking at the SQL side, you’ll see everything in the view dbo.POHeader.
Otherwise, you will need to join Erp.POHeader_UD to Erp.POHeader.
Good call.

1 Like
9

I see the data in the erp.POHeader_UD table now.

Thank you everyone!!!

Richard

1 Like
10

If you’re going to use this field in a SSRS report, you’ll need to add the UD field to the report’s DataSet. For PO it’s the POHeader dataset.

image
image895×730 46.4 KB