Getting to the Bottom of the Data Basic FileMan

1. Getting to the Bottom of the Data � Basic FileMan Michele Krajewski Vista Imaging System Manager Philadelphia VAMC David W. Massey Product Support Specialist Clinical 3 Team, Imaging Support

2. Introduction VA FileMan � What is it? Menu Options List File Attributes from Data Dictionary Utilities FileMan report functions (Inquire, Sort, Search and Print) FileMan Templates �Jumping� files & Multiples References & Links Questions?

3. VA FileMan � What is it and Who uses it? Creates and maintains a database management system (DBMS). Used to define, enter, and retrieve information from a set of computer-stored files, each of which is described by the data dictionary. Has several levels of users, ranging from a data entry person who enters, edits, inquires or prints information, to software application developers who utilize the DBMS features and utilities. As a database management system, VA FileMan supports the entering, editing, printing, searching, inquiring, transferring, cross-referencing, triggering, and verifying of information. It includes special functions to create new files, modify an existing file, delete entire files, re-index files, and create or edit templates. VA FileMan functions as a database management system with powerful Application Programmer Interfaces(APIs) and provides useful utilities to package application developers and programmers. VA FileMan can be used as a database management system for data entry and output and its DBS calls are utilized in application packages with tools like auditing, archiving, and statistics. SAMPLE API CALLS As a database management system, VA FileMan supports the entering, editing, printing, searching, inquiring, transferring, cross-referencing, triggering, and verifying of information. It includes special functions to create new files, modify an existing file, delete entire files, re-index files, and create or edit templates. VA FileMan functions as a database management system with powerful Application Programmer Interfaces(APIs) and provides useful utilities to package application developers and programmers. VA FileMan can be used as a database management system for data entry and output and its DBS calls are utilized in application packages with tools like auditing, archiving, and statistics. SAMPLE API CALLS

4. Sample API Calls � Programmer Alert!

5. Fields, Records, and Files A computer database such as VA FileMan organizes your data, storing it in fields, records, and files, much as you might arrange and preserve information on paper. A record is a group of fields of data. Each record has a name (e.g., a patient name for a patient record). You can use the record name to recall the record for editing or for printing out. Records are also known as file entries. For example, a record for a patient might have various fields such as NAME, AGE, ADMISSION DATE, etc. for a single patient. Each record for a patient would contain the same set of fields (places to fill in information).

