You are currently offline, waiting for your internet to reconnect

PO Number and Date on the Purchase Receipts Report

TechKnowledge Content
Issue:

How can I get the PO number and date on the Purchase Receipts Report?

Resolution:

LINKING:

Choose the Tables icon from the top menu bar.

In the Tables window, highlight IV_Purchase_Receipts and choose the Open button.

In the Table Definition window choose the Relationships button.

Then choose the New button.

New:

Click on the Ellipse button to the left of the Secondary Table line. (The button with three periods)

Select the Purchasing Receipt Line History and click OK.

For the Secondary Table Key select: POP_RcptLineHistIdx_ItemNum

Match the appropriate fields:

Primary Table: Secondary Table:

Item Number Item Number

Leave blank POP Receipt Number

Leave blank Receipt Line Number

Choose OK.

Close the Table Relationships window by clicking on the X in the upper right corner of that window.

Click OK.



In the Tables window, highlight POP_ReceiptLineHist and choose the Open button.

In the Table Definition window choose the Relationships button.

Then choose the New button.

New:

Click on the Elipse button to the left of the Secondary Table line. (The button with three periods)

Select the Purchasing Order Work and click OK.

For the Secondary Table Key select: POP_POIdxID

Match the appropriate fields:

Primary Table: Secondary Table:

PO Number PO Number



Choose OK.

Choose the New button again. We need to link the Purchase Order History table to the Inventory Purchase Receipts table

New:

Click on the Elipse button to the left of the Secondary Table line. (The button with three periods)

Select the Purchasing Order History and click OK.

For the Secondary Table Key select: POP_POHistIdxID

Match the appropriate fields:

Primary Table: Secondary Table:

PO Number PO Number



Choose OK.

Close the Table Relationships window by clicking on the X in the upper right corner of that window.

Click OK.

Opening the Report:

Click on the Reports icon from the top menu bar.

From the Original Reports side, highlight the Inventory Purchase Receipts and choose insert.

Then on the Modified Reports side, highlight the same report and choose the Open button.

In the Report Definition window choose the Tables button.

Tables:

In the Report Table Relationships window, highlight the Inventory Purchase Receipts Work and choose the New button.

Highlight the Purchasing Receipt Line History* and choose the OK button.

In the Report Table Relationships window, highlight the Purchasing Receipt Line History* and choose the New button.

Highlight the Purchasing Order Work and choose the OK button.

In the Report Table Relationships window, highlight the Purchasing Receipt Line History* and choose the New button.

Highlight the Purchasing Order History and choose the OK button.

Restrictions:

In the Report Restrictions window choose New.

New:

Name the restriction Document Date

In the fields section:

In the fields section, click on the drop down list for Report Table and choose the Purchase Order Work.

Click on the drop down list for Table Fields and choose Document Date.

Choose the Add Field button.

In the operators section:

In the operators section, choose the = button.

In the fields section:

In the fields section, click on the drop down list for Report Table and choose the Purchase Order Work.

Click on the drop down list for Table Fields and choose Document Date.

Choose the Add Field button



The Restriction Expression should read: POP_PO.Document Date = POP_PO.Document Date

Choose the OK button.

Close the Report Restrictions window.



Choose the Layout button You will need to create 3 Calculated fields.

Calculated Fields:

In the Toolbox choose Calculated Fields from the drop down list.

Then choose the New button.

In the Calculated Field Definition window, type PO Number as the name.

For the Result type choose String from the drop down list.

The Expression Type should be Conditional.



In the fields section:

Place your cursor in the Conditional Expression.

Choose the Fields tab.

Click on the drop down list for Report Table and choose Inventory Purchase Receipts Work.

Click on the drop down list for Table Fields and choose Vendor ID and choose the Add button.

In the operators section, choose the = button.

Choose the Constants Tab.

Click on the drop down list for Type and choose String.

Type INV ADJ in the Constants field, and choose the Add button.

Place your cursor in the True Case.

Choose the Constants Tab.

Click on the drop down list for Type and choose String.

In the Constants field, leave it blankand choose the Add button.

Place your cursor in the False Case.

Choose the Fields tab.

Click on the drop down list for Report Table and choose Inventory Purchase Receipts Work.

Click on the drop down list for Table Fields and choose Purchase Order Number and choose the Add button.

The Conditional Expression should read: IV_Purchase_Receipts.Vendor ID = "INV ADJ"

True Case: ""

False Case: IV_Purchase_Receipts.Purchase Order Number

Choose the OK button.

Select the New button again.

In the Calculated Field Definition window, type Date as the name.

For the Result type choose Date from the drop down list.

The Expression Type should be Conditional.



In the fields section:

Place your cursor in the Conditional Expression.

Choose the Fields tab.

Click on the drop down list for Report Table and choose Purchase Order History.

Click on the drop down list for Table Fields and choose Document Date and choose the Add button.

In the operators section, choose the = button.

Choose the Constants Tab.

Click on the drop down list for Type and choose Date.

In the Constants field you will see 00000000000. Choose the Add button.

Place your cursor in the True Case.

Choose the Fields tab.

Click on the drop down list for Report Table and choose Purchase Order Work.

Click on the drop down list for Table Fields and choose Document Date and choose the Add button.

Place your cursor in the False Case.

Choose the Fields tab.

Click on the drop down list for Report Table and choose Purchase Order History.

Click on the drop down list for Table Fields and choose Document Date and choose the Add button.

The Conditional Expression should read: POP_POHist.Document Date = 0/0/0000

True Case: POP_PO.Document Date

False Case: POP_POHist.Document Date

Choose the OK button.

Select the New button again.

In the Calculated Field Definition window, type Purchase Order Date as the name.

For the Result type choose Date from the drop down list.

The Expression Type should be Conditional.



In the fields section:

Place your cursor in the Conditional Expression.

Choose the Fields tab.

Click on the drop down list for Report Table and choose Purchase Receipts Work.

Click on the drop down list for Table Fields and choose Vendor ID and choose the Add button.

In the operators section, choose the = button.

Choose the Constants Tab.

Click on the drop down list for Type and choose String.

Type INV ADJ in the Constants field, and choose the Add button.

Place your cursor in the True Case.

Choose the Constants Tab.

Click on the drop down list for Type and choose Date.

In the Constants field you will see 00000000000. Choose the Add button.

Place your cursor in the False Case.

Choose the Fields tab.

Click on the drop down list for Calculated Fields and choose Date .

The Conditional Expression should read: IV_Purchase_Receipts.Vendor ID = "INV ADJ"

True Case: 0/0/0000

False Case: Date (calculated field created earlier)

Choose the OK button.

In the Toolbox choose Calculated Fields from the drop down list.

In the scrolling window find PO Number and Purchase Order Date and drag it into the H3 section on the report.

In the report layout, double click on the both of the fields that were just put on the report.

In the Report Field Options window, be sure the Display Type is set to Hide When Empty.

Choose the OK button.



Choose the X from the top right corner of the Report Layout window.

Save your changes.

Click OK in the Report Definition window.

From the top menu bar, choose File | Great Plains Dynamics.

Give users access to the modified report in the Security Setup window (Setup | System | Security) within Great Plains.



This article was TechKnowledge Document ID: 22002
Properties

Article ID: 871951 - Last Review: 04/27/2016 23:15:00 - Revision: 4.0

Microsoft Dynamics GP 10.0

  • kbnosurvey kbmbsmigrate KB871951
Feedback
html>