Walk-through: updating Quoted Total Cost using BOM Loader

This walk-through describes how to update the Quoted Total Cost field using the BOM Loader (for other available field options, see Mapping BOM loader headings), in the following stages:

Stage 1: Prepare the CSV input file

  1. Create a spreadsheet in MS Excel with the following column headers: Part Number, Quantity, Level, Manually Costed, Quoted Cost, Scenario Name, and Type (the order is not important as you will map these headers to aPriori fields later).

  2. Populate the fields as follows:

    • Part Number and Scenario Name: populate these columns using the exact syntax displayed in the aPriori UI for Name and Scenario. To do this, first copy the table of the roll-up that contains the parts to a separate MS Excel spreadsheet:

      Then copy the Name and Scenario values into the Part Number and Scenario Name columns in the CSV input spreadsheet.

    • Quantity and Level: enter ‘1’ for both (this will not overwrite the existing quantity of the part in a BOM or the level of the part in a BOM in the database).

    • Type: enter either ‘Part’ or ‘Assembly.’ This is important for aPriori to differentiate between classifications as a part, assembly, or roll-up and so that comparisons can have the same part number and scenario name.

    • Manually Costed: this must have a value of ‘FALSE’ for scenarios in the database already costed in a process group that is not manually costed (Sheet Metal, Plastic Molding, and Stock Machining). (The BOM Loader default process group is set to ‘manually costed’ as it is used so frequently to load catalog parts that are costed as ‘manually costed.’)

    • Quoted Cost: enter the quoted cost from your supplier.

    Note: Some customers may have a required UDA field that should also be included.

  3. Save the spreadsheet as a CSV (comma-delimited) file. The completed file should look similar to this:

Stage 2: Configure the Mapping

  1. Launch the BOM Loader tool and open the CSV input file (see Loading a BOM).

  2. Give the mapping a name relevant to the data being added:

  3. Use the drop-down menus in the Column Mapping pane to map the Input File Column headers to the appropriate aPriori Field:

    For more information, see Mapping BOM loader headings.

  4. Ensure that Processing Rule if Scenario Exists is set to Update so that the quoted cost is added to the existing scenarios in the database.

  5. (Optional) Add a name for the roll-up in the Roll-up Name field if a roll-up of the parts does not already exist. This is good practice for tracking the parts being loaded using the BOM Loader. You can then delete the roll-up after you have checked that the BOM loading has been successful (this will not delete the parts within the roll-up).

  6. Click the Save icon or select File > Save Changes to save the mapping.

Note: You can create multiple mappings that correspond to different input CSV templates. For example, one for fastener mapping, quoted cost mapping, UDA mapping, and so forth.

Stage 3: Import the Mapping

  1. Click the Import button in the top-right corner of the UI.

    A ticker starts in the lower-right corner of the UI and then the Import Results pop-up window shows the results:

    Note: The text in the screen capture above states that the scenario has been updated rather than created; if the text states created, there has likely been a data input mismatch error whereby aPriori has been unable to recognize a match for the Part Number or Scenario to identify the appropriate file in the database to update.

Stage 4: Verify the Quoted Cost Update

  1. Open the roll-up file.

  2. Check the Quoted Total Cost field to verify that the quoted cost BOM has been successful:

    Note: if the data being added is expected to be visible by default in an assembly, ensure that the Scenario is the ‘Official’ one:

    (If required, you can also update the Official field for a Scenario using the BOM Loader.)