Transcript Locate File Names

QUERY
LANGUAGE
Locate File Names
Option A
•
Select A - for Amend in desired
process
SUPMNT Rev/x.x
Company
Screen : 1
Supplier File Maintenance
Please Select : CRE/AME/REV/DEL/END A
*1) Supplier Code [XXXX
•
Enter a fictitious code
•
Press <enter>
•
File name associated with this file
will be displayed at the bottom of
the screen
Time Date
x User Name
3) Supplier Name
5) Address line 1
6) Address line 2
7) Address line 3
8) Post Code
10) Our Debtor ID
]
2) Date Opened
Last Changed
4) Alpha Key
9) Pay to Supplier
11) Telephone No
12) Contact
13) Title
16) A.B.N
17) Postal line 1
18) Postal line 2
19) Postal line 3
record not on file SUPPLIER
14) Fax No
15) Mobile No
20) Post Code
Locate File Names
Option B
•
ARDOCTXT Rev/x.x Company
Time
Date
Screen : 1
Maintain Document Text x User Name
Select A to Amend - in desired
process
Please Select : CRE/AME/REV/DEL/END A
Text Code [
•
Press Enter
Text
P1
•
In the first field of the process
Press F1 HELP function key
]
Transfer
Locate File Names
Option B (continued)
•
•
You will then view the Help text
associated with that field.
This will display the file that stores
the information from this process.
ARDOCTXT Rev/x.x Company
Time Date
HELP Processor Maintain Document Text x User Name
Text Code
This is an alphanumeric field of up to 5 characters
A minimum entry of 1 character is required
The FND function key can be used with this field
Lowercase entries are converted to uppercase
The BCK key can be used from this field, but FWD is not
allowed. The entry must already exist on the DOC.TEXT
file which is maintained by the ARDOCTXT process
This text will act as a default description for this billing
code when it is referenced in "ARDOCENT", and of
course acts as a description for thebilling code itself.
Types of Files
• Master Files
• Transaction
Files
• Index Files
• Balance Files
File Structure
Data Portion
FILE
Dictionary Portion
• What is a dictionary?
File and Dictionary Terms
• ID/Key
• Attributes
• Values
Sorting Information
• Reports
• Lists
Verbs - LIST and SORT
Verbs - SELECT, QSELECT
SSELECT
QLMNT
QLMNT Rev/x.x
Screen : 1
1.
A code that you give to this
procedure
2.
A descriptive name that help
describe this procedure.
3.
A valid Pick Verb as maintained
by PICKVERB.
Company
Maintain Stored Procedures
Time Date
x User Name
Please Select : CRE/AME/REV/DEL/END [ ]
*1) Procedure Name
2) Description
4.
Only asked if verb is SELECT
or SSELECT
5.
The file name you wish to extract
information from.
P1…etc.. Will contain a list of query
commands.
3) Verb Name
4) List Name
5) File Name
----------------------Query Language Commands-------------------------P1
P2
P3
XQL
1.
The procedure code as entered in
QLMNT
XQL Rev/x.x
Company
1) Procedure Name [
2.
Only asked for select lists in ‘change’
mode
Purpose
Verb Name
3.
Enter a list name if you want to use
an existing select list, as a pre-list to
this procedure.
File Name
2) Save List Name
3) Pre-Start List
4.
‘N’ or a number between 1 and 9
4) Via Batch
5) Screen/Printer
5.
‘S’ to send report to Screen
‘P’ to send report to printer
Time
Date
Screen : 1 Execute a User Defined Procedure x User Name
]
Selection Criteria
• Selection criteria is
necessary in order to limit
the items that are being
processed.
• If no selection criteria is
entered all items in the file
will be processed
• WITH is used in
conjunction with a
dictionary item, a
relational operator and a
value.
• Multiple selections can be
joined together with AND
and OR
Relational Operators
Symbol
= or EQ
# or NE,NO, NOT
> or GT,AFTER
< or LT,BEFORE
>= or GE
<= or LE
Meaning
Equal to
Not Equal to
Greater than
Less than
Greater than or equal to
Less than or equal to
Wildcard characters
[
Left Square bracket
]
Right Square bracket
^
Carat
QUERY
LANGUAGE
How to turn your QL procedure
into a Menu item.
How to turn a QL procedure into a Menu item
1. QLMNT
Screen : 1
Maintain Stored Procedures
X User Name
Please Select : CRE/AME/REV/DEL/END A
*1) Procedure Name CUSDISC
2) Description
3) Verb Name
• Create your procedure in
QLMNT
Listing of customers and discount amounts
SORT
Generate a sorted report
4) List Name
5) File Name
CUSTOMERS
Customer Master file
----------------- Query Language Commands------------------------------P1 BY CUSTOMER.CODE
P2 WITH CUSTOMER.CODE >= "AA]”
P3 AND WITH CUSTOMER.CODE <=“Z]”
P3 AND WITH DISCOUNT.TERMS = “COD”
P4 CUSTOMER.CODE
P5 TELEPHONE
P6 CONTACT
P7 DISCOUNT.TERMS
How to turn a QL procedure into a Menu item
2. XQL
Cust.... Telephone........... Contact......................... Discount Terms
Code
• Run the report in XQL
AA001 58 5765
AA002 54 7848
AA003 69 2768
AA006 63 1312
AA201 69 2965
AA202 65 2222
AAMI01 42-2660
AATK01 9666-3899
[4051] 8 items listed.
STEPHEN COOMBES
Bill Brown
Joe Smith
RON & PETER
Smith’s Hardware
Jack Brown
Steve Connor
Gary King
COD
COD
COD
COD
COD
COD
COD
COD
How to turn a QL procedure into a Menu item
3. DEFQLPRC
•
The process code will be the name
assigned to your report when it has
been copied to the menu. The name
will commence with a Z…..
DEFQLPRC Rev/x.x
Testing Company
Screen : 1
Define QL user interface process
Time Date
x User Name
Please Select : CRE/AME/REV/DEL/END [C]
*1) Process Code
•
•
•
The QL procedure is the name of
the report you developed in
QLMNT. (Step 1)
Is the description that will be
associated with your process when it
is copied to the menu.
If your report uses a pre-select list,
enter the name here. If not leave this
field blank.
2) QL Procedure
3) Process Name
4) Pre-Select Procedure
Constant
Tag
---------------------------------------------------------------------------------
EXAMPLE
How to turn a QL procedure into a Menu item
DEFQLPRC - Screen 1
DEFQLPRC Rev/x.x
Screen : 1
1. When turned into a menu item our
report code will be ZCUSDISC
2. Is the existing QL report we
created in QLMNT
3. Is the description of the report
name to display on the Menu.
Company
Define QL user interface process
Time
x
Date
User Name
Please Select : CRE/AME/REV/DEL/END C
*1) Process Code
CUSDISC
2) QL Procedure
CUSDISC Listing of customers
3) Process Name
Customers/Discounts Report
4) Pre-Select Procedure
Constant
Tag
---------------------------------------------------------------------------------P1 AA]
[FIRST CUSTOMER
]
Is the prompt/tag that will appear on the
Menu after the report has been created.
How to turn a QL procedure into a Menu item
DEFQLPRC - Screen 2
1. What is the minimum length of
characters that you want the user to
enter? If you want the user to always
enter something set this field to 1 at least.
2. What is the maximum length of
characters that you want the user to
enter? The maximum length of
Customers Codes is 8. If you only have
customer codes of 4 characters, enter 4
here.
3. Is this field type letters or letters and
numbers or only numbers. Enter A if
your customer codes are alpha characters
only, Enter N if your customer codes are
alpha and numbers.
DEFQLPRC Rev/x.x
Company
Screen : 2 Define QL user interface process
Field Definition for
1) Minimum Length
First Customer
[]
2) Maximum Length
3) Field Type
4) Validation
Min
Filename
Optional
5) Help Code
6) Find Code
7) Default
8) Include in Heading
Max
Time
Date
x User Name
EXAMPLE
How to turn a QL procedure into a Menu item
DEFQLPRC - Screen 2
4. Do you want the input to be checked
against a valid file to make sure the
customer code does exist? If your answer is
Yes. Enter F in this field. If your answer is
No leave this field blank.
Enter a valid file name to validate the input
against.
DEFQLPRC Rev/x.x
Company
Screen : 2 Define QL user interface process
Field Definition for
Time
Date
x User Name
First Customer
1) Minimum Length
1
2) Maximum Length
8
3) Field Type
N
Is input optional? Yes or No
5. Can be used to specify your own help
text for this field. Leave blank if not
required.
6. Is the name of the file that will be used if
the user hits the Find Function key.
7. You can specify a default that will always
display in this field. If not, leave this field
blank.
8. Allows automatic insertion of the
Customer Code (in this example) in the
heading. Yes or No response is required.
4) Validation
F
Min
Max
Filename CUSTOMERS
Customers Master File
Optional N
5) Help Code
6) Find Code FIND.CUSTOMERS
7) Default
8) Include in Heading
N
EXAMPLE
How to turn a QL procedure into a Menu item
DEFQLPRC - Screen 1
DEFQLPRC Rev/x.x
Screen : 1
•
You will then be returned to Screen 1 of
DEFQLPRC if you have other lines in
your QLMNT procedure that asks for
input Eg.Last Customer, Discount
Terms
Define QL user interface process
Follow the same steps from above to
define this extra input data validation.
Time
x
Date
User Name
Please Select : CRE/AME/REV/DEL/END C
*1) Process Code
CUSDISC
2) QL Procedure
CUSDISC Listing of customers
3) Process Name
Customers/Discounts Report
4) Pre-Select Procedure
Constant
•
Company
Tag
---------------------------------------------------------------------------------P1 AA]
FIRST CUSTOMER
P2 Z]
LAST CUSTOMER
P2 COD
DISCOUNT TERMS
EXAMPLE
How to turn a QL procedure into a Menu item
DEFQLPRC - Screen 2
•
•
•
DEFQLPRC Rev/x.x
Company
Screen : 2 Define QL user interface process
Set up valid lengths for the Field
Discount Terms now - data input, Field
types, validation details if required as
you did for Customer Code.
For each line in your QLMNT
procedure where you have a selection
line eg customer.code = “AA]”,
discount.terms = “COD]” etc..etc. you
will be prompted to set up the details.
When you have set up all your selection
criteria and pressed <enter> the
procedure will be generated and
inserted into the appropriate user menu.
Field Definition for
Time
Date
x User Name
Discount Terms
1) Minimum Length
1
2) Maximum Length
4
3) Field Type
N
4) Validation
F
Min
Max
Filename TERMS.CODE
Maintain Payment Terms
Optional N
5) Help Code
6) Find Code FIND.TERMS
7) Default
8) Include in Heading
QUERY
LANGUAGE
Using your DEFQLPRC
procedure from the Menu.
Using your DEFQLPRC procedure from the
Menu.
ZCUSDISC Rev/x.x Company
Screen : 1 Customers/Discounts Report
•
Access your report by typing
ZCUSDISC.
First Customer
Last Customer
•
You may need to change security
profiles that are in place before you are
able to access the generated process.
Discount Terms
Via Batch
Screen/Printer
[
]
Time Date
x User Name