Mapping Custom Fields

Will79 · July 16, 2020, 6:34pm

I am trying to Map ShipTo Address–>Zip code into OrderHed table as an Custom field. I have everything entered, but I am not sure how to get the mapping to show where to look for the data. When I go into UD Column Map and click on target, nothing populates. What am I missing?

Will79 · July 16, 2020, 6:38pm

ckrusen · July 16, 2020, 6:43pm

You want the OrderHed.Myzip_c filed to be set to the ZIP of the ShipTo entry selected on order header?

Will79 · July 16, 2020, 6:45pm

Yes. The ShipTo in the Order to show up as you stated.

Will79 · July 16, 2020, 6:48pm

So, I changed the Like Column to match the label in the ShipTo and tried to save. I received the error:

Business Layer Exception

The like is not valid.

Exception caught in: Epicor.ServiceModel

Error Detail

Correlation ID: 74d56768-9788-4bad-8437-f7933eef42cb
Description: The like is not valid.
Program: Ice.Services.BO.ZDataTable.dll
Method: ZDataFieldBeforeUpdate
Line Number: 869
Column Number: 21
Table: ZDataField

Client Stack Trace

at Epicor.ServiceModel.Channels.ImplBase1.ShouldRethrowNonRetryableException(Exception ex, DataSet[] dataSets) at Ice.Proxy.BO.ZDataTableImpl.Update(ZDataTableDataSet ds) at Ice.Adapters.ZDataTableAdapter.OnUpdate() at Ice.Lib.Framework.EpiBaseAdapter.Update() at Ice.UI.App.UDTable.Transaction.adapterUpdate() at Ice.Lib.Framework.EpiMultiViewTransaction.Update(IEnumerable1 dataSets)

Will79 · July 16, 2020, 6:51pm

On the Data Type, should it be SysName instead of String?

ckrusen · July 16, 2020, 6:55pm

You’ll have to make a BPM to populate the UD field. The BPM will also allow you to add what ever logic you need in determining how to set it. For example, should MyZip_c be set to the SHipTo.Zip only if it was previously blank? Or anytime it is different.

Consider the following scenarios:

Cust XYZ has (2) Ship To’s
a. The default ShipTO (which usually has ShipTo of <blank>), has a ZIP of 12345
b. The 2nd ShipTo (ShipTo ABC) has a zip of 99999
And order is created and Cust XYZ is selected.This deafults the ShipTo on the OrderHed as <blank>.
The BPM sets OrderHed.MyZip_c to 12345
Later the ShipTo on the order is changed to ABC.

Do you want your OrderHed.MyZip_c to remain 12345? Or be changed to 99999 ??

And this doesn’t even take into account of what happens if the SHipTo’s address is changed, after it was selected on an order.

Cust XYZ has (2) Ship To’s
a. The default ShipTO (which usually has ShipTo of <blank>), has a ZIP of 12345
b. The 2nd ShipTo (ShipTo ABC) has a zip of 99999
And order is created and Cust XYZ is selected.This deafults the ShipTo on the OrderHed as <blank>.
The BPM sets OrderHed.MyZip_c to 12345
Later the ZIP in the ShipTo record (for cust XYZ with ShipTo <blank>) is changed to 54321.

The order would never be aware of this, and therefore OrderHed.MyZip_c would remain 12345

Will79 · July 16, 2020, 7:21pm

Ok, so the reason I am wanting to do this is because I need to get what invoices was sent out in 2019 based on the ShipTo state. Every time I try to bring in the tables for that, it crashes. I am using InvcHead, OrderHed, and ShipTo

Banderson · July 16, 2020, 7:30pm

When? In a BAQ?

Will79 · July 16, 2020, 7:31pm

Yes, that is correct.

Banderson · July 16, 2020, 7:36pm

Well, to state the obvious, that shouldn’t be happening… Not sure how to fix that though.

But, for historical s the BPM or mapping approach isn’t going to work on existing records., You would have to populate the field with DMT or something. That you would get from a … BAQ… So looks like you either have to figure out why your BAQ is crashing, or do a backend query in the DB.

