Query Builder Enhancements

July 10, 2012Duane Myers

Design Mode:

  • Left Pane (New) – Tree view of Available Tables & Fields
  • Vertical Splitter allows you to size the Tree Pane
  • Right Upper Pane – List of Fields for the report and other Functions
  • Right Lower Pane – Criteria used to select data for the report
  • Screen size and Pane Sizes are saved and restored on Screen close/open

Tree Features: (New)

  • Click + in the Tree to see Fields and Child Tables for that table
  • Double Click a Table in the Tree to Open/Close the Tree
  • Double Click a Field in the Tree to add that Field to the Current Pane on the Right

Note: “*” will add all fields for that table.

  • If Current the Row is Empty or
  • If Focus is on the last row the field will be added to the last row of the grid
  • If the Current Row is Selected and already has Data then you will be prompted as as to what to do with the data:
  • Overwrite will replace the data in the Row
  • Insert will insert below the Selected Row
  • Append will add the data to the Last Row of the grid
  • Cancel will cancel the action

Data Action Buttons (New)

Buttons in the Column between the Tree and the Grids on the Right.

One set of buttons is for the Upper Grid and One set is for the Lower Grid

  • Append Tree data to the Associated Grid – Same Actions as “Double Click a Field in the Tree” in Tree Features above.
  • Delete Current Row or Selected Rows in the Associated Grid. Note: Multiple rows can be selected by Holding Ctrl or Shift Key and Clicking the Buttons on the left Column of the Grid.
  • Move Data Up – Move data in Current Row UP one row
  • Move Data Down – Move data in Current Row DOWN one row
  • Insert Blank Row – Insert an Empty Row ABOVE Current Row.

Tool Bar

  • Public/My Reports Dropdown list. – Used to Open/Save report to Public Area or Personal Area.
  • Open a New Report (Same as File -> New)
  • Open an Existing Report (Same as File -> Open)
  • Save Current Report (Same as File -> Save)
  • Design Mode (Same as View -> Design)
  • Results Mode (Same as View -> Data Sheet)
  • View the SQL that the report generates (Same as View -> Show SQL Test…)
  • Verify the Query – This will detect any issues with the Query (Same as Query -> Verify)
  • Query Properties - Show/Edit Name and Other options for the Query (Same as Query -> Properties)
  • Resize Form (New) – Resize the Form to Standard Size or Saved Size.

Note: Form Size settings are saved when you exit the screen and are restored during your next session.

  • Re-login (New) – re-login to a different Branch or Department.
  • Close the Screen (Same as File Close)

Results (Data Sheet)

Results Toolbar

  • Save (was disabled) – Save Results to disk (Same as File -> Save as to Disk)
  • Design Mode (Same as View -> Design)
  • Results Mode (Same as View -> Data Sheet)
  • View the SQL that the report generates (Same as View -> Show SQL Test…)
  • Print (Same as File -> Print)
  • Print Preview – Show the report in HTML format as it will be printed (Same as File -> Print Preview)
  • Export to Excel (New) – Export Data Sheet to Excel (Same as File -> Export Data Sheet to Excel)
  • Export to MS Word (New) – Export Data Sheet to MS Word (Note: Formatting is poor)
  • Email (New) – Email he data sheet – Brings up a Message box so you can select the Format for the Email Attachment that will be created. Then brings up the Standard CargoPoint Email Screen with the attachment. Below is the Message Box to select the Attachment format.
  • - Sort Descending (New) – Select a Column or Columns to sort by Clicking on the Column Heading then Click the Sort Button. NOTE: All data will be refreshed (the report will be run again) so if any rows have been manually deleted or Cleanup operation has already been performed those changes will be lost.
  • - Sort Ascending (New) – Select a Column or Columns to sort by Clicking on the Column Heading then Click the Sort Button. NOTE: All data will be refreshed (the report will be run again) so if any rows have been manually deleted or Cleanup operation has already been performed those changes will be lost.
  • Delete Row(s) (New) – Manually delete a row or rows from the datasheet. Current Row will be deleted or you can select Multiple rows by Holding Ctrl or Shift Key and Clicking the Buttons on the left Column of the Grid.
  • Cleanup (New) – Manually remove repeating data from Columns. Select a Column or Columns to be cleaned up and press the Cleanup Button. Program will start at the top of each Column and search down the column. It saves the first value it sees then moves to the next row. If the data is the same it removes it from that cell if the data is Different it keeps the data and now that data is the will be used as the Saved value and any repeats under that row will be removed. It will repeat this process for each column selected.

Query Properties

  • Branch Selection (New) – Old Selection was Show Branches Check box
  • All Branches/Depts (New) – Data will be shown for the Whole Company no matter what branch you are logged in to.
  • Current Branch + Depts – (Was Include Branches – Checked) Shows data for Current Login Branch and any Sub Departments (CN = X)
  • Current Branch Only – (Was Include Branches – Un-Checked) Shows data for Current Login Branch Only (CN = X, DN = Y)