Field Name - BAQ

Is there a field name for the entry date in order entry that could be pulled up in a BAQ?

Hi Yash - I believe you’ll want to use OrderHed.OrderDate. It defaults to the system date but can be changed by the user.

So for all orders currently in the system we have entered the order date as when the customer sent us the PO, not necessarily when the order was created initially

I wasn’t sure if Epicor had a behind the scenes field that generated the date when the order was created

Oh, I see what you mean. Doesn’t look like that exists, according to Data Dictionary Viewer:

What might be the best way to go about creating a field that automatically gets filled out to the current date whenever the order number has been generated?

I believe the field you need is ChangeDate - it gets a value when the order is first created if I’m not mistaken. Another one of Epicor’s ‘well named’ fields…

Unfortunately ChangeDate would end up updating any time changes are made to the order (at least in my case it does). If Yash’s company doesn’t touch the order after being created, that might work though!

There are instances the order does get touched to shift around need by/ship by dates for example or even the order line items itself. Is there a way to only pull the initial changelog date?

You would start by adding a UD field to OrderHed via the User Defined Column Maintenance menu. You then need to run the Sync Data Model program and restart the server. From there, you could do a data directive BPM to set the field to the current system date whenever a new row is added to the OrderHed table.

3 Likes

pretty new to data directive BPM’s… how would I go about setting that up?

1 Like

If you go to the EpicWeb website, you can download the ICE tools user guide for your version, and that’s a great place to start understanding. There should be a section on Business Process Management.

I have a similar BPM where we have Epicor check the Alert on Shipment box whenever an order is created. You’ll go to Data Directives Maintenance, add an in-transaction directive (i’m not an expert on in-transaction versus Standard, but this is what works for me :slight_smile: ). In the workflow designer, I’ve got a condition of “at least one added row to the OrderHed table.” Then connect that to a Set Argument/Variable action - this is where yours will differ from mine. You should be able to select your new UD field for the field and then grab the system date for the expression. When you’re done with the design, be sure to Enable the directive.

image

2 Likes

I agree with @jnbadger on all points - ChangeDate will change if you edit after the creation. Also on the BPM/Custom field piece. It’s a nice, specific use field you can add to the database that way.

If you are already doing a Changelog, you should be able to see the Min() date using a calculated field in a BAQ against that table. Something like this should do it…

SELECT [Company],[Key1] as OrderNum,[DateStampedOn],[LogText]
   FROM [Database].[Ice].[ChgLog] 
  where tablename='orderhed' and LogText like '%new record%'
2 Likes

In addition to the ChangeLog, if you turn on Bookings, there is a record for every transaction that changes value - when the user clicks ready to process though.

1 Like