6. Fields, Records, and Files Each field in a record functions just like a blank on a form. When you're editing a record on a terminal, for each field, you'll find a prompt that tells you what type of data to enter. All of the records that contain like data are collected and stored in the same file. For example, all patient records are stored in the PATIENT file (#2) and all computer user records are stored in the NEW PERSON file (#200). This way, to look up a patient, you know that the file to go to is the PATIENT file. Also, to do a report on all patients, you would generate the report from the PATIENT file.

7. Database The database contains all the data files on your computer system.The database contains all the data files on your computer system.

8. DD � Map to the City (Database) City = Database Block = Package House = File Room = Record

9. VA FileMan Options Select VA FileMan Option: ??? 'Data Dictionary Utilities' Option name: DI DDU 'Enter or Edit File Entries' Option name: DIEDIT This option is used to enter new entries in a file or edit existing ones. You specify the file and fields within the file to edit. 'Inquire to File Entries' Option name: DIINQUIRE This option is used to display all the data for a group of specified entries in a file. This is useful for a quick look at a small number of entries. Use the Print File Entries option for larger numbers of entries. 'Modify File Attributes' Option name: DIMODIFY This option is used to modify the structure of a file or the characteristics of its fields. 'Other Options' Option name: DIOTHER This menu contains a series of menus which lead to enhancements in current and coming versions. These include auditing, filegrams, and FileMan management. 'Print File Entries' Option name: DIPRINT This option is used to print a report from a file, where a number of entries are to be listed in a columnar format. Each column can be individually controlled for format, tabulation, justification, etc. 'Search File Entries' Option name: DISEARCH This option is used to print a report in which entries are to be selected according to a pre-determined set of criteria. After the search criteria is met, a standard report will be generated. 'Transfer Entries' Option name: DITRANSFER This option is used to transfer entries from one file to another or to merge data from one entry to another in the same file. 'Utility Functions' Option name: DIUTILITY This option is a menu of VA FileMan utilities used to maintain the more technical aspects of files.Select VA FileMan Option: ??? 'Data Dictionary Utilities' Option name: DI DDU 'Enter or Edit File Entries' Option name: DIEDIT This option is used to enter new entries in a file or edit existing ones. You specify the file and fields within the file to edit. 'Inquire to File Entries' Option name: DIINQUIRE This option is used to display all the data for a group of specified entries in a file. This is useful for a quick look at a small number of entries. Use the Print File Entries option for larger numbers of entries. 'Modify File Attributes' Option name: DIMODIFY This option is used to modify the structure of a file or the characteristics of its fields. 'Other Options' Option name: DIOTHER This menu contains a series of menus which lead to enhancements in current and coming versions. These include auditing, filegrams, and FileMan management. 'Print File Entries' Option name: DIPRINT This option is used to print a report from a file, where a number of entries are to be listed in a columnar format. Each column can be individually controlled for format, tabulation, justification, etc. 'Search File Entries' Option name: DISEARCH This option is used to print a report in which entries are to be selected according to a pre-determined set of criteria. After the search criteria is met, a standard report will be generated. 'Transfer Entries' Option name: DITRANSFER This option is used to transfer entries from one file to another or to merge data from one entry to another in the same file. 'Utility Functions' Option name: DIUTILITY This option is a menu of VA FileMan utilities used to maintain the more technical aspects of files.

10. Data Dictionary Utilities The structures of VA FileMan files are stored in the data dictionary (DD). Locate specifications of every field in every file. Use the information in the DD (usually field names and descriptions) to successfully access and use the data in VA FileMan's files. The challenge isn�t learning the FileMan syntax for retrieving data, it�s learning where the data is stored. Approx. 3,000 files. Over 50,000 individual fields. VAH>S X=0,Y=0 F S X=$O(^DD(X)) Q:'X S:$D(^DIC(X)) Y=Y+1 VAH>S X=0,Y=0 F S X=$O(^DD(X)) Q:'X I $D(^DIC(X)) S J="" F S J=$O(^DD(X,"B", J)) Q:J="" S Y=Y+1The challenge isn�t learning the FileMan syntax for retrieving data, it�s learning where the data is stored. Approx. 3,000 files. Over 50,000 individual fields. VAH>S X=0,Y=0 F S X=$O(^DD(X)) Q:'X S:$D(^DIC(X)) Y=Y+1 VAH>S X=0,Y=0 F S X=$O(^DD(X)) Q:'X I $D(^DIC(X)) S J="" F S J=$O(^DD(X,"B", J)) Q:J="" S Y=Y+1

11. List File Attributes Option  This listing displays the structure of the file and the characteristics of the fields in the file. It does not show entries, records, or any data contained in the file! This information can be very useful when deciding what fields to include in a report, or what fields to edit. Where stored, not what stored.Where stored, not what stored.

12. List File Attributes  Formats Brief [good place to start!] Condensed Standard (or Modified Standard) Custom-Tailored [try on your own!] Templates Only Global Map [Programmer Alert!] Indexes and Cross-References Only Keys Only

13. Brief Data Dictionary  Brief format is more readable but less complete than the default of a Standard listing. Let�s look at a Brief DD for an Imaging File! (2005 IMAGE FILE) But how do I know what Imaging files are on my system?? Tip: (? ^2005) What if I don�t know the file range start? Tip: (Package Technical manual) 1. Sign on to KEA. 2. Menus - ???. 3. ?,^2005 4. Brief DD1. Sign on to KEA. 2. Menus - ???. 3. ?,^2005 4. Brief DD

14. Brief Data Dictionary PLACE HOLDER. Describe different parts � header (#, name, version), fields, field number, field type, field description, Multiples PLACE HOLDER. Describe different parts � header (#, name, version), fields, field number, field type, field description, Multiples

15. Multiples (Subfiles) Sometimes, a single field in a record is not enough to hold all the information required. Subfiles (Multiples) are widely used throughout Vista. Subfiles can themselves contain Subfiles creating additional levels in files. Sometimes, a single field in a record is not enough to hold all the information required. For example, to keep track of the appointment history for a patient, a single field would not be enough. An ordinary field could only hold enough information to record a single appointment. But there needs to be a way to record as many appointments as a patient has had, which could range from none to several hundred. In addition, each appointment might have its own information that needs to be stored, such as date, time, location, doctor, etc. 2005 multiple to 2005Sometimes, a single field in a record is not enough to hold all the information required. For example, to keep track of the appointment history for a patient, a single field would not be enough. An ordinary field could only hold enough information to record a single appointment. But there needs to be a way to record as many appointments as a patient has had, which could range from none to several hundred. In addition, each appointment might have its own information that needs to be stored, such as date, time, location, doctor, etc. 2005 multiple to 2005

16. Field Types Date/Time Numeric Set of Codes - codes that represent values Free Text � length 1 to 250 characters Word Processing - multi-line document of text Computed � virtual field, value not stored Pointer to another File Variable Pointer � point to more than one file Mumps - designed specifically to contain executable M code. Set of Codes - can be used to restrict a user to just a few possible values, codes that represent values 1=MALE, 2=FEMALE Word Processing - allows entry of unlimited free-text data. The data can be edited, formatted, and printed with word processing text editors. Computed - value is determined at the time the field is accessed. AGE Pointer to a File � Exported Location points to the Network Location file. Question to class: Can a field point to the same file? 2005 � Group Parent Variable Pointer � ORDER file, ITEM ORDERED field. Points to: 19 PROTOCOL, 60 LAB TEST, 101.41 ORDER DIALOG VAH>S X=0 F S X=$O(^DD(X)) Q:X>2008 S Y=0 F S Y=$O(^DD(X,Y)) Q:'Y I $D(^DD(X,Y,0)) S TYPE=$P(^(0),U,2) W:$E(TYPE,1)="V" !,X,?10,Y,?20,^(0) Set of Codes - can be used to restrict a user to just a few possible values, codes that represent values 1=MALE, 2=FEMALE Word Processing - allows entry of unlimited free-text data. The data can be edited, formatted, and printed with word processing text editors. Computed - value is determined at the time the field is accessed. AGE Pointer to a File � Exported Location points to the Network Location file. Question to class: Can a field point to the same file? 2005 � Group Parent Variable Pointer � ORDER file, ITEM ORDERED field. Points to: 19 PROTOCOL, 60 LAB TEST, 101.41 ORDER DIALOG VAH>S X=0 F S X=$O(^DD(X)) Q:X>2008 S Y=0 F S Y=$O(^DD(X,Y)) Q:'Y I $D(^DD(X,Y,0)) S TYPE=$P(^(0),U,2) W:$E(TYPE,1)="V" !,X,?10,Y,?20,^(0)

17. Pointer to a File Using a pointer field from an entry in the current file, you can easily retrieve information from the pointed-to entry in another file. You can include the attending physician's phone number for each patient in your report, by using a simple extended pointer at the "PRINT FIELD:" prompt. PRINT FIELD: ATTENDING PHYSICIAN :PHONE NUMBER The most common form of relational navigation uses simple extended pointers. This type of navigation requires a pointer field to exist from the current file to another file. Using a pointer field from an entry in the current file, you can easily retrieve information from the pointed-to entry in another file. For example, suppose you are printing a report from the PATIENT file. Further suppose that the PATIENT file has a pointer field called ATTENDING PHYSICIAN field to the NEW PERSON file. Now, what if you wanted to include the phone number of the attending physician for each patient in your report from the PATIENT file? The attending physician's phone number is stored in the NEW PERSON file, not the PATIENT file. You can include the attending physician's phone number for each patient in your report, by using a simple extended pointer at the "PRINT FIELD:" prompt: PRINT FIELD: ATTENDING PHYSICIAN:PHONE NUMBERThe most common form of relational navigation uses simple extended pointers. This type of navigation requires a pointer field to exist from the current file to another file. Using a pointer field from an entry in the current file, you can easily retrieve information from the pointed-to entry in another file. For example, suppose you are printing a report from the PATIENT file. Further suppose that the PATIENT file has a pointer field called ATTENDING PHYSICIAN field to the NEW PERSON file. Now, what if you wanted to include the phone number of the attending physician for each patient in your report from the PATIENT file? The attending physician's phone number is stored in the NEW PERSON file, not the PATIENT file. You can include the attending physician's phone number for each patient in your report, by using a simple extended pointer at the "PRINT FIELD:" prompt: PRINT FIELD: ATTENDING PHYSICIAN:PHONE NUMBER

18. Standard Data Dictionaries  The most complete information about a file is obtained by using the Standard data dictionary format. Default for the List File Attributes option. In addition to detailed information about every field in the file: file description file access identifiers other files pointing to the file cross-references files pointed to by the file templates associated with the file The Standard DD is more suited to advanced FM users and programmers.The Standard DD is more suited to advanced FM users and programmers.

19. File Access � Data Security VA FileMan has facilities for screening access to entire files and to data within files on a field-by-field basis. Access to previously defined templates is also controlled. Imaging files are secured with the �@� file access symbol.

20. Standard Data Dictionaries Let�s look at a Standard DD for an Imaging File! (2005 IMAGE FILE) Description � good info! Look up an individual field (Start with field: FIRST// .01 OBJECT NAME) Tip: For faster searches on files that contain many entries (2005!), start w/cross referenced fields.

21. Standard DD Field All Files have a .01 field!All Files have a .01 field!

22. Templates Only DD Displays information about the templates associated with a file. Input Templates Print Templates Sort Templates

23. Quick FYI�Other DD�s � Programmer Alert! Global Map format shows the actual structure of the global (file) that contains the data for the file The Indexes and Cross-References Only format shows the Traditional cross-references and New-Style indexes that are defined on a file. The Keys Only format shows the keys that are defined on a file. KEY DD: FILE #62.47 ----------- PRIMARY KEY: A (#78) Uniqueness Index: C (#783) File, Field: 1) DATABASE CODE (62.47,.03)KEY DD: FILE #62.47 ----------- PRIMARY KEY: A (#78) Uniqueness Index: C (#783) File, Field: 1) DATABASE CODE (62.47,.03)

24. What does a Global look like?? - Programmer Alert! LEAVE STUDENTS AT FILEMAN MENU PROMPT FOR MICHELE LEAVE STUDENTS AT FILEMAN MENU PROMPT FOR MICHELE

25. Inquire Hands-on

26. Print Hands-on

27. Print Hands-on (con�t)

28. Search Hands-on 1

29. Search Hands-on 1 (con�t)

30. Jumping Hands-on

31. Jumping Hands-on (con�t)

32. Search Hands-on 2

33. Search Hands-on 2 (con�t)

34. Other 2008 VeHU Classes

35. Helpful Links http://www.hardhats.org/fileman/FMmain.html http://vista.med.va.gov/fileman/index.asp http://www.va.gov/vdl/application.asp?appid=5 http://vaww.vehu.med.va.gov/vehu2008/Default.cfm

36. Resources VA FileMan manuals � Technical, User, Advanced User and Programmer VA FileMan v. 22.0 website Hardhats website

37. Questions?