Revision of Magazine Database Help from Fri, 05/09/2014 - 16:55

Help file for ANRL Magazine Database in FileMaker 12
Updated May 09, 2014 rap – I’m still working on this but it’s getting better.

General Comments

  1. It is best to work with an experienced user before working independently.
  2. Think before you click on anything If you don’t understand something then please get help. Don’t just poke around because you could be entering or changing data while you think you are just looking.
  3. FileMaker has no button to click on to save changes. Changing a field and then clicking away confirms the change. See Click Away in the definitions section below.

Objectives of the Magazine Database

  1. To have a single file that keeps track of all magazines in the library
  2. To have one unique record for each bit of material (magazine) that shows
    1. Number in Archive–1 and Archive–2
    2. Number in Surplus–1 and Surplus–2
    3. Scanning Status and name of scanned file
    4. Suggested donation price for Surplus Magazines

Learning FileMaker 12

  1. <lynda.com> has a number of training videos and that’s what I used
  2. <youtube.com> Lots of videos here
  3. <google.com> You can always search for stuff
  4. I plan to give demos on how FileMaker 12 works and how the Magazine Database works.
  5. Books are available
  6. When you first launch FileMaker there is a place to click to get training
  7. In general, you could ask me and I will answer questions, but I can’t take time to teach every mouse click or things that are already documented and easy to read. I can help with finding documentation.

Definitions

Note: These terms are used in the following sections

  1. Click Away This is a term for clicking anywhere away from the box you just edited. For example, assume you just edited the Publisher field by entering “AJAX Publishing, Inc.” There is no save button, but the act of clicking outside the field confirms and saves the entry.
  2. Master Database This lives in Bob’s laptop computer and is backed up in many places.
  3. Library Database This is a snapshot of the Master Database and is refreshed from time to time with scripts and layouts from the Master Database.
  4. Found Set This is a subset of records that became selected with a Find command. Find commands are part of the scripts used in Form View.
  5. Form View This is the main working area of the Database.
  6. Table View This shows records of the Found Set. Columns can be re-sized, hidden, re-ordered as desired.
  7. Master_Magazine This is the main Database for Magazines.
  8. Mag_Arch_Surp This is the name of the primary FileMaker area where Librarians work.
  9. Selected Record While in Table View, there is always one and only one selected record. It is shown in a way that’s different from the rest. Notice the tiny grey square box on the left edge of the table.
  10. Select a Record This is done by clicking any field on the record or the empty light grey square area at the very left edge of a record in table view. Only one record can be selected at a time. This is the record that will show when going back to Form View by clicking Main Magazine Window
  11. Script Numbers These are the 3-digit numbers on buttons, like 103, 112, etc. These make it easier for the script developer to quickly find a script that’s attached to a button.
  12. Value Lists
    1. These are lists of things that you see in ‘Pop-Up’ or ‘Drop-Down’ selection windows. There are slight differences between these two kinds of windows, such searching, scroll bars and getting new information into the value list. If you need to add a term, please remember to write it down someplace and then tell me.
    2. Values Based on values in a field It means exactly that. These can not be edited but if you can get a new value into a record, it will show up in this kind of Value List.
    3. Values Coded into tables These tables can be edited as you are doing your work. Just scroll to the bottom of the list and click on the Edit field. A new window will pop up and you can enter something new there. Periodically, the Database Administrator will refresh these tables to put them back into alphabetical order.
    4. Note If the number of items is large, you might see an ellipsis (…) and that says there are more unshown items. Simply click on the (…) and then you will see the full list.
  13. Date Information There are five fields in the Scanned table and in the Archive/Surplus table for this
    1. Yr-Mon-Vol-Iss-Num This is a way to refer to the five fields in the tables

Working Areas In The Database

