WELCOME TO Advanced VA FILEMAN 210 H

1
WELCOME TO Advanced VA FILEMAN 210 H

• Tom Fagan Clinical Applications Coordinator at
  VAMC
• Richmond, Virginia
• [phone]

2
LEARNING OBJECTIVES AND CLASS FORMAT

• 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
• Know where to find additional References FILEMAN
  Website and REFERENECE SLIDES Qualifiers,
  Functions and Operators(48 49)
• Understand Importance of Data Validation and
  Privacy/Security

3
TERMINOLOGY Most Important

• SYNTAX exact sequence of characters and words for
  a function to work, may be case sensitive
• Primary Source of Frustration

4
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

5
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

6
BASIC REPORT FUNCTIONS REVIEW

• Inquire to File Entries
• Print File Entries
• Search File Entries
• ??? For help

7
LIST FILE ATTRIBUTES

• Data Dictionary Utility Display Structure
• File Number or Label
• Field Number or Label
• Data Type
• Index and Pointers
• Suggest Brief format.

8
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

9

10
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)
  PATIENTAGE
• OR
• FIRST PRINT FIELD PATIENT
• THEN PRINT PATIENT FIELD AGE

11
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)_"_at_"_P(
  NAME,",",2)
• THEN PRINT PATIENT FIELD "_at_"
• THEN PRINT PATIENT FIELD STREET ADDRESS LINE
  1L25X
• THEN PRINT PATIENT FIELD "_at_"

12
JUMP FILES PRINT OUTPUT EXAMPLE (continued)

• THEN PRINT PATIENT FIELD STREET ADDRESS LINE
  2L10X
• THEN PRINT PATIENT FIELD "_at_"
• THEN PRINT PATIENT FIELD CITYL15X
• THEN PRINT PATIENT FIELD "_at_"
• THEN PRINT PATIENT FIELDSTATEABBREVX
• THEN PRINT PATIENT FIELD "_at_"
• THEN PRINT PATIENT FIELD ZIP4L9X
• THEN PRINT PATIENT FIELD "_at_"

13
JUMP FILES PRINT OUTPUT EXAMPLE OUTPUT

• 1. 2. 3. 4. 5. 6.
• THREE_at_PATIENT_at_1312 ASHTON PLACE_at__at_ROWLING_at_WV_at_99998-
  007_at_
• 1. P(NAME,",",1)_"_at_"_P(NAME,",",2)
• 2. STREET ADDRESS LINE 1L25X
• 3. STREET ADDRESS LINE 2L10X
• 4. CITYL15X
• 5. STATEABBREVX
• 6. ZIP4L9X

14
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

15
MULTIPLE Example (Standard)

• Inquire to File EntriesOUTPUT FROM WHAT FILE
  PATIENT// Select PATIENT NAMESEVENTYTWO,PATIEN
  T
• 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
  CLINICTHEN PRINT FIELD

16
MULTIPLE Standard Output

• FIRST PRINT FIELD APPOINTMENT (multiple)
• DATE/TIME CLINIC
• --------------------------------------------------
  -----------
• MAY 21,2000 1000 GENERAL MEDICINE
• MAY 22,2000 1000 GENERAL MEDICINE
• MAY 23,2000 1000 GENERAL MEDICINE
• MAY 24,2000 1000 GENERAL MEDICINE

17
MULTIPLE Example (INSIDE)

• TEMPLATE H210.INSIDE
• FIRST PRINT FIELD APPOINTMENT// (multiple)
• FIRST PRINT APPOINTMENT SUB-FIELD NAMEL15//
• By 'name', do you mean PATIENT 'NAME'? Yes//
  THEN PRINT APPOINTMENT SUB-FIELD APPOINTMENT
  DATE/TIMEC20
• THEN PRINT APPOINTMENT SUB-FIELD CLINICC45//
  
• THEN PRINT APPOINTMENT SUB-FIELD
• THEN PRINT FIELD

18
MUTIPLE Example (INSIDE) Output

• THEN PRINT APPOINTMENT SUB-FIELD APPOINTMENT
  DATE/TIMEC20
• NAME DATE/TIME
  CLINIC
• --------------------------------------------------
  ---------------------------
• SEVENTYTWO,PATI MAY 21,2000 1000
  GENERAL MEDICINE
• SEVENTYTWO,PATI MAY 22,2000 1000
  GENERAL MEDICINE
• OUTSIDE OUTPUT
• DATE/TIME CLINIC
• --------------------------------------------------
  -----------
• MAY 21,2000 1000 GENERAL MEDICINE

19
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

20
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

21
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)

22
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 910am

23
STEPS TO MS APPLICATION

