Rob's TIMSS Blog

Moving...

2009-05-04T13:52:00.003-05:00

I am moving my blog. We have created a social site for TAUG that I have moved my posts to and new posts will be made there.

http://tmar.higherlogic.com/

Your login is using our Single Sign-on and should be the same as you would have used to register for TAUG.

Come on over and setup your profile and create your own Personify Blog!

Applies to: TIMSS5 TIMSS6 & Personify

Personify Hot Keys

2008-12-17T16:41:00.001-05:00

Yes Virginia, there are hot keys in Personify.

F9 – Opens Audit Info screen
Ctrl+S – Save
Ctrl+F – Takes to Search screen with the results that are already available
Ctrl+R – Takes to Search screen after clearing the results and the filters that were applied earlier
F5 – Refresh
F1 – Accesses the context-sensitive online help

Applies to: Personify

TIMSS 6: QAS Address Validation

2008-12-05T12:16:00.000-05:00

Looks like there is another TIMSS blogger out there. Nice job Flavio!

How to Enable QAS Address Validation with TIMSS

Applies to: TIMSS 6

Notification with Calendar Attachement

2008-11-12T16:43:00.002-05:00

So you are sending an order confirmation to your meeting attendees when they register (using Personify's Notification Services) and you want to attach an Outlook ICS file to put the meeting in their Outlook Calendar. All you need to do is have a notification event for that meeting and attach the ICS file to the notification email.

An ICS file is a simple file defining you event. Details can be found here: http://en.wikipedia.org/wiki/ICalendar.

Create you event on your calendar, then click on Actions, Forward as iCalendar. The file in the email is an ICS file that you can attach to your notification emails.

If you open the ICS file in NotePad it will look something like this:

BEGIN:VCALENDAR
PRODID:-//Microsoft Corporation//Outlook 11.0 MIMEDIR//EN
VERSION:2.0
METHOD:REQUEST
BEGIN:VEVENT
DTSTART:20081112T140000Z
DTEND:20081114T210000Z
LOCATION:Somewhere Sunny
TRANSP:OPAQUE
SEQUENCE:0
X-MICROSOFT-DISALLOW-COUNTER:TRUE
UID:040000008200E00074C5B7101A82E008000000008014BB16AD44C9010000000000000000100
000002EA11601D9E28D4A9CEE04CA5EDE5162
DTSTAMP:20081112T151124Z
DESCRIPTION:Here are the details of my Annual Meeting\n
SUMMARY:Annual Meeting
PRIORITY:5
X-MICROSOFT-CDO-IMPORTANCE:1
CLASS:PUBLIC
END:VEVENT
END:VCALENDAR

Not to complex.

Applies to: Personify

The Personify OPEN Summit

2008-11-04T16:13:00.000-05:00

So have you been wondering what I've been doing since joining TMA Resources? Well now is your chance to see: come to the Personify Open Summit, December 1st & 2nd in Washington, DC at the Ronald Reagan Building & International Trade Center.

If you were at the ASAE annual meeting in San Diego back in August, you saw the launch of Personify Open, which promoted the open API model, the Personify platform, and our partners who are integrating their products with Personify. The Personify Open Summit takes things a step further by providing association professionals the opportunity to learn how the open API model is revolutionizing the way associations conduct business.

Keynote speaker JP Morgenthal, tech evangelist and emerging technologies thought leader, will kick off the Open Summit with a presentation on the value of the open API model and how this model is revolutionizing the way associations conduct business.

Then several TMA Resources’ solutions partners will demonstrate how they have used this model to build out-of-the-box integrations between their products and Personify. As the Partnership Manager, my job is to get partners to build standard out-of-the-box integrations with Personify. We will have partners show their integrations with content management systems, email marketing, exhibition management, social neworking applications, and online customer support.

The second day of the Personify Open Summit is designed for developers and those interested in getting into the nuts and bolts of the Personify platform. Similar to the Developer Day held at the TAUG conference in Louisville, this course will provide a hands-on, in-depth technical tour of Personify. There will be intro and advanced tracks.

You can register for just the first day or for both. Learn more at http://www.tmaresources.com/opensummit.

Hope to see you there!

Applies to: Personify

Personify: Spell checking a Text Box

2008-10-08T13:39:00.002-05:00

So you have added a text box to a form in Personify so your users can type in a paragraph about something or other. Wouldn't it be great if you could spell check what they have entered?

Well you can.

Put a button on the form and for the command use:

cmd://Form/DoSpellCheck?ControlName=name of your control

so if you added a text box called txtCompDescr, the command would be:

cmd://Form/DoSpellCheck?ControlName=txtCompDescr

This opens a dialog box that spell checks the data in the control.

Applies to: Personify

Running Personify on a Mac

2008-09-23T16:02:00.001-05:00

Can you run Personify on Mac OS X? Nope. That's usually not a problem as the people who use Macs at an association usually don't need access to the AMS.

However, if you really need to have Personify (or TIMSS) on a Mac, there is a program for you can buy that allows you to run Windows applications on a Mac called Parallels Desktop for Mac: http://www.parallels.com/ .

You don't even have to reboot your machine as it runs in "parallel". And since it is an actual install of Windows and not just an emulation, it works just like the real thing. For $80, it a bargain.

Applies to: TIMSS5, TIMSS6, & Personify

Personify: Passing info to a Web browser URL

2008-09-10T17:18:00.000-05:00

One of the things I like to show off with the Partner integrations in Personify is pulling up a customer in Personify and having a tab that has a Web browser window which pulls up that customer's information in the partner application.

This is done by simply creating a tab with a Web Browser control on it. The key is in the Code behind the screen and when calling the Open method with the the master customer id added to the URL.

WebBrowser1.Open(String.Concat("http://tma.higherlogic.com/Personify.aspx?MasterCustomerId=", Context.MasterCustomerId, "&SubCustomerId=", Context.SubCustomerId))

So this will open a tab with the current customer's information. VERY COOL!

Obviously the URL you are calling will need to know what to do with the master customer id to pull display their informaiton. You will also want include code to refresh the browser window when you change customers.

Applies to: Personify

VeriSign --> PayPal

2008-05-09T15:15:00.001-05:00

In 2005, PayPal acquired VeriSign’s payment gateway product Payflow Pro, which is integrated into TIMSS and Personify. As a part of the re-branding from VeriSign to PayPal they are updating their host URLs

- The new pilot host URL is: pilot-payflowpro.paypal.com
- The new production host URL is: payflowpro.paypal.com

The verisign.com host URLs are still available and will be until 2010. However, they suggest that you update your application(s) to the new paypal.com domain host URLs at your earliest convenience.

Applies to: TIMSS6 & Personify

Personify Idea Manager

2008-03-26T17:05:00.004-05:00

On many of the screens in Personify you will see a light bulb at the top right with the Search, Save, & Refresh buttons.

This icon is for the Personify Idea Manager. All it does is allow users to send emails to a specific address. If your users are working on a screen and have an idea, they can click on the light bulb to send an email. The To: address is controlled by a system parameter: IDEA_MANAGER_DEST_ADDRESS.

TMA Resources uses it internally for ideas related to Personify, however you could obviously reuse it for something else, setting the email address to one that would is more meaningful for you.

Applies to: Personify

CUS_DUP_MERGE_TABLE

2008-03-12T12:59:00.002-05:00

When merging 2 customer records, TIMSS 6 & Personify use a lookup table to see which columns in which tables should be moved to the retained customer: CUS_DUP_MERGE_TABLE & CUS_DUP_MERGE_COLUMN.

There have been a couple postings on the Yahoo Group about tables not being merged. For them to be merged the table and associated column must be in these tables with the appropriate action (MERGE/DELETE).

To see what tables are missing, I wrote the following query:

select c.table_name, c.column_name
from INFORMATION_SCHEMA.COLUMNS c, INFORMATION_SCHEMA.TABLES t
where c.DOMAIN_NAME='CUSTOMER_ID' -- user defined data type
and c.table_name=t.table_name
and t.table_type='BASE TABLE'
and not exists (select 1 from dbo.CUS_DUP_MERGE_TABLE d
where d.table_name=c.table_name)
order by c.table_name, c.column_name

This looks for tables in the database schema that have a column with a type of CUSTOMER_ID that are not in the CUS_DUP_MERGE_TABLE table.

If the table (and column) is missing, then the table will not be merged. If it's a table added by you, or one that was added by TMAR but was not added for some reason in an upgrade, it needs to be added for the merge to work correctly.

This will return a lot of temporary tables. Including all those tables will impact performance of the merge process, so I would only add the ones that you really need to be there. For example, if you use the ORD660_Processed table to generate a letter using a Crystal Report and need that data to remain consistent after records are merged, add it.

Applies to: TIMSS6 & Personify

Meeting Packages

2008-02-18T08:50:00.001-05:00

When we setup the TAUG08 meeting product, we wanted to automatically include some sessions on the order like the Welcome Reception, Ask TMA Resources, A Night at the Races, etc. To do this, we created a package with all the sessions we wanted to include on the order.

Then we included the package on the rate codes for the meeting.

And since it's done for the rate code, different components can be added depending on the rate you get. So members (who get the Member rate) can get one package of included sessions, and non-members (who get the List rate) can get a different package.

Applies to: TIMSS6 & Personify

Personify Enhancements

2008-02-08T16:53:00.000-05:00

Now that I'm on the "inside" I can tell you a bit about how product marketing works to get new features into the product. If you have heard of Pragmatic Marketing, that is the methodology we are following.

We are using a product called FeaturePlan to collect data about what should go into the product. Enhancement requests that are submitted though the customer service portal, items you tell your PM about, things our sales people hear from prospects, market reserach and competitive analysis are all entered in and linked to problem statements. Problem statements receive a score based on all those inputs.

Problems statments that have high scores then get requriements gathered by product marketing, and designs are created from these requirements to go into the product.

How can you help? Tell us what you want.

Not only by submitting enhancement requests, but also volunteering for customer reserach. In many cases we will send questions to the Yahoo Group and/or ask for input from customer directly. Please help us make a great product.

Most recently we launched a new and improved the Notifications module for Personify. I sent an email to the Yahoo Group asking for information on notifications used and wanted. Those who responded had input into how the design was done and were invited to see a prototype and give more feedback. Then when the design was done, they were invited back to view the finished product and see if we got it right.

Applies to: Personify

TAUG 2008

2008-01-22T10:33:00.001-05:00

Is it really TAUG time again? Boy time flies.

Now that I'm on the "inside" I can see this year's TAUG will be one of the best. Not only do we have some great session planned, but a lot of fun activities. TAUG is a great opportunity to learn about TIMSS & Personify, and to meet your counterparts at other organizations (putting faces to the names from the Yahoo Group).

So go to TAUGConference.com now to register. The creative people in our Marketing Department are determined to make Sean C. and myself into cartoon characters with pages like Just Ask Rob & Where in Louisville is Sean? Those pages will be updated weekly so you can have a good laugh at our expense.

You will also find information on the TAUGConference.com site about how we are trying to make TAUG green. You may have seen the post from Lauren R. that we will buy a lunch for those who decide to drive to TAUG instead of fly.

Applies to: TIMSS5, TIMSS6, Personify

TIMSS 6: Adding views to the Search Screen

2007-12-24T16:41:00.000-05:00

In TIMSS 6, you can add views to the APPQUERYSEARCH screen. First create the views you want to search with. Make sure the view includes the MASTER_CUSTOMER_ID and SUB_CUSTOMER_ID.

Next use Extender to add to the OnOpening event of the screen you want to add the view to the search. If that's CUS001, then the code would look like this:

Public Sub CUS001C_OnOpening ( ByRef MyForm As Object)

MyForm.SearchViewNames =
“Cus_Address_Alias_C_VW,Cus_OSR_Relationships_VW,
Cus_ISR_Relationships_VW,Cus_Proj_Mgr_Relationships_VW”

End Sub

Applies to: TIMSS6

Google Maps

2007-12-13T10:39:00.001-05:00

So you've added a Google search button to a screen to search for a customer. Want to get launch Google Maps from within TIMSS/Personify? Do the same thing (launch a browser window) only pass the address in the URL string.

http://maps.google.com/maps?f=q&hl=en&q=1919+gallows+rd+22182

The information in the URL above after "q=" is the address we are querying on with pluses instead of spaces.

Want directions? Then you’d use the start address (SADDR) and destination address (DADDR) to pass both addresses.

http://maps.google.com/maps?f=d&hl=en
&saddr=1919+gallows+rd+22182
&daddr=1600+Pennsylvania+Ave+NW+20006

Applies to: TIMSS6 & Personify

Logging TCMS Users

2007-12-03T08:07:00.000-05:00

This one comes from my replacement at COF: Russ, but I've heard requests for this functionality at TAUG.

So you want to track customer logins to TCMS, but the only information stored in the WEB_USER table is their last login. If you put a trigger on that table to populate a log table (or even CUS_ACTIVITY) every time the last login time changes, you've done it as this field is updated every time the customer logs in.

Not only can you track the logins, but using the same methodology you can track changes to user names and passwords also stored in that table. You can also track the MODOPER to see who made the change: the customer or a staff member.

Applies to: TIMSS6 & Personify

Min & Max Adjusted Price

2007-11-28T09:19:00.001-05:00

When setting up a product's pricing, you may have noticed fields for the Minimum & Maximum Adjusted Price. These fields are used in conjunction with the Short Pay code.

When the Short Pay code is REJECT, you can't edit the field, but BOTH the Min and Max are set to the full price of the product. Thus for the order line to be Active, the full price has to be paid.

When the Short Pay code is AR, you can't edit the field, but the Max is set to the full price of the product and the Min is set to zero. Thus for the order line to be Active, anything can be paid, and the remainder is a receivable on the order.

When the Short Pay code is ADJUST, you CAN edit the field. The Max is the largest amount that the price will be adjusted up to on an order. For example if the price is $100 and the Max is $200 and a customer pays $150, the price of the line item will be adjusted from $100 to $150. However, if they paid $500, the price of the line item would only be adjusted to $200.

The Min is the smallest amount the price can be adjusted to for the order to be active. For example if the price is $100 and the Min is $50 and a customer pays $75, the price of the line item will be adjusted from $100 to $75. However, if they paid $25, the price would not be adjusted that low and the order would not be made Active.

Applies to: TIMSS6 & Personify

.NET WebService Studio

2007-11-12T13:42:00.000-05:00

Looking for a good tool to call Web Services? Try the .NET WebService Studio 2.0. This tool will

...invoke webmethods interactively. The user can provide a WSDL endpoint. On clicking button Get the tool fetches the WSDL, generates .NET proxy from the WSDL and displays the list of methods available. The user can choose any method and provide the required input parameters. On clicking Invoke the SOAP request is sent to the server and the response is parsed to display the return value.

It can be found here.

Applies to: TIMSS5, TIMSS6, & Personify

Module Abbrivations

2007-11-05T19:16:00.000-05:00

Here is a listing of the 3 letter abriviations for the diffent modules in TIMSS & Personify:

ABC - ABC Subsystem
ABS - Abstract Subsystem
ADR - Address Validation Subsystem
ADV - Advertising Subsystem
APP - Application Subsystem
AUD - Audit Logging Subsystem
BPA - BPA Subsystem
CCP - Credit Card Processing Subsystem
COM - Committee Subsystem
CPN - Coupon Subsystem
CRT - Certification
CUS - Customer Subsystem
ECD - Electronic Content Delivery
EFT - Electronic Fund Transfer
FAC - Facility Subsystem
FAR - Accounting Subsystem
FGL - Financial General Ledger
FND - Fund Raising
INV - Inventoried Products Subsystem
LCK - LockBox
MBR - Membership Subsystem
META - Application Metadata
MISC - Miscellaneous Subsytem
MKT - Marketing Subsystem
MRM - Call Center Subsystem
MTG - Meeting Subsystem
ORD - Order Entry Subsystem
PCK - Package Subsystem
SEC - Security Subsystem
SPK - Speaker Subsystem
SUB - Subscription Subsystem
TRN - Transcript
TRS - TIMSS Reporting System
UAR - Un-applied Receipt
USR - User Defined Subsystem
WEB - TIMSS Web (TIMSS e-Commerce)
XBT - Exhibition System

Applies to: TIMSS6 & Personify

Customer URL Format

2007-10-29T09:02:00.000-05:00

In TIMSS and Personify, you can store a URL for a customer which can be up to 100 characters long (data type of EMAIL_ADDRESS).

The question often comes up if one should store the URL with the leading http:// or leave that off. I found it best to add it in, that way whenever I'm working with the URL most programs will recognize it as such and make it a link.

However, because the field only holds 100 characters and some URLs are very long, you might want to save the extra 7 characters an not include the http://.

The most important thing is that your data is consistent. If all the URLs don't have the http:// you can always add it in later when pull the data.

Also if you find yourself continuously having URLs that are too long, you can always change the data type for EMAIL_ADDRESS to be longer.

Applies to: TIMSS6 & Personify

TIMSS 6: Inactive Codes in Drop-Downs

2007-10-22T08:54:00.000-05:00

I have heard many complaints about inactive codes being displayed in drop down boxes in TIMSS. These are displayed for 2 reasons. First, it allows you to search for codes that are inactive. Second, when an inactive code is actually used, the description will be displayed, otherwise the field would be blank.

What you can do to clean up this list is change the display order on the inactive codes. In types and codes, when you change a code to inactive, also set the display order to something like 99 so it will be displayed at the end of the listing.

Applies to: TIMSS6

Free DNN Training Videos

2007-10-08T15:58:00.000-05:00

Personify e-Business was created using DotNetNuke (DNN) version 4. On the DNN site (http://www.dotnetnuke.com/) there are some free training videos to get you familar with the DNN product and how to work with it.

They are actually on DNN 3, but it's similar to 4.

Applies to: TIMSS6 & Personify

Search Phone

2007-10-01T16:50:00.000-05:00

In Personify and TIMSS 6, in the CUS_COMMUNICATION table, there is a column called SEARCH_PHONE_ADDRESS. This field is the phone number with the formatting stripped out. For example,

7035645200

instead of

(703) 564-5200

In both applications you can customize search screens. You can add this field to your search screens so that your CSRs don't have to enter the formatted number when they want to search by phone number.

Applies to: TIMSS6 & Personfiy

Personify: Copying Customer Information

2007-09-24T09:14:00.000-05:00

So you pull up a customer on the Call Center screen in Personify and you want to copy the customer ID to the clipboard so you can paste it somewhere. You can't highlight it so what do you do?

If you right click on the customer ID, a little menu pops up with one option: Copy. This copies it to the clipboard so you can paste it.

This also works on the Address and Phone information on the screen.

Applies to: Personify

Name Prefix, Suffix & Credential

2007-09-17T17:08:00.000-05:00

In TIMSS 6 and Personify, an individual's name is made up of different parts, including Prefix, Suffix & Credential fields that are based on types and codes. The types are NAME_PREFIX, NAME_SUFFIX, CREDENTIALS, respectively. You CAN use periods in the code.

The prefix and suffix are selected from a drop down on the customer name screen. There are options in the System Parameters (USE_PREFIX_DESCR_LBL & USE_SUFFIX_DESCR_LBL) to determine if the code or it's description should be used in the label name.

For credentials, there is a look up on the customer name screen because you can select more than one credential (use the crtl key to select more than one). The code is used in the label name, and the description is shown to define the credential (i.e. CPA=Certified Public Accountant).

As on all codes, you can set a Display Order to control how these are displayed. You can use this to display the codes with common values first or in a logical order (Jr, Sr, III, IV, etc). If you have a long list of codes, it's probably best to display them alphabetically so your users can easily find what they are looking for.

Applies to: TIMSS6 & Personify

Personify: Web Services

2007-09-10T08:49:00.000-05:00

Personify comes with 2 types of Web services: Universal Web services & Simple Web services.

The Universal Web service essentially wraps the Personify API for Web Service access. You can do just about anything with the Universal Web service, but it may take some knowledge of the Personify application. The Universal Web service is the most flexible approach but also the most complex. In general, it will return whatever you are looking for (including extender fields) but will return a lot of things you probably aren't looking for.

The Simple Web service is designed to do one thing simply. These Web services have simple parameters and simple return values. They are straight forward and easy to use but can’t do everything. The list of available Simple Web services will grow over time based on market research.

Applies to: Personify

Data Integrity: Gender & Prefix

2007-09-03T07:08:00.000-05:00

One script that I ran on a regular basis had to do with a individual's gender and prefix. In general, they should agree and when they don't, something is wrong.

select master_customer_id, label_name, gender_code, name_prefix, formal_salutation, nickname
from customer
where customer_status_code='ACTIVE'
and ((gender_code='M' and name_prefix in ('Ms.','Mrs.','Miss'))
or (gender_code='F' and name_prefix in ('Mr.')))

If I had them all agreeing and then one day there were some that didn't agree it would mean that either someone had just created the record and entered one wrong or someone had changed the name of the customer (instead of creating a new customer record) and changed one but not the other.

Some further updates I did with these fields would be populating null values, for example if the prefix was Mr. then the gender is M.

update customer set gender_code='F'
where record_type='I'
and gender_code is null
and customer_status_code='ACTIVE'
and name_prefix in ('Ms.','Miss','Mrs.')

update customer set gender_code='M'
where record_type='I'
and gender_code is null
and customer_status_code='ACTIVE'
and name_prefix in ('Mr.')

You could also do the opposite, updating the prefix (and label name, search name, formal salutation, etc.) when the gender is known but the prefix is missing.

Applies to: TIMSS5, TIMSS6, & Personify

PCI Compliance

2007-08-20T07:34:00.000-05:00

Are TIMSS and Personify PCI compliant? That's not the right question. What you should be asking is if you, as a merchant, are PCI compliant.

Much of the Payment Card Industry Data Security Standard (PCI DSS) requirements focus on the security of your network, and having your customer's information protected from external and internal hacks.

Credit card numbers are stored encrypted in TIMSS and Personify, and future version may not even store credit card numbers. But if you have faxed in order forms laying around the office with credit card numbers on them, then you have a problem and no software can protect you.

Here are some links so you can find out more and what you need to do:
https://www.pcisecuritystandards.org/
http://www.pcicomplianceguide.org/
http://usa.visa.com/merchants/risk_management/cisp.html?ep=v_sym_cisp
http://www.owasp.org/

Applies to: TIMSS5, TIMSS6, & Personify

Personify: Debug Tracer

2007-08-07T07:16:00.000-05:00

Any of you who have been using TIMSS for a long time will remember (and may still use) the router32.log file which captured all of the SQL statements that were executed by the TIMSS 4.x & 5.x applications. The purpose of this log was to capture what was happening when an error occurred. But the real value I found was learning what pieces of data were stored where by reviewing these SQL statements. It not only helped me understand what was going on, but also where data was stored when I needed to report on it.

So in Personify, one of the Debug tools is the Debug Tracer (under Tools, Debug, Debug Tracer). This opens a window that logs the commands and SQL statements that are executed by Personify. Very cool. Normally it's not running to improve performance, but you can turn it on to see whats happening on a particular screen.

Applies: to Personify

TIMSS 6: Personalized Standard Letters

2007-07-30T13:55:00.000-05:00

In TIMSS 6, you can create standard letters to be used under Contact Tracking. What you might not know is that you can setup Word documents to have merge fields in them so that you can have that standard letter personalized for a customer.

First you will need to create a template Word document. The standard letters use the CUS_PRIMARY_INFO_VW to pull data into the document, so you need to create a document with fields based on this view.

Open APP100, Ad-Hoc QueriesClick on the ‘Build New Queries’ tab

Highlight the view ‘CUS_PRIMARY_INFO_VW’

Within the pane, ‘Fields to Display’ reflect the appropriate fields from the ‘Fields Available’ pane that are within the Word document you will be using.

Click on the ‘Run Query’ button

Enter a valid value within any of the search fields and click on the ‘Query’ button

Once data is displayed within the ‘Search Results’ pane, click on the ‘WordMerge’ button

When asked if you want to open an existing document, choose NO

Create your document, inserting the appropriate Merge fields and save it.

Next setup the Standard Letter with the Word Document.

Open MRM004, Standard Letter Setup

Click on the appropriate Letter Type and click on the “Add Document” button

Select the appropriate word document (the one you just created)

Enter an ID in the field labeled “ID” – this is a user-defined value

Confirm that the correct Application is reflected (if you don’t see the correct application listed within the drop-down options, go to System Types & Codes, Type = APPLICATION_TYPE and add “MSWORD” as a Code value. You’ll need to reopen MRM004 to see this newly added Code.)

Confirm that the correct Letter Type is reflected

Within the field “View Name” select the row labeled “Customer Primary Information”

Confirm that the file name is correctly reflected

Enter an appropriate description of the document/its usage in the field labeled “Description”

Enable the “Personalized” check box

Hit Save to upload the document

Now you are ready to go! Open Contact tracking, pull up a customer, and create an inquiry. When it's created, you can choose the standard letter you just created (the ID from step 13 above is what's displayed), then click on the Open button. This will open Word with your letter and the merge fields. In Word, click on the View Merged Data button and you will see your personalized letter.

Applies to: TIMSS6

TIMSS 6: Active Customer Statuses

2007-07-23T21:35:00.000-05:00

Starting in TIMSS 6.2.2, you can have multiple customer status codes that are active. Previously, there was only one active status: ACTIVE.

In System Types and Codes, the CUSTOMER_STATUS code is determined to be active based on option 2. Those that are active have ACTIVE in option 2. Those that are not active should have an option 2 of INACTIVE.

Now you can add your own statuses, like PROSPECT or VIP, to help distinguish certain types of customers. Please note that if you take advantage of this you will need to update any custom process and reports that you've created to also check the option 2 value of the CUSTOMER_STATUS to make sure you are pulling the active statuses.

Similarly, option 3 of the CUSTOMER_STATUS controls if customers with this status can place an order with a value of Y or N. This will default the Can Place Order flag on the customer record when the customer is created or when the customer's status is changed.

Applies to: TIMSS 6

TIMSS 6: Highlighting Data Behind a Button

2007-07-16T09:35:00.000-05:00

If you have created a button with that opens a window, wouldn't it be nice to tell your user if there was data behind the button without clicking on it? You can through extender. When you load the screen, check to see if there is data behind the button. If there is, change it's attibutes to highlight the fact that there is data behind it. For example, if you had a button that opened a comments screen that had data stored in a table you created, in the appropriate event put:

strSQL = "Select * from USR_CUS_COMMENTS where master_customer_id='" & oMaster.Text & "' and Sub_customer_id=" & CInt(oSub.Text)

oRecDS = MyForm.ScriptingGetDataSetWithSQL(strSQL, "USR_CUS_COMMENTS")

If Not oRecDS is nothing Then
If oRecDS.Tables(0).Rows.Count > 0 Then
oButton.ForeColor = System.Drawing.Color.Red
Else
oButton.ForeColor = System.Drawing.Color.Black
End If
End If

Applies to: TIMSS6

TIMSS 6: Call Center User Areas

2007-07-09T19:15:00.000-05:00

We had an issue where a product's orders weren't showing up on the Active Orders user area on the call center. Turns out the meeting date was incorrect and in the past so it didn't show up.

You hopefully know that each user can pick what is displayed in these three areas on the User Preferences screen (SEC001A):

ACTIVEORDERS- Display Active (Paid) Orders Info
ACTIVITY- Display Customer Activity Info
COMMITTEEMEMBERSHIP- Display Committee Membership
CONTACT- Display Contact Tracking Info
CURRENTMKTCODE- Display Current Market Codes Info
MEMBERSHIP- Display Membership Info
OPENORDERS- Display Open (Un-Paid) Orders Info
RELATIONSHIP- Display Customer Relationships
SEARCHRESULTS- Display tree of search results (all the customers returned from query search)
USEURL- Use a customer supplied URL

Much of the call center user area data is retrieved using the MRM001_CustInfo_SP stored procedure. Looking at this code, you can see how the data is returned and what table/view TIMSS is looking at to populate these areas.

Please note that if you make changes to base stored procedures or views, TMAR may overwrite them when you upgrade.

Applies to: TIMSS6

Changing Teams

2007-07-02T09:01:00.000-05:00

I've gone to the other side.... I left COF and I'm now working for TMA Resources.

I wanted to get out of COF and Edi was nice enough to give me a job. I'm working for Paul Gannon in Marketing as the Partnership Manager. That role will have me working with partner companies like Syscom Services and Boxwood Technology, helping them work with TMAR and integrate with Personify.

One of the goals is to come up with a standard toolbox for partners to use in their integrations with Personify. I'll need to talk to not only these partner companies, but TMAR clients and others outside of the family to see what should be included in the toolbox.

Wish me luck, and I might be calling you asking what you think we might want to include.

And yes, I plan to continue blogging about TIMSS & Personify.

TIMSS 6: Base Crystal Report Naming

2007-06-25T20:32:00.000-05:00

There are a ton of Crystal Reports that come with TIMSS 6. Several of them are similar and are classified/named in a particular fashion.

This first distinctions are for Trade Associations and Professional Societies. Trade Association version will have a C after the report number (for company), whereas reports for Professional Societies will have a P. C reports will focused on companies, and P reports will focus on individuals. Both will be sorted/grouped appropriately.

The second distinctions are for Internal (I) and External (E) version of the report. Internal versions are meant for internal use only. External versions are for distribution outside of your association and not all customer data is to be shared outside. Thus the external version should check the Publish flag on communications (i.e. phone/email) and the Confidential flag on addresses:

CUS_COMMUNICATION.PUBLISH_FLAG='Y'

CUS_ADDRESS_DETAIL.CONFIDENTIAL_FLAG='N'

In general, reports for orders are considered internal and directories are external.

Applies to: TIMSS6

TIMSS 6: More Ad-Hoc Query Security

2007-06-18T16:57:00.000-05:00

It was pointed out to me that there is another way to add security to Ad-Hoc Queries. On the META001 screen where one sets up views for use with ad-hoc queries, there is an Available for Public check box. If you un-check this box, the Special Security button is activated, and you can select groups that can use this view for queries.

When a user goes to Ad-Hoc Queries they will only see queries that use views that they have access too. This is different from my previous post that puts security on the reporting category in Ad-Hoc Queries.

Applies to: TIMSS6

TIMSS 6: The Magic of CUS500

2007-06-11T10:20:00.000-05:00

The CUS500 TRS report can be used to print a variety of labels. In System Types & Codes, there is a type for LABEL_FORMAT with codes corresponding to the various labels available. These correspond to Crystal Reports on your TRS. So if you run the report with the AVERY5160 label format parameter, the process will run the CUSLABEL_AVERY5160.rpt report on your TRS. If you don't like the layout of the labels or the font, that's the report you'd update.

And if you wanted to add a new label format for name badges or tent cards, just add the appropriate code and the corresponding Crystal Report to your TRS.

You can also pull all kinds of data into this report. It is based on the CUS500_VW so you can use the filter on the Advanced Job Parameters tab to select any customers you want: in a given state, or participants in a meeting, or committee members (the latter two would have an exists in the filter query and can have addresses other than their primary; see below).

There are 3 parameters for this report: Label Format- the layout of the report from code described above; Sort Order- to sort the report based on a field; Select Primary Address Only- Y for only their primary address, N to print all their addresses (for committee or ship to addresses other than primary, you'd choose N and then filter with exists to select the correct address).

You will probably want to have 2 runs for your labels: one that pulls GOOD addresses, and one that pulls BAD address so you can see who won't be getting your mailing.

Applies to: TIMSS6

TIMSS 6: Add Application Parameters

2007-06-04T12:16:00.000-05:00

Have you created any custom processes that could use a system wide parameter? If so you can add it to the Application Parameters and have your process look up the value.

Just pull up the screen (APP008) and hit the plus to add a parameter. You probobaly want to put it under the USR-User Defined Subsystem so TMAR knows it's yours.

This data is kept in the APP_PARAMETER table. So in your process, query that table for the value of your parameter. This will allow users to change the parameter (if you give them permission) without re-writing your process, plus you can re-use the parameter in other processes.

Applies to: TIMSS6 & Personify

ISBN-10 to ISBN-13

2007-05-28T12:04:00.000-05:00

The International Standard Book Number (ISBN) is used as a unique identifier for books. It is not only used to distinguish different titles, but different version of the same book (for more information see the ISBN User's Guide). Starting January 1st, 2007, the number was increased from 10 to 13 digits.

The ISBN-13 can be calculated from the ISBN-10. First, the numbers 978 are put in front of the ISBN-10. The last number of both ISBN-10 & ISBN-13 is a check digit (used to validate the previous numbers) . So the check digit is dropped from the ISBN-10 and a new one is calculated for the ISBN-13.

ISBN-10: 0-060541-71-7
ISBN-13: 978-0-060541-71-2

There are several sites on the web that will do this conversion:

http://www.isbn-international.org/converter/converter.html
http://custompcsoftware.com/isbn13.html

In TIMSS there is one field for ISBN and it will hold 20 characters, so the new size is not an issue. The issue is if you want to maintain both numbers in your database.

I've chosen to just convert all my ISBN number to 13 since we don't deal with them that much and you can find the ISBN-10 based on the ISBN13. If you want to maintain both you can add an extender field, or you could create a view that calls a function that converts one to the other. It's really up to you.

I have created an SQL function to convert 10 to 13 and it's shown below:

CREATE Function dbo.ISBN10TO13(@ISBN10 as varchar(20))
Returns varchar(20) as

Begin
declare @ISBNO varchar(20)
Declare @NewISBN varchar(12)
Declare @ISBN13 varchar(20)
Declare @ISBN13F varchar(20)
Declare @i integer
Declare @n integer
Declare @v integer

--remove any hyphens or spaces
Set @ISBNO=replace(replace(@ISBN10,'-',''),' ','')

If len(@ISBNO)=10 --correct length
Begin
Set @NewISBN = '978' + SubString(@ISBNO, 1, 9)
Set @n = 0
Set @i = 1

While @i<=12 Begin Set @v = SubString(@NewISBN, @i, 1) If @i%2 = 0 Begin Set @n = @n + 3 * @v End Else Begin Set @n = @n + @v End Set @i = @i + 1 End Set @n = @n%10 If @n<>0
Begin
Set @n = 10 - @n
End

Set @ISBN13= @NewISBN+str(@n,1)
-- add hyphens
Set @ISBN13F= substring(@ISBN13,1,3)+'-'+substring(@ISBN13,4,1)+'-' +substring(@ISBN13,5,6)+'-'+substring(@ISBN13,11,2)+'-'+substring(@ISBN13,13,1)
End

Else -- wrong length, return same value
Begin
Set @ISBN13F=@ISBN10
End

Return @ISBN13F
End

Applies to: TIMSS5, TIMSS6, Personify

TIMSS 6: Ad-Hoc Query Security

2007-05-21T18:30:00.000-05:00

Another good idea I saw at TAUG was adding security to your Ad-Hoc Query categories. I don't necessarily want to keep people from running a particular query, but I want to limit their choices so they can easily find what they are looking for.

Similarly to the Call Topic Security you can setup security on the Ad-Hoc Query Report Categories. In Types and Codes for QUERY_CATEGORY, un-check the Public Code flag and save. This will activate the Special Security button where you can pick the groups that have access to this Query Category.

Thanks to Pam R. who mentioned this in her TAUG presentation.

Applies to TIMSS6

Crystal Reports: Sorting Parameter

2007-05-14T18:29:00.000-05:00

I have a couple Crystal reports that I've added a parameter to that allows the report to be sorted different ways. For example, our meeting function sheets can either be sorted by room or by date. To do this in the same report, I created a parameter called SortBy that is either DATE or ROOM (see Online Report Parameter Pick List to see how to pick parameters for your online report).

Then I added a formula field to the report that creates a value to sort on based on the parameter value:

If {?SortBy}='DATE' then ToText({Command.start_date})
else
If {?SortBy}='ROOM' then ToText({Command.facility_room_id})

and I use this value to sort my report.

I've done something similar with my committee roster. One group wants their committee sorted one way, and the rest a different way. So I created a parameter and a formula that generates the value to sort by:

if {?Format}='NORMAL' then
if ({COM_COMMITTEE_MEMBER.VOTING_STATUS_CODE}= 'NON_VOTING' and {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'STAFF') then 99 else
if {COM_COMMITTEE_MEMBER.VOTING_STATUS_CODE}= 'NON_VOTING' then 11 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'CHAIR' then 1 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'VCHAIR' then 2 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'SEC' then 3 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'TREAS' then 4 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'EXOFF' then 5 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'MEMBER' then 10
else 9
else if {?Format}='CFLT' then
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'CHAIR' then 1 else 9

Thanks to Edward B. for the hint at TAUG.

Applies to: TIMSS6 & Personify

Spamming with Outlook

2007-05-07T09:54:00.000-05:00

If you organization is like mine, you love to spam your members.

All kidding aside, one of the features with Outlook 2003 was that pesky dialog box that pops up every time you send a message using a mail merge or a Cognos Script. There is a way you can turn this off!

This Microsoft white paper has a section on Customizing Outlook Security Settings. You'll need to make a change to the exchange server, and update the registry on the local machine. It's well worth it when you are emailing a couple thousand renewal notices and don't have to click OK every 5 seconds to send each message.

Applies to TIMSS5, TIMSS6, Personify

TAUG 2007 wrap-up

2007-04-30T18:14:00.000-05:00

Well another TAUG has come and gone. It was great to see you all, and now I've got some new tips for my blog! There were lots of good sessions and thanks to all of you who participated.

Next year's TAUG (or will it be PAUG) will be held in Louisville, Kentucky. Paul G told me that we will be right next to Fourth street which looks pretty cool:

http://www.iglou.com/restaurants/4sl.shtml

I should caution you that many of these places are open until 4 am!

And not to mention an evening event at Churchill Downs.

I hope to see you all there!

Applies to: TIMSS5, TIMSS6, Personify

TIMSS 6: Held Line Status Code

2007-04-16T09:32:00.000-05:00

The other day I received an email from a member in response to the web order confirmation that we automatically send when some one orders from our website. She was wondering why she had received the email confirmation since she had not ordered any books.

I sent her the ship address for the order (which wasn't her) and asked her if maybe that person had logged in as her and ordered books.

In the mean time, what do I with this order? I didn't want to fulfill it since it might not be legitimate, but I didn't want to cancel and refund since it might be valid.

So I changed the line status to Held (not the fulfill status). This way the order is still in the system, but won't get picked up by the fulfillment process.

When the member confirmed that her co-worker had used her account to order books, I changed the line status back to Active, and the order got picked up the next time the fulfillment process was done.

Applies: to TIMSS6 & Personify

Market, List & Key Codes

2007-04-09T09:00:00.000-05:00

So what's the difference between a market code, a list code, and a key code?

A marketing code is associated with a particular marketing piece, for example a conference brochure or a pre-recorded phone call.

A list code would be a particular group of customers to which you send marketing materials.

A key code is a combination market and list codes for a particular effort.

Let's say I have a postcard that I want to sent out to promote my annual meeting. That piece would have a particular marketing code associated with it. In TIMSS, I create several lists: purchased prospects, members, previous attendees who aren't members, etc. , and I send them each this post card.

When I use Marketing Production Control in TIMSS, I will create different efforts for this post card to go to each one of those groups, and each would have a different key code.

Or I can have different marketing pieces (a postcard and a brochure) go to the same groups to see who responds best to different efforts.

The combination of marketing & list codes is a key code, so that when an order is entered, entering a key code is the same a entering both a list and a market code. Thus the key code is what should be printed on the piece and communicated to the CSR when the order is taken.

You might want to name them consistently, for example, key codes always start with the letter K, so that the CSR knows it's a key code.

Applies to TIMSS5, TIMSS6, & Personify

Credit Cards

2007-04-02T17:05:00.000-05:00

Here are a couple articles I found about credit cards that you might find interesting.

How Credit Cards Work- gives a history of credit cards as well as how they work with the bank.

Anatomy of Credit Card Numbers- tells how the actual credit card number is made and how you can tell if a number is valid.

Credit Card Magic- more about card numbers.

Card Security Code- Info about the CVV2

Applies to: TIMSS5, TIMSS6, Personify

Emailing Web Order Confirmations

2007-03-26T14:15:00.000-05:00

You might remember a thread on the listserv a few months ago regarding emailing confirmations to customers for their orders. TIMSS6 has this as base function, however I didn't like the fact that is sends confirmation for all orders when I just wanted to send them for orders that came in through the web (customers expect this).

I found an article on how to send email from SQL Server using xp_smtp_sendmail. Following the steps in the article, I created a stored procedure in my database that emails confirmations for orders with a method of WEB. Then I used the SQL Server Agent to schedule a task to execute that procedure every couple minutes.

The xp_smtp_sendmail has a couple nice features, including changing who the email is from and allowing an HTML message.

I also added a table to the TIMSS database to store the messages that were sent.

Applies to: TIMSS5, TIMSS6, Personify

TAUG 2007

2007-03-19T14:41:00.000-05:00

Yes it's TAUG time again. This year the TMA Resources Annual Users Group (TAUG) meeting is in Albuquerque, April 22-25. I'm on the Program Committee and I'm really looking forward to this year.

The sessions are categorized by personas. If you've been to the registration site (http://www.taugconference.com/) you will see the different personas that the sessions are aimed atWhat's so great about TAUG? Here's a short list:

1. Great educational content
2. Networking with your counterparts that also use TIMSS
3. Free time with your Project Manager
4. Meet your TIMSSCare Rep.
5. See what other people are doing with TIMSS that you should be doing.
6. Get a look at Personify
7. Put a face to a helpful name from the listserv

This is my 7th TAUG (oh my God!) and I still get lots of good information from it. Just look at my blog posts from just after TAUG a year ago. Most of them were gleaned from it.

I hope to see you there! And if you have anthing you'd like to see here, let me know.
Applies to: TIMSS5, TIMSS6, Personify

TIMSS 6: Membership Default Date Masks

2007-03-12T15:26:00.000-05:00

When setting up a membership product, there are 2 variables that you can set to determine what the default begin and end date of the membership will be. They are set on the Membership Structure Setup screen (MBR000) and on each membership product (MBR001).

The available codes for these to variable are found in types and codes under DEF_END_MASK and DEF_START_MASK.

DEF_START_MASK:

BEGJAN- First of January
BEGFEB- First of February
BEGMAR- First of March
BEGAPR- First of April
BEGMAY- First of May
BEGJUL- First of July
BEGJUN- First of June
BEGAUG- First of August
BEGSEP- First of September
BEGOCT- First of October
BEGNOV- First of November
BEGDEC- First of December
BEGMY- First of the current month
CUTOFFMONTH- First of Next Month after cutoff days- stored in OPTION_1 of the DEF_START_MASK code
CUTOFFYEAR- First of Next Year after cutoff months- stored in OPTION_1 of the DEF_START_MASK code
NEXTMONTH- First of Next Month
NONE- NONE
TODAY- Today

DEF_END_MASK:

1YEAR- One Year
1YRMINUS- One Year Minus (end date is last date of previous month next year)
1YRPLUS- One Year Plus (end date is last day of current month next year)
ENDJAN- End of January
ENDFEB- End of February
ENDMAR- End of March
ENDAPR- End of April
ENDMAY- End of May
ENDJUL- End of July
ENDJUN- End of June
ENDAUG- End of August
ENDSEP- End of September
ENDOCT- End of October
ENDNOV- End of November
ENDDEC- End of December

A common choice for the end date is the One Year Plus. This will give the member a few extra days of membership on their FIRST order by making the end date the last day of the month. So if they joined on 3/15/2007, their end date would be 3/31/2008, giving 12.5 months of membership.

Many organizations go with the One Year Plus so that they have a few extra days in that first year to get setup. For example if they joined at the end of the month they might miss that month's magazine, but they would still get 12 issues; if they joined early in the month, they might actually get 13 issues, but they would not get 11 issues and short change the member.

My organization is a Trade Association and our membership runs annually. Thus we use the value for End of December as our default end mask.

I would imagine that most organizations use TODAY for the start date of their new membership orders.

You should note that this really only effects new membership orders. Renewals are based on the end date of the current order and whatever length you have setup for the membership product.

Applies to: TIMSS6

TIMSS 6: Extender Textbox

2007-03-05T11:15:00.000-05:00

This came across the listserv a couple weeks ago. It's good information and I like to put stuff up here so I know what I've done in TIMSS. All credit goes to Dave D. and Eric E.

When adding a text box to a screen, Dave wanted to have it read only with a scroll bar. Here's what he did:

Dim oTextBox as Object
try
oTextBox = MyForm.getformcontrolbyname("usr_txt11")
oTextBox.ReadOnly = "True"
oTextBox.ScrollBars=2
Catch Ex as Exception
Msgbox(ex.Tostring())
End Try

Values for scrollbars: 0=None; 1=Horizontal; 2=Vertical; 3=Both

Applies to: TIMSS6

Using a SQL statement in Crystal Reports

2007-02-26T19:13:00.000-05:00

Have you ever had a complicated report that you just couldn't write using the "wizards" in Crystal Reports? Well you can write an SQL query and enter it directly into Crystal using a Command.

When adding tables you can add a query by clicking on Add Command.

That will open a box where you can enter a query. You can even add prompts (Parameters).

The data returned to in you Select statement is then available in your report.

Applies to TIMSS5 & TIMSS6

TCMS Meeting Registration Modification, part 3

2007-02-19T18:59:00.000-05:00

The third customization we did to the TCMS meeting registration had to do with the Hotel reservation. As I mentioned in a previous post (TIMSS 6: Hotel Blocking for Meetings) we found that the icon for making a Hotel Reservation to be almost hidden. So we changed how the process works.

After someone clicks on the Place My Order button to purchase a meeting product, we show the hotel reservation screen (if a hotel is setup) instead of showing the confirmation screen.

We not only added ADA Accessible and Special Requests, but also Room Sharing process to this screen.

After giving hotel reservation (or skipping it) the confirmation screen is then shown with the order summary and the hotel reservation information at the bottom.

Applies to: TIMSS6

TCMS Meeting Registration Modification, part 2

2007-02-12T22:48:00.000-05:00

Another customization we did with the TCMS meeting registration was to add a Questionnaire. This adds an extra step to the registration process where the registrant is asked questions based on the sessions they have signed up for.

This required 2 extender tables, one for the questions (USR_MTG_QUESTIONS) and one to hold the answers (USR_MTG_ANSWERS) for each order.

A new tab was added to MTG001 with a grid to enter the questions along with a sort order, answer type (check box, combo, or text field) along with choices for a combo, and if it's a required field.

A new button was added to the Meeting tab on ORD001 that opened a new screen to show the Q&A entered by the registrant through TCMS.

The questions asked are for each product in the cart, so I can ask generic questions of an attendee under the meeting product, and if I have a golf outing I can ask those who've signed up for it if they are bringing or renting clubs.

Applies: to TIMSS6

TCMS Meeting Registration Modification, part 1

2007-02-05T21:19:00.000-05:00

We had TMAR customize the meeting registration web part. One change was how the "sessions" were displayed. We use products under the meeting for our registration rates and wanted to duplicate this in our online registration.

First, we only show the products that are web enabled under the meeting which have Include Add-to Cart Button checked.

Next they are grouped by the product class. In the example below, the class descriptions are Registration Rates, Spouse Rates, and Optional Events.

The classes are sorted by the code and my codes are 1REGREATE, 2SPOUSERATE, 3OPTENVET. When registering, at least one item from the first group must be selected.

The date/time was removed from the web part. This is so registration rates can have times that conflict with conflicting rates. For example, one can't sign up for Full Registration and Sunday Registration, so they are setup in TIMSS with conflicting times.

Finally, the Brief Description is displayed under the session name. This is where the date/time can be displayed along with any other information needed about the session.

Applies to: TIMSS6

TIMSS 6: Meeting Planning for Rooms & Requirements

2007-01-29T12:21:00.000-05:00

For our conferences, the meeting planning staff enters rooms, configurations, and requirements for each meeting session/product as well as the responsible vendor and or staff person in TIMSS. A Crystal report is linked to the MTG001 screen to display all this information sorted by time or sorted by room.

The report shows the session name, time, room, configuration, capacity, and requirements. Requirements are setup under system types and codes. Each type (ie. AV or F and B) have sub codes under each.

When these are entered for a session on MTG001, quantity and any comments can be added that also show on the report.

Applies to: TIMSS6

TIMSS 6: Relations to non-customers

2007-01-22T12:18:00.000-05:00

In TIMSS 6 you can create a relatioinship to someone who isn't a customer. Usually you would want to create a relationship to a customer, but in some cases you might not. For example if you wanted to store the names of family members, but not enter the family as customers.

You will notice that on CUS005, the customer ID is not required, only the name of the relation is required.

When you enter them, the name is stored in the CUS_RELATIONSHIP.RELATED_NAME field of the database.

The begin date could be used to store brithdates or anniversaries for these relationships.

Applies to: TIMSS6

Process Tracking with Contact Tracking

2007-01-15T15:53:00.000-05:00

We have a couple process that have several steps: a foundation compliance process and a publication award process. We use contact tracking to handle both of them.

I've created and overall topic for the process. Each step in the process has a different subject under that topic which are entered as follow-ups, or child records.

Theses processes are for the companies, but the person contacted is the person we contact in reference to this process. The person contacted on the follow-ups can be used for judges or reviewers.

We then have reports that pull all this data together in a readable format along with other pertinent data about the customer: class, status, size.

One of the processes was previously done in an access database, but the data was not in sync with TIMSS and thus mistakes were made with eligibility and other criteria. They tell me it's now much easier and the data is better.

Applies to: TIMSS5 & TIMSS6

TIMSS 6: Ad-Hoc/Search Results Order

2007-01-08T14:13:00.000-05:00

When using the Ad-Hoc Query or Search screens, you can not only control how you search (see TIMSS 6: Searching for other fields) you can also control how they are displayed when they are returned.

Highlighting one of the Fields To Display and using the up and down arrows will allow you to choose the order in which the resulting columns are displayed in the Search Results pane.

Results are sorted by the column that is displayed first.

Applies to: TIMSS6

end_date>=getdate()

2007-01-01T06:43:00.000-05:00

There are several pieces of data that have begin and end dates in TIMSS. Writing queries to see when that is valid is usually written:

begin_date<=getdate() and end_date is null or end_date>=getdate()

Customer relationships are this type of data. With a query where the end date is greater than or equal to, it implies that if the end date is today, we still want the relationship to be valid. Unfortunately, this will not be the case because time is included with the getdate() function.

The end date is stored in TIMSS without time: 2006-12-31 00:00:00.000, whereas getdate() is returned with time: 2006-12-31 06:48:06.710. When the comparison is done, the time portion makes them never equal, unless the getdate() function was called exactly at midnight.

In my MS SQL database I have a function called TRUNCDATE written by TMAR (there is a similar base function in Oracle). Using this you can remove the time portion of the date so that when the date portion in the comparison is equal, the relationship is still valid.

begin_date<=getdate() and end_date is null or end_date>=dbo.TRUNCDATE(getdate())

If you don't have this function in MS SQL, it's below:

CREATE Function TRUNCDATE(@PDTDATETIME AS DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN CONVERT(DATETIME,(CONVERT(VARCHAR(50),@PDTDATETIME,101)),101)
END

Applies to TIMSS5 & TIMSS6

Spell Checking Job Titles

2006-12-25T15:48:00.000-05:00

Here's a simple idea to help with data integrity. Write a report or query that pulls distinct job titles from TIMSS. Put the results into Microsoft Word, or similar application, and run the spell checker. When you find one that's not spelled correctly, search for it in TIMSS and fix it.

Note that if you are pulling job titles from customers or addresses it will affect where you search for the spelling mistake.

You can do this for other fields, product descriptions for example, where the words would be found in a dictionary.

Applies to: TIMSS5 & TIMSS6

TIMSS 6: Short Pay Codes

2006-12-18T08:44:00.000-05:00

When setting up rate structures for products in TIMSS there is an option for the Short Pay Code under each rate code:

ADJUST- Accept whatever is paid
AR- Create Receivable
REJECT- Do not activate Order unless fully paid

This code is used by TIMSS when the full amount is not paid for a product. ADJUST could be used for fundraising products where you will accept what ever amount is paid by the customer. AR makes the line status Active and creates a receivable in TIMSS for when you want to give them the product but are going to go after them for the remaining balance. REJECT keeps the order proforma until the full amount is paid (you don't want receivables).

If a user tries to make a short pay order Active and the Short Pay Code is REJECT they will receive the following message:

You should also note that once an order becomes Active, you can not make it Proforma again.

Applies to: TIMSS6

TIMSS 6: Quick Pick Products Configuration

2006-12-11T22:03:00.000-05:00

On the Call Center screen, there is an area for quick picks where your users can easily place order for products (see TIMSS 6: Ordering Multiple Products).

You can control what products are displayed on this panel of the Call Center screen on the User Quick Picks Setup screen (MRM000A).

One thing I didn't like about this screen were the check boxes at the bottom, in particular the Make Quick Pick Product Available for All Users. I didn't want to make this screen available to all staff since they could control what others saw. We don't use Fund Raising so the other is not applicable to us.

So I hid these controls using Extender. Now everyone has access to this screen and they can only change what their own quick picks are.

This data is stored in the PRODUCT_QUICK_PICK table. The two notable columns in the table are the PUBLIC_FLAG and the USER_ID. If the PUBLIC_FLAG is Y then all users see this quick pick product. If it is N then only the person in the USER_ID column sees it in their quick picks.

Applies to: TIMSS6

TIMSS 6: CUS_ALIAS Table

2006-12-04T11:11:00.000-05:00

That the CUS_ALIAS_TABLE is where TIMSS stores the aliases you create for a customer. Important fields in the table are the CUS_ALIAS_SEQ, ALIAS_CODE, and SEARCH_NAME.

The SEARCH_NAME is the alternate name that a user can search for and pull up this customer.

The ALIAS_CODE is a user defined code (the type is ALIAS) that can be used to describe each alias entered in the table. I have

ALPHA- The name we would use to sort this customer (company) alphabetically. The Joe Smith Foundation, would have an alias of SMITH, JOE FOUNDATION. The Community Foundation of Greater Green Bay would have an alias of GREEN BAY, COMMUNITY FOUNDATION OF GREATER. Since we store these as alias instead of elsewhere, they can be used when searching.
MAIDEN- Maiden Name- to store a woman's maiden name
ACRONYM- Acronym (COF for Council on Foundations)
ALT-SPELL- Alt. Spelling- SMITH;ROBERT or SMITH;BOB, or we use it when a name has alternate characters.
DBA- Doing Business As- some organizations use multiple names

The ALIAS_CODE is not a required field, but is very useful for tracking why an alias is used.

The CUS_ALIAS_SEQ is a sequence number and used as a key in the table, each entry getting the next number. However, the alias with a CUS_ALIAS_SEQ of zero is the primary SEARCH_NAME and is the same as the SEARCH_NAME in the customer table. You can not edit this primary alias on the Alias screen (CUS007) and can only change it by changing the customer's actual name.

I had some old converted data where the primary SEARCH_NAME in the CUS_ALIAS_TABLE did not match the SEARCH_NAME in the CUSTOMER table, so that something you might want to check.

Applies to: TIMSS6

TIMSS 6: Revenue Distribution with Fixed Amount & Remainder

2006-11-27T15:38:00.000-05:00

Our membership dues are calculated on a dues schedule and can be from $400 to $50,000. We wanted to have the first $400 go to one revenue account and the remainder go to a second account.

When setting up the revenue distribution you will setup 2 pairs by priority.

For the $400 revenue pair, the % should be 0, the priority should be 1, and the amount 400.
For the next revenue pair, the % should be 100, the priority 2, and the amount all 9's.

Applies to: TIMSS6

TIMSS 6: Online Report Parameter Pick List

2006-11-20T13:22:00.000-05:00

So you've written a Crystal report and attached it to a screen, and one of it's parameters needs to be one a few specific values. You can use the List SQL at the bottom of the screen to allow users to select certain values.

You can put a query here to pull values to show in a list. Something from types and codes:

select code from app_code where subsystem='CUS' and type='GENDER'

or if your parameters don't exists anywhere you could use the following query:

select 'NORMAL' union select 'CFLT'

to get a listing of just NORMAL and CFLT.

When the report is run, there will be binoculars next to the field that the user can click on and select a value from the list. You should note that this List SQL is available for each parameter, so which ever row you have highlighted in the parameter table above the List SQL box will be the parameter that you are creating the list for.

Applies to: TIMSS6

TIMSS 6: Hot Key your Buttons

2006-11-13T14:43:00.000-05:00

If you've added a button to a screen, you can make it so a hot key will click your button. In the customizer, all you need to do is name the button with an ampersand before the letter that you want to be the hot key.

So if you added a Google button to a screen and you want Alt-G to launch the click event, you'd put an "&" before the G in the Text property of the control.

Then the button will be labeled Google and Alt-G will start the click event. Make sure that there are no other hot keys for that letter.

Applies to: TIMSS6

Prepayment Liability Account

2006-11-06T18:34:00.000-05:00

I was going though old emails on the listserv today and came across this from Tom B. regarding the Prepayment Liability Account. It's helpful information.

The name actually has been used in TIMSS for over 20 years and originally came from an accounting professor at the University of Chicago who consulted on the first design of the old RAMS accounting system and has been used ever since. (I even heard today that iMIS uses the same term but I can't verify that personally.)
The idea and accounting behind it is that when money comes into the organization and is applied to an order that has not been invoiced yet, you cannot consider that to be any kind of revenue because you haven't fulfilled any commitment on your part, i.e. shipped a product. When there is an active order for membership or subscription, the typical thing is to call these amounts Deferred Revenue. For inventoried products, these prepayment amounts are obviously liabilities to the organization until the product is shipped. If you don't ship, you owe the customer the money back. Once shipping takes place, the amount is transferred to a revenue account. Thus begat the term "Prepayment Liability" since this was an amount applied to a specific order prior to shipping and was applied to a GL account that appears on the liability section of the balance sheet. So, a PPL is a liability account. It is a form of deferred revenue but a special one that separates money received that isn’t yours yet because you haven’t invoiced versus money you know you will earn if you only follow through with scheduled deliveries of subscriptions or memberships.

Applies to: TIMSS5 & TIMSS6

TIMSS 6: Internal Staff Listing

2006-10-30T11:29:00.000-05:00

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

2006-10-23T13:25:00.000-05:00

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

2006-10-16T08:29:00.000-05:00

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

2006-10-09T14:16:00.000-05:00

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

2006-10-02T07:29:00.000-05:00

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

TIMSS 6: Product Discounts

2006-09-25T12:21:00.000-05:00

It's really easy to setup discounts in TIMSS 6. These discounts work automatically based on the customer and what's ordered. The two examples I'll show here are for bulk discounts on books, and a 5% discount applied to web orders with a given market code (actually called Promo Code in TCMS).

One note of caution is that TIMSS will only apply one discount code per line item. When mulitple discounts can apply, TIMSS should go with the on that gives the customer the best price.

Bulk Discounts: I've setup a discount of 20% on selected products when the quantity is between 11 and 20. The 20% is just for this item and only applies to the products in the table. At the bottom, the quantity is defined.

When and order is placed, either through the TIMSS application or the TCMS, for one of the books where the quantity is between 11 and 2o, a 20% discount is applied automatically. You will see the discount code on the order so you know which was applied.

Promo Discount: Here we wanted to reward those who purchased books through the web, so we are giving them a 5% discount when they purchase a book though TCMS (we sent them a coupon with a previous order to drive them to the website).

This discount is setup for the Order Level so the whole order gets the disocunt. The market code is chosen (this must already be setup in TIMSS as a market code) and the method is selected was WEB.

At the bottom, this is only valid for a certain date range.

So when someone comes to our TCMS store, and puts a book in their cart. Before they check out, they enter the promo code (marketing code) and they get a 5% discount on their order (if it's in the date range).

The extremely nice thing is that there is no extra setup for TCMS. It follows the same rules as TIMSS right out of the box!

Applies to: TIMSS6

Tweaking how TCMS web parts display

2006-09-11T08:41:00.000-05:00

Aside from changing skins or the style sheet to change the format of how things are displayed, you can also tweak the aspx page for web parts to control how things are positioned.

For example, if you don't like that the featured products web part displays the product's small image in 2 different ratios or has an extra space at the start of the product's name, you can change this in the ..DesktopModules\TimssFeaturedItems\ctrlFeaturedItemSm.ascx file.

In this file you will see how the web part is layed out. You can not control what data is returned. With a little HTML knowledge you can move tables around and change the sizing of images or remove their border.

As always, you are responsible for any changes you make. If you break it and can't fix it, you will need to pay TMAR to fix it; and when you upgrade your changes may be over written. You may want to submit your changes to TIMSSCare so that the become a part of the base product.

Applies to: TIMSS6

TIMSS 6: VeriSign Referral Error

2006-09-04T13:04:00.000-05:00

When charging a card the other day, we received the following error when we tried to authorize the card:

When I went to the VeriSign Manager, I saw that the result code returned was 13 and looked it up (http://knowledge.paypal.com):

Referral. Transaction was declined but could be approved with a verbal authorization from the bank that issued the card. Submit a manual Voice Authorization transaction and enter the verbal authorization code. You normally request a voice authorization from a card-issuing bank when you receive a result code 13, "Referral" error message. This indicates the card issuer is requesting direct contact with you (the merchant) in order to authorize the sale. Have the credit card number, expiration date, and amount available, before contacting the bank.

I called the card issuer and they told me to have the card holder call them before the charge would go through.

Turns out the customer had lost the card a couple months ago and the account was put on hold and this was the first time she tried to use it since.

Applies to: TIMSS6

TIMSS 6: INV620 Packing Slip Mod

2006-08-28T14:15:00.000-05:00

I wanted to add email and phone number to the packing slip that comes out of the TIMSS INV620 TRS process. I though I'd have to pay TMAR to do this simple mod because I can't modify the INV620.exe that the process runs. But I was wrong... I don't have to touch the exe.

This process works off a stored procedure: INV620_sp, a view: INV620_vw, and a table: INV620_Processed. So I added my 2 columns to the view (included primary_phone & primary_email_address in the view for the ship to customer), added the columns to the table, and updated the stored procedure to include these 2 columns... and voila! The next time I ran the process, the columns were available in the Crystal Report!

For the Crystal Report, I ran the INV620 process and saved the output as a Crystal Report. Then I opened it, added the columns on the report where I wanted them and saved it as my new INV620_PackSlip.rpt report.

I do have to caution you that anything you mess up and can't fix, TMAR will charge you to fix it. So do it in TTST first and make sure you can undo it. Also when you upgrade, you will be responsible to make sure it still works since you did it, not TMAR.

Applies to: TIMSS6

TIMSS 6: Default Authorized Group for Batches

2006-08-21T16:32:00.000-05:00

In TIMSS 6, batches can have an Authorized Group that can use the batch. This allows anyone to use or manipulate the batch who is a member of a given security group.

I have made the default group the one that has my accounting staff so that they always have access to all batches. I have done this by adding a constraint to the table that defaults in the group when a row is inserted into the FAR_BATCH table. I did it this way instead of through extender because there at batches that are created outside of the FAR001 Batch screen, for example TCMS eCommerce batches.

I have hidden this field on the FAR001 screen, so that it's defaulted to that groups and it can't be changed on FAR001.

Applies to: TIMSS6

TIMSS 6: Addresses

2006-08-11T13:08:00.000-05:00

There seems to be a lot of confusion on how addresses are stored in TIMSS 6. First of all, addresses are broken into 2 parts: the address & the address detail. The address is stored in the CUS_ADDRESS (AD) table and has the street address (i.e. Address lines, City, State, Zip, Country).

The address detail, stored in the CUS_ADDRESS_DETAIL (CAD) table, has the label name, job title, company, personal line, mail stop, etc. It's stored this way so many customers can share the same street address.

Joining the 2 tables is done on the CUS_ADDRESS_ID. The CAD will have the MASTER_CUSTOMER_ID of the customer we are looking at. The CA will have the MASTER_CUSTOMER_ID of the customer who is the "owner" of the address. There can be many references to the CUS_ADDRESS_ID in the CAD table, but only one in the CA table. If an address is linked, the MASTER_CUSTOMER_ID will be different customers (i.e. the person in the CAD is not the Owner of the address in the CA table).

A primary address for the customer is denoted by the PRIORITY_SEQ field in the CAD. A value of 0 (zero) is the primary. The sequence also controls the display order on the Customer's Address tab.

There are also FORMATTED_ADDRESS and FORMATTED_DETAIL fields in the CA & CAD tables, respectively, that have all the address lines separated by CRLF (carriage return/line feed, ASCII values 13 and 10). These 2 fields can be added together to give the full address label. It's very handy when merging into a document because you only have to use one field and it's already formatted for the given country as you've defined in TIMSS.

When linking to an address (from the ORDER_DETAIL table) there are 3 thinks that make the address distinct: the MASTER_CUSTOMER_ID in the CAD table, the CUS_ADDRESS_ID, and the ADDRESS_TYPE_CODE in the CAD table. A customer can have multiple address, even multiple address linked to the same address, but they must be of different types.

A couple handy views are in TIMSS. CUS_ADDRESS_VW can be used when linking to addresses from orders or committee terms. CUS_PRIMARY_ADDRESS_VW is an easy way to get the customer's primary address. You can always refer to these views for a reminder how the CA and CAD should be joined.

Applies to: TIMSS6

TIMSS 6: Hotel Blocking for Meetings

2006-08-07T12:00:00.000-05:00

I don't know how many clients actually use hotel blocking in TIMSS, but we use it extensively. The theory is that when you have a meeting, you book room blocks at hotels and your attendees will stay in those blocks. Usually the hotel will charge attrition if you don't book enough rooms in your block.

TIMSS allows you to take hotel reservation requests with a meeting order. This is not only more convenient for your customers (one stop shopping), but you can also keep track of how many room nights your attendees actually booked at the hotel. TIMSS does not charge the customer for the reservation, instead (using the HTL500 report) you pass the reservations to the hotel and they charge the customer.

To set this up:

Setup the hotel as a Facility, adding room types on the appropriate tab
On the meeting setup screen for the main meeting product, go to the Hotel Blocking tab and add your hotel
Create room blocks at the hotel.

You will need to setup every room type for every night. One nice feature is the Nights box which lets you choose a number of nights to create when setting up a Date. Thus it will copy that setup for so many nights (the field is only available when creating a date).

Once you have setup the hotel(s), when you place an order for the meeting, go to the Product Specifics tab, and click on the Hotel Blocking button to reserve a hotel room for the order.

On this screen, you can search for all room, reserved rooms, or available rooms. Check the boxes for the nights are each hotel and each room type to make a reservation. You can also set flags like Smoking, Wheelchair Access & Special Needs, plus record any persons sharing the room. Credit card information stored on this screen will be passed to the hotel so they can charge the customer.

Now that you have reservations, you need to give them to the hotel. Running the HTL500 process will take all the reservations that haven't been reported to the hotel (based on the Reservation Status) and create a report that you can send to the hotel.

Here are a couple of notes:

I changed the field from Non-Smoking to Smoking since most reservations are not smoking you only need to check the box when someone is a smoker and that saved processing time; I also had to modify the HTL500 report accordingly.
You need to be very clear on the departure date. If I'm staying on at the hotel from 9/18-9/20 does that mean I need a room on the 20th or only on the 19th. Just make sure you data entry staff and hotel are in agreement.
All hotels are different and want this information passed differently. Some will take a PDF and key it in, others want an excel file they can import. The good thing with a Crystal Report is that you can give them either, although you might need to modify the excel file a bit to get it in a good format for them.
If you are using TCMS there is a Web Enable on each room block, so if there are rooms you don't want to make available on the web, that's the place to do it. Also in TCMS, to make the reservation, after you've purchased the meeting product, there is a tiny hotel icon( ) on the screen that the user has to click on to get to the reservation screen. I'm trying to make this more visible so that no one misses it... I don't want to be charged attrition!

Applies to: TIMSS6

TCMS: IFRAME not scrolling to top

2006-07-31T06:08:00.000-05:00

We are using TCMS in an IFRAME on our website. This way TCMS is a separate site but looks like it's still a part of our main website.

One issue we had was that when visitors navigated within the TCMS IFRAME, the IFRAME was not scrolling to the top of the next page. For example, when checking out, the Place My Order button is down far on the page, especially if there are several items in the cart. When the button is clicked, then order goes through and an invoice is displayed, however the IFRAME did not scroll to the top so the user might see a blank page, unless they scrolled up.

I found the following solution on the web (http://www.webmasterworld.com/forum21/10675.htm) and it worked like a charm. On the page calling the IFRAME add the following function:

<script language="javascript">
function scrollTotop()
{scroll(0,0);}
</script>

Then where the IFRAME is called add

<IFRAME name="iframe" onload="scrollTotop()" ... etc>

This way every time the IFRAME loads a page, it scrolls to the top.

Applies to: TIMSS6

TIMSS 6: Searching for other fields

2006-07-24T06:46:00.000-05:00

Say you wanted to search on a screen for a field that isn't on the search screen by default. You can add to the fields that you can search to so you can find exactly what you are looking for.

For example, say you are in accounting and wanted to find all the meeting products that hadn't been validated yet so you could validate them. You'd want to add the VALID_FLAG to the search screen.

To do this, first you will need to see what you are searching against on that screen. So open the MTG001 screen and click on the binoculars to open the search screen. Then click on the down arrow next to the name of the view to see it's actual name.

Once you know what view/table you are searching against, open the Ad-Hoc/Query Metadata screen and pull it up.

Here you can add fields to query against when you hit the binoculars on any screen that searches against this view/table.

Moreover, if it is searching against a view you can alter that view in the database and then click the Synchronize with DB button to have your new column shown here. For example, if you had add a column using extender and wanted to be able to search against it you could add it to the view and then to this screen.

Please note that TMAR may update views when you upgrade and your changes may be lost, so make note of things you've added.

Applies to: TIMSS6

TIMSS 6: Shipping Charges

2006-07-17T20:59:00.000-05:00

There are four methods that can be used to calculate shipping in TIMSS:

By Weight- a flat rate plus an amount per a unit weight
By Price- a percentage of the price is used for shipping
A Flat Rate- a flat dollar amount is charged
By Schedule- a fixed or percentage is used for different price ranges.

You will need to set this up for each shipping method (SHIP_VIA in types & codes) and for each country; although you can have more than one type of shipping calculation for each.

I have 8 setups since I have 2 shipping methods: Standard & Expedited, and four countries: USA, MEX, CAN, & ALL COUNTRIES (All other countries that don't have shipping defined). We charge shipping by a Schedule where an amount is charged based on the order total. For the USA: $0-$30=$5; $30.01-$75=$7.50; $75.01-$150=$12.50; an over $150=$15.00.

Once you have shipping setup, you should add it to each inventoried product. Domestic countries are setup on the Shipping Setup tab of the Org Unit Definition.

For each product you can pick a calculation method that you've created. You may also add a surcharge (some extra shipping) and override the shipping calculation and give a dollar amount.

You will need to make sure that the SHIP_VIA codes and the Shipping Calculations are Available to Web if you want to use these with TCMS. I have also updated the description of the SHIP_VIA codes since they are displayed when checking out in TCMS:

Standard (5-10 days domestic; 4-6 weeks int'l)
Expedited (2 days domestic; 5-7 days int'l)

Applies to: TIMSS6

Meeting Confirmation Email

2006-07-10T06:13:00.000-05:00

This came up on the listserv this week, so I figured I'd tell how we do meeting confirmation emails. Firstly, we require all regisrants to have an email address so we can email them confirmations.

Then weekly we run the TRS ORD660 process in ACK format for a selected meeting. This generates acknowledgement letters and (in PROD mode) stores the data in the ORD660_Processed table with the job key. The process picks up those orders that haven't had an acknowledgement letter, or have had a line item added after the acknowledgement letter date (stored on the order and populated by this process).

We then have a Crystal reoprt that pulls orders for a particular job key run of ORD660 from the ORD660_Processed table along with other pertant data (assistant email, hotel reservation dates, special needs). I'd show the query, but it's one of the longest I've written!

This data is then merged with a Word document to send out an email. One run is done for the primary email address and another is done with the same information to the person's assistant's email address (ASSISTANT is a phone location we use).

When we were on TIMSS 5 we had a similar process, but the BJS job was MTG660 in ACK mode and we used an Impromptu report to get the data from the MTG660_MASTER_TAB table based on a job key.

Applies to: TIMSS5 & TIMSS6

Web account creation

2006-07-03T06:59:00.000-05:00

We just launched a new website that is integrated with TIMSS using TCMS and web services. I was telling our good friend Sean about how new accounts are created on our site and he thought it was excellent blogging material, so here it is.

When a new user comes to www.cof.org and wishes to create an account, the process is as follows:

The new user enters their email address (this will be their username).
This email address is compared against primary email addresses (and usernames) in TIMSS.
If the email address already is in use, a password reset is sent to the user either for their existing account, or a new account with the email as the login is created and a password reset is sent.
If not in use, a message is sent to the address with a link to follow to complete the registration process (this validates the email address):

A message has been sent to your e-mail address containing information about the registration process. You must use the link in this e-mail to complete the registration process.

This message has been sent to you because you requested to sign-up for access to the Council on Foundation website at http://www.cof.org. Please follow the link provided below to complete the registration process. https://www.cof.org/Account/NewAccounts.cfm?emailVerification=%2D3%3C%5EWVBTNG%2D20R7%26%240%20%20%250
Following the link lets the new user enter their information (name, address, etc).
If the email domain is recognized as an organization's domain (i.e. cof.org) then the new user is linked to that organization. The Council's members are foundations and their employees and board members need to receive member benefits.
If the domain is not recognized (as is the case for many board members who have accounts elsewhere), the new user can pick which organization they should be affiliated with. This generates an email to our primary contact at the organization (if there is not an email for that contact, it is sent to a TIMSS administrator) for verification.

Yes, we do have a small issue where email primary addresses are not unique (foundations have one email address, or couples sharing an email address), but this number is small and it is very easy to get your own free email account. Using email addresses this way (as a username/unique identifier) is fairly common across the industry.

To help make email addresses unique, I've "extended" TIMSS to not allow email address with a location of ASSISTANT to be primary.

We did import the usernames (that weren't email addresses) and passwords into our new site (and thus TIMSS because that's where they are now stored) so everyone wouldn't need to create a new account.

Applies to TIMSS5, TIMSS6 & TIMSS7

Tax on shipping charges in DC & MD

2006-06-27T11:09:00.000-05:00

I asked our lawyers if we were required to charge tax on shipping charges in DC & MD (states where we have a presence). Here is what they determined for our organization:

District of Columbia

According to DC ST § 47-2002, sales tax is imposed on the gross receipts from sales. Gross receipts are defined in DC ST § 47-2001(h) as the total amount of sales prices for retail sales. According to DC ST § 47-2001(p)(2)(E), the term "sales price" does not include "transportation charges separately stated, if the transportation occurs after the sale of the property is made." This information was confirmed with Rochelle Washington (202-442-6636) of the DC Office of Tax and Revenue on June 27, 2006. Based upon this information, shipping charges are not subject to DC sales tax so long as the charges are separately itemized. Since our shipping is a combination of both shipping & handling, tax must be charged.

Maryland

According to the Maryland Sales and Use Tax Facts (July 2006-June 2007) , "separately stated shipping charges are not taxable; however, handling charges are a part of the taxable price." So long as shipping and handling charges are not combined, Maryland tax does not need to be applied to shipping charges. Since our shipping is a combination of both shipping & handling, tax must be charged.

Applies to: TIMSS5 & TIMSS6

Processing Bounce-Backs

2006-06-26T10:24:00.000-05:00

If your association is like mine, you send out large blast emails... which means you get a lot of bounce backs. Wouldn't it be nice if there was a piece of software that could help you process all the bounce backs? Well there is: G-Lock Email Processor.

This software will monitor an inbox and using rules you define, allow you to group similar types of bounce backs and let you get the email addresses they came from. Then you can update these email addresses in TIMSS.

Yes, this is the second product I've mentioned from this company (see Email Address Verification), and no they aren't paying me.

Applies to: TIMSS5 & TIMSS6

TIMSS 6: Error Messages

2006-06-19T06:59:00.000-05:00

Have you ever received an error message in TIMSS that had a spelling or grammar error? How about wishing you could add more info to an error message to make it clearer for your users? Well in TIMSS 6 you can. Error messages are stored in tables starting with APP_MESSAGE.

For example:

can be found in APP_MESSAGE_TEXT as message 51 in the APP subsystem. And

can be found in APP_MESSAGE as message_id 18 in the CUS subsystem.

Please note that it's not obvious where the message is used so you might not want to change it too much. Also TMAR may update the message in a future release and overwrite your changes. Hopefully you will report your change to TIMSSCare and they will make the change to the message in the base product.

Applies to: TIMSS6

TIMSS 6: POS Sales Tax

2006-06-12T13:00:00.000-05:00

Sales tax can be automatically charged by TIMSS. In general, sales tax will be charged if three things are true: 1) a tax is setup for the location of the sale; 2) the product has a tax defined; and 3) the customer is taxable.

Usually the location is where the product is shipped to, however if you are selling to the customer in-person (i.e. at your bookstore) you will charge the tax of the locality you are currently in.

TIMSS handles this by letting you setup a POS (point of sale) batch. On the top right of FAR001 you can make this batch a POS batch by checking the box and putting in the country, state, and warehouse.

To charge tax in this locality, you will need to setup a tax for it.

Checking the POS box (I've changed my caption to POS Only) at the bottom right of ORD199 tells TIMSS that this tax should only be used on POS batches. So in the figures above, my conference bookstore is in PA and I will charge 7% tax on POS sales there.

I changed the caption (using extender) to make it clearer. If I only want to charge this tax for POS batches then I check the box, but in the case where my bookstore is at my headquarters in DC, I'd need to charge POS tax on sales there, as well as orders shipped to DC (since my business has a presence in DC I need to collect DC sales tax). Thus I would NOT check the POS box for DC so that I can use it for both regular sales where products are shipped to DC and POS sales where they are bought at my bookstore in DC.

Applies to: TIMSS6

TIMSS 6: More Extender Security & Help

2006-06-06T07:57:00.000-05:00

Here's some info from release notes about setting up field level security in TIMSS 6:

Field Level Security
You can set security for individual fields using Extender using the existing security codes under the SEC_PRIVILEGE type or create new ones. Use the SetObjectSecurity method and the ApplyField Security function to implement field level security.

Steps in setting up Field Level Security

Define an Extender field on TIMSS Screen.
Submit the Extender batch.
Create a new code under SEC_PRIVILEGE app type.
Assign appropriate privilege to user groups.
Open Extender screen again and invoke the MyForm.SetObjectSecurity method under appropriate event in the script.
Submit the Extender batch and deploy the Extender dll (...EXT.dll).

SetObjectSecurity

You can use public method SetObjectSecurity to enable/disable any object (e.g., textbox, drop down, button, and grid) using Extender.
This method takes the following six parameters (two required and four optional)

pstrName - Name of the Control for which you setup the security.
pstrSecurityOperation - This is the custom security that needs to be setup.
strApplication (Optional) - The screen/application name on which the field exists.
pstrTablLevel1 (Optional) - The tab level 1 of the screen/application
pstrTablLevel2 (Optional) - The tab level 2 of the screen/application
pstrTablLevel3 (Optional) - The tab level 3 of the screen/application

ApplyFieldSecurity

You can use public function ApplyFieldSecurity to make a field non-editable and masked (like the SSN field on Individual screen) using Extender. This method takes following six parameters (three required and three optional)

pstrField - This parameter is used to pass the Textbox Value for which you setup the security
pstrSecurityOperation - This will be the custom security that needs to be setup.
pstrApplication - The screen/application name on which the field exists.
pstrMaskChar (Optional) - The character that is used for masking the chars of the field. The default is #
pintNumberOfDisplayChar (Optional) - The number of characters in the text field that you do not want to Mask. The default is 0 (Zero)
pstrDirection (Optional) - Which direction you want to mask. If you want to show the first set of characters and mask the rest then you pass the parameter as First. Otherwise pass End. The default is End.

Note: This method works only for the Textbox type of controls which have string data.

Also if want to see the Extender Scripting Help document, when you have the Scripting Editor screen open, click on the blue question mark on the top right of the screen.

Applies to: TIMSS6

TIMSS 6: Call Topic Security

2006-05-31T17:00:00.000-05:00

For Contact Tracking (MRM001 & MRM003) you can add security to control who can see certain call Topics and their Subjects.

In Types and Codes (APP005) pull up the CALL_TOPIC type. For the topic you want to add security to, make sure the Public Code is not checked (Save and Refresh screen if needed). This will activate the Special Security button at the bottom of the screen.

This will allow you to choose security groups that have access to these topics and their subjects when creating/updating a contact as well as when searching.

Applies to: TIMSS6

Extender Scripting: Birth Date Validation

2006-05-27T14:43:00.000-05:00

Someone at TAUG asked if it was possible to do validation on a customer's birth date. You can use the script below in the OnValidated event for the Birth Date field on the Individual screen (CUSoo1I) to do just that.

Public Sub dtBirthdate_OnValidated ( ByRef MyForm As Object, ByRef MyControl As Object)
dim Age as String
If Myform.getformControlByName("dtBirthdate").text = "(none)" Or Myform.getformControlByName("dtBirthdate").text= "" Then
Exit Sub
End If
Age=year(now())-year(Myform.getformControlByName("dtBirthdate").value)
If Age>100 or Age<15 then
MsgBox("The age of the person is "+Age+". Please make sure the birth date is correct.",MsgBoxStyle.Information, "TIMSS - Information")
End If
End Sub

If the person is over 100 or under 15 (ok, the calculation isn't exact but it's close enough) the user will get the following box:

Applies to: TIMSS6

TIMSS 6: Ad-Hoc & Search Grids

2006-05-23T09:12:00.000-05:00

When searching or running an Ad-hoc Query in TIMSS 6, there are a few of things you can do to manipulate the table to make it easier to look at your data in the grid (the thrid April showed me at TAUG).

Sorting: You can sort on any of the columns by clicking on the name of the column. When you do so, you will see a grayed out arrow pointing up and the data will be sorted in ascending order. Clicking on it again will flip the arrow and change the sort to descending order.

Move Columns: You can move columns in 2 ways. First you can drag and drop them. Second, you can click on the little black arrow next to the column name and choose which column you want to see displayed.

Splitting the Grid: At the bottom left of the grid, you will see a little bar that you can grab and drag over. This will split the grid in two. This is handy when you are looking at a lot of fields and want to keep the customer ID or name on the left while scrolling on the right.

Applies to: TIMSS6

Crystal Reports: Conditionally suppressing a Field

2006-05-18T18:29:00.000-05:00

In Crystal Reports, you can suppress fields based some conditions you define. To do this, right click on the object and click the Format option to get the Format Editor window. The click on the button to the right of the Suppress check box.

This opens the Formula Workshop screen. If the formula returns TRUE then the box is checked and the object is suppressed. If it is FALSE then the box is unchecked and the object is not suppressed.

So in the example above when PROSP_Filter is PROSP, the object should be shown. Otherwise it should be suppressed.

Applies to: TIMSS5 & TIMSS6

TIMSS 6: Ordering Multiple Products

2006-05-15T19:00:00.000-05:00

Here are a couple quick ways to order multiple products that I learned at TAUG.

1. On the Call Center, you can select mulitple products from the Quick Pick Products and enter one in the Product Code at the bottom of the screen; then hit Enter Orders to create an order with all of them. They can't be sub-products.

2. On ORD001, when searching for a product, you can hold down Ctrl and select multiple products to be added to an order.

Applies to: TIMSS6

TAUG 2006 is over

2006-05-12T10:49:00.000-05:00

TAUG 2006 is over now and I'd have to say it was a pretty good one. TMAR always puts on a great event and this was no exception. Not only was it great to see the old familiar faces, but it was also great meeting all the first time attendees.

Thanks to all of you who told me that you get a lot out of this blog. One person told me that something he found here saved his organization $8,000!

Even thought it was my sixth TAUG I still got a lot off good ideas and tips, one of which will save my staff a lot of time when entering orders. Don't worry, they will show up here soon. :)

Although once again my favorite session had to be the open training session for 5 hours! Thanks to April and Parag for staying the extra hour and answering all our questions. It's always fun trying to stump you.

What next year's TAUG really needs is new presenters (I need a rest). I know you all are doing some neat stuff, present it. You've got a year to prepare.

See you all at the next TAUG in Albuquerque, April 22-25!

Applies to: TIMSS5 & TIMSS6 & TIMSS7

Crystal Reports: Using Parameters to Control a Report

2006-05-06T13:05:00.000-05:00

When you add a Crystal Report to the TRS, you can control the behavior of the report using paramters that are passed to the report. Using the Select Expert you can use an or to control what happens.

For example the PROSP_Filter parameter is used to pass either ALL, PROSP or NON-PROSP to control what USR_COF_STATUS is pulled for the report. In the statement below, only one of the conditions is true because of the paramete value:

({?PROSP_Filter}="ALL"
or ({?PROSP_Filter}="PROSP" and {CUSTOMER.USR_COF_STATUS} in ["PROSP","RMEM"])
or ({?PROSP_Filter}="NON-PROSP" and not({CUSTOMER.USR_COF_STATUS} in ["PROSP","RMEM"])))

For the or statements, both parts must be true, so the PROSP_Filter controls which USR_COF_STATUS is pulled for the report. You could obviously add more conditions to the or statements to give the parameter more control over what data is pulled for this report.

Applies to: TIMSS6

Customer Class and Status

2006-05-01T08:32:00.000-05:00

We use these fields extensively, but had to change slightly when we moved from 4.7.2 to 6.1.9.

We use Customer Class to define what type of organization the customer is (or works for). For example a Community Foundation, a Family Foundation, an Attorney/Law Firm, or a Hotel.

We were using Customer Status in TIMSS 4.7.2 to show how the customer was related to us. For example, a member, prospect, resigned member, or a collegial group. However in TIMSS 6, customer status had to be ACTIVE for a customer to be allowed to do things (I think this has been changed in a later version where you can define which codes mean ACTIVE).

So I created an extender field to hold the converted status called usr_cof_status. This has worked splendidly.

The main thing I would suggest is that when you have similar groups you want to be able to pull together, name them similarly so you can use the LIKE operator to pull them all in queries.

For example, we have several forms of members that we want to count/pull as members, but we want to be able to easily differentiate them. So they have been named:

MEMBER- a regular member
MEMEMG- an emerging member
MEMSUP- a supporting organization of a member
MEMAFF- an affiliate of a member

Using LIKE 'MEM%' (or Starts With in ad-hoc query) I can pull them all instead of listing them all out. More importantly, when searching on screens in TIMSS, one can just enter MEM to get them all since TIMSS also searches with the LIKE operator.

The same was done with customer class, where we have

CORPFDN- Corporate Foundation
CORPGP- Corporate Giving Program
CORPBO- Corporate Foundation & Giving Program
CORPNON- Non-US Corporate Foundation

And if only my international foundation classes end in NON in them, I can search for LIKE '%NON' to get all the international foundations.

Applies to: TIMSS5 & TIMSS6

Extender Scripting: Finding the right event

2006-04-25T08:05:00.000-05:00

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

2006-04-20T11:49:00.000-05:00

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