Table links

Epicor ERP 10
1

Hello,
Is there a better way to link OrderMsc to InvcMisc tables?
I’ve tried this, which works in most cases:
where OrderMsc_Row.OrderNum == OrderHed.OrderNum && OrderMsc_Row.SeqNum == ttInvcMisc_xRow.SeqNum/10

However, when an OrderMsc record is deleted from the middle of a set of Misc Charges, the OrderMsc_Row.SeqNum will stay with its original sequence
image
While when the Invoice is generated and my BPM fired, the ttInvcMisc_xRow.SeqNum will resort to a sequence with no gaps
image

Thus, my association fails.

I need a better solution.

Here is the entire code of the BPM:

Erp.Tables.OrderMsc OrderMsc = null;
Erp.Tables.InvcHead InvcHead = null;
Erp.Tables.InvcMisc InvcMisc = null;
var ttInvcMisc_xRow = (from ttInvcMisc_Row in ttInvcMisc where ttInvcMisc_Row.RowMod == IceRow.ROWSTATE_ADDED || ttInvcMisc_Row.RowMod == IceRow.ROWSTATE_UPDATED select ttInvcMisc_Row).FirstOrDefault();
if (ttInvcMisc_xRow != null)
{
	InvcHead = (from InvcHead_Row in Db.InvcHead
             where InvcHead_Row.Company == Session.CompanyID && InvcHead_Row.InvoiceNum == ttInvcMisc_xRow.InvoiceNum
select InvcHead_Row).FirstOrDefault();
if(InvcHead != null)
{
	OrderHed = (from OrderHed_Row in Db.OrderHed
						where OrderHed_Row.OrderNum == InvcHead.OrderNum
select OrderHed_Row).FirstOrDefault();
if (OrderHed != null)
{
OrderHed.Proposal_Num_c = ttInvcMisc_xRow.InvoiceNum.ToString();
OrderMsc = (from OrderMsc_Row in Db.OrderMsc
						where OrderMsc_Row.OrderNum == OrderHed.OrderNum && OrderMsc_Row.SeqNum == ttInvcMisc_xRow.SeqNum/10
select OrderMsc_Row).FirstOrDefault();
if (OrderMsc != null)
{
ttInvcMisc_xRow.ExtDesc_c = OrderMsc.ExtDesc_c;
}
}
}
} ```
2

Why are you trying to link those? EDIT: I don’t know a better way to link those.

So if you respond, I am going to probably take this some other direction. If that’s definitely what you want to do, I don’t have a better way.

3

To pass the UD field info from Order Misc to Invc Misc

The end result is all that matters. I don’t care to keep this way, if I have to worry about a user deleting out a Charge from the middle of the list and this occasionally failing.

1 Like
4

I see, yeah I can’t help right now with that. I guess what I want to ask is, can you break down the business process so we can all see what you need to have happen?

Why do you need a UD field to begin with?

5

I was told that 30 characters was too limiting for the standard field. I don’t make the rules on that one.

When an Invoice is generated, the 1000 character UD of the Order Misc Charges needs to pass to the corresponding Invoice Misc Charges 1000 character UD field that matches that line.

6

It seems that I could first resort the OrderMsc number sequence to remove any gaps when a delete happens.
image

7

Couldn’t you use UD Column Map for this?

e: nm I didn’t realize you were limited to the pre-defined table maps. I guess there isn’t one for those 2 tables.

1 Like
8

Looks like OrderMsc.SeqNum is a Key field.
Does that mean Epicor would limit me from resetting/resorting the values after the delete function?
I don’t know if there would be secondary ramifications to messing with a Key field like that.

Maybe I force my own sequence and insert it into a never (in my company) used field like Rpt3InMiscAmt.

9

@estm8ben Making a lot of assumptions without the data behind this, but IF both sets are on the header as it seems and they are added to the invoice every time then you could process them after an update of something like calculate taxes. Something like below.

var currentSeq = 0;

foreach (var InvcMiscRow where it is my invoice)
{
OrderMsc = (from OrderMsc_Row in Db.OrderMsc
						where OrderMsc_Row.OrderNum == OrderHed.OrderNum && OrderMsc_Row.SeqNum > currentSeq
select OrderMsc_Row).FirstOrDefault();

if (OrderMsc != null)
{
InvcMiscRow.ExtDesc_c = OrderMsc.ExtDesc_c;

currentSeq = OrderMsc_Row.SeqNum;
}
}
10

If there’s always a 1:1 relationship between OrderMsc and InvcMisc, could you just build a couple of arrays. You could have the sequence number in the array. So, your order array would have 5 elements with sequences 1, 2, 4, 5 and 6. Your invoice array would have 5 elements with sequences 1, 2, 3, 4, 5. Then, just match the corresponding elements in the arrays.

Not sure if that would work for you?

1 Like