Multi Part BAQ UD Field selection to Single record

Epicor ERP 10
,
1

We are currently implementing Epicor 10 and by design have multi assembly parts where the sub assembly parts hold user defined (UD) fields depending on what production process the sub assembly part is progressing through. Obviously getting all the required fields into a single result makes priting out a document that may well contain details from the finished part, the printed part as well as the extruded part a trivial exercise.

So:
Finished Part:ABCD/1000 Fields UD_10, UD_11 … etc
Extruded Part: ABCD/1000-E Fields UD_20, UD_21 … etc
Printed Part: ABCD/1000-P Fields UD-30, UD_31 …etc

I want to end up with a single flat record containing the relevant UD fields from each of the “-E”, “-P” and finished part in a single record, i.e UD_10, UD_11, UD_20, UD_21, UD_30, UD31 What is the best way to do this via a BAQ.

Obviously all UD fields are held against all part records but they are all unique to the sub assembly so a UD field will only be held against the finished part or the -P or -E part not all parts.

Initially I thought about selecting the Finished part then linking the parts table twice to itself with the where statement forcing the -E or -P as part of the part number but this ended in failure! I know I will have to use subqueries but am a little stuck as how to do this at the moment. I would have no problem writing this directly in SQL but the BAQ designer has me stumped at the moment with regards to subqueries.

Thanks in anticipation.
Dave

2

Are one (or more) of the Keys in the UD tables used to relate the record to the part, by partnumber?

Can you give us an example of how the UD tables connect to the part and what fields in the UD table are used?

edit

Or did I read it wrong, and UD_10, UD_11, etc… are UD fields of the Part table, and not UD tables themselves?

3

Yes, UD_10 … etc are UD fields held in the part table.

I was just trying to show how UD_10 will only ever hold data against the finished part and UD_20 only ever against the Extruded Part. The only link between the 3 parts is that they start with the Customer Number ABCD/1000, the finished part not containing any postfix.

4

So your Part table data would be like:

part.partnum part.description   part.UD_10_c part.UD_20_c part.UD_30_c
ABCD/1000    Widget             XYZ
ABCD/1001    Wodget             XXY
ABCD/1000-E  Widget Extrusion                PDQ
ABCD/1001-E  Wodget Extrusion                QRS
ABCD/1000-P  Printed Widget                                ABC-77
ABCD/1001-P  Printed Wodget                                DEF-99
5

Yes … and I want to return back a single record with only the UD fields from the sub assembly parts that have them:
BAQ should return one record per finished part:

part UD_10_c UD_20_c UD_30_c
ABCD/1000 XYZ PDQ ABC-77
ABCD/1001 XXY QRS DEF-99

Oh and forgot to say based on Epicor Public Cloud

Dave

6

Make 2 inner subqueries. Assuming subquery 1 is your top level …

  • Subquery 2 (name it ExtParts)

    • Table: part (it will be given the alias part1
    • Display columns:
      • Company
      • UD_20
      • Calc field Ext_PN, with the expression
        (case when CHARINDEX('-E', Part1.Partnum) <> 0 THEN LEFT(Part1.Partnum, CHARINDEX('-E', Part1.Partnum)-1) ELSE '' END)

  • Subquery 3 (name it PntParts)

    • Table: part (it’ll be named part2)
    • Display columns:
      • Company
      • UD_30
      • Calc field Pnt_PN, with the expression
        (case when CHARINDEX('-P', Part2.Partnum) <> 0 THEN LEFT(Part2.Partnum, CHARINDEX('-P', Part2.Partnum)-1) ELSE '' END)

Link the ExtParts subquery to the part table in your top level query with
part.partnum = ExtParts.Ext_PN & part.company = ExtParts.company

And similarly for the PntParts sub query

edit

Not sure if the -1 is needed in

image

The intent of that formula is to set the ExtParts.Ext_PN to either blank ('') - when -E is not on the P/N, or to the characters leading up to the -E. Ex: ABC -> '', ABC-E -> ABC

7

Calvin,
That was exactly what I needed to get me started off, which is all you need when approaching something brand new!

I have just done the complete query and implemented it in a dashboard thanks to your help.

Many thanks indeed!
Dave

1 Like