Extender Scripting: Finding the right event

So you are writing scripts in the extender and you aren't sure which event fires when you when you want your code to be executed? Use a MsgBox to see which event fires when.

In the event you are curious about, put the following statement:

MsgBox("Test Message #1")

This will put a dialog box on the screen in TIMSS when the event fires.


You can obviously do this for multiple events, just change the message so you know which event gave the message box.

Applies to: TIMSS6

TIMSS 6: Ad-Hoc & Search Operators

I was inspired by Susannah 's WebEx on Ad-hoc Queries today. When looking at the Advanced Query in Ad-Hoc or the Search screen, there are several operators you can use. Below is a listing of them and how to use them.

Operator	Description	Example Data
BT	Between 2 values, separated by commas (no space)	1/1/2005,1/31/2005 (e.g. date between, and including, 1/1/2005 and 1/31/2005 )
ENDS	Ends with entered text	NON (e.g. customer class ends with NON)
EQ	Equals (=)	VA (e.g. state equals VA)
GT	Greater than (>)	12/31/2004 (e.g. date is greater than 12/31/2004)
GTE	Greater than or equal to (>=)	12/31/2004 (e.g. date is greater than or equal to 12/31/2004)
HAS	Data as entered text in it	Council (e.g. Name has the word Council in it)
IN	In list of values, separated by commas (no spaces)	MD,DC,VA (e.g. State is either MD, DC, VA)
LT	Less than (<)	12/31/2004 (e.g. date is less than 12/31/2004)
LTE	Less than or equal to (<=)	12/31/2004 (e.g. date is less than or equal to 12/31/2004)
NOT IN	Not in list of values, separated by commas (no spaces)	MD,DC,VA (e.g. State is other than MD, DC, VA; and not missing)
NOTEQ	Not equal to	VA (e.g. state other than VA; and not missing)
NOTNULL	Not missing	no data needed (e.g. email is not missing)
NULL	Missing	no data needed (e.g. email is missing)
STARTS	Starts with entered text	COR (e.g. customer class starts with COR)
LT OR NULL	Less than or missing	12/31/2004 (e.g. date is less than 12/31/2004 or date is missing)
GT OR NULL	Greater than or missing	12/31/2004 (e.g. date is greater than 12/31/2004 or date is missing)

When you setup the Ad-Hoc Metadata (META001), you can choose a default operator. The important thing to note here is that this is not only what will be defaulted on the Advanced Query tab, but is also the operator that is used on the Simple Query tab. If you are searching on the Simple Query tab and are unsure what the default operator is, click over to the Advanced Query tab and see what it is set to.

