Transcript VA FILEMAN REPORTS ADVANCED 210 H, 2007 VHA eHealth

WELCOME TO Advanced VA
FILEMAN 210 H
Tom Fagan Clinical Applications
Coordinator at VAMC
Richmond, Virginia
(804) 675-5298
1
LEARNING OBJECTIVES AND
CLASS FORMAT
1.
2.
3.
4.
5.
6.
Review and Understand Basic Functions
Review use and compare Templates
Use of Sort and Print Qualifiers and Functions
Understand how to use File Jumping (pointing)
Understand the File Multiples
Understand concepts of Export data using Text
File
7. Know where to find additional References:
FILEMAN Website and REFERENECE SLIDES
Qualifiers, Functions and Operators(48 &49)
8. Understand Importance of Data Validation and
Privacy/Security
2
TERMINOLOGY Most
Important
• SYNTAX exact sequence of characters
and words for a function to work, may
be case sensitive
• Primary Source of Frustration
3
TERMINOLOGY Database
Related
• DATABASE collection of files used to
store data
• FILE collection of records
• RECORDS are unique entries in File
• FIELDS data type place holders to store
information in records
• IEN NUMBER is the unique number
assigned to record by software,
contained in the .001 field
4
TERMINOLOGY- ADVANCED
• MULTIPLE is a sub file or a file within a
file can contain zero or multiple entries
• JUMPING(POINTING) access a field in a
related file.
• QUALIFIERS and FUNCTIONS are
MUMPS expressions used to enhance
sort or output
5
BASIC REPORT FUNCTIONS
REVIEW
• Inquire to File Entries
• Print File Entries
• Search File Entries
??? For help
6
LIST FILE ATTRIBUTES
• Data Dictionary Utility “Display
Structure”
• File Number or Label
• Field Number or Label
• Data Type
• Index and Pointers
• Suggest Brief format.
7
TEMPLATE COMPARISON
• Search = saved Search Results or
Search Logic
• Print Sort = saved Sort Logic
TIP: Sort by:[ “TEXT” = Contains TEXT
• Print Output = saved Print or
Display Format
8
INQUIRE
SEARCH PRINT PRINT
SORT OUTPUT
Type of
Templates
To
Inquire
Create or
Edit
Template
Use
Function
Search
(NO Edit)
Print
ALL
Functions
from
FIRST
PRINT
FIELD
To
Print
Access
Template
Use
Function
Print for
results
OR
Search –
run again
Print
ALL
Functions
from
FIRST
PRINT
FIELD 9
INTERMEDIATE ADVANCED
CONCEPT JUMP FILES
• Jump or Point with :
• Access a field in related file (example
patient demographics from prescription
file)
• Syntax: Prescription (first print field):
PATIENT:AGE
OR
FIRST PRINT FIELD: PATIENT:
THEN PRINT PATIENT FIELD: AGE
10
JUMP FILES PRINT OUTPUT
EXAMPLE
OUTPUT FROM WHAT FILE: PRESCRIPTION//
FIRST PRINT FIELD: [H210.DEMOGRAPH
FIRST PRINT FIELD: PATIENT://
THEN PRINT PATIENT FIELD:
$P(NAME,",",1)_"@"_$P(NAME,",",2)
THEN PRINT PATIENT FIELD: "@"
THEN PRINT PATIENT FIELD: STREET
ADDRESS [LINE 1];L25;X
THEN PRINT PATIENT FIELD: "@"
11
JUMP FILES PRINT OUTPUT
EXAMPLE (continued)
THEN PRINT PATIENT FIELD: STREET
ADDRESS [LINE 2];L10;X
THEN PRINT PATIENT FIELD: "@"
THEN PRINT PATIENT FIELD: CITY;L15;X
THEN PRINT PATIENT FIELD: "@"
THEN PRINT PATIENT
FIELD:STATE:ABBREV;X
THEN PRINT PATIENT FIELD: "@"
THEN PRINT PATIENT FIELD: ZIP+4;L9;X
THEN PRINT PATIENT FIELD: "@"
12
JUMP FILES PRINT OUTPUT
EXAMPLE OUTPUT
1.
2.
3.
4.
5.
6.
THREE@PATIENT@1312 ASHTON PLACE@@ROWLING@WV@99998-007@
1. $P(NAME,",",1)_"@"_$P(NAME,",",2)
2. STREET ADDRESS [LINE 1];L25;X
3. STREET ADDRESS [LINE 2];L10;X
4. CITYL15;X
5. STATE:ABBREV;X
6. ZIP+4;L9;X
13
INTERMEDIATE ADVANCED CONCEPT
MULTIPLES FIELDS
• Multiples Fields: Sub file within a file
• Designation: (multiple) and “indent”
• Can be blank or contain one or more entries
• Subfield has all characteristics of a file,
folder within a folder
14
MULTIPLE Example
(Standard)
Inquire to File Entries
OUTPUT FROM WHAT FILE: PATIENT//
Select PATIENT
NAME:SEVENTYTWO,PATIENT
STANDARD CAPTIONED OUTPUT? Yes// n
(No)
FIRST PRINT FIELD: APPOINTMENT
(multiple)
FIRST PRINT APPOINTMENT SUB-FIELD: .001
APPOINTMENT DATE/TIME
THEN PRINT APPOINTMENT SUB-FIELD: .01
CLINIC
15
THEN PRINT FIELD:
MULTIPLE Standard Output
FIRST PRINT FIELD: APPOINTMENT
(multiple)
DATE/TIME
CLINIC
------------------------------------------------------------MAY 21,2000 10:00 GENERAL MEDICINE
MAY 22,2000 10:00 GENERAL MEDICINE
MAY 23,2000 10:00 GENERAL MEDICINE
MAY 24,2000 10:00 GENERAL MEDICINE
16
MULTIPLE Example (INSIDE)
TEMPLATE H210.INSIDE
FIRST PRINT FIELD: APPOINTMENT// (multiple)
FIRST PRINT APPOINTMENT SUB-FIELD:
NAME;L15//
By 'name', do you mean PATIENT 'NAME'? Yes//
THEN PRINT APPOINTMENT SUB-FIELD:
APPOINTMENT DATE/TIME;C20
THEN PRINT APPOINTMENT SUB-FIELD:
CLINIC;C45//
THEN PRINT APPOINTMENT SUB-FIELD:
THEN PRINT FIELD:
17
MUTIPLE Example (INSIDE)
Output
THEN PRINT APPOINTMENT SUB-FIELD: APPOINTMENT DATE/TIME;C20
NAME
DATE/TIME
CLINIC
-----------------------------------------------------------------------------
SEVENTYTWO,PATI
SEVENTYTWO,PATI
MAY 21,2000 10:00
MAY 22,2000 10:00
GENERAL MEDICINE
GENERAL MEDICINE
OUTSIDE OUTPUT:
DATE/TIME
CLINIC
------------------------------------------------------------MAY 21,2000 10:00 GENERAL MEDICINE
18
SORT and PRINT Qualifiers
• Suffix and Prefix Syntax –USE CAPS
• Example ;Ln limit to n characters
• Harmony SORT-PRINT +field then !field
subtotal count
• Reference Slides
19
EXAMPLES of FUNTION SORT
Sort by COUNT of patient APPOINTMENTS Multiple
OUTPUT FROM WHAT FILE: PATIENT//
SORT BY: NAME// +APPOINTMENT
APPOINTMENT SUB-FIELD: +CLINIC
START WITH CLINIC: FIRST//
WITHIN CLINIC, SORT BY:
FIRST PRINT FIELD: !NAME
Sample output
CLINIC: 19 LINDA'S FUNNY CLINIC
SUBCOUNT 14
CLINIC: 20 MINUTE
SUBCOUNT 2
CLINIC: 45 PATTERN
20
SORT and PRINT Functions
• Function File
• Local entries may be file dependent
• Example NUMDATE(DATE field) = date
MM/DD/YY format
• Standard Syntax
FUNCTION (Field Name)
21
ADVANCED CONCEPT
DATA EXPORT
DATA VS. INFORMATION
• Information has meaning
• EXCEL formatting and analyzing data
• ACCESS capacity or advanced analysis
• RELATED Session 216H Extracting and
Exporting Pharmacy Data (Absolute
Power) - Extended Session on
Thursday 8/23/07 at 9:10am
22
STEPS TO MS APPLICATION
1. Select Output Source File and Find
Records (SEARCH or PRINT SORT)
2. Select Output Details in Fields, use
Template (PRINT OUTPUT) to Format
3. Save Output to ASCII Text File “PACK”
4. Import to Chosen MS-Application with
Import Wizard “UNPACK”
5. Parse and Label Data and Save as MSFile
23
FORMATTING-SMUSH
TAKES OUT ALL THE SPACES
Syntax= FIELD:NAME;X
24
FORMATTING-DELIMITER
• Signals New Column
• Carriage Return Signals New Row
Syntax
THEN PRINT FIELD: “any character or text string”
25
SMUSHING AND DELIMITING
example
OUTPUT FROM WHAT FILE: PATIENT//
THEN PRINT FIELD: $E(SSN,6,9);X
THEN PRINT FIELD: "@"
THEN PRINT FIELD: LOWERCASE($P(NAME,",",1));X
THEN PRINT FIELD: "@"
THEN PRINT FIELD: LOWERCASE($P(NAME,",",2));X
THEN PRINT FIELD: "@"
FIRST PRINT FIELD: LOWERCASE(STREET ADDRESS [LINE 1]);X
THEN PRINT FIELD: "@"
THEN PRINT FIELD: LOWERCASE(STREET ADDRESS [LINE 2]);X
THEN PRINT FIELD: "@"
THEN PRINT FIELD: LOWERCASE(CITY);X//
THEN PRINT FIELD: "@"
THEN PRINT FIELD: LOWERCASE(STATE);X//
THEN PRINT FIELD: "@"
THEN PRINT FIELD: ZIP+4;X//
THEN PRINT FIELD: "@"
26
SMUSHING AND DELIMITING
example Sample Output
1.
2.
3.
0003@Three@Patient@1312 Ashton Place@@Rowling@West Virginia@99998-0071
1. $E(SSN,6,9);X
2. LOWERCASE($P(NAME,",",1));X
3. LOWERCASE(STREET ADDRESS [LINE 1]);X
27
CAPTURE TEXT FILE “PACKING”
1. At VistA DEVICE Prompt, 0;132;99999
SYNTAX
0= display on screen; 132= column width; 99999= page
length before page break
2. WAIT!
3. Turn On Capture
KEA- Tools/Capture Incoming Data
4. Enter File Name, Location,
And Click Save
5. Press ENTER
6. When Scrolling Stops, End Capture
KEA-Tools/End Capture
28
IMPORT TEXT FILE “UNPACKING”
1. Open MS Application Excel
2. Click On File/Open
3. Choose TEXT File You Captured
REMEMBER THE LOCATION
4. Use The Import Wizard
5. Parse and Label Data-Save as Excel
File
29
SESSION MENU
VA FileMan Version 22.0
Enter or Edit File Entries
Print File Entries
Search File Entries
Modify File Attributes
Inquire to File Entries
Utility Functions ... Template Edit
Data Dictionary Utilities ...
List File Attributes
Transfer Entries
Other Options ...
30
LOG IN FOR HANDS ON
ACCESS: 00VEHU
VERIFY:VEHU00
31
#1 HANDS ON Jumping (Pointing)
PRINT SORT OUTPUT
PRINT File Entries
OUTPUT FROM WHAT FILE: PRESCRIPTION OR 52
SORT BY: RX #// DRUG["DIAZEPAM"
WITHIN DRUG["DIAZEPAM", SORT BY:
FIRST PRINT FIELD: PATIENT:
THEN PRINT PATIENT FIELD: AGE
1 AGE
…..
5 AGENT ORANGE EXPOSURE LOCATION
CHOOSE 1-5: 1 AGE
THEN PRINT PATIENT FIELD:
THEN PRINT FIELD: RX #
THEN PRINT FIELD: DRUG
1 DRUG
……..
4 DRUG EXPIRATION DATE
CHOOSE 1-4: 1 DRUG
THEN PRINT FIELD:
Heading (S/C): PRESCRIPTION LIST//
STORE PRINT LOGIC IN TEMPLATE:
START AT PAGE: 1//
DEVICE: TELNET PORT Right Margin: 80//
^
32
#2 HANDS ON search order file
INPATIENT PACKAGE
SEARCH File Entries
OUTPUT FROM WHAT FILE: ORDER//
-A- SEARCH FOR ORDER FIELD:
-A- CONDITION: CONTAINS
-A- CONTAINS: MEDICATIONS
PACKAGE
-B- SEARCH FOR ORDER FIELD: WHEN ENTERED
-B- CONDITION: GREATER THAN
-B- GREATER THAN DATE: 6/8/2007@1600 (JUN 08, 2007@16:00)
-C- SEARCH FOR ORDER FIELD:
IF: AB
PACKAGE CONTAINS (case-insensitive) "MEDICATIONS"
and WHEN ENTERED GREATER THAN JUN 8,2007@16:00 (6/8/2007@1600)
OR:
STORE RESULTS OF SEARCH IN TEMPLATE: 00.IPMEDS6/8/2007
Are you adding '00.IPMEDS6/8/2007' as a new SORT TEMPLATE? No// Y (Yes)
DESCRIPTION:
No existing text
Edit? NO//
SORT BY: NUMBER//
START WITH NUMBER: FIRST//
FIRST PRINT FIELD:
Heading (S/C): ORDER SEARCH//
DEVICE: TELNET PORT Right Margin: 80//
ORDER SEARCH
JUL 25,2007 14:10
--------------------------------------------------------------------------------
PAGE 1
182 MATCHES FOUND..
33
#3 HANDS ON search results in
order file orderable item contains codeine
PRINT File Entries
OUTPUT FROM WHAT FILE: ORDER//
SORT BY: NUMBER// [00.IPMEDS 6/8/2007
WITHIN ‘00.IPMEDS6/8/2007' NUMBER, SORT BY:
ORDERABLE
ORDERABLE ITEMS SUB-FIELD: ORDER["CODEINE“
By 'ORDER', do you mean ORDERABLE ITEMS 'ORDERABLE
ITEM'? Yes
STORE IN 'SORT' TEMPLATE:
FIRST PRINT FIELD: [H210.ORDERABLE
34
#4 HANDS ON PRINT OUT
TEMPLATE EDIT
PRINT File Entries
OUTPUT FROM WHAT FILE: ORDER
SORT BY: NUMBER// [ 00.IPMEDS 6/8/2007
WITHIN 'TF.IPMEDS' NUMBER, SORT BY:
FIRST PRINT FIELD: [ H210.ORDER
WANT TO EDIT 'H210.ORDERABLE' TEMPLATE? No// Y
NAME: H210.ORDERABLE//
READ ACCESS:
WRITE ACCESS:
FIRST PRINT FIELD: ORDER #//
THEN PRINT FIELD: ORDERABLE ITEMS// (multiple)
THEN PRINT ORDERABLE ITEMS SUB-FIELD: ORDERABLE ITEM//
THEN PRINT ORDERABLE ITEMS SUB-FIELD: WHEN;d;L18//
By 'WHEN', do you mean ORDER 'WHEN ENTERED'? Y
THEN PRINT ORDERABLE ITEMS SUB-FIELD: WHO//
By 'WHO', do you mean ORDER 'WHO ENTERED'? Y
THEN PRINT ORDERABLE ITEMS SUB-FIELD: OBJEC//
By 'OBJEC', do you mean ORDER 'OBJECT OF ORDER'? Y
THEN PRINT ORDERABLE ITEMS SUB-FIELD: // Y
THEN PRINT ORDERABLE ITEMS SUB-FIELD:
35
# 5 HANDS ON multiple PRINT
OUTPUT TEMPLATE
PRINT File Entries
OUTPUT FROM WHAT FILE: 52 OR PRESCRIPTION (2644 entries)
SORT BY: RX #//
START WITH RX #: FIRST//
FIRST PRINT FIELD: [H210.REFILLINSIDE
WANT TO EDIT 'H210.REFILLINSIDE' TEMPLATE? No// (No)
Heading (S/C): PRESCRIPTION LIST//
DEVICE: TELNET PORT Right Margin: 80//
ENTER UNTIL PAGE 5
RX #: 300077
RX #: 300078
RX #: 300079
RX #: 300082
RX #: 300173
RX #: 300211
RX #: 300212
RX #: 300226
RX #: 300227
RX #: 300228
RX #: 300248
RX #: 300249
300249@05/31/95@666427065@05/31/[email protected]@
300249@05/31/95@666427065@05/31/[email protected]@
300249@05/31/95@666427065@05/31/[email protected]@
300249@05/31/95@666427065@05/31/[email protected]@
RX #: 300250
^
36
# 6 HANDS ON multiple PRINT
OUTPUT TEMPLATE
Print File Entries……..OUTPUT FROM WHAT FILE: PRESCRIPTION/
SORT BY: RX #//
START WITH RX #: FIRST//
FIRST PRINT FIELD: [ H210.REFILLINSIDE
WANT TO EDIT 'H210.REFILLINSIDE' TEMPLATE? No// Y (Yes)
NAME: H210.REFILLINSIDE//
FIRST PRINT FIELD: REFILL// (multiple)
FIRST PRINT REFILL SUB-FIELD: rx;X//
By 'rx', do you mean PRESCRIPTION 'RX #'? Yes// (Yes)
THEN PRINT REFILL SUB-FIELD: "@";X//
THEN PRINT REFILL SUB-FIELD: NUMDATE(issue);X//
By 'issue', do you mean PRESCRIPTION 'ISSUE DATE'? Yes// (Yes)
THEN PRINT REFILL SUB-FIELD: "@";X//
THEN PRINT REFILL SUB-FIELD: patient:ssn;X//
By 'patient', do you mean PRESCRIPTION 'PATIENT'? Yes// (Yes)
By 'ssn', do you mean PATIENT 'SOCIAL SECURITY NUMBER'? Yes// (Yes)
THEN PRINT REFILL SUB-FIELD: "@";X//
THEN PRINT REFILL SUB-FIELD: NUMDATE(REFILL DATE);X
THEN PRINT REFILL SUB-FIELD: "@";X//
THEN PRINT REFILL SUB-FIELD: (curren*qty);X//
By 'curren', do you mean REFILL 'CURRENT UNIT PRICE OF DRUG'? Yes// (Yes)
By 'qty', do you mean REFILL 'QTY'? Yes// (Yes)
THEN PRINT REFILL SUB-FIELD: "@";X//
Heading (S/C): PRESCRIPTION LIST//
STORE PRINT LOGIC IN TEMPLATE: 00.REFILL INSIDE
Are you adding '00.REFILL INSIDE' as a new PRINT TEMPLATE? No// Y
37
#7 HANDS ON TEMPLATE EDIT
with PRINT OUTPUT
Utility Functions Option: Template Edit
MODIFY WHAT FILE: PRESCRIPTION//
Select TEMPLATE File: print TEMPLATE
Select PRINT TEMPLATE:
00.REFILLINSIDE
Y for screen mode
FOLLOWING INSTRUCTIONS IN SCREEN
MODE TO DELET ROW AND SAVE
38
# 8 HANDS ON Search with
MATCH number 1 place
SEARCH File Entries
OUTPUT FROM WHAT FILE: DRUG// 50 OR DRUG (3587
entries)
-A- SEARCH FOR DRUG FIELD: DEA, SPECIAL HDLG
-A- CONDITION: MATCHES
-A- MATCHES: 1.N
-B- SEARCH FOR DRUG FIELD:
IF: A// DEA, SPECIAL HDLG MATCHES 1.N
STORE RESULTS OF SEARCH IN TEMPLATE:
SORT BY: GENERIC NAME//
START WITH GENERIC NAME: FIRST//
FIRST PRINT FIELD: .01
THEN PRINT FIELD: DEA, SPECIAL HDLG
THEN PRINT FIELD:
39
HANDS ON DEMO EXCEL import
Jumping (Pointing) using
Prescriptions Patient Demographics
OUTPUT FROM WHAT FILE: PRESCRIPTION
SORT BY: RX #// [H210METOPROLOL TARTRATE
WANT TO EDIT '210METOPROLOL TARTRATE'
TEMPLATE? NO
FIRST PRINT FIELD: [H210.RECALLIMPORT
WANT TO EDIT “H210.RECALLIMPORT”? N
Heading (S/C): PRESCRIPTION LIST//
START AT PAGE: 1//
DEVICE: TELNET PORT Right Margin
40
HANDS ON Optional Print and
Sort Functions
• DRUG FILE
FIRST PRINT FIELD: [H210.NARC
$S(DEA["2":"NARC",1:"NOT NARC")
• Function file
41
STEPS TO IMPORT MSACCESS (Optional)
1.
2.
3.
4.
5.
6.
Open MS-Access
Start New Database (File/New)
Select Location and Name File
File/Get External Data
Import (Select Location of text file)
Use Import Wizard (Must format and
name fields)
42
E-TRIP NATIONAL
RESORUCES
• Fileman Homepage:
http://www.va.gov/vdl/application.asp?appid=5
43
PRIVACY REVIEW
For Protected Health Information (PHI)
Distribution:
Need to Know- VISTA Email or Outlook
with PKI
Secure – Encryption or Server
Role of Local VISN Information Security
Officer
National Website:
https://vaww.ocis.va.gov/portal/server.pt
44
DATA VALIDATION
•
•
•
•
•
•
Does Your Output Make Sense?
Random Sample
Time Or Single Events (Snowbirds)
Intuition
Consult An ADPAC Or Expert
Other DATA SOURCES, CPRS
QUERY TOOLS, VISTA REPORTS,
Clinical Reminders
45
1-800-HELP ME
Phone: Tom Fagan (804)675-5298
EMAIL: [email protected]
46
QUESTIONS
?
47
Reference 1- Select Print, Sort
Qualifiers and Operators
48
Reference 2 – Select
Fileman Functions
49