TIMSS 6: Internal Staff Listing

We wanted to generate an internal staff listing with phone numbers, room numbers, departments, pictures & bios from TIMSS. I didn't want to add department & room to the customer table since this information is only for a few customers.

So I added these fields to the SEC_USER table and the User Setup screen (SEC001) : master customer id, room, department. This way I can join SEC_USER with CUSTOMER and get all the information I need (I had already added a BIO field on the customer table). I also setup a type for department and used a validation code to populate a drop-down on the screen.

Now I just need to make sure my staff all have customer records and user ids.

Applies to: TIMSS6

TIMSS 6: "The" Company Names

In TIMSS 6, TMAR added a Prefix field for a company's name. This is where you can store the word "The" for a company's name. For example The Council on Foundations would have Council on Foundations as the the company's name and the prefix would be The. These 2 would be combined to make the label name The Council on Foundations. The search name for this customer would be COUNCIL ON FOUNDATIONS;THE. It's very similar to how first and last names are stored for individuals.

There are 2 places where you will see this Prefix field: on CUS001SF where you create a company; and CUS003 when you can edit a company's name.

If all your companies are stored this way, you don't have to search for the name with and without the "The" in the name. Thus you have less chance of creating duplicates.

There were some minor issues with this functionality, but they are fixed as of version 6.3.1.

Applies to: TIMSS6

TIMSS 6: Short-cut Keys

In TIMSS 6 there are serveal "hot keys" you can use so you don't have to use the mouse all the time (TIMSS 5 also has them). Here is a listing from the TIMSS 6 Help:

Action	Key-stroke Combination
Adding a New Record	Ctrl + N
Deleting a Record	Ctrl + D
Saving a Record	Ctrl + S
Find / QBE	Ctrl + Q (or F10)
Open Search/Binocular Screen	Ctrl + F
Navigating between screens	Ctrl + F6
Navigating between tabs	Go to the tab header and then use the left and right arrow keys to move between tabs
Close the active window	Ctrl + F4
Close the application	Alt + F4

Also, many buttons will have a letter underlined that can be used with the Alt key to "click" the button. For example the C is underlined in this cancel button:

Hitting Alt+C will be the same as clicking on this Cancel button.

Applies to: TIMSS 6

TIMSS 6: Opening IE from TIMSS

This is really cool! So if you add a button to a screen in TIMSS and put the following in the OnClick event:

Dim myTargetURL As String = "http://www.google.com"
System.Diagnostics.Process.Start(myTargetURL)

When you click on the button it will open up IE outside of TIMSS and go to the given website.





So I added a "Google" button to my Company screen (CUS001C) and put in the following for the OnClick event:

Dim myTargetURL As String = "http://www.google.com/search?hl=en&lr=&q=%22" +Myform.getformControlByName("txtCustName").text +"%22&btnG=Search"
System.Diagnostics.Process.Start(myTargetURL)

This passes the company name to Google and does a search. I added quotes (%22) around the name so it would be an exact match.

Or if you wanted to search the news instead, replace http://www.google.com/search with http://www.google.com/news in the passed URL to search for the company name in the news.

Applies to: TIMSS6

SQL Server: TitleCase function

For those of you switching from Oracle to SQL server, one function that is missing is the InitCap that makes the first letter of every word capitalized. It's a handy function to have when cleaning up data.

I have an SQL book that gives the following function that does the same thing.

CREATE FUNCTION TitleCase (@StrIn NVARCHAR(1024))
RETURNS NVARCHAR(1024)
AS
  BEGIN
    DECLARE
      @StrOut NVARCHAR(1024),
      @CurrentPosition INT,
      @NextSpace INT,
      @CurrentWord NVARCHAR(1024),
      @StrLen INT,
      @LastWord BIT

    SET @NextSpace = 1
    SET @CurrentPosition = 1
    SET @StrOut = ''
    SET @StrLen = LEN(@StrIn)
    SET @LastWord = 0

    WHILE @LastWord = 0
      BEGIN
        SET @NextSpace = CHARINDEX(' ',RTRIM(@StrIn), @CurrentPosition)
        IF  @NextSpace = 0 -- no more spaces found
          BEGIN
            SET @NextSpace = @StrLen
            SET @LastWord = 1
          END
        SET @CurrentWord = UPPER(SUBSTRING(@StrIn, @CurrentPosition, 1))
        SET @CurrentWord = @CurrentWord +
LOWER(SUBSTRING(@StrIn, @CurrentPosition+1, @NextSpace - @CurrentPosition))
        SET @StrOut = @StrOut +@CurrentWord
        SET @CurrentPosition = @NextSpace + 1
    END
    RETURN @StrOut
  END

Applies to: TIMSS5 & TIMSS6