TechKnowledge Content
Issue:How can I get the PO number and date on the Purchase Receipts Report?Resolution:LINKING:Choose theTablesicon from the top menu bar.In the Tables window,highlight IV_Purchase_Receiptsand choose theOpenbutton.In the Table Definition window choose theRelationshipsbutton.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 thePurchasing Receipt Line Historyand clickOK.For the Secondary Table Key select:POP_RcptLineHistIdx_ItemNumMatch the appropriate fields:Primary Table: Secondary Table:Item Number Item NumberLeave blank POP Receipt NumberLeave blank Receipt Line NumberChooseOK.Closethe Table Relationships window by clicking on the X in the upper right corner of that window.ClickOK.In the Tables window,highlight POP_ReceiptLineHistand choose theOpenbutton.In the Table Definition window choose theRelationshipsbutton.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 thePurchasing Order Workand clickOK.For the Secondary Table Key select:POP_POIdxIDMatch the appropriate fields:Primary Table: Secondary Table:PO NumberPO NumberChooseOK.Choose the New button again.We need to link the Purchase Order History table to the Inventory Purchase Receipts tableNew:Click on the Elipse button to the left of the Secondary Table line. (The button with three periods)Select thePurchasing Order Historyand clickOK.For the Secondary Table Key select:POP_POHistIdxIDMatch the appropriate fields:Primary Table: Secondary Table:PO Number PO NumberChooseOK.Closethe Table Relationships window by clicking on the X in the upper right corner of that window.ClickOK.Opening the Report:Click on the Reports icon from the top menu bar.From the Original Reports side, highlight theInventory 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 theTablesbutton.Tables:In the Report Table Relationships window, highlight theInventory Purchase Receipts Workand choose theNewbutton.Highlight thePurchasing Receipt Line History*and choose theOKbutton.In the Report Table Relationships window, highlight thePurchasing Receipt Line History*and choose theNewbutton.Highlight thePurchasing Order Workand choose theOKbutton.In the Report Table Relationships window, highlight thePurchasing Receipt Line History*and choose theNewbutton.Highlight thePurchasing Order Historyand choose theOKbutton.Restrictions:In the Report Restrictions window chooseNew.New:Name the restrictionDocument DateIn thefieldssection:In the fields section, click on the drop down list for Report Table and choose thePurchase Order Work.Click on the drop down list for Table Fields and chooseDocument Date.Choose theAdd Fieldbutton.In theoperatorssection:In the operators section, choose the=button.In thefieldssection:In the fields section, click on the drop down list for Report Table and choose thePurchase Order Work.Click on the drop down list for Table Fields and chooseDocument Date.Choose theAdd FieldbuttonThe Restriction Expression should read:POP_PO.Document Date = POP_PO.Document DateChoose theOKbutton.Closethe Report Restrictions window.Choose theLayoutbutton You will need to create 3 Calculated fields.Calculated Fields:In theToolboxchooseCalculated Fieldsfrom the drop down list.Then choose theNewbutton.In the Calculated Field Definition window, typePO Numberas the name.For theResult typechooseStringfrom the drop down list.TheExpression Typeshould beConditional.In thefieldssection:Place your cursor in theConditional Expression.Choose the Fields tab.Click on the drop down list forReport Tableand chooseInventory Purchase Receipts Work.Click on the drop down list forTable Fieldsand chooseVendor IDand choose theAddbutton.In the operators section, choose the=button.Choose theConstantsTab.Click on the drop down list forTypeand chooseString.TypeINV ADJin the Constants field, and choose theAddbutton.Place your cursor in theTrue Case.Choose theConstantsTab.Click on the drop down list forTypeand chooseString.In the Constants field, leave it blankand choose theAddbutton.Place your cursor in theFalse Case.Choose the Fields tab.Click on the drop down list forReport Tableand chooseInventory Purchase Receipts Work.Click on the drop down list forTable Fieldsand choosePurchase Order Numberand choose theAddbutton.The Conditional Expression should read: IV_Purchase_Receipts.Vendor ID = "INV ADJ"True Case: ""False Case: IV_Purchase_Receipts.Purchase Order NumberChoose theOKbutton.Select theNewbutton again.In the Calculated Field Definition window, typeDateas the name.For theResult typechooseDatefrom the drop down list.TheExpression Typeshould beConditional.In thefieldssection:Place your cursor in theConditional Expression.Choose the Fields tab.Click on the drop down list forReport Tableand choosePurchase Order History.Click on the drop down list forTable Fieldsand chooseDocument Dateand choose theAddbutton.In the operators section, choose the=button.Choose theConstantsTab.Click on the drop down list forTypeand chooseDate.In the Constants field you will see 00000000000. Choose theAddbutton.Place your cursor in theTrue Case.Choose the Fields tab.Click on the drop down list forReport Tableand choosePurchase Order Work.Click on the drop down list forTable Fieldsand chooseDocument Dateand choose theAddbutton.Place your cursor in theFalse Case.Choose the Fields tab.Click on the drop down list forReport Tableand choosePurchase Order History.Click on the drop down list forTable Fieldsand chooseDocument Dateand choose theAddbutton.The Conditional Expression should read: POP_POHist.Document Date = 0/0/0000True Case: POP_PO.Document DateFalse Case: POP_POHist.Document DateChoose theOKbutton.Select theNewbutton again.In the Calculated Field Definition window, typePurchase Order Dateas the name.For theResult typechooseDatefrom the drop down list.TheExpression Typeshould beConditional.In thefieldssection:Place your cursor in theConditional Expression.Choose the Fields tab.Click on the drop down list forReport Tableand choosePurchase Receipts Work.Click on the drop down list forTable Fieldsand chooseVendor IDand choose theAddbutton.In the operators section, choose the=button.Choose theConstantsTab.Click on the drop down list forTypeand chooseString.TypeINV ADJin the Constants field, and choose theAddbutton.Place your cursor in theTrue Case.Choose theConstantsTab.Click on the drop down list forTypeand chooseDate.In the Constants field you will see 00000000000. Choose theAddbutton.Place your cursor in theFalse Case.Choose the Fields tab.Click on the drop down list forCalculated Fieldsand chooseDate.The Conditional Expression should read: IV_Purchase_Receipts.Vendor ID = "INV ADJ"True Case: 0/0/0000False Case: Date (calculated field created earlier)Choose theOKbutton.In theToolboxchoose 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 theDisplay Typeis set to Hide When Empty.Choose theOKbutton.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
