agvantage software’s most commonly used queries lab - part 2
DESCRIPTION
AgVantage Software’s Most Commonly Used Queries Lab - Part 2. Presented by: Mike Smark AgVantage National Conference 2010. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/1.jpg)
AgVantage AgVantage Software’s Software’s Most Commonly Most Commonly Used Queries Used Queries
Lab - Part 2Lab - Part 2
Presented by: Mike Smark AgVantage National Presented by: Mike Smark AgVantage National Conference 2010Conference 2010
![Page 2: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/2.jpg)
AGENDAAGENDA
More “common” queries More “common” queries discusseddiscussed
How to read and use File How to read and use File Structure DocumentsStructure Documents
Share Queries – discuss central Share Queries – discuss central databasedatabase
How to modify to your specific How to modify to your specific needsneeds
![Page 3: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/3.jpg)
TerminologyLibraries, Files, Records and Fields
• I-Series – Stores information much like a file cabinet
• Libraries – AgVantage stores your information in a company library named CO*DATA where * is your company letter, number or a symbol.
COUDATA
COBDATA
COSDATA
iSeries
![Page 4: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/4.jpg)
Example Library Co*data – Data library which
contains all AgVantage files
Files U4CSTMR – Customer Master Records Each individual customer’s
information
Fields CSLKNM – Customer Lookup Name
![Page 5: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/5.jpg)
Query Libraries
• Recommendations:– Create your own library for queries.
• CRTLIB command
– Use QRY at the end of the name.• Ex. “COOP”QRY or SMARKQRY (own server)• Most already have “base” Query Library• ASP customers (Use company name/abbrev, then your
name in description)
– Common AgVantage Query Libraries• GRNQRYV65 (Version 7 not there yet)• SYSQRYV6 (Version 7 not there yet)
![Page 6: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/6.jpg)
File Structures
USYSTM AgVantage System Menu Select one of the following: 1. General Ledger GLS or U1 2. Accounts Payable U2 3. Payroll U3 4. Accounts Receivable U4 5. Grain Accounting U5 6. Agronomy U6 7. Energy Management U7 8. Inventory U8 9. Patronage U9 10. Mailing Manager UA 11. Counter Invoicing 12. Investments UI 13. Interfaces 14. Feed Selection or command F3=Exit F4=Prompt F9=Retrieve F12=Cancel
![Page 7: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/7.jpg)
Accounts Receivable
U4CSTMR Customer Master
U4ITMMR Item Master
U4ITMBL Item Master Balance
U4DEPT Department
U4FLC Fine Line Code
U4TRNH A/R Header Information
U4TRND A/R Detail Information
U4CAGE A/R Aging Information
![Page 8: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/8.jpg)
GrainU5CROPS Crop InformationU5PCTRL Purchase Control/Contracts U5PLOAD Purchase Load Header FileU5PLOADJN Purchase Load Header File
Joined with ArchiveU5PSBLD Sub-Load InformationU5SCTRL Sales Control/ContractsU5SETTLH Purchase Settlements HeaderU5SETTLD Purchase Settlements DetailU5SETTLDJN Purchase Settlements Detail
Joined with ArchiveU5SARLD Sales Settlements
SPECIAL NOTE: Library GRTRANx contains crop position and general ledger daily posting information
![Page 9: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/9.jpg)
![Page 10: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/10.jpg)
![Page 11: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/11.jpg)
![Page 12: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/12.jpg)
![Page 13: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/13.jpg)
![Page 14: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/14.jpg)
![Page 15: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/15.jpg)
RUNQRY (F4)Used to display the data in the
fileUsed to run a specific query
Type choices, press Enter. Query . . . . . . . . . . . . . Name, *NONE Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB Query file: File . . . . . . . . . . . . . Name, *SAME Library . . . . . . . . . . *LIBL Name, *RUNOPT, *LIBL, *CURLIB Member . . . . . . . . . . . . *FIRST Name, *RUNOPT, *FIRST, *LAST + for more values Report output type . . . . . . . *RUNOPT *RUNOPT, *DISPLAY... Output form . . . . . . . . . . *RUNOPT *RUNOPT, *DETAIL, *SUMMARY Record selection . . . . . . . . *NO *NO, *YES
![Page 16: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/16.jpg)
WRKQRYUsed to create, change, delete, display &
run queries
Work with Queries Type choices, press Enter. Option . . . . . . 2 1=Create, 2=Change, 3=Copy,4=Delete 5=Display, 6=Print definition 8=Run in batch, 9=Run Query . . . . . . . Name, F4 for list Library . . . . . SMARKQRY Name, *LIBL, F4 for list
Let’s review more Queries !
![Page 17: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/17.jpg)
QUERIES TO SHARE ?
VIA AgVantage MESSAGE BOARD (Track updates, User interaction)
VIA AgVantage WIKI BOARD (Similar to Message Board)
MAINTAIN ON AGVANTAGE SERVER (Own Library CUSTV65QRY, Need
Administrator)
![Page 18: AgVantage Software’s Most Commonly Used Queries Lab - Part 2](https://reader036.vdocuments.us/reader036/viewer/2022081506/56814531550346895db1f82b/html5/thumbnails/18.jpg)
THANKS FOR ATTENDING !
PLEASE FILL OUT YOUR EVALUATION FORM !