Custom Report and Label Making
15.4.1 Overview
- The configuration menu in pc/MRP for Windows version 6.49AH contains a custom report writer that allows users to modify pc/MRP reports. The table below contains the name of pc/MRP's standard and custom reports and the dbf file used with each report. Prior to modifying or creating a report, back up the original standard frx. and frt. report files and or the standard .lbx and lbt label files to floppy disk for safe keeping. These back up files will come in handy if you make a mistake and need to restore the original files. It is best to open a standard report, rename it to the correct custom name, and then modify the new report as standard reports are overwritten and custom reports are not overwritten when pc/MRP is updated to a newer version.
To create a custom report you would:
- Go to the configuration menu, select next
window, select report writer
Enter your configuration code number
Select the correct frx file you wish to modify or use as a basis for custom report
Click on Open
Select file, save the file as a custom .frx report (see list below for correct file name)
Make your modifications
Select file, close, save changes
Return to pc/MRP's Main Menu
Print out the new report selecting custom instead of standard
pc/MRP's Custom Report Writer is shown above with the following icons in the
upper left hand corner text A, field ab, line, sq box , rounded
box, and picture (bit map). Clicking on an icon will allow you to add an
object. Double clicking on any object allows you to edit and or move it. Double
clicking a field object or clicking on the word object brings up the Report
Expression dialog box (shown below) which allows you to edit the object.
The first box contains the expression (field, memory variable, or text) you
would like to print out in that area of the report. In the example above the
part number would print out in the area you are editing. The format field and
button will allow you to format the data (!!!! = upper case, 999.999.99 =
numeric only with 2 decimal places, etc.). The Print When Box will allow you to
specify conditions that must be true for this item to print out. For example
entering len(alltrim(partno)) > 0 would print out the part number only if it
the length of the trimmed part number were greater than zero. Clicking on the
Expression Button will allow you to select the memory or field variable from a
list rather than typing in the variable expression by hand as shown below:
In the above example the partno field was selected by double clicking on the
partno field in the fields list. If we wanted to print out the first 4
characters of the part number, you could type in left(partno,4) into the expression
box or click on the string function list and select the function left(). The
String list contains all FoxPro string functions (substr(), alltrim(), etc.).
The Logical list contains all FoxPro logic functions (and, or, etc.). The Math
list contains all FoxPro math functions (+, -, /, *, etc.). The Date list
contains all FoxPro date functions (ctod(), dtoc(), month(), etc.). The Verify
button will automatically verify that the expression you have built in the
expression box is valid.
15.4.1.1 Example of Modifying a Report
Suppose that you wish to make some changes to an inventory report.
- Go to the Main Menu
- Click on the Configuration option
- Click on Settings and Utilities
- Click on the Next button
- Place your cursor over the spinner button
- Click the "up" arrow until you get "24"
- Click OK
- Enter your code number and click OK
- Click on the Form button
- Select the partmaster.dbf file by double clicking or click USE
- Select the parts$.frx file by double clicking or click OPEN
Your screen should look like this:
The first thing you want to do is to save the file.
You want to save using the custom form name so as not to overwrite the original file.
- Click on File
- Click on Save As
- The Save Report As box should read: cuspar*.frx (* means any characters you want. Keep the report names relatively short. Some reports will only have a ? or ??. This means your report name can end in any single character or double character you want.)
- Click the Save button
- Place your cursor over the title "Partmaster Report"
- Hit the "delete" button on your keyboard
- Click on the text box (A) over on the left hand side of the screen
- Place the cursor over where "Partmaster Report" use to be and click
- Type in "Partmaster Inquiry" and click somewhere on the screen
- Click back onto your new text to highlight it with the little square boxes
- Now go to the menu bar and click on Object
- Click on Font; this should bring you to a font dialog box
- The font should be Courier New, font style is Bold and size is 16; click OK
- Click on Object again
- Click Pen Color
- Chose the color red
Your screen should look similar to this:
- Delete the text "DESCRIPTION" in the Page Header field
- Delete the field "DESCRIPTION" in the Detail field
- Click on the Text (A) icon
- Place the cursor where DESCRIPTION used to be in the Page Header field and click
- Type in "CLASSIFICATION" (make sure you type in capital letters)
- The font should be Courier New, bold, size 8
- Highlight CLASSIFICATION and check the font information to make sure
- Click on the Field (ab) icon
- Place the cursor where DESCRIPT used to be the Detail field and click
- The Report Expression dialog box should pop up
- In the Expression Box type in "CLASS"
- Check the middle radio button: Top--Field Can Stretch
- Click OK
- Now you must stretch out the Field (ab) cursor using the arrow cursor
- You will know when you can stretch out the field when you get a cursor with arrows pointing out from the middle
Your screen should look similar to this:
When you close the parts$.frx screen, you do not need to save any of the changes.
This exercise is designed to get you familiar with modifying any report you
want.
15.4.2 List of Reports
pc/MRP for Windows includes the report, forms, and labels listed below. If the last character in the report name is "w" it is the wide carriage report. For example the UNCSIN report is the report used for 8.5" x 11" print outs and the UNCSINW report is the report form use for 14" x 11" printouts.
When viewing any report you can view the open tables by pressing CTRL-F10. This will open a TXT file with all of the open tables in the "select area" statement. By example: "Select area: 1, Table in Use: C:\MY DOCUMENTS\PCMRPTEST\PCMRPW750\PARTMAST.DBF Alias: PARTMAST" This means the Partmast.dbf is open when running this report.
REPORT(.FRX) REPORT DESCRIPTION DBF FILE USED 15.4.2.1 BOM Module Standard BOM Reports UNCSIN standard uncosted single level BOM bomtemp.dbf UNCSRF standard uncost single level BOM w ref des bomtemp.dbf SINBOM standard costed single level BOM bomtemp.dbf UNCEXP standard uncosted exploded BOM bomtemp.dbf EXPBOM standard costed exploded BOM bomtemp.dbf ALLBOM standard all BOMs bomtemp.dbf AVABOM standard parts availability report bomtemp.dbf PICBOM standard pic list exploded and singlelevel bomtemp.dbf CNVBOM standard convert parts to assy report bomtemp.dbf CNVSBOM standard issue parts infinite levels dp report bomtemp.dbf PICBSN standard pic list with available serial#'s bomtemp.dbf Custom BOM Reports CUNCSIN custom uncosted single level BOM bomtemp.dbf CUNCSRF custom uncost single level BOM w ref des bomtemp.dbf CSINBOM custom costed single level BOM bomtemp.dbf CUNCEXP custom uncosted exploded BOM bomtemp.dbf CEXPBOM custom costed exploded BOM bomtemp.dbf CALLBOM custom all BOMs bomtemp.dbf CAVABOM custom parts availability report bomtemp.dbf CPICBOM custom pic list exploded and single level bomtemp.dbf CCNVBOM custom convert parts to assy report bomtemp.dbf WHEBOM standard where used bomtemp.dbf WHEBOMML multi-leve where used wheretree (tmp) Note: Version 6.49BI, 6.53N, AND 6.54A and and higher use tmpb+[the first four characters of the users name].dbf instead of bomtemp.dbf for BOM reports. Note: Custom BOM reports on pc/MRP versions 6.73 and higher can also be named CUSBOM?. Note: Custom BOM reports on pc/MRP versions 7.50 and higher can also be named CUSBOM*. 15.4.2.2 Accounting Module Standard Accounting Reports GLINCOME standard gl income statement chartacc.dbf PRDEBIT standard asset side of balance sheet chartacc.dbf PRCREDIT standard liability + equity side of bal sheet chartacc.dbf GLTRIAL standard trial balance report chartacc.dbf PRYTD year to date expense and revenue report chartacc.dbf CHART standard, narrow, wide carriage chart of acct chartacc.dbf PRGL t-acct,4th close mon, curr bal accting reports prgltmp.dbf PRTRAN general ledger and 5th close month gl reports prtrntmp.dbf CHKREC check reconciliation report prgltmp.dbf CNVSBOM parts issued for a sales or work order report stocktra.dbf COSTSALE cost of sales report based on invoices invoice.dbf COSTSALD cost of sale from stckroom and Employee Modulestocktra.dbf ACCREC accts receivable report invoice.dbf ACCRECW accts receivable wide carriage report invoice.dbf SREC summary accts receivable report billtmp.dbf HREC accts receivable payment history report billtmp.dbf ACCPAY accts payable report receive.dbf ACCPAYW accts payable wide carriage report receive.dbf SPAY2 summary accts payable report acpaytmp.dbf TAXLOG in state sales tax report invoice.dbf TAXLOGO out of state sales tax report invoice.dbf PRCHECKS single laser check form mchktmp.dbf (tmp) PRDCHK single dot matrix check form mchktmp.dbf (tmp) BILL billing statement form billtmp.dbf COMMISS commission report based on payment transact.dbf COMMISSI commission report based on invoices issued invoice.dbf PPV purchase price variation report ppvtmp.dbf FIFO FIFO report fifotmp.dbf ROYALTY standard royalty report invoice.dbf CASHOUT standard cash out report purchase.dbf RECLOG standard debit memo report receive.dbf REMIT single check remittance report transact.dbf REMITALL standard remittance report transact.dbf PRGLALL standard balance for a time period prgltmp.dbf Custom Accounting Reports and Checks invoice.dbf CCOMMISS custom commission report based on payment transact.dbf CCOMMISS custom commission report based on invoice invoice.dbf CUSCHECK custom check (6.73 and Higher) mchktmp.dbf (tmp) The following custom reports were added to versions 7.01K, 7.02N and higher CPRTRAN custom gen ledger, 5th close month gl reports prtrntmp.dbf CPRGL cus t-acct,4th close mon,cur bal acct reports prgltmp.dbf CHKREC custom check reconciliation report prgltmp.dbf CTAXLOG custom in state sales tax report invoice.dbf CTAXLOGO custom out of state sales tax report invoice.dbf CCOMMISS cus commission on payment/invoice transact/invoice.dbf CROYALTY custom standard royalty report invoice.dbf CCOSTSAL custom cost of sales report based on invoices invoice.dbf CCASHOUT custom cash out report purchase.dbf CRECLOG custom debit memo report receive.dbf CACCPAY custom accts payable report receive.dbf CACCPAYW custom accts payable wide carriage report receive.dbf CSPAY2 custom summary accts payable report acpaytmp.dbf CACCREC custom accts receivable report invoice.dbf CACCRECW custom accts receivable wide carriage report invoice.dbf CSREC custom summary accts receivable report billtmp.dbf CHREC custom accts receivable payment history reportbilltmp.dbf CBILL custom billing statement form billtmp.dbf CPPV custom purchase price variation report ppvtmp.dbf CFIFO custom FIFO report fifotmp.dbf 15.4.2.3 Partmaster Module Standard Partmaster Reports PART standard uncosted disp and pr inventory rpt partmast.dbf PARTW standard uncosted wide inventory report partmast.dbf PART$ standard costed pr and wide inventory report partmast.dbf PARTS$ standard costed disp inventory report partmast.dbf PRICELIS standard price list report partmast.dbf BLNKINV standard disp and pr physical/inventory form partmast.dbf BLNKINVW standard wide carriage physical/inventory frt partmast.dbf COSTINV physical inventory variance report partmast.dbf PARMOVP physical inventory, inventory movement report movement.dbf PARMOV partmaster inventory movement report movement.dbf CYCLECNT cycle count standard report cyctmp.dbf DISSPART inventory/partmaster find part report partmast.dbf PIH physical inventory history report pih.dbf Custom Partmaster Reports (Prior to Ver 6.49AR) CUSTPARS custom disp inventory report partmast.dbf CUSTPART custom pr inventory report partmast.dbf CUSTPARW custom wide inventory report partmast.dbf Custom Partmaster Reports (After Ver 6.49AR) CUSPARS0-9 custom disp inventory report partmast.dbf CUSPART0-9 custom pr inventory report partmast.dbf CUSPARW0-9 custom wide inventory report partmast.dbf CUSTPHY custom physical inventory variance report partmast.dbf MCUSTREPNO custom inventory movement report movement.dbf (tmp) Custom Partmaster Reports (After Ver 6.73) CUSPAR?? custom inventory report partmast.dbf Custom Partmaster Reports (Version 7.50 and above) CUSPAR* custom inventory report partmast.dbf Custom Partmaster Reports (After Ver 6.91G) CBLNKINV custom physical inventory report partmast.dbf CUSPIH* custom physical inventory report pih.dbf 15.4.2.4 Invoice Module Standard Invoice Reports INVLOG narrow carriage invoice report invoice.dbf INVLOGW wide carriage invoice report invoice.dbf Standard Invoice/Shipper Form INVOICE shaded invoice form invoice.dbf INVOICEB boxed invoice form invoice.dbf SHIPPER shaded shipper form invoice.dbf SHIPPERB shaded boxed shipper form invoice.dbf Custom Invoice Reports (Prior to Ver 6.49AR) CUSTINV custom invoice report invoice.dbf Custom Invoice Reports (After Ver 6.49AR) CUSINV?? custom invoice report invoice.dbf Custom Invoice Reports (Version 7.50 and above) CUSINV* custom invoice report invoice.dbf Custom Invoice/Shipper Form CINVOICE custom invoice form invoice.dbf CSHIPPER custom shipper form invoice.dbf Note: Invoice reports automatically have a set relation into the Partmast.dbf and Addrbook.dbf. 15.4.2.5 Sales Order/ Work Order Module Standard Sales Report SALELOG narrow carriage and displayed sales report sales.dbf SALELOGW wide carriage sales report sales.dbf SOPER ISO 9000 Customer on time delivery report sales.dbf Standard Sales/Shipper FormSALE shaded sales order form sales.dbf
SALEB boxed sales order form sales.dbf SHIPPER shaded shipper form sales.dbf SHIPPERB shaded boxes shipper form sales.dbf Custom Sales Order Reports (Prior to Ver 6.49AR) CUSTSO custom sales report sales.dbf Custom Sales Order Reports (After Ver 6.49AR) CUSSO?? custom sales report sales.dbf Custom Work Order Reports (Version 7.50 and above) CUSSO* custom work order reports sales.dbf Custom Work Order/Sales/Shipper Form CSO custom work order and sales order form sales.dbf CSHIPPER custom shipper form sales.dbf Custom Work Order Reports (Up to version 7.06) CUSWO?? custom work order reports sales.dbf Custom Work Order Reports (Up to version 7.06) CUSWO?? custom work order reports sales.dbf Custom Work Order Reports (Version 7.50 and above) CUSWO* custom work order reports sales.dbf Note: Sales Order reports automatically have a set relation into the Partmast.dbf and Addrbook.dbf. 15.4.2.6 Receive Module Standard Receive Report RECLOG narrow carriage receiver report receive.dbf RECLOGW wide carriage receiver report receive.dbf Standard Receive Form REC shaded receiver form receive.dbf RECB boxed receiver form receive.dbf Custom Receive Report (Prior to Ver 6.49AR) CUSTREC custom receiver report receive.dbf Custom Receive Report (After Ver 6.49AR) CUSTREC0-9 custom receiver report receive.dbf Custom Receive Report (Version 7.50 and higher) CUSTREC* custom receiver report receive.dbf Custom Receiver Form CREC custom receiver form receive.dbf Note: Receiver reports automatically have a set relation into the Partmast.dbf and Addrbook.dbf. 15.4.2.7 Purchase Module Standard Purchase Report PURLOG narrow carriage purchase report purchase.dbf PURLOGW wide carriage purchase report purchase.dbf VENPER ISO 9000 vendor performance report purchase.dbf Standard Purchase FormPO shaded PO form purchase.dbf
POB boxed PO form purchase.dbf
Custom Purchase Report (Prior to Ver 6.49AR) CUSTPO custom purchase order report purchase.dbf Custom Purchase Report (Version 6.49AR to 7.06) CUSTPO? custom purchase order report purchase.dbf Custom Purchase Report (Version 7.50 and higher) CUSTPO* custom purchase order report purchase.dbf Custom Purchase FormCPO custom PO form purchase.dbf
Note: Purchase Order reports automatically have a set relation into the Partmast.dbf and Addrbook.dbf. 15.4.2.8 Sales Quote Module Standard Sales Quote Report QUOLOG standard quote report quote.dbf Custom Quote Report (Prior to Ver 6.49AR) CUSTQUO custom quote report quote.dbf Custom Quote Report (Version 6.49AR to 7.06) CUSTQUO? custom quote report quote.dbf Custom Quote Report (Version 7.50 and higher) CUSTQUO* custom quote report quote.dbf Standard Sale Quote Form
QUOTE standard shaded sales quote form quote.dbf QUOTEB standard boxed sales quote form quote.dbf Custom Quote Form CQUOTE custom sales quote form quote.dbf Note: Sales Quote reports automatically have a set relation into the Partmast.dbf and Addrbook.dbf. 15.4.2.9 MRP, Min Max, Infinite Bucket Reports Standard MRP, Min Max, Infinite Bucket Reports LGPARREQ standard display/print MRP report partmast.dbf WPARREQ standard wide carriage MRP report partmast.dbf LGMINREQ standard min max report partmast.dbf MRP2 standard nonconsolidated inf bucket MRP report action.dbf MRP2CONS standard consolidated inf bucket MRP report mrp2cons.dbf MRP2EXCS standard infinite bucket excess inventory rep exectmp.dbf (tmp) MRP2CBPP standard infinite bucket pre-shortage report action.dbf PUSHPULL standard infinite bucket push pull report avail.dbf MRP2BUY standard infinite bucket buy report action/partmast.dbf Custom Min Max Report CUSTMRP custom min max report partmast.dbf Custom MRP Report CUSTCMRP custom MRP report found in purchasing module partmast.dbf CMRP2 custom MRP report found in infinite bucket action.dbf CUSMRP* custom MRP report found in infinite bucket action.dbf CUSTMRP* custom MRP report found in infinite bucket action.dbf Note: Most Infinite Bucket MRP reports use the Action.dbf and Mrppart.dbf. 15.4.2.10 Sales Analysis Module Standard Sales Analysis Report MAKECONS standard consolidated single BOM report makecons.dbf MAKECONM standard consolidated multiple BOM report makecons.dbf SANAL std sales analysis report sanal.dbf SANALCS std cost of sales report costsale.dbf SANAL2 std sales analysis report sorted by division sanal.dbf Custom Sales Analysis Report CUSSANA custom sales analysis report sanal.dbf 15.4.2.11 Address Book Module Standard Address Book Report ADDRESS standard address book report addrbook.dbf ADDRESSW standard wide carriage address book report addrbook.dbf Custom Address Book Report CUSADD?? custom address book report (Prior to 6.73) addrbook.dbf CUSADR?? custom address book report (6.80 and Higher) addrbook.dbf CUSADR* custom address book report (7.50 and Higher) addrbook.dbf 15.4.2.12 Stockroom Module Standard Stockroom Reports STOCKLOG standard stock room transaction report stocktra.dbf CNVSBOM standard stockroom I transaction report stocktra.dbf MAKEUPWO standard stockroom P transaction by W/O stocktra.dbf ISUSHORT standard stockroom P consolidated report stocktra.dbf UNISSUE standard stock room U transaction report stocktra.dbf SWOLOG std stock room report subtot by sales/work# stocktra.dbf AVABUY standard buy report bomtemp.dbf AVABOM standard parts available report bomtemp.dbf PARLAB2X standard part labels for I transaction, see LABEL (.LBX) below. SKTRCMFG standard receive from manufacturing report stocktra.dbf BOMBUILD standard max no of BOMs that can be built bomlist(cursor) Custom Stockroom Reports CUSMUS* custom stockroom P transaction report stocktra.dbf CUSUIS* custom stockroom I transaction report stocktra.dbf CUSSTK* custom stock room report stocktra.dbf CUSTSTK* custom stock room report stocktra.dbf CAVABOM custom parts available report bomtemp.dbf CPARLAB custom part labels for I transaction, see LABEL (.LBX) below. CUSBMB custom max no of BOMs that can be built bomlist(cursor) 15.4.2.13 Purchase Request/Quote Module Standard Purchase Request FormsREQUESTB standard boxed PO request form request.dbf
REQUEST standard shaded PO request form request.dbf
Custom Purchase Request FormCREQUEST custom PO request form request.dbf
Standard Purchase Quote Forms POQUOB standard boxed quote form request.dbfPOQUO standard shaded PO quote form request.dbf
Custom Purchase Quote FormCPOQUO custom PO quote form request.dbf
Note: Purchase Request/Quote reports automatically have a set relation into the Partmast.dbf and Addrbook.dbf. 15.4.2.14 Serial/Lot Number Module Standard Serial/Lot Number Form AUDITSN standard Serial/Lot Audit report form auditsn.dbf SNLOG standard master serial/lot number report snlot.dbf SNDLOG standard master serial/lot number report snlotdet.dbf Custom Serial/Lot Number Form CAUDITSN custom Serial/Lot Audit report form auditsn.dbf CUSTSN standard master serial/lot number report snlot.dbf CUSTSN standard master serial/lot number report snlotdet.dbf 15.4.2.15 Routing Module Standard Routing Form ROUTE standard Routing Module report routing.dbf
ROUTEW wide carriage Routing Module report routing.dbf
Custom Routing Forms CUSTRTR* custom Routing Module report form routing.dbf CUSRTR* custom Routing Module report form routing.dbf 15.4.2.16 Employee Time Tracking Module Standard Employee Tracking Forms EMPLOG all employee form employee.dbf EMPFRM employee profile form employee.dbf EMPTIMESH all time sheets forms emptimesh.dbf EMPTIMECOST all Labor cost forms emptimesh.dbf Custom Employee Time Tracking Forms CUSEMP* custom employee report form employee.dbf CUSTIM* custom time sheet form emptimesh.dbf 15.4.2.17 Inspection Module Standard Inspection Forms INSPIL standard inspection list form inspect.dbf INSPALL standard all inspection form inspect.dbf Custom Inspection Form CUSINS* custom inspection form inspect.dbf 15.4.2.18 ECN Module Standard ECN Forms ECN standard inspection list form ecn.dbf Custom ECN Form CECN custom inspection form ecn.dbf LABEL(.LBX) LABEL DESCRIPTION DBF FILE USEDPARLABEL standard parts labels for all parts dot matrix partmast.dbfPARLAB2X standard parts labels for all parts laser pr partmast.dbfCPARLAB custom parts labels for all parts,prod,pn partmast.dbfBOMLB standard parts labels for an assy dot matrix bomtemp.dbfBOMLB2X standard parts labels for an assy laser pr bomtemp.dbfCUSBOMLB custom parts labels for an assy bomtemp.dbfRECLABEL standard parts label for a receiver receive.dbfRECLAB2X standard parts labels for a receiver receive.dbfCRECLAB custom parts labels for an assembly receive.dbf ADDRLBS pin fed standard address label 15/16x3-1/2 addrbook.dbfADDRLBM laser standard address label 4"x2-7/8" addrbook.dbfADDRLBMS2 pin fed standard address label 4"x2-7/8" addrbook.dbfCUSADRXX custom address labels (version 6.80 and higher) addrbook.dbf CUSTADRX custom address labels (version 6.80 and higher) addrbook.dbf
15.4.3 Adding Bar Code Fonts to Custom Labels, Forms, and Reports
Barcodes can be added to
pc/MRP labels, forms, and reports by installing pc/MRP's $149.00 optional True
Type bar code font into Windows Font file (Select Main, Control Panel, Fonts,
Add, and enter the location of the barcode font A: for example). To modify a
report or label use pc/MRP's report generator, select the related data base
file, select the report, select the variable, select object such as part
number, select font, select the bar code font. The bar code font should be 26
points or higher and should not be printed out in bold. The 3 of 9 font
requires that the variable be entered as:
"*" + ALLTRIM(PARTNO) + "*"
or
BAR39STR(PARTNO)
The above example is for the variable PARTNO.
To create a custom part label with a bar coded part number you would.
Go to the configuration menu,
select next window, select report writer
Select Label
Select PARLABTMP.DBF as your dbf file, click on Use
Select PARLABEL.LBX as your label, click on open
Save the PARLABEL.LBX as CPARLAB.LBX
Click on the part number object, select FONT, select BC 39 3/1 HD medium,
regular, 26 points
Click on the part number object and change it from partno to read
"*" + ALLTRIM(PARTNO) + "*"
To print your custom part number labels
Go to the Inventory Module, select labels, select all part numbers, select custom
15.4.4 Clearing the save environment feature on FoxPro for Windows reports
FoxPro for Windows reports save the name of the dbf files that were open when the report was created or last run. This feature can inhibit customizing the report if several dbf files were open when the report was last run or created. If several data base files were open at the time, the report may pick the wrong dbf file to open when you try to view your modifications. To turn off the "save environment" feature, open up the report using the modify report command in FoxPro’s command window and follow the steps listed below:
Click on report
Click on page layout
Click on environment
Click on clear
Click on ok
Click on file
Click on save
Reopen the report and repeat the above process, if you were successful you will see the environments clear and restore buttons are now disabled. Go ahead and click on cancel. Now you will have to enter the related dbf file manually prior to modifying the report, however it will be the correct dbf file.
15.4.5 Opening an additional related database file when printing/displaying a report
The following modifications should only be made by a person who is familiar with dbase and FoxPro commands.
This section explains how to open up another related database file when running a report for the purpose of printing out data from the original database file and the related database file. For example, a user may wish to have a custom sales order report print/display the drawing number for each sales order listed in the report. The drawing number is not in the sales.dbf, so the user must modify pc/MRP such that the partmast.dbf file is open and related to the sales.dbf file when ever that report is run. To make the modifications follow the steps listed below:
pc/MRP versions 6.80 to 6.85A
- Use Window's Notepad program to create an ASCII text file named "RPTREL.EF" for a report or "FRMREL.EF" for a form. For example if you had a custom sales report named "CUSTSO1.FRX" and wanted to include data from another data base besides sales.dbf you would either open or create a file in the pcmrpw directory named "RPTREL.EF" with Window's Notepad program.
- Enter the following dbase/Foxpro commands into the "RPTREL.EF" file. As this file may handle other custom reports the initial line of this section of code must identify the report you want to open up additional dbf files for. In this case you would enter "[CUSTSO1.FRX]". The next line, ~IN~, tells pc/MRP to follow this set of commands before starting to print the report. The second command, THISAREA=SELECT(), stores the name of the first database file in a memory variable named THISAREA. The third through fifth command then set relation between the two files. The last command, GO TOP, makes sure the original dbf file (in this case, sales.dbf) is initialized and ready to begin printing. The seventh command, ~OUT~, begins the instruction set pc/MRP will follow after it has finished printing the report. The remaining commands after the ~OUT~ command, simply close the related dbf file and select the original dbf file as the primary dbf file. Please note that REPREL.EF may contain sections of similar code for other custom reports.
[CUSTSO1.FRX]~IN~THISAREA=SELECT()SELECT 0USE PARTMAST INDEX BYPARTNOSELECT (THISAREA)SET RELATION TO LEFT(PARTNO,MPNSIZE) INTO PARTMASTGO TOP ~OUT~THISAREA=SELECT()SELECT PARTMASTUSESELECT (THISAREA)
- Last but not least, one would modify the custom report (in this case, CUSTSO1.FRX), adding the fields from the related dbf file to the report (in this case, PARTMAST.DRAWINGNO). Please note, that you must preface the fields from the related dbf file with the name of the related dbf file followed by a period and the name of the field.
pc/MRP versions 6.85B and higher
- Use Window's Notepad program to create an ASCII text file with the same name as the report you plan on running with the extension of ".ef". For example if you had a custom sales report named "CUSTSO1.FRX" and wanted to include data from another data base besides sales.dbf you would use Notepad to create a new file in the pcmrpw directory named "CUSTSO1.EF"
- Enter the following dbase/FoxPro commands into the "CUSTSO1.EF" file. The first command, ~IN~, tells pc/MRP to follow this set of commands before starting to print the report. The second command, THISAREA=SELECT(), stores the name of the first database file in a memory variable named THISAREA. The third through fifth command then set relation between the two files. The last command, GO TOP, makes sure the original dbf file (in this case, sales.dbf) is initialized and ready to begin printing. The seventh command, ~OUT~, begins the instruction set pc/MRP will follow after it has finished printing the report. The remaining commands after the ~OUT~ command, simply close the related dbf file and select the original dbf file as the primary dbf file.
~IN~THISAREA=SELECT()SELECT 0USE PARTMAST INDEX BYPARTNOSELECT (THISAREA)SET RELATION TO LEFT(PARTNO,MPNSIZE) INTO PARTMASTGO TOP ~OUT~THISAREA=SELECT()SELECT PARTMASTUSESELECT (THISAREA)
- Last but not least, one would modify the custom report (in this case, CUSTSO1.FRX), adding the fields from the related dbf file to the report (in this case, PARTMAST.DRAWINGNO). Please note, that you must preface the fields from the related dbf file with the name of the related dbf file followed by a period and the name of the field.
pc/MRP versions 7.06B, 7.43C and Higher
- With these versions pc/MRP can set the relation by calling a simple function named relate. For example.
= relate
("partmast") Sets a relation with the inventory database.
= relate ("addrbook") Sets a relation with the address book database.
= relate () Sets a relation with both the inventory and address book database.
- The relate() function can also take a second parameter containing the name of the field in the related database that matches the parents index. The relate function will use "PARTNO" for the Inventory database and "ACCOUNTNO" for the Address Book database if a parameter is not entered.
pc/MRP versions 7.05E and 7.50A and Higher
- These versions automatically open and set a relation into both the partmast.dbf (Inventory file) and the addrbook.dbf (Address Book file) for reports in the following modules: Sales Quote, Sale Order, Invoice, Purchase Quote, Purchase Order, and, Receiver.
- When viewing any report you can view the open tables by pressing CTRL-F10. This will open a TXT file with all of the open tables in the "select area" statement. By example: "Select area: 1, Table in Use: C:\MY DOCUMENTS\PCMRPTEST\PCMRPW750\PARTMAST.DBF Alias: PARTMAST" This means the Partmast.dbf is open when running this report.
15.4.6 Setting an additional filter when printing/displaying a report
The following modifications should only be made by a person who is familiar with dbase and FoxPro commands.
This section explains how to get a custom report to prompt for an additional filter condition. For example, a user may wish to have a custom bill of materials report that lists only part numbers that start with the prefix "33-". To make the modifications follow the steps listed below:
pc/MRP versions below 7.00C
- Versions below 7.00C require a file named apndfil.fxp be downloaded from Software Arts and placed in pc/MRP's directory, pcmrpw.
- Use Window's Notepad program to create an ASCII text file named "RPTREL.EF" for a report or "FRMREL.EF" for a form. For example if you had a custom BOM report named "CUSBOMVE.FRX" and wanted to include only part numbers that started with "33-", you would either open or create a file in the pcmrpw directory named "RPTREL.EF" with Window's Notepad program.
- Enter the following dbase/FoxPro commands into the "RPTREL.EF" file.
- The first line [CUSBOMVE.FRX], tells pc/MRP what report this set of commands are for.
- The next line, ~IN~, tells pc/MRP to follow this set of commands before starting to print the report. The second command, DO APNDFIL WITH "PARTNO","Enter part of the part number" tells pc/MRP to set up a filter on the field PARTNO and ask the user to "Enter part of the part number". The field name, such as PARTNO should be upper case and surrounded with quotation marks.
- Please note that, REPREL.EF may contain sections of similar code for other custom reports.
o [CUSBOMVE.FRX]
o ~IN~
§ DO APNDFIL WITH "PARTNO","Enter part of the part number"
pc/MRP versions 7.00C and higher
- Versions 7.00C and higher ship with a file named apndfil.fxp compiled into pcmrpw.exe and do not require it to be downloaded from Software Arts.
- Use Window's Notepad program to create an ASCII text file with the same name as the report you plan on running with the extension of ".ef". For example if you had a custom BOM report named "CUSBOMVE.FRX" and wanted to include only part numbers that started with "33-", you would either open or create a file in the pcmrpw directory named "CUSBOMVE.EF" with Window's Notepad program.
- The first line, ~IN~, tells pc/MRP to follow this set of commands before starting to print the report.
- The second line, DO APNDFIL WITH "PARTNO","Enter part of the part number", tells pc/MRP to set up a filter on the field PARTNO and ask the user to "Enter part of the part number". Please note that the field name, such as PARTNO should be upper case and surrounded with quotation marks.
o ~IN~
o DO APNDFIL WITH "PARTNO","Enter part of the part number"
With pc/MRP versions 7.54D and higher, users can use numeric and or logical fields not just text fields. When users run the report, users will be prompted for the default value (1) and the operator (>). If the filter is a text field:1. contains
2. does not contain
3. start with
4. is equal to
5. is not equal to
If the filter is a numeric or date field:1. not equal to
2. less than
3. less than or equal to
4. equal to
5. greater than or equal to
6. greater than
To set the defaults with version 7.54 users can add a default value and operator. For example, in the above example, users could enter DO APNDFIL WITH “DESCRIPT”, “Enter a Description”, “BEARING”, 1 When the report is printed pc/MRP will pop up a window asking the user to enter a description with a default entered of BEARING.
15.4.7 Email and HTML Files
Email and HTML forms can only be modified by personnel at Software Arts. Software Arts can send you a custom fxp file containing your modifications .
Modules Std Fxp File Custom fxp
Sales/Sales Quote/Work Order EMSO.FXP CEMSO.FXPPurchases EMPO.FXP CEMPO.FXPReceivers EMREC.FXP CEMREC.FXPPurchase request EMREQ.FXP CEMREQ.FXPInvoices EMINV.FXP CEMINV.FXP
The custom fxp files would be copied into the main pcmrpw directory.
15.4.8 Converting custom 16 bit report forms to 32 bit report forms
Installing an update to a 32 bit version of pc/MRP will copy in all new 32 bit standard report forms. Starting pc/MRP for the first time after installing the update will convert all non 32 bit reports, Custom and Standard, to 32 bit.
To individually
convert a 16 bit report to 32 bit, open the command line and enter
DO RPTCONGO.EXE WITH "FOLDER\FILE", "REPORT",
"FOX", .T.
Note: "REPORT" & "FOX" MUST BE UPPERCASE!!!