Here are 2 update statements to set the default operator to Starts With for views you've already added (starting with USR_) and queries you've saved (where a value isn't already defined). They only update those that are String values, not Date or Nuemeric.

begin tran
update meta_query_view_column
set operator_code='STARTS'
where view_name like 'USR_%'
and datatype_code='S'
rollback tran

begin tran
update meta_saved_query_detail
set operator_code='STARTS'
where (filter_data is null or operator_code not in ('NULL','NOTNULL'))
and exists (select 1 from meta_query_view_column c
where c.view_name=meta_saved_query_detail.view_name
and c.column_name=meta_saved_query_detail.column_name
and c.datatype_code='S')
rollback tran

Applies to: TIMSS6

Extender Scripting: Field Level Security

So tab level security isn't secure enough for you. You can use the UserId & Enabled values to see who the current user is and enable or disable fields for them.

If Myform.AppContext.UserId ="STARTUP" then
Myform.getformControlByName("cboType").Enabled="True"
Else
Myform.getformControlByName("cboType").Enabled="False"
End If

The code above will have the combo box enabled only for the STARTUP user.

To expand on this, you could write an SQL statement to see if the user is a member of a particular security group:


dim strSQL as String
dim dsGroup as Object

strSQL = "select user_group from sec_group_member "
strSQL = strSQL & "where user_group='ADMIN' and user_id='"
strSQL = strSQL & Myform.AppContext.UserId &"'"
dsGroup=MyForm.ScriptingGetDataSetWithSQL(strSQL, "SEC_GROUP_MEMBER")

If Not dsGroup Is Nothing and not dsGroup.Tables(0).Rows.Count=0 then
Myform.getformControlByName("cboType").Enabled="True"
Else
Myform.getformControlByName("cboType").Enabled="False"
End If

So if the SQL returns a value for the group ADMIN and the current user, then we enable the control, otherwise we disable it.

Applies to: TIMSS6

What is TAUG?

TAUG is the TIMSS Annual User's Group meeting. This year's meeting is being held in Savannah, Ga. It is the 7th annual TAUG & the 6th one I've been to.

They are always held in great places (New Orleans, Miami, San Diego, San Antonio, Memphis, Tucson) and TMAR always puts on a fun event.

Not only is the location great, but the sessions are pretty good, too. I'll admit that when I was new to TIMSS I got a lot more out of the sessions, but after all I've been to, I still get little nuggets that help me in my day to day work. Last year I went to Susannah's open training session where I got to pick her brain for 3 hours; that alone was worth the registration fee.

TIMSSCare has presentations from previous TAUGs on their website if you want to see the kind of things that have been done in the past.

And it's always good to see where the product is going... I can't wait to see what's happening with version 7!

But for me, the best part of TAUG has to be the networking. Many of the TMAR staff attend TAUG and you not only get to put a face with the name, but you get to talk to them and pick their brains for free.

And of course there are all the attendees: your counterparts at other organizations. I've made a lot of great contacts (and friends) at previous TAUGs. It's great to have someone to bounce ideas off of and meet people who do the same thing you do.

I hope to see you there and remember to bring lots of business cards!

Applies to: TIMSS5 & TIMSS 6

TIMSS 6: Zip Codes in Radius

Those of you using TCMS might have seen that you can search for customers within a given distance of a selected zip code when pulling directories.

Want to know how that works? Well there is a table called APP_GEO_INFO in TIMSS 6 that has the longitude and latitude of zip codes. If you know the longitude and latitude of two zip codes, you can calculate the distance between them (go here for the mathematical theory, or here).

I don't know where the data in this table came from or when the last time it was updated, but it seems to be fairly accurate. You can find places on the Internet where you can buy this data, or subscribe for updates.

So if you wanted to find all the zip codes with in 200 miles (driving distance) of your conference (in 20036), you could use this query to do it (formula from http://jan.ucc.nau.edu/~cvm/latlongdist.html):

select p1.zipcode
from app_geo_info p1, app_geo_info p2
where p2.zipcode='20036'
and acos(cos(p1.lat_rad)*cos(p2.lat_rad)
*cos(p1.long_rad)*cos(p2.long_rad)
+cos(p1.lat_rad)*sin(p1.long_rad)
*cos(p2.lat_rad)*sin(p2.long_rad)
+sin(p1.lat_rad)*sin(p2.lat_rad))*3963.1<=200

And you can use this to find all your customers within 200 miles to send them a special announcement about your upcoming conference.

Or if you wanted to create a function:

CREATE FUNCTION DBO.ZipDist(@Zip1 AS NVARCHAR(10), @Zip2 AS NVARCHAR(10))
RETURNS FLOAT AS
BEGIN
DECLARE @lat1 as FLOAT
DECLARE @long1 as FLOAT
DECLARE @lat2 as FLOAT
DECLARE @long2 as FLOAT
DECLARE @distance as FLOAT
SET @lat1 = (select lat_rad from app_geo_info where zipcode=@Zip1)
SET @long1 = (select long_rad from app_geo_info where zipcode=@Zip1)
SET @lat2 = (select lat_rad from app_geo_info where zipcode=@Zip2)
SET @long2 = (select long_rad from app_geo_info where zipcode=@Zip2)
SET @distance = acos(cos(@lat1)*cos(@lat2)*cos(@long1)*cos(@long2)+cos(@lat1)*sin(@long1)*cos(@lat2)*sin(@long2)+sin(@lat1)*sin(@lat2))*3963.1
RETURN @distance
END

NOTE: Some of the zip codes were missing a leading zero in my table

Applies to: TIMSS6

TIMSS 6: Email Address for TIMSS Error Messages

When the TIMSS 6 application encounters an error, you will receive the following dialog box.

One of the options is to Send Error Report. Clicking this button will create an email message with the error message and a screen shot when the error occurred. You can edit this email just like any other email message sent from TIMSS.

To change the default TO: for this message, edit the ..\\Services\TimssInterfaceServer\TimssInterfaceConfig.xml file. In the EmailAddressForError section, put in the email address you want these messages to be sent to.

Instead of having them go directly to TIMSSCare, you could have them go to your organization's helpdesk software.

Applies to: TIMSS6