In general, there are buttons on top of every working areas to quickly get to any of the others working areas. Please report any problems with navigation.

  1. Main Magazine Window
    1. This is where you land when you launch FileMaker by clicking on the FM–12 Mag shortcut on the home screen of the Gateway Computer.
    2. This is the primary area for Librarian work of adding new magazines or changing the number of Surplus or Archive for existing magazines
    3. You will see a Navigation Button on the top of the screen 113 Mag Central
  2. Magazine Central
    1. This is Layout that provides two portals which give a side-by-side look at:
      1. Archive/Surplus Magazines
      2. Scanned Magazines
    2. Each of these portals is a smaller window with a scroll bar.
    3. While identifying magazines we use Date Information consisting of Yr-Mon-Vol-Iss-Num
      1. Year This is normally a 4-digit number however anomalies exist: 1995–96, 2001–2002, ’89-’90, etc.
      2. Month This is ideally a 2-digit number and it is in Scanned magazines however in Archive, it can be any of a hundred variations: Jan-Feb, Apr-May-Jun, 03–04, etc.
      3. Volume This is normally a one or two digit number
      4. Issue This is normally a one or two digit number
      5. Number This is normally a one or two digit number but there may be variations
    4. The purpose Magazine Central is to sort out name anomalies in which an issue has a Yr-Mon-Vol-Iss-Num value in Archive/Surplus that is different from the value in Scanned.
    5. The objective in the long term is to connect every Scanned Magazine to a record in Archive/Surplus.
    6. Go to a Scanned Magazine record These buttons takes you to the selected record in the portal. You click on one field of the desired record to choose the record, then press button 114 Go To Selected Record
    7. Go to an Arch/Surp Magazine Record Select one field of the record of choice then press button 112 Go To Selected Record
  3. Scanned Magazine
    1. There is one record for each Scanned Magazine. Although this isn’t of much value now, I’m working on a way to link the PDF file for the magazine here so you can view the magazine.
  4. Name Mapper
    1. This is useful in a Table View to find the Mapped Name for a given Magazine or to find the Magazines for a given Mapped Name.
    2. This could be very useful if you are having trouble finding a given magazine.
    3. Please report any anomalies to Bob Proctor.

Tables in the Relational Database

  1. MAG_Arch_Surp This is where Surplus and Archive magazines reside. There is a layout Main Magazine Window that is where most of the Librarian work is accomplished for adding magazines and adjusting numbers of Archive and Surplus and for adding comments and notes about the magazine.
  2. MAG_Central The content of this table is basically a list of names in the name map file. There is a layout for this table that is called Magazine Central More on this later in this document.
  3. MAG_Scanned This is where scanned magazines reside. Scanned magazines are loaded into the Database with a program that reads Ed’s Magazine Archive disc of scanned magazines, parses the Date Information into Yr-Mon-Vol-Iss-Num fields. Every time Ed makes an update to his disc I can do the update to the Database. Any manual changes to this table will be lost when the next automatic update takes place therefore, do not make changes to this table.
  4. Name_Mapper This table has four columns:
    1. PK_Name_Map
    2. Title This is the exact title in the Archive/Surplus table or the exact title that’s in the Scanned table.
    3. Name_Mapper This is a common name for magazines which ended up with several names in the Archive/Surplus table and a totally different name in the Scanned table. What I’m trying to do is to have a common name which can be used for searching the Database to gather all magazines of a common title. It’s this name that gets used when finding magazines to update.
    4. Source This is the place where the name came from, for example Scanned means the name came from Scanned Magazines.

