Adding Bill of Material Information to the SOP Blank Invoice Form for Microsoft Dynamics GP

Hi Everyone!  

Wow!  How is it already January 31^st.  This month sure flew by! 

I had a couple of questions on the SOP Blank invoice form specifically around adding Bill of Materials information onto the report.  Information such as the BOM components, component item description and quantity from the BOM assembly window. 

Overall, there is not a great/easy way to add this information because of the table relationship between the SOP tables and the BOM assembly tables.  Unfortunately, there is not a valid relationship between them.  So, it’s not as easy as linking tables and pulling out the desired fields.   However, I have found a way to link the BOM header and line tables to the report in order to pull some of the data in a roundabout way and then use a functions script to get to the rest of the information. I will share with you what I found.  There are some limitations which I have listed below.  Hopefully this is helpful if you are looking to pull some BOM information onto the report.

So, part of the challenge as I mentioned above is the table linking and what tables are already linked onto the report.  There is already a one-to-any with the serial/lot table.  So really the only way to get the tables linked that you need is to remove the serial/lot table.  If you are okay with this here are steps to do for the SOP Blank Invoice form.  I was able to get the component items from the BOM to be listed.

NOTE: You will lose the ability to see serial and lot numbers on Invoices if you choose to do this.  If you do not want to lose this on the main SOP blank invoice form, the only option is to use another SOP form (SOP Short for example) for when you need to see components for items you built.

1. Go into RW and start by linking the following tables together. 
    
   LINKING:
   Choose the Tables icon from the top menu bar.
   In the Tables window, highlight SOP_LINE_WORK and choose the Open button.
   In the Table Definition window choose the Relationships button.
   Then choose the NEW button.
   
   New:
   1. Click on the Ellipse button to the left of the Secondary Table line.  (The button with three periods)
   2. Select the Bill of Materials Header and click OK.
   3. For the Secondary Table Key select: bmBillIdxID
   4. Match the appropriate fields: Save and close the windows
   Primary Table:                                       Secondary Table:
   
               Item Number                                         Item Number
    
   5. Save and close the windows
    
   Opening the Report:
   1. Click on the Reports icon from the top menu bar.
   2. From the Original Reports side, highlight the SOP Blank Invoice Form and choose insert.
   3. Then on the Modified Reports side, highlight the same report and choose the Open button.
   4. In the Report Definition window choose the Tables button.
   
   Tables:
   1. In the Report Table Relationships window, highlight the Sales Serial/Lot Work and History* and choose the Remove button. (Will give you a message “Are you sure you want to remove this table and it’s related tables?  Removing all corresponding tables.) Select OK.
   2. In the Report Table Relationships window, highlight the Sales Transaction Amounts Work and choose the New button.
   3. Highlight the Bill of Materials Header* and choose the OK button.
   4. In the Report Table Relationships window, highlight the Bill of Materials Header* and choose the New button.
   5. Highlight the Bill of Materials Components* and choose the OK button
   6. Select the Close button.
   
   
   In the Report Definition window choose the Restrictions button.
   
   Restrictions:                
   1.In the Report Restrictions window choose the Type and Number of SOP_Serial_Lot = self restriction and select Delete.  Select Yes.
   2. Select New
   3. Name the restriction Item Number
   4. In the fields section, click on the drop down list for Report Table and choose the Bill of Materials Component
   5. Click on the drop down list for Table Fields and choose Item Number 
   6. Choose the Add Field button.
   7. In the operators section, choose the = button.
   8. In the fields section, click on the drop down list for Report Table and choose the Bill of Materials Component
   9. Click on the drop down list for Table Fields and choose Item Number 
   10. Choose the Add Field button
   11. Save/ok and close the window. 
   
    
   In the Report Definition window choose the Layout button.
   
   1. Delete the Serial/Lot Number and the ( C) Serial Lot Quantity fields out of the body.  Go to Tools>Report Section Options, unmark the suppress when field is empty (under the body).
   2.  Select OK.
   
   Calculated Fields: (Have to open and then delete)
   1. In the Toolbox choose Calculated Fields from the drop down list. 
   2. Select (C) Serial Lot Band, Select Open, Select Delete.
   3. Select (C) Serial Lot Quantity, Select Open, change the expression type to Calculated.  Select Continue, Change the result type to integer.  Go to the Constants tab and select integer as the type 0.  Leave the Constant blank and select ADD.
   4. Select OK
   5.  Exit out of the layout and save your changes.
   
   Choose the Layout button.
   
   Bringing fields onto the report:
   1. In the Toolbox choose Bill of Materials Component from the drop down list.
   2. In the scrolling window find Component Item Number and drag it into the B section on the report.
   3. Exit out of layout and save your changes. 
   Give access to the report. 
    
   COMPONENT ITEM DESCRITPION
   Create a new Calculated field using a function to pull the Item Description.  

1. In RW Open the Layout for the SOP form.
2. On the toolbox select Calculated field and select New.
3. Name the calculated field: Example: Microsoft Item Description (or whatever you like)
4. Result type: String
5. Expression: Calculated
6. Put your cursor in the Expressions section.
7. Select Functions tab
8. Select User Defined
9. Select Core: Inventory
10. Select Function: rw_GetItemDescription
11. Select Add
12. Select Fields Tab
13. Select Resources: Bill of Materials Component
14. Select Fields: Component Item Number
15. Select Add
16. Select OK
17. Drag the field into the Body.
18. Go to File > Microsoft Dynamics GP and Save/Close/Ok to any message. 

QUANTITY:
Create a calculated field just by taking the Design quantity times the Quantity to invoice.  Give this a try.  NOTE: If you want the Component Quantity.  It’s kind of a back way to get to it as we are basically pulling the design quantity value.

Result Type is Integer

In the fields section:
Choose the Fields tab.
Click on the drop down list for Report Table and choose Bill of Materials Component. 
Click on the drop down list for Table Fields and choose Design Qty and choose the Add button. 

In the operators section:
In the operators section, choose the * button.

In the fields section:
Choose the Fields tab.
Click on the drop down list for Report Table and choose Sales Transaction Amounts Work. 
Click on the drop down list for Table Fields and choose Qty To Invoice and choose the Add button. 

Select OK to save the Calculated Field

Drag this field into the Body next to the Component Item Number field.

I hope this information is helpful if you choose to add Bill of Materials information onto the SOP Blank Invoice form.

Warmest Regards,

Angela Ebensteiner | SR Technical Advisor | Microsoft Dynamics GP