• Select Output Source File and Find Records
  (SEARCH or PRINT SORT)
• Select Output Details in Fields, use Template
  (PRINT OUTPUT) to Format
• Save Output to ASCII Text File PACK
• Import to Chosen MS-Application with Import
  Wizard UNPACK
• Parse and Label Data and Save as MS-File

24
FORMATTING-SMUSH
25
FORMATTING-DELIMITER

• Signals New Column
• Carriage Return Signals New Row
• Syntax
• THEN PRINT FIELD any character or text string

26
SMUSHING AND DELIMITING example

• OUTPUT FROM WHAT FILE PATIENT//
• THEN PRINT FIELD E(SSN,6,9)X
• THEN PRINT FIELD "_at_"
• THEN PRINT FIELD LOWERCASE(P(NAME,",",1))X
• THEN PRINT FIELD "_at_"
• THEN PRINT FIELD LOWERCASE(P(NAME,",",2))X
• THEN PRINT FIELD "_at_"
• FIRST PRINT FIELD LOWERCASE(STREET ADDRESS LINE
  1)X
• THEN PRINT FIELD "_at_"
• THEN PRINT FIELD LOWERCASE(STREET ADDRESS LINE
  2)X
• THEN PRINT FIELD "_at_"
• THEN PRINT FIELD LOWERCASE(CITY)X//
• THEN PRINT FIELD "_at_"
• THEN PRINT FIELD LOWERCASE(STATE)X//
• THEN PRINT FIELD "_at_"
• THEN PRINT FIELD ZIP4X//
• THEN PRINT FIELD "_at_"

27
SMUSHING AND DELIMITING example Sample Output

• 1. 2. 3.
• 0003_at_Three_at_Patient_at_1312 Ashton Place_at__at_Rowling_at_West
  Virginia_at_99998-0071
• 1. E(SSN,6,9)X
• 2. LOWERCASE(P(NAME,",",1))X
• 3. LOWERCASE(STREET ADDRESS LINE 1)X

28
CAPTURE TEXT FILE PACKING

• 1. At VistA DEVICE Prompt, 013299999
• 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

29
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

30
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 ...

31
LOG IN FOR HANDS ON

• ACCESS 00VEHU
• VERIFYVEHU00

32
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//

33
2 HANDS ON search order file INPATIENT PACKAGE

• SEARCH File Entries
• OUTPUT FROM WHAT FILE ORDER//
• -A- SEARCH FOR ORDER FIELD PACKAGE
• -A- CONDITION CONTAINS
• -A- CONTAINS MEDICATIONS
• -B- SEARCH FOR ORDER FIELD WHEN ENTERED
• -B- CONDITION GREATER THAN
• -B- GREATER THAN DATE 6/8/2007_at_1600 (JUN 08,
  2007_at_1600)
• -C- SEARCH FOR ORDER FIELD
• IF AB PACKAGE CONTAINS (case-insensitive)
  "MEDICATIONS"
• and WHEN ENTERED GREATER THAN
  JUN 8,2007_at_1600 (6/8/2007_at_1600)
• OR
• STORE RESULTS OF SEARCH IN TEMPLATE
  00.IPMEDS6/8/2007

34
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

35
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
  WHENdL18//
• 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

36
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

37
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 rxX//
• By 'rx', do you mean PRESCRIPTION 'RX '?
  Yes// (Yes)
• THEN PRINT REFILL SUB-FIELD "_at_"X//
• THEN PRINT REFILL SUB-FIELD NUMDATE(issue)X//
  
• By 'issue', do you mean PRESCRIPTION 'ISSUE
  DATE'? Yes// (Yes)
• THEN PRINT REFILL SUB-FIELD "_at_"X//
• THEN PRINT REFILL SUB-FIELD patientssnX//
• 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 "_at_"X//
• THEN PRINT REFILL SUB-FIELD NUMDATE(REFILL
  DATE)X
• THEN PRINT REFILL SUB-FIELD "_at_"X//

38
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

39
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

40
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

41
HANDS ON Optional Print and Sort Functions

• DRUG FILE
• FIRST PRINT FIELD H210.NARC
• S(DEA"2""NARC",1"NOT NARC")
• Function file

42
STEPS TO IMPORT MS-ACCESS (Optional)

• 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)

43
E-TRIP NATIONAL RESORUCES

• Fileman Homepage
• http//www.va.gov/vdl/application.asp?appid5

44
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

45
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

46
1-800-HELP ME

• Phone Tom Fagan [phone]
• EMAIL THOMAS.FAGAN_at_VA.GOV

47
QUESTIONS
?
48
Reference 1- Select Print, Sort Qualifiers and
Operators
49
Reference 2 Select Fileman Functions