When you say crash, what exactly do you mean? The server? Your computer? Just the BAQ screen?

ckrusen · July 16, 2020, 7:39pm

I don’t know your exact business situation, but a better thought would be to use the Packer table instead of the Order.

And do it at the invoice line level, not the header. Because it’s possible to have an invoice with lines for different ShipTo’s. Again, your business might never do this, but it is technically possible in E10.

Each invoice line relates to a Packer. So use the ShipTo info from the related ShipTo table for the packer.

Will79 · July 16, 2020, 7:57pm

Didn’t know there was a packer table.

Will79 · July 16, 2020, 7:59pm

The BAQ times out. Sorry for not using better wording.

Banderson · July 16, 2020, 8:01pm

Yeah, that’s a lot different than crashing.

try to limit the row to only a couple, then see what happens. If you can get in the filtering correct, there is no reason you shouldn’t be able to make this BAQ.

Mark_Wonsil · July 16, 2020, 8:05pm

Like Brandon says, start a new BAQ and limit the records as quickly as possible. One table and a selection. Then add the next table. Add one thing at a time. You will quickly find out what is causing the issue. I build all my BAQs this way. Every time.

Mark_Wonsil · July 16, 2020, 8:10pm

Tax reporting? If so, do yourself a favor. Use tax codes! Even a zero percent. You get to use actual Epicor reports and nothing to upgrade! If not, you have to worry about One Time Ship-Tos (OTS) and Freight Forwards, etc. It’s a mess.

Will79 · July 16, 2020, 8:11pm

Moving to Avalara. Need the info for that, is my guess right now.

ckrusen · July 16, 2020, 8:15pm

Here’s a simple BAQ to return the ZIP code and state of each Invoice lines ShipTo

InvceDtl to ShipHead table relations

ShipHead to ShipTo table relations

Display Columns

edit

It will produce Invoices without ShipTo Info (for Misc Invoices)

You’ll probably need to tune it for things like credit memos too.

Will79 · July 16, 2020, 8:36pm

In the end, this worked. I think me trying to bring in InvcHed, OrderHed, OrderDtl, ShipTo was just stupid and too much. Why it didn’t cross my mind sooner, only God knows!

select
[SubQuery1].[InvcHead_FiscalYear] as [InvcHead_FiscalYear],
[SubQuery1].[InvcHead_FiscalPeriod] as [InvcHead_FiscalPeriod],
[SubQuery1].[ShipTo_State] as [ShipTo_State],
(SUM(SubQuery1.InvcHead_DocInvoiceAmt)) as [Calculated_TOTAL_Amount]
from Erp.InvcHead as InvcHead1
inner join (select
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[InvcHead].[FiscalYear] as [InvcHead_FiscalYear],
[InvcHead].[FiscalPeriod] as [InvcHead_FiscalPeriod],
[InvcHead].[DocInvoiceAmt] as [InvcHead_DocInvoiceAmt],
[ShipTo].[State] as [ShipTo_State]
from Erp.InvcHead as InvcHead
inner join Erp.InvcDtl as InvcDtl on
InvcHead.Company = InvcDtl.Company
and InvcHead.InvoiceNum = InvcDtl.InvoiceNum
inner join Erp.ShipTo as ShipTo on
InvcDtl.CustNum = ShipTo.CustNum
and InvcDtl.ShipToNum = ShipTo.ShipToNum
where (InvcHead.FiscalYear = 2019)
group by [InvcHead].[InvoiceNum],
[InvcHead].[FiscalYear],
[InvcHead].[FiscalPeriod],
[InvcHead].[DocInvoiceAmt],
[ShipTo].[State]) as SubQuery1 on
InvcHead1.InvoiceNum = SubQuery1.InvcHead_InvoiceNum
group by [SubQuery1].[InvcHead_FiscalYear],
[SubQuery1].[InvcHead_FiscalPeriod],
[SubQuery1].[ShipTo_State]
order by SubQuery1.InvcHead_FiscalYear, SubQuery1.InvcHead_FiscalPeriod