IQ as a Relational Database: Book Review Process

• Authors
• Books
• Reviewers
• Book Reviews
• Rating (individual and overall)
• Outcome
• Communications

• Flagging late reviews
• List of book reviews for each reviewer with status
• Pivot pie chart showing the count of Authors per country

1. Creating the Source Grids

• Authors
• Reviewers
• Books

1.1 Authors Grid

1. View > Grids > New Grid...
2. Set the Grid name to Authors.
   This automatically creates a Yes/No field, named Authors, to mark specific items as being book authors
3. Add any contact information fields to this view (such as Tel and EMail)
   One way to do this is to show the Properties pane, open the All Fields section in field tree-view mode. All build-in contact related fields are under the field AddressBook
4. We'd like to enter the Author's country, which is not in the sample IQBase, to do this:
   1. Open the Manage Fields dialog
   2. Click on New
   3. Name it AuthorCountry
   4. Type = Text
   5. Editor type = Auto-list of values
   6. Click Close
5. Add this new field to the grid (using Add / Remove Column dialog or right-click on the field in the Properties pane and select Show field in the current grid
6. To have a more user friendly field caption, right-click on the header and select Column Header > Caption...
   1. Replace AuthorCountry with just Country.
   2. Right-click and choose the flag icon
   3. Click OK
7. Add a few authors to this grid

• Grid properties > Options > Grid visible to other applications
• Grid > Create an ODC file

1.2 Reviewers Grid

• Grid name: Reviewers
• Create a numeric field ReviewerID with Auto checked (creates an auto-number)
• Columns: Item, Address, Email, Notes, CallDate, ReviewerID

1.3 Books Grid

• Book information (name, author, date received, average rating, outcome, status)
• Under each book entry, a list of review entries, one per reviewer (reviewer's name, date submitted, deadline, rating, done date)
• Summary of communications with reviewers
• The HTML pane can contain rich text content for each review and book review process output report

1. Create a new grid, named Books
2. Create field BookAuthor: This popup will show entries in the grid Authors
   (It uses an SQL query to be able to pick up both the name and country of the author at the same time)
   • Type: Text
   • Editor: SQL query
   • Pop-up list: SELECT Item as BookAuthor, AuthorCountry from Authors
   • Save all listed fields: checked
      
3. Create field BookReceived
   • Type: Date
   • Default value: int(now)
      
4. Create field Outcome: Outcome of the review, pop-up list
   1. Type: Text
   2. Editor: Auto-list of values
       
5. Create field BookRating: Average of Reviewer's rating of the book, between 1 and 10 (max)
   1. Type: Number
   2. Column Equation: Parent = AVG(Children)
       
6. Create field Status
   • Type: Text
   • Editor: Auto-list of values
      

1. Create field BookReviewer: This popup will show entries in the grid Reviewers
   (It uses an SQL query to be able to pick up both the name and ID of the reviewer at the same time)
   • Type: Text
   • Editor: SQL query
   • Pop-up list: SELECT Item as BookReviewer, ReviewerID as BookReviewerID from Reviewers
   • Save all listed fields: checked
      
2. Create field BookReviewerID
   1. Type: Number
       
3. Create field DateSent
   • Description: Date the review request was sent to the reviewer
   • Type: Date
   • Default value: int(now)
   • Auto-assignment rules: A: [FollowUpDate] = [DateSent] + 60 | E: [FollowUpDate] =
      
4. Create field FollowUpDate: Will be shown in yellow when a follow up is required (such as when the review is late)
   • Type: Date
   • Conditional Formats:
       Criteria: [ReviewLateNbDays] > 0 and [Done]=0
       Formats: BackColor=&H00FFFF
      
5. Create field ReviewLateNbDays: Will be shown in yellow when the review arrived late
   • Description: Nb of days the review is late
   • Type: Number
   • Row Equation: = datediff("d", [FollowUpDate], now)
   • Auto-update checked
   • Conditional Formats
       Criteria: >0
       Formats: = BackColor=&H00FFFF

2. Creating Reviews Management Grids

2.1 FollowUps Grid

• List all late reviews (i.e. FollowUpDate is past and not done)
• Sorts reviews by how late they are
• Book name is shown as a context parent (it could also be shown as a column if desired)
• Sub-items show the call log
• CallDate at the review and book levels show the most recent call

 2.2 BookReviews Grid

2.3 Overview Grid

1. Create a new grid, named Overview
2. View > Grids and right-click on Authors. Select Copy View.
3. Back in the Overview grid, right-click on the tab and select Paste View
4. The Authors grid will be shown in a pane to the right edge of the Overview grid
5. Repeat the above procedure for the other grids
6. Rearrange panes to built a set-up close to the fist screenshot at the top of this page
   (The end result will depend on how panes were actually rearranged)

2.4 Adding a Pivot Chart

1. Open the Authors grid
2. If the Overview is grid is already open, this will move focus to the Authors pane, which is not what we want. Hold Shift when opening the Authors grid to open a new copy
3. View > Pivot Chart
4. Show fields and drag Item to the data area. Drag the AuthorCountry field to the x-axis
5. Choose the chart type. In this case, a Pie chart was chosen
6. Show the Legend
7. Right-click on the tab and select Copy View
8. Switch back to the Overview grid (or open it if closed)
9. Right-click and select Paste View
10. Drag the pane to move it if desired
     

3. Using the Book Review App

3.1 Managing Authors

• The column Item contains the author's full name
• The column Country (field AuthorCountry) is an auto-list:
  • Enter the country in the text box, or
  • Press F2 to show a pop-up list of current values:
    
• As the number of authors increases, the list can be filtered using column filters

3.2 Managing Reviewers

• The ReviewerID is an auto-number, so no need to edit it manually.
  It isn't really used in this example but is there to show how to normalize the data.

3.3 Managing Books

• Enter the title in the Item column
• Move to the Author column and press F2. The list of authors will be shown:
  
  Select the author and both the BookAuthor and AuthorCountry fields will be automatically filled
• Enter the date the book was received for review
• Update the Status and Outcome columns (i.e. fields) as the review process goes on

3.4 Managing Book Reviews

• Add a sub-item (Ctrl + Enter or use the Review form)
• Enter the date sent.
• The DateSent field has an auto-assignment which will automatically fill the FollowUpDate to be 60 days later. You can change that default or manually change the FolowUpDate
• Move to the Reviewer's name column and press F2. The list of reviewers will be shown:
  
  Select the reviewer and both the BookReviewer and BookReviewerID fields will be automatically filled
• Update the Status and Outcome columns (i.e. fields) as the review process goes on
• The Late by column (field ReviewLateNbDays) automatically updates as time goes by (its row equations has Auto-Update checked). It is recalculated on each change of day
• When the review is done, do Item > Mark as Done (Ctrl + M) to fill the done date
• Both FollowUp and Late by cells will turn yellow when a review is late. Once the review is done, the FollowUp column yellow color will be removed as no follow up is required
• For each review, sub-items can be added to track that review (calls, documents sent, etc)
• The review item's HTML pane can be used to hold the reviewer's evaluation:
  
  (a little bit of humor here, though based on a true story. See J.K. Rowling post here)
   

3.5 Doing Follow-ups

• Call the reviewer
  • Calls are entered as sub-items of the review. You can use / customize the Call form to quickly enter call information / log
  • Call date is automatically entered and bubbles up to the parent items, so you see when the last call was made
    (avoids the embarrassment of calling a reviewer twice)
• Send an automated email, reminding he or her about the deadline. To do this:
  • Create a template item and enter the email content in the HTML pane. You'll probably need 2 templates, one for a coming deadline, and one for a past deadline
  • See 6. Example : Generating Invoices and -- link to nonexistent node ID 884 -- for details
• The grid shows reviews that are not done and have a past follow-up date. This can be fine-tuned for individual needs. A column filter could be used, but in this example, a source filter was used instead, as it has more advanced filtering capabilities
  To do so:
  • Grid > Source Bar
  • The current filter is: FollowUpDate<=Now and done is null
  • To show the coming deadlines, next week for example, the filter would be ReviewLateNbDays<0 and ReviewLateNbDays>-7 and done is null
  • The source filter has a dropdown button which shows recently used filters (press Delete to clean up the list if need be)
      

4. Building upon this Sample

• Color-code items based on user-specified criteria
• Show reviews on the Calendar. Color coded, such as the start of the review could be shown in green and the due date in red
• Use the Gantt chart to show a graphical view
• Use a Timeline view when the number of reviews increases, as a more compact view than Gantt charts
• Use filtering, sorting, hoisting and searching to effectively manage all reviews
• Use Microsoft Office Pivot table and Pivot charts as a reporting tool
• Create a live connection to other applications, such as Excel, so other people can view all (or part) of the review process
• Use the HTML pane to capture reviewer's reports and eventually send these to the author
• Use Email merge to send information to reviewers, to authors, etc
• Set-up an EmailToIQ address so reviewers and authors can send information to be automatically retrieved by InfoQube