Wednesday 6 November 2024

The pitfalls of the PARTVAR table/form

Recently I was asked to write a report that would show the time required to build a sales part (actually for all the parts that were ordered over a year). For those that don't know, this time data is to be found in the table PARTVAR that is displayed as a son form of the PART form. One very important field in this table is VALID - a part can have several activities listed in its PARTVAR form, but not all of them necessarily are valid. A report that wants to show how much time would be required to build a sales part should ignore lines not marked as valid.

I wrote the report, ran it - and discovered that the values that the report was showing did not match those in a 'normal' BOM report. After getting very dirty hands by examining the data being summed at a very low level, I discovered that the report was including PARTVAR lines that were not valid, despite the fact that the query explicitly excluded those lines.

Even stranger, if I ran the report again after having examined the data in the PARTVAR form, the cumulative time for the part examined would be reduced!

Eventually I found the reason for this strange behaviour (actually I had discovered it several years ago but had forgotten). When descending from the PART form to its son PARTVAR form, the latter has a pre-form trigger than runs an external program called PARTVAR (the actual command is EXECUTE PARTVAR :$$.PART). Presumably this program updates the data stored in the PARTVAR table so that a query accessing it will receive up-to-date values.

This is a really important pitfall! Eventually I was reduced to calling the external program for every part whose time I was accumulating. A procedure would descend through the exploded BOM and call PARTVAR for every P part. This adds no small amount of time to the procedure's execution, but it's very important. 

At the same time I added the EXECUTE PARTVAR command to the procedure that displays the BOM - I had called it for the 'father' part whose BOM was being exploded, but not for the sons. Thus I discovered that the previously reported time for my test part was wrong and did not include a certain activity; the opposite behaviour to the big 'time only' report that was over-reporting by including non-valid activities.