Basic Navigation for using this Database

  1. Logging into FileMaker 12 Please use the link on the desktop called FM–12 Mag This will automatically open the correct Database. There is no need to use a UserID or Password but in case you are interested: UserID: Librarian Password: FM12librarian
  2. Opening Layout The layout that comes up when the Database opens is Mag_Arch_Surp and that stands for Magazine Archive and Surplus. This is the main working area for Librarians. Normally there is no need to change Layouts although it is possible to end up in another Layout and then you will need to change back to Mag_Arch_Surp. Notice the Layout pull-down option box on the lower left edge of the toolbar. Use the pull-down window to select the Layout of choice if you need to.
  3. Areas of the Screen Notice the tabbed box with 6 tabs
    1. Tab1: Main Window There are two red boxes on this screen. The top red box is for finding something to update. The bottom red box is for making the updates. It is entirely possible to really mess things up if you type into the bottom box while trying to find something. It may take some time to get the correct rhythm for using the Database.
      1. The Top Rd Window
        1. Find Mag Title This pop-up window allows quick selection of Mapped Titles
        2. 104 Find All Magazines for this title actually does the search
        3. Last Used Search is a window that records the last search in case you want to quickly do it again. It gets built when the 104 Find All Magazines is clicked
        4. 104 Find All Magazines for this title (last Used) actually does the search
        5. 120 Clone this record If you are adding a new magazine in a series and it isn’t in the Database, you can select a record close to the new one, clone it to a new record, then quickly make adjustments in number of Surplus, Archive, etc. Doing this keeps things like Publisher, Editor, Title, etc.
        6. 101 New Record This is for creating a totally new Magazine Title when no such name is already in the Database.
      2. The Bottom Red Window
        1. All fields for this record are shown here. You can change anything (normally not the title) and then Click Away to save the changes as you go. The Green +1 and –1 change the count of whatever is to the left of the box.
    2. Tab2: Status There isn’t much in this now but I’ll add some Database statistics soon
    3. Tab3: Help Main This file will appear in here
    4. Tab4: Help Nav This is intended to show some pictorials of various parts of the screen
    5. Tab5: Scanned This is a portal into the scanned table but it isn’t working today
    6. Tab6: Special Here is where the special function buttons are located
      1. 105 Find Surplus Not In Archive (this helps to identify magazines that should be move from Surplus to Archive)
      2. 106 Sort by number in Archive presents a table view of
      3. 107 Find Second Title For some reason there were extra things attached to original title. I called these Second Title and now we need to see if there is any significance to this data. The values that are in this field are Nude and Clothed.
      4. 117 Export All Records Note that this takes awhile, like a minute, so be patient.
        1. This button sends 3 Emails, one for each table using the “Merge” filetype which has a .mer extension.
          1. MAG_Arch_Surp
          2. MAG_Scanned_Main
          3. MAG_Central_Main
          4. The process is automatic however there are some informational boxes which you will need to close with a click on OK
          5. Please run this both before and after doing Database work. In the event that things get totally messed up, these files can be used to restore the state of the Database.
          6. I have a program called “ANRL_Diff_Mag.pl” that can find fields that have been changed. This is useful to monitor change activity and insure that it’s being done as intended. When a librarian feels confident in the changes that are being made then there may not be a need for this kind of checking.
      5. 140 Print Inventory Sheet for Selected This will print all records for a selected title and will be handy while working on our inventory.
        1. After this table has printed, you should go to the “MAG_Arch_Surplus” layout and continue your work.
  4. Table View Once you click on one of these Buttons, a Table View comes up.
    1. You can return to the main screen by clicking on 116 Main Magazine Window which is on the top of the page.
    2. While in Table View you may want to edit a record. To edit a record do the following:
      1. Select the record by clicking on any field in the record. Notice that the the tiny grey square area at the left edge of the table row becomes highlighted with a darker shade of grey.
      2. Click the 116 Main Magazine Window button and you will go back to Form View where you can make edits.
  5. Views There are three available views from the View option of the ToolBar.
    1. Form: This is the main working area and it displays fields and the Help file.
    2. List (this is greyed out since there is no need to use it)
    3. Table: This is useful to see a bunch of records at once in a table.

Working with Value Lists

  1. Selecting from Value Lists
    1. Pop-Up List These are identified by a little triangle inside the box where you enter things like price, number of magazines and so on. This kind of list is normally used when the number of pre-programmed values is small.
    2. Drop-Down List These are identified by the word LIST in purple text. When you click on one of these Drop-Down Lists, you can start typing and the auto-complete feature kicks in to converge into items you are looking for. For example: typing sun will find all titles that begin with Sun. If you keep on typing the list gets shorter. This feature is used when the list of pre-programmed items is long.

Options while in Table View

  1. Sorting values while in Table View:
    1. Click the right edge of the title area of a column and you will see options to Sort Ascending, Sort Descending and so on.
  2. Moving Columns You can move columns by clicking and dragging the Column Name lift or right.
  3. Adding or removing Columns There is a tiny box on the top right corner called Modify Click on that and check or uncheck column names to hide or show the column. You can drag names up or down and this repositions columns just like dragging a column left or right.

Basic Workflow to use the Database

  1. Adding new magazine issues to a magazine that’s already defined
    1. Select the Title in the green box that’s named Find Mag Title
    2. Click the blue button 105 Find All Magazines for this Title
    3. You will see a table view of all magazines with the selected Title
    4. Search the table to see if the magazine you are adding is already in the Database
    5. If it’s in the Database, then click on the tiny grey box to the left edge of the record. When clicked, this box turns a darker shade of grey.
    6. Then click on the Go to Magazines button which is at the top of the screen
    7. Make edits for the number of copies: Surplus or Archive, etc.
    8. If the magazine you want to add isn’t already in the Database, then proceed to the next step:
  2. Adding a new Magazine Title that is not already defined
    1. Click on the blue 01 New Record button
    2. An empty frame appears and you can fill in the blanks. The serial number will automatically fill in to the next available value
    3. Use the Drop-Down windows like you do for selecting values that are already in the Database. In this case the desired value isn’t in the Database. Scroll to the very bottom of the red Drop-Down list named Title and you will see an option to Edit… Another edit box pops up and you can enter the desired name. It doesn’t have to be in the correct alphabetical location but it would be nice if you positioned it correctly.
    4. If you don’t see a good selection then you can add something new.
    5. There are several ways of managing these edit boxes and I’m still looking for the best solution. If you have any suggestions, then please let me know.
  3. Deleting Records
    1. In general, you should Never have to do this
    2. You can set the number of Archive or Surplus to zero and that’s OK
  4. Changing number of Archive and Surplus
    1. The Surplus–1 and Surplus–2 Fields can be changed but the Archive Field can’t because it’s a calculated field. Change the number by clicking on the + or - next to the box. To change Archive, you need to change one of following fields that contribute to the calculation:
      1. Main Room
      2. Alcove
      3. Hold
      4. Bound
  5. Show All Records
    1. You may want to do this to get the big picture of the Database.
    2. While in the Form View you can click on the Show All button in the tool bar and then all records will be in the Found Set. While viewing all records in Table View you can select one (Select a record) and then click on Go To Magazines and then you can edit that record in Form View.
  6. Basic Search across all fields. Look in the upper right part of the screen and you will see a white box with rounded ends and a magnifying glass. Just enter what you are searching for and all records with that value will appear the Found Set. You can see how many by looking at the upper-left part of the screen that shows Found Sets. You could click on View and then select Table to see the found set in table view.
  7. Search in Fields Be VERY CAREFUL while doing this. Make sure you are in Find Mode by clicking on the Find option on the bottom left corner of the screen. While in Find, each box will show a Magnifying Glass symbol. If you don’t see that, you will be changing the value of a field and that’s probably not what you want to do.
  8. Screen Magnification You can use the + and - at the bottom left corner of the screen to zoom in or out and make the text bigger or smaller.

Location of the Master Database

  1. Master Database is on my laptop. Copies are automatically sent to the Gateway computer in the Library. I’m making lots of changes so it’s important that I have the only master copy.
  2. Library Copy of the Database This is on the Gateway computer in the Transporter folder.

Process of moving data from ‘Library Copy’ to ‘Master Copy’

  1. All updates made by Librarians are encapsulated in the Export File
  2. The Export File gets moved into the Master Database file
  3. A new copy of the Master Database File will be loaded on the Library computer. Since changes to the Database layout forms or the scripts are part of the Master Database, they will be part of what gets loaded into the Library computer.
  4. There is a period of time when the Library Copy should not be used because updates are being made.

Tips

If you get a pop-up with a warning after trying to select a Title, then check the number in Found Set. If it’s zero, then click on Show All in the toolbar.

Problem Areas

  • I don’t have a clue as to what the Duplicate field is for.
  • When I merged the FM5 Magazine Surplus and Archive Database files together I found
    1. There are different names for the same magazine. You will need to resolve this by adjusting numbers of Archive and Surplus. When the number of magazines in one record is down to zero, then that magazine can be marked for deletion. At some point I will run the script that deletes these records.
    2. Surplus titles that did not exist in Archive. As of today there are ~500 Surplus records w/o Archive records. It’s your job to fix this.
  • Scanned Magazines use Ed’s Naming convention both for Magazine Names and for names of Year :: Month :: Volume :: Number :: Issue. If somebody was looking for the exact name in Ed’s files it would be hard to say if they would find it.
  • The Condition field in the Database now has info which is mostly meaningless. It was entered as the condition of a single magazine. Since we can have a number of the same magazines with differing conditions, it is hard to tell its significance. Ed Westen suggests that this field should indicate the condition of the worst copy of the magazine in the collection. If we knew that information and if a magazine was donated with a better condition, then we could keep the better magazine and ditch the poorer magazine.
  • Names of Scanned are very different from the names used in FM12 Database. The way I’m trying to handle this is to write a name mapping program that gives each magazine a common name. With that, when we look at Scanned Magazines we can match FM12 Archive/Surplus names. This will take lots of tinkering and manual work to get all sorted out but I think it can be done.
  • Names for Magazines in FM12 files sometimes join an old magazine name with a new magazine name because it’s the same magazine with a slightly different title. Names of all Scanned Magazines are unique and only have one title in the name. The directory in which Scanned Magazines are located sometimes has two names which could be slight name variations because the magazine changed it’s name.
    • Example 1 Scanned directory name is NudeLook-NudeLark and the FM12 files have two names for these which are Nude Look and Nude Lark. Apparently the name of the Magazine changed over the years and Ed joined them together into a single name. Helen used the exact name that was on the magazine. Now how do we match Scanned with Archive/Surplus?
    • Example 2 Scanned name is ClothedSun_Nude&Natural and the FM12 files have two names for these which are Clothed With The Sun and Nude & Natural. Apparently the name of the Magazine changed over the years and Ed joined them together into a single name. Helen used the exact name that was on the magazine. Now how do we match Scanned with Archive/Surplus?
  • Wanting to use different colors on alternate rows of a table. How to do that?
  • There is no distinction between Archive–1 and Archive–2
  • There is no distinction between Surplus–1 and Surplus–2

Quick Start Flow

Note This section is at the bottom of the document. This is intentional. You should have read and understood everything written above before doing anything.

  1. Launch Click on FM–12 Mag icon on main screen of the Gateway Computer
  2. The First Thing To Do
    1. Export All Records Find this button on the Main Magazine Window in the Special Tab. This sends a snapshot of the Database to the Database administrator before work starts.
  3. Process One Magazine (Note You will be using this step over and over many times.)
    1. Determine whether the magazine you are adding/updating is already in the Database
      1. Use window called Find Mag Title to select your magazine from a list of Mapped Titles. Simply start typing the first few letters of the name and the list will zoom in to possible choices. Click the correct choice.
      2. Use the 104 Find All Magazines For This Title button to select records and make a Found Set
      3. If necessary, you can go back by hitting the 102 Main Magazine Window button, then
      4. click on the 105 Find all magazines from last selected title
      5. Is the Issue already in the Database?
        1. (Search the table view for your magazine)
        2. Yes: While in Table view, select a field on the Issue then click 102 Main Magazine Window and make changes to number of Archive/Surplus
        3. No:
          1. Click on a field in a record similar to the one you want to add
          2. Click 102 Main Magazine Window
          3. Click on 120 ‘Clone’ This Record into New
          4. Make updates like setting the number in Archive to 1, etc. Most of the values for Publisher and Editor will already be correct.
    2. No:
      1. Use 101 Make A ‘New’ Record
      2. Update fields as necessary to correctly enter the magazine
  4. Last Thing To Do
    1. Export All Records Find this button on the Main Magazine Window in the Special Tab. This sends a snapshot of the Database to the Database administrator after your work is complete.
  5. Close the Database
  6. Close FileMaker