<< Click to Display Table of Contents >> Navigation:  The BOM > The Excel BOM > Customize the Excel BOM > Post processing BOM data

Post processing BOM data

BOM data can be updated after being calculated automatically and before being sent to the final output, either an Excel BOM or a BOM inside a drawing or a dataset to the ERP.

Please note the first step is to enable the script in the parameter file for the given BOM type

...

OPTIONS
 ...
 POST_PROCESS_SCRIPT <scriptName>.vbs
 ...

Example 1

Here we provide an example of a script that given a calculated recordset groups the items by ID and sums the quantities of the items having the same ID.

In this case we select an indented BOM and add the following line to the options section:

...

OPTIONS
 ...
 POST_PROCESS_SCRIPT GroupDuplicateIDs.vbs
 ...

Now we add the script GroupDuplicateIDs.vbs in the directory LST as follows:

Sub Main()

 set dict = CreateObject("Scripting.Dictionary")
 while not rsbom.eof
  id = rsbom.fields("document_id").value
  qty = rsbom.fields("qty").value
  if not dict.exists(id) then
   ' keep track of existing quantities
   dict.add id,cstr(qty)
  else
   oldQty = cdbl(dict.item(id))
   newQty = oldQty + qty
   ' update the dictionary in memory with the new quantity
   dict.remove id
   dict.add id,cstr(newQty)
   ' remove the record from the recordset
   rsbom.delete
  end if
  rsbom.movenext
 wend
 ' now rescan the entire recordset and update the quantity where necessary
 rsbom.movefirst
 while not rsbom.eof
  id = rsbom.fields("document_id").value
  oldQty = rsbom.fields("qty").value
  newQty = cstr(dict.item(id))
  if newQty <> oldQty then
   rsbom.fields("qty").value = cdbl(newQty)
   rsbom.update
  end if
  rsbom.movenext
 wend
End Sub

As you can see, the script contains the sub Main that is invoked at runtime to update the data
The script includes automatically the Recordset rsBOM as a variable of type ADODB.Recordset

The recordset contains the following fields that can be read and/or updated:

You can also have access to the data of the DOCUMENT table contained for each BOM item as in the following script, that updates the field DESCRIPTION:

Sub Main()

 while not rsbom.eof
  set rsDocument = rsbom.fields("DBWorksDocuments").value
  descVal = rsDocument.fields("Description").value
  descVal = descVal + "_AAA"
  rsDocument.fields("DESCRIPTION").value = descVal
  rsDocument.update
  rsbom.movenext
 wend
End Sub

Example 2

This script shows how to sum the level 2 quantities but avoiding duplicates

Here is the definition in the parameter file:

...

OPTIONS
 ...
 POST_PROCESS_SCRIPT CountLevel2Qty.vbs
 ...

Here is the CountLevel2Qty.vbs content:

sub main()

 set dict = CreateObject("Scripting.Dictionary")
 set dictUpdatedQties = CreateObject("Scripting.Dictionary")
 ' assign a filter to visit only items at level 2
 rsbom.filter = "LEVEL = 2"
 while not rsbom.eof
  uid = rsbom.fields("uid").value
  qty = rsbom.fields("qty").value
  if dict.exists(uid) then
   ' item already found
   oldQty = dict(uid)
   newQty = cint(oldQty) + cint(qty)
   dict.remove uid
   ' update the dictionary
   dict.add uid,newQty
   if dictUpdatedQties.exists(uid) then
    dictUpdatedQties.remove uid
   end if
   ' mark as a qty to be updated later
   dictUpdatedQties.add uid,newQty
   ' remove the duplicate record from the recordset
   rsbom.delete
  else
   dict.add uid,qty
  end if
  rsbom.movenext
 wend
 rsbom.movefirst
 while not rsbom.eof
  uid = rsbom.fields("uid").value
  if dictUpdatedQties.exists(uid) then
   ' update the qty in the recordset
   rsbom.fields("qty").value = cint(dictUpdatedQties(uid))
   rsbom.update
  end if
  rsbom.movenext
 wend
 set dict = nothing
 set dictUpdatedQties = nothing
 rsbom.updatebatch
 rsbom.movefirst
 rsbom.filter = ""
end sub