Cost Overrides on Purchase Orders

This week I had a request from a customer for a workbook to display all Purchase Order line items that have had the cost overridden.  This post will detail the construction of that workbook.

When beginning a project you need to determine as closely as you can exactly what the person requesting the workbook want but frequently you'll find that even though they "think" they know what they want you will end up making additions or changes to the workbook before you complete the project.  Just be aware that the better your understanding of what the end user wants/needs the easier the project will be.

When starting on a new workbook I usually begin by entering the headings based on what the user has requested.  You need to consider the fields that will be used to populate the data for each heading and make additional fields that will be used but not displayed in your workbook (i.e. dates will display in one cell in Excel but require three or four fields from the HD database).  This workbook has no such requirements but it should be kept in mind.  The image below shows the worksheet with the headings entered.

Now we select cell B6 and create the MS query to return the data to the worksheet.  Based on the headings we will be extracting data from the Purchase Order Line Item File (POPTRL) and the Item Master File (IVPMSTR).  These two files are joined on the Item Number field (IVNO07).  The worksheet does not display a date but a date is used as part of the record selection process.  The date that will be used is the Last Update date.  For the date selection criteria the HD fields are joined to create a single field that can be passed as a parameter to the query.  Since date fields in HD are numeric this is accomplished by summing the fields like this: POCC01*1000000+POYR01*10000+POMO01*100+PODY01.  This result of this formula for July 1, 2011 is 20110701 which can be passed to the query to return records for the requested date.  The value for the date is set in brackets so it can be changed from the worksheet (i.e. [chgdate]).  Since the user is only interested in records where the cost has been overridden then the criteria also includes the Line Item Cost Override Code (POCD17).  After including the criteria and selecting the fields to be returned to the worksheet the query will look like this:

When the query is run to return the data to the workbook a dialog box will display requesting the parameter (date) to be passed to the query.  Enter the desired date and click OK.

The data is now returned to the worksheet as below:

NOTE: Since this is data from a Customers Production the Product Numbers have been blanked out.

Now add a heading for the date select in cell D4 (i.e. Enter Date >>) and enter a date in E4.  In cell M6 enter this formula - =SUM(YEAR(E4)*10000+MONTH(E4)*100+DAY(E4)).  This formula provides a field that will be used as the parameter for the query.  Now open the Parameter dialog box.  To open the dialog box in Excel 2007 click Data Tab > Connections  and select the query and click Properties > Definition Tab > Parameters button; for 2003 select a cell in the data returned by the query and right click and click Parameters.  In the Parameters dialog box

click the radio button "Get the value from the following cell:" and the check box "Refresh automatically when cell value changes".  Click in the field below "Get the value..." and then click in cell M6.  Click OK.  Now when the user changes the date in cell E4 the query will run and return data for the entered date.  Test the workbook to verify that it functions correctly and returns the correct data.

Lastly format the workbook as desired and test once more and it should be ready for your user.

Here's my formatted worksheet.

As always if you have any comments and/or questions leave a comment here or email me at the office.