TechKnowledge Content
Issue:
How can I get the PO number and date on the Purchase Receipts Report?
Resolution:
LINKING:
Choose the
Tablesicon from the top menu bar.
In the Tables window,
highlight IV_Purchase_Receiptsand choose the
Openbutton.
In the Table Definition window choose the
Relationshipsbutton.
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 Historyand 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.
Closethe Table Relationships window by clicking on the X in the upper right corner of that window.
Click
OK.
In the Tables window,
highlight POP_ReceiptLineHistand choose the
Openbutton.
In the Table Definition window choose the
Relationshipsbutton.
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 Workand click
OK.
For the Secondary Table Key select:
POP_POIdxID
Match the appropriate fields:
Primary Table: Secondary Table:
PO NumberPO 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 Historyand click
OK.
For the Secondary Table Key select:
POP_POHistIdxID
Match the appropriate fields:
Primary Table: Secondary Table:
PO Number PO Number
Choose
OK.
Closethe 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 Receiptsand choose insert.
Then on the Modified Reports side, highlight the same report and choose the Open button.
In the Report Definition window choose the
Tablesbutton.
Tables:
In the Report Table Relationships window, highlight the
Inventory Purchase Receipts Workand choose the
Newbutton.
Highlight the
Purchasing Receipt Line History*and choose the
OKbutton.
In the Report Table Relationships window, highlight the
Purchasing Receipt Line History*and choose the
Newbutton.
Highlight the
Purchasing Order Workand choose the
OKbutton.
In the Report Table Relationships window, highlight the
Purchasing Receipt Line History*and choose the
Newbutton.
Highlight the
Purchasing Order Historyand choose the
OKbutton.
Restrictions:
In the Report Restrictions window choose
New
.
New:
Name the restriction
Document Date
In thefieldssection:
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 Fieldbutton.
In the
operatorssection:
In the operators section, choose the
=button.
In thefieldssection:
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 Fieldbutton
The Restriction Expression should read:
POP_PO.Document Date = POP_PO.Document Date
Choose the
OKbutton.
Closethe Report Restrictions window.
Choose the
Layoutbutton You will need to create 3 Calculated fields.
Calculated Fields:
In the
Toolboxchoose
Calculated Fieldsfrom the drop down list.
Then choose the
Newbutton.
In the Calculated Field Definition window, type
PO Numberas the name.
For the
Result typechoose
Stringfrom the drop down list.
The
Expression Typeshould be
Conditional.
In the
fieldssection:
Place your cursor in the
Conditional Expression.
Choose the Fields tab.
Click on the drop down list for
Report Tableand choose
Inventory Purchase Receipts Work.
Click on the drop down list for
Table Fieldsand choose
Vendor IDand choose the
Addbutton.
In the operators section, choose the
=button.
Choose the
ConstantsTab.
Click on the drop down list for
Typeand choose
String.
Type
INV ADJin the Constants field, and choose the
Addbutton.
Place your cursor in the
True Case.
Choose the
ConstantsTab.
Click on the drop down list for
Typeand choose
String.
In the Constants field, leave it blankand choose the
Addbutton.
Place your cursor in the
False Case.
Choose the Fields tab.
Click on the drop down list for
Report Tableand choose
Inventory Purchase Receipts Work.
Click on the drop down list for
Table Fieldsand choose
Purchase Order Numberand choose the
Addbutton.
The Conditional Expression should read: IV_Purchase_Receipts.Vendor ID = "INV ADJ"
True Case: ""
False Case: IV_Purchase_Receipts.Purchase Order Number
Choose the
OKbutton.
Select the
Newbutton again.
In the Calculated Field Definition window, type
Dateas the name.
For the
Result typechoose
Datefrom the drop down list.
The
Expression Typeshould be
Conditional.
In the
fieldssection:
Place your cursor in the
Conditional Expression.
Choose the Fields tab.
Click on the drop down list for
Report Tableand choose
Purchase Order History.
Click on the drop down list for
Table Fieldsand choose
Document Dateand choose the
Addbutton.
In the operators section, choose the
=button.
Choose the
ConstantsTab.
Click on the drop down list for
Typeand choose
Date.
In the Constants field you will see 00000000000. Choose the
Addbutton.
Place your cursor in the
True Case.
Choose the Fields tab.
Click on the drop down list for
Report Tableand choose
Purchase Order Work.
Click on the drop down list for
Table Fieldsand choose
Document Dateand choose the
Addbutton.
Place your cursor in the
False Case.
Choose the Fields tab.
Click on the drop down list for
Report Tableand choose
Purchase Order History.
Click on the drop down list for
Table Fieldsand choose
Document Dateand choose the
Addbutton.
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
OKbutton.
Select the
Newbutton again.
In the Calculated Field Definition window, type
Purchase Order Dateas the name.
For the
Result typechoose
Datefrom the drop down list.
The
Expression Typeshould be
Conditional.
In the
fieldssection:
Place your cursor in the
Conditional Expression.
Choose the Fields tab.
Click on the drop down list for
Report Tableand choose
Purchase Receipts Work.
Click on the drop down list for
Table Fieldsand choose
Vendor IDand choose the
Addbutton.
In the operators section, choose the
=button.
Choose the
ConstantsTab.
Click on the drop down list for
Typeand choose
String.
Type
INV ADJin the Constants field, and choose the
Addbutton.
Place your cursor in the
True Case.
Choose the
ConstantsTab.
Click on the drop down list for
Typeand choose
Date.
In the Constants field you will see 00000000000. Choose the
Addbutton.
Place your cursor in the
False Case.
Choose the Fields tab.
Click on the drop down list for
Calculated Fieldsand 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
OKbutton.
In the
Toolboxchoose 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 fieldsthat were just put on the report.
In the Report Field Options window, be sure the
Display Typeis set to Hide When Empty.
Choose the
OKbutton.
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 Setupwindow (
Setup| System| Security) within Great Plains.
This article was TechKnowledge Document ID:22002
