Tuesday, 5 July 2022

Programming challenge with Bills Of Materials

The standard reports for displaying bills of materials (BOMs) that come with Priority show the depth of a part in the BOM in a field that is slightly complicated to program. Instead of trying to explain this in words, I'll use a table

Number Part description Father Level
1 A chair - 0
2 Arm rests 1 1
3 Mechanism 1 1
4 Back 1 1
5 Wood 4 .2
6 Sponge 4 .2
7 Fabric 4 .2
8 Back rest 1 1
9 Wood 8 .2
10 Sponge 8 .2
11 Fabric 8 .2

A chair is built from arm rests, a mechanism (for raising and lowering the seat), a seat and a back rest. For these parts, their father is the chair, or in terms of the above table, part #1. A seat (and a back rest) is made from wood, sponge and fabric; for these parts, their father is the seat (or back rest), part #4 or part #8. With regard to levels, the chair is at level 0; the arm rests, mechanism, seat and back rest are at level 1 (direct sons of the chair); the wood, sponge and fabric are at level 2, which is shown as .2 in the above table.

A customer did not want the level to be displayed as above; instead, he wants that in the field equivalent to level, the arm rests will appear as 1 (no change), the mechanism as 2, the seat as 3, the wood of the seat as 3.1, etc. The table below shows how the customer wants the level to be displayed.

Number Part description Father Level
1 A chair - 0
2 Arm rests 1 1
3 Mechanism 1 2
4 Back 1 3
5 Wood 4 3.1
6 Sponge 4 3.2
7 Fabric 4 3.3
8 Back rest 1 4
9 Wood 8 4.1
10 Sponge 8 4.2
11 Fabric 8 4.3

This might seem to the customer to be a reasonable request, but in terms of Priority (or any SQL based language) this - at least to me - is extremely difficult to program. It took me half an hour of walking the dog to come up with a solution, but I am not convinced that this is the best/only way of creating those values.

So I am opening this problem as a programming challenge: send me code that achieves what the customer requested.

Some assumptions:
The external program SONRAW has been run, and the exploded BOMs are in a linked copy of PARTARC.
There is a linked table STACK4 whose key is PARTARC.SONACT, i.e. an index (see here for a discussion how SONACT can be used in conjunction with STACK4).
The level string should be stored in STACK4.DOCNO.

No comments:

Post a Comment