ENDWHILE

HowTo: Quickly and easily display the number of child instances

2008-01-30T05:08:00.000-07:00

Here's a quick trick for displaying the number of child instances.
1. Create a calculated field on the parent view of the view for which you want to total the number of instances. For example, if you have a set Named ViewCatalog with parent/child views of Catalog..CatalogItems, you put the calculated field on the parent view (Catalog).
2. On the calculated field, right-click and select "Edit field details", then go to "Suggestions" and in the "Evaluate these conditions immediately after query" section, include the following, updated with the appropriate SetName and ViewName: InstanceCount (SetName, "ViewName"). In our example above it would read: InstanceCount (ViewCatalog, "CatalogItems"). Be sure to note that the set name does not use quotation marks but the View name does.
3. You'll probably also want to make the field non-enterable and non-modifiable.

Now whenever you query in this set, you'll see displayed the number of child instances.

HowTo: Improve performance with better READ statements

2008-01-28T12:00:00.000-07:00

When you're dealing with large sets of data, it's easy to get lazy and kill the performance of a TFL function. For example, I need to process some data in a parent/child set. I only want to process the data if a record in the child view meets certain criteria.

The lazy way to do this is to READ your set and then WHILE through each parent, WHILE through each child, and then do your processing if a record in the child view meets your criteria.

However, there's a much better way - READ only those records that have child data which meets your criteria, and don't get any records that don't meet your criteria. That way, you simply process everything that's returned.

Using a View condition on your child view, or doing the READ below (which is basically the same as a View condition) will still return a parent record even if there is no child record matching your criteria (i.e. you'll still have to WHILE and IF in order to determine if you should process each record):

READ JasonTestQuery ALL WHEN
VIEW=Parent(ParentField1 = vParentValue)
VIEW=Childparentid(ChildField1 = vChildValue)

However, use the following READ statement and you'll *only* get parent records if there's a child record that matches your criteria - i.e. you can simply process everything that's returned because you already know it meets your criteria.

READ JasonTestQuery ALL WHEN
VIEW=Parent(ParentField1 = vParentValue AND ChildField1 = vChildValue)

Enjoy.

Info: Table upgrade considerations

2008-01-24T06:58:00.000-07:00

This article stems from this post on the TenFold message boards. Changing certain attributes or constraints on a logical table definition will require special work to complete an upgrade of the associated physical table. First, here's an explanation:

Let's say, as in the forum post above, you have a table with two columns:
- FirstName
- LastName

Let's also assume that you already have data in this table. However, you now want to add a SmartCode column for Gender. DesignDatabase does something quirky when you add a SmartCode column: It makes it requires. Don't ask me why, I have no idea.

In itself this isn't a big deal, unless you understand the implications of adding a required column to a table that already contains data. Here's what happens and why it was problematic for the above post's author:
1. TenFold creates a temporary table (tfddbackup) that's a copy of your target physical table
2. TenFold copies the data from your target table into the tfddbackup table
3. TenFold truncates the data in the target table and implements the changes (in this case it adds a new, required column)
4. TenFold copies the data from tfddbackup into the *upgraded* target table

Step 4 is where we run into problems. Because the poster added a new, required column on a table that already had data, when step 4 took place and TenFold tried to commit the data he received an error. The error makes sense once you understand what's happened, because he now has data in a table with a required column, but no data in said required column. The data doesn't meet the new constraints of the table definition and therefore the commit fails.

The simple way to get around this is to make the SmartCode column non-required. However, there are other cases where you will encounter upgrade problems and you can't work around it by changing a column's attributes (maybe you actually *want* the new column's attributes). In these cases, should you want to preserve your existing data and not use a "Drop and recreate", you have a few options.

First is to write a table upgrade. It's not terribly difficult, but it's not the most elegant process in the world. You can read about doing so in the DatabaseUpgrades document in Documanage.

Personally, in all but the most challenging cases, I prefer to work around the issue with a little creativity. In the above example, if you wanted the SmartCode column to be required, you could implement it first as non-required, perform the table upgrade (it will succeed), and then manually or automatically (using a TFL function) update the table so that every instance contains data in the new, required column. You then change the column to be required and perform the upgrade again (which succeeds now that the data in your table meets the new constraint - i.e. every instance has data in the required column).

I've also done table upgrades by exporting the table from the DB vendor, opening the export file, and changing the CREATE TABLE statement to include the changes I need to make. This works in a few cases and it's incredibly easy (plus, if all else fails, you have a backup of your table on-hand).

TenFold Layoffs

2008-01-23T12:10:00.000-07:00

If you haven't heard, TenFold completed a 40% reduction in force today. If your business lost consultants as a result of this development, please contact me. ENDWHILE can help support your application while you decide how to proceed.

Back from the Holidays

2008-01-08T05:28:00.000-07:00

Getting back into the swing of things. New posts to come shortly. Hang tight...

Update: READ using Date for a DateTime column

2007-12-08T05:22:00.000-07:00

Trevor Allred emailed me and said that this is actually a database issue and that it has nothing to do with TenFold. Fair enough.

Because I expect to repeatedly encounter this issue (how I've not until this point perplexes me), I've created a couple of functions to make it easy. You pass the date for which you want a date time returned (either the beginning of the day or the end of the day).

Call it like this:

LET vStartDate = DateToDateTimeStart(&DBToday)

DateToDateTimeStartDate() looks like this:

LET vDateString = DateToString (iStartDate, "YYYY/MM/DD")
LET vDateTimeString = vDate||'00:00:00'
RETURN StringToDate(vDateTimeString , 'YYYY/MM/DD HH MI SS')

How To: READ a date time field with only a date

2007-12-06T04:08:00.000-07:00

I'd have to say that this little task is about the most annoying thing I've ever encountered in TenFold. Maybe I'm just dumb, or maybe I'm just spoiled because everything else in TenFold is so easy.

Here's the scoop: I have a table against which I want to READ all the records created on a certain date. For this I want to use the CreationDateTime column in said table, which TenFold populates automatically. The problem is that CreationDateTime is a DateTime column, and I only want to supply a Date.

Were you to do something like this in an attempt to retrieve all of the records created yesterday...:

CALL AddDays (&DBToday, vDate, -1)
READ Orders WHEN OrderCreationDateTime = vDate

...your READ statement would use the DateTime from &DBToday and it would show you only those records created yesterday at the exact same time of day that you run the function. That's not what I'm after.

I want to know ALL the records created yesterday. I tried just about every thing I could think of - converting the &DBToday DateTime to a Date string, using ApplyMask on both the &DBToday string as well as the CreationDateTime field in my set, trying to create a DateTime string for the start of the day and the end of the day and doing a READ between those DateTimes. No luck.

Finally, I got some help from John Davenport. He saved my ass. I was headed down the right path with the last approach I listed, but there was more to it than I thought. Unfortunately, it's rather cumbersome what one must do to accomplish what should be a simple task. Here goes:

CALL AddDays (&DBToday, vYesterday, -1)
LET vDate = DateToString (vYesterday, "YYYY/MM/DD")
LET v1 = vDate||'00:00:00'
LET v2 = vDate||'23:59:59'
LET vStartDate = StringToDate(vStartDate, 'YYYY/MM/DD HH MI SS')
LET vEndDate = StringToDate(vEndDate, 'YYYY/MM/DD HH MI SS')

#Get list of yesterday's orders
READ Orders WHEN OrderCreationDateTime BETWEEN vStartDate AND vEndDate

That's a lot of garbage to go through and you're probably not going to remember it the next time you need to do it. I hope there's a better way (I found some information in the documentation, but quite honestly, I couldn't understand it). Feel free to leave a comment if you know how to do this more efficiently.

How To: Use DistributeConfigurations between servers

2007-12-03T15:30:00.000-07:00

DistributeConfigurations is a super handy tool for performing obtains and delivers. Anybody that's used to doing this manually will agree. However, as with manually obtaining and delivering configurations, you're somewhat stuck if you need to obtain from a product area on one server and deliver to a product area on another server - usually this requires that you FTP the package from your source area to your target area.

If your network supports it, you can simply mount a drive from your target server to your source server and then define your Product Area definition in DefineProductAreas to point to the target area's /pkg folder and DistributeConfigurations will do the rest. Of course, you can also do this if you're still typing tfobtain... and tfdeliver... manually.

How To: Create a multi-column unique key

2007-12-03T15:12:00.000-07:00

Here's how to define a multi-column unique key using DesignDatabase. This isn't rocket science, but if you're like me then you just kept doing this using RefineTables and never really bothered to learn to do it in DesignDatabase (which is much easier):

- Select your table in DesignDatabase and choose the Keys frame
- Click [New] at the top of the screen
- Name your key in the Key/Column field and check the Unique box
- Right-click on the Key/Column field and select "Change key"
- Select the column(s) you wish to comprise your unique key and click [Save and return]

You may also need to change the priority of your key if you intend it to be used in a POST statement in TFL or for AutoAdd update behavior. Higher-priority = lower number (i.e. 100 is a higher priority than 200).

How To: AutoAdd data via Reference Field

2007-11-28T09:56:00.000-07:00

Here's a nice tool to have in your tool kit: The ability to AutoAdd new values in a Reference Field. Here's what I mean:

Let's say you have a Transaction called ManageContacts. In this transaction, you manage your contacts and their contact information (address, city, state, et cetera). This is a single-view Transaction, and therefore it has only one table - the Contact table. The Contact table has foreign keys to the State and CityState table. These foreign key fields are StateID and CityStateID, respectively.

The State table stores a list of unique states. The CityState table stores a list unique Cities by State (i.e. each record in the CityState table contains the StateID for the state in which the city is found, and the City Name). In our ManageContacts transaction, this allows us to first pick a State and then limit the Cities we display in a SmartPick based on the StateID.

What we need is a simple way to enter new Cities. For example, we pick California for our state, and in our Cities SmartPick let's imagine that we see San Francisco, San Diego, La Jolla and Los Angeles. However, we now need to add Ventura to our list of California cities. Here's how to do it:

In our CityState table, we have the following columns:
- CityStateID (Primary key)
- StateID (Foreign key to State table)
- CityName

When we use AutoAdd, it checks the highest priority, non-primary key (which must be defined as unique) to see if an existing record exists. If it doesn't, it prompts you to create one. In our example, we'd define our highest-priority, non-primary key as a combination of the StateID and CityName columns, thereby allowing us to have a unique city name for each state. We do this because we could have the same city names for multiple states. (Now, before you DB gurus out there get worked up at my choice to not also have a City table and simply store StateID and CityID in the CityState table, I've purposefully not done that for the sake of keeping this example simple to understand).

So where are we currently? Our CityState table contains four records. Let's assume California's StateID is 5. Our CityState table would look like this:

CityStateID | StateID | CityName
1.........................5.................San Diego
2.........................5.................San Francisco
3.........................5.................La Jolla
4.........................5.................Los Angeles

If we were to populate a new City record for Ventura, DatabaseLibrary would look for an existing record in the CityState table of:

CityStateID | StateID | CityName
. ........................5.................Ventura

Since it doesn't exist, it would prompt us and ask if we would like to create it, which we do.

For this to work, we need to do a few things:
1. In our Transaction's Contact view, we need to reference in from the CityStateID column the two columns in the CityState table that comprise our unique key: StateID and CityName.
2. In BuildTransactions..Views we change the Update behavior for the CityStateID reference from "Choose" to "AutoAdd".
3. We must populate every field we've referenced into our view (i.e. StateID and CityName). In this example, I populate the StateID field when I pick my State, which I pick prior to selecting a city (this allows me to limit the cities I display based on the State I selected - clearly I only want to display cities found in the State I've selected). Next, I pick an existing city (if it's already in my CityName SmartPick), or I provide a new one, in this case, Ventura.

If a record doesn't exist in my CityState table for the combination of StateID and CityName I've provided, I'm prompted and asked if I would like to add it. By choosing Yes, TenFold DatabaseLibrary automatically:
1. Creates a new record in the CityState table for the StateID and CityName combination I supplied
2. Updates the CityStateID (my foreign key to the CityState table) with the PrimaryKey value for this new CityState record.

Now, were I to go look in my CityState table, I'd see the following:

CityStateID | StateID | CityName
1.........................5.................San Diego
2.........................5.................San Francisco
3.........................5.................La Jolla
4.........................5.................Los Angeles
5 ........................5.................Ventura

The next time I needed to select Ventura, it would be a choice in my CityName SmartPick. In the next few Blog posts I'll explain how to define multi-column unique keys and some details about how DatabaseLibrary validates by reference (which is what it does here).

Info: RETURN arrays

2007-11-22T07:31:00.000-07:00

RETURN-ing an array is a nice trick I like to use when creating TFL functions that I want to reuse throughout an application. It saves me from having to initialize and pass a bunch of variables as output parameters.

For instance, let's say that you have a function that performs a bunch of end user data validations - email validation, mailing address validation, telephone number validation, credit card number validation, et cetera. You want to know the status of each of these validation, which you perform in your TFL function.

Instead of doing something like this in the function from which you call your address validation:

LET vAddressValidation = NULL
LET vPhoneNumberValidation = NULL
LET vEmailValidation = NULL
LET vCreditCardValidation = NULL

Call ValidateEndUserInformation
...(vValidationResult,
... vAddressValidation,
... vPhoneNumberValidation,
... vEmailValidation,
... vCreditCardValidation,
... EndUserSet)

IF vValidationResult= 0
...Call Information ("All validations succeeded")
ELSE
...Call Error ("At least one validation failed")
...IF vAddressValidation = 1
......Call Error ("Address validation failed")
...ENDIF...
ENDIF

You can instead do something like this:

(In your calling function)

LET ValidationStatus = ValidateEndUserInformation (EndUserSet)

(In your ValidateEndUserInformation TFL function, you RETURN the status of each of your four validations as follows):

LET ValidationStatus[1] = 1 #0 if all passed, 1 if any failed
LET ValidationStatus[2] = 0 #Address
LET ValidationStatus[3] = 1 #Phone number
LET ValidationStatus[4] = 0 #Email
LET ValidationStatus[5] = 0 #Credit card

RETURN ValidationStatus

(Now back to the calling TFL function):

IF ValidationStatus[1] = 0
...Call Information ("All validations succeeded")
ELSE
...Call Error ("At least one validation failed")
...IF ValidationStatus[2] = 1
......CALL Error ("Address validation failed")
...ENDIF...
ENDIF

I like this method because it reduces the number of lines in my TFL function logic and I think it's a more convenient method for passing information from a called function back to a calling function.

...

Info: Causing snippets to fire in WindowsClient

2007-11-16T16:10:00.000-07:00

Here's something you should know about WindowsClient: A TFL function that modifies a field in your set does not cause any snippets on or watching that field to fire. For example:

- I have a field called ExpiredFlag in a parent view
- I want the text in the entire view to be red when ExpiredFlag = 'Y'
- I have a TFL function that determines if the account is expired, and if it is, I use a MODIFY operation to change the ExpiredFlag = 'Y'.
- None of the text in my view turns red
- When I manually check and uncheck the ExpiredFlag checkbox, my context-sensitivity works correctly and my text is red

What gives?

In order to make the snippets that fire the context-sentitive attributes on the fields watching ExpiredFlag do their thing, I have to change the ExpiredFlag value using an output parameter in my TFL function, not a MODIFY operation.

For example, with an Output parameter defined and mapped to our ExpiredFlag field:

IF &DBToday > ExpiredDate
...LET oExpiredFlag = 'Y'
ELSE
...LET oExpiredFlag = 'N'
ENDIF

This will cause the snippets watching my ExpiredFlag field to fire, thereby effecting the context-sensitive attribute that renders my text red when ExpiredFlag = 'Y'.

Oddly, BrowserClient snippets will fire when you update a field using a MODIFY statement.

Info: Built-in TFL function of the week: GenerateDefaults

2007-11-14T05:06:00.000-07:00

GenerateDefaults() allows you to, well, generate defaults in a set. This is extremely useful if, for example, you're performing an ADD and you need the primary key value of that ADD statement's record(s) in order to propagate it elsewhere in your application.

Let's say that you're entering some product information, which is a combination of a Manufacturer, ManufacturerID, ProductName and ProductID. You store this information in the ManufacturerProduct table (an intersection between Manufacturer and Product), and ultimately what you want is to update some set with the resulting primary key (ManufacturerProductID) for the new instance in ManufacturerProduct.

Since the ADD statement only performs the ADD in memory until the TFL function completes, this creates a problem: You need the primary key value that comes with the completed ADD statement before the function completes - say, to store it in another table.

Here's where you can use GenerateDefaults(). It might look something like this in TFL:

#See if we already have a record for this
#Manufacturer & Product combination

READ ManufacturerProduct WHEN
...ManufacturerID = iManID AND
...ProductID = iProdID

#If not, ADD it
IF &ReadCount = 0
...ADD ManufacturerProduct
......ManufacturerID = iManID
......ProductID = iProdID

...#Generate defaults to get primary key value
...Call GenerateDefaults (ManufacturerProduct)

...LET vManufacturerProductID = ManufacturerProductID
ENDIF

#ADD new ManufacturerProduct record ID to InventoryItems
ADD InventoryItem
...InventoryManufacturerProductID = vManufacturerProductID

New Website!

2007-11-08T21:14:00.000-07:00

No useful TenFold post today - I just finished a minor overhaul of ENDWHILE's website. Check back tomorrow. It's time for a vodka tonic and some television to numb the mind.

How To: Use TFL functions in cool ways

2007-11-07T18:40:00.000-07:00

Any function that RETURNS a value can be used for, say, context-sensitive attributes. For example, let's say you wanted a field in your parent view to only be visible if there were child records, you could do something like this in View field details in BuildTransaction:

[X] Invisible when: InstanceCount (MyTransaction, "ChildView") = 0

Or, let's say you want to call a function to update a field. You don't need to attach the function as a Business rule. Instead, you can write the function so that it RETURNS the value you want and attach it as a Simple default. For example, let's say an end user enters their credit card number into a calculated field and you want to store the credit card number in your database, but you want to encrypt it. You write a function that takes an input parameter (the credit card number) and RETURNS the encrypted string. You then attach the function as a Simple default where it might look like this (click on the image to enlarge it):

Info: Best practices for BusinessRule field defaults

2007-11-06T07:58:00.000-07:00

Calling a Business rule based on data entry is a great feature. One problem, unfortunately, is easily identifying the fields to which you've attached Business rules. It's one thing if you built the application and you remember where to look, it's entirely different if you didn't build the application and you must go in and open every field to see where a particular Business rule is attached. Hell, even in the applications I've built I often forget where I've attached Business rules. After getting annoyed with this, I started doing it differently.

Now, I never attach Business rules to basis fields. Instead, I create a CalculatedField, give it a descriptive name (like "LaunchCreditCardValidation") and attach my Business rule to *that* field. Now I don't even have to try to remember the fields to which I've attached Business rules, and, if I'm smart about how I name the CalculatedFields to which I attach them, I'll know the function I call just from the field name (i.e. CreditCardValidation).

How To: Make a dual-state checkbox

2007-11-06T07:53:00.001-07:00

This one might be a bit of a softball post, but if you don't know this, you want to.

TenFold checkboxes are, by default, three-state: NULL, N, Y. I'd say about 99% of the time, you want your checkboxes to be dual-state (N, Y). You can accomplish this by defining a New instance Literal default of 'N' (no quotes in the actual field) for your checkbox field in BuildTransactions.

The one drawback to this is that in WindowsClient (iClient or Standard) StandardGrid display, you will actually see an unchecked checkbox on the instance immediately following your last instance. BrowserClient won't display the New instance Literal default, so no problem there.

How To: Manually stop and start ServerManager

2007-11-06T04:29:00.001-07:00

Here's something that's nice to have in your arsenal, especially if you develop using iClient (which requires ServerManager be running, and thereby makes it impossible to stop and restart the ServerManager from your TenFold iClient session).

1. Log into your server. You can't log in as root, as ServerManager won't start under root. It also used to be the case that whatever user you started your ServerManager under, if you came back later and killed it (by hand) you'd kill ALL ServerManager processes running under that user. TenFold fixed this problem long ago.
2. Set your environment variables - i.e. the TENFOLD_ROOT for your product area and the TENFOLD_OPTIONS for your OptionsFile. One thing to note is that the ServerManager will start with the FastConnect setting you specify in your OptionsFile. Be sure that [FastConnect] DefaultServer has the correct Server and Port information.
3. It won't hurt to run tfstop or tfstop -hard, just to be sure any lingering processes from your previous ServerManager are cleaned up.
4. Here's the command to start ServerManager:

tfstart ServerManagerName user=TenFoldUser password=TenFoldPassword responsibility="ResponsibilityNameInQuotes" outputtype=screen

Here's a real example:

tfstart ConsultingPortalStandard user=jason password=jason responsibility="Applications expert" outputtype=screen

If you want to make it really easy, throw it into a script.

How To: Launch PowerQuery from TFL

2007-11-05T16:15:00.000-07:00

Launching PowerQuery from TenFold Language is only half of this post. The other half is actually the reason that I *needed* to launch PowerQuery from TFL - I needed to do a child-view query but ONLY return the records matching my child view query criteria. For example:

Let's say I have a set called CustomerOrders, which has the Customer table as the parent and the Orders table as its child. I need to query a specific Order, and I only want to get that Order record back (but of course I also want the Customer data, too). Under normal circumstances, if I queried on a child record, and the record existed, TenFold would return all the child records for the parent. I'm not sure I'm a fan of this behavior, but at least it's easy to work around. Hence, PowerQuery.

In my example I was actually Zooming from one transaction (where I had a list of OrderIDs) to the CustomerOrder transaction. Using the standard AutoQuery mode for my Zoom didn't give me the results I wanted, because I *only* wanted a single order record, but I would instead get back every order record related to the customer.

Instead of using a Zoom, I wrote a TFL function that launches a PowerQuery, which I create on-the-fly (and never store in the database). You'll want to look up PowerQuery Set in DocuManage for more information, but here's the logic for this particular case:

ADD PowerQuery NOSAVE
...NameName = 'CustomerOrderByOrderID'
...NameTransaction = 'CustomerOrders'
...NameExcludeFlag = 'N'
...CriteriaFieldName = 'CustomerOrderID'
...CriteriaOperationSmartCodeMeaning = '='
...CriteriaOperationCode = 'EQ'
...CriteriaOperand = iOrderID

CALL LaunchPowerQuery(PowerQuery)

What am I doing? Here's a run down:
1. Adding my PowerQuery to the PowerQuery Set, but specify NOSAVE so that I don't actually commit it to the database - I only want to create it, use it, and then go away.
2. I specify the Name of my PowerQuery, the Transaction against which I want it to run, and most importantly, the "NameExcludeFlag". Here's the explanation of this Field from PowerQuery Set in DocuManage:

"SmartCode code for ExcludeResults flag. Valid values are:
• Y – PowerQuery returns all parent instances that have a
child instance that matches your child view query criteria,
and returns all child instances for those parent instances.
• N – PowerQuery returns all parent instances that have a
child instance that matches your child view query criteria,
and limits child instances according to your child view
query criteria."

For my needs, I want to limit the child instances to only the one for which I queried, so I set ExcludeResultsFlag = 'N' (specifying 'N' seems counter-intuitive to me, but hey, it works).
3. I specify the rest of the necessary values in my PowerQuery definition, which are are pretty self-explanitory - I query CustomerOrderID where CustomerOrderID = the value I'm sending in iOrderID (which I get from an input parameter in my TFL function).
4. My TFL logic then calls LaunchPowerQuery (which launches the PowerQuery I added to the PowerQuery set) and voila, I have my CustomerOrder set queried with the sole matching child instance for the Order I specify.

How To: Validate credit cards

2007-11-04T04:35:00.000-07:00

Here's a TFL function I wrote that does the following:
- Validates the number provided matches the card type provided
- Validates the number length is correct for the card type provided
- Validates that the card has not expired
- Uses the Luhn formula (MOD10) to validate the card number

A few notes:
- The function RETURNS a value of 'Y' or 'N', denoting the success or failure of the validation attempt.
- The function calls ReportError and describes the cause of the failed validation, if applicable.
- The function requires four input parameters, in the following order: CCNumber, CCType, CCExpMo, CCExpYr
- This function simply tells you if the information on the card is valid. It doesn't tell you if the merchant will approve the card. However, if you dinged for every card you try to submit, it's a good way to avoid submitting bad card information and to save yourself some time and money.

My apologies for the formatting - Blogger makes it a bit tough. You can view the logic in a txt file here. Here's the logic:

#Check card expiration

#Format the year as YYYY if it's not already
IF iExpYr <>
...LET vExpYr = iExpYr + 2000
ELSE
...LET vExpYr = iExpYr
ENDIF

LET vExpYrLength = StringLength(vExpYr )
IF vExpYrLength <> 4
...CALL Error ("Card does not have a valid expiration year string")
...RETURN 'N'
ENDIF

CALL MonthNumber(&DBToday, vCurrentMonth)
CALL YearNumber(&DBToday, vCurrentYear)

IF vCurrentYear > vExpYr
...CALL Error ("Card has expired")
RETURN 'N'
ENDIF

IF vCurrentYear = vExpYr
...IF vCurrentMonth > iExpMo
......CALL Error ("Card has expired")
......RETURN 'N'
...ENDIF
ENDIF

#CARD TYPE Prefix Length Check digit algorithm
#=========== ====== ====== ======================
#MASTERCARD 51-55 16 mod 10
#Discover 6011 16 mod 10
#AMEX 34,37 15 mod 10
#VISA 4 13,16 mod 10

#Requires card type of DS, MC, VI, AM
IF iCCType NOT IN ('DS', 'MC', 'VI', 'AM')
...CALL Error ("Card type must be one of the following: DS, MC, VI, AM")
...RETURN 'N'
ENDIF

#Identify mis-matched card number and card type
LET vFirstDigit = SubString(iCCNum, 1, 1)
IF vFirstDigit = 6 AND iCCType <> 'DS' OR
...vFirstDigit = 5 AND iCCType <> 'MC' OR
...vFirstDigit = 4 AND iCCType <> 'VI' OR
...vFirstDigit = 3 AND iCCType <> 'AM'
...CALL Error ('Card number does not match card type')
...RETURN 'N'
ENDIF

#Identify incorrect card number length
LET vNumberLength = StringLength(iCCNum)
IF iCCType IN ('DS', 'MC') AND vNumberLength <> 16 OR
...iCCType = 'VI' AND vNumberLength NOT IN (13, 16) OR
...iCCType = 'AM' AND vNumberLength <> 15
...CALL Error ('Card type and number length mismatch')
...RETURN 'N'
ENDIF

#Start from second digit from right going every-other-digit in the card number
#Multiply number by 2
#If number > 10, add each digit (12 becomes 1 + 2 which equals 3)
#Add all numbers together
LET vMODPosition = -2
LET vMODNumber = NULL
LET vMODTotal = 0
LET vMODcount = 0
WHILE vMODPosition => vNumberLength * -1
...LET vMODcount = vMODcount + 1
...LET vMODNumber[vMODcount] = SubString(iCCNum, vMODPosition, 1) * 2
...IF vMODNumber[vMODcount] > 9
......LET vMODNumber[vMODcount] = (vMODNumber[vMODcount] - 10) + 1
...ENDIF
...LET vMODTotal = vMODTotal + vMODNumber[vMODcount]
...LET vMODPosition = vMODPosition - 2
ENDWHILE

#Start from first digit from right going every other digit in the card number
#Add all numbers together
LET vNumPosition = -1
LET vNumber = NULL
LET vNumTotal = 0
LET vNumCount = 0
WHILE vNumPosition => vNumberLength * -1
...LET vNumCount = vNumCount + 1
...LET vNumber[vNumCount] = SubString(iCCNum, vNumPosition, 1)
...LET vNumTotal = vNumTotal + vNumber[vNumCount]
...LET vNumPosition = vNumPosition - 2
ENDWHILE

#Add totals from two sections above
LET vCheckSum = vNumTotal + vMODTotal

#Determine the last digit of the total. Valid value = 0
LET vValidCard = SubString(vCheckSum, -1, 1)

#Failure
IF vValidCard <> 0
...CALL Error ("Invalid card number")
...RETURN 'N'
ENDIF

#Success
CALL Information ("Valid card number")
RETURN 'Y'

How To: Request a blog subject

2007-11-03T13:09:00.001-06:00

Shoot me an email! If there's something you'd like to do, but you don't quite know how to do it, let me know. No question is too silly (believe me, I've already asked them all), too simple, or too crazy. If I can't answer it, I'll find the answer for you.

How To: Display end-user specific data

2007-11-03T05:59:00.000-06:00

As I have time, I've been working on an application that I use to run my consulting business. I plan to eventually turn this application into a web-based product. It's called, for now, ConsultingPortal.

The staple, off-the-shelf business applications out there are ill-suited for my needs. SalesForce.com, QuickBooks, Microsoft Project...I have the same complaints about all of them:
- They're over built for my simple needs. This makes using them frustrating and confusing.
- They have overlapping functionality (which often requires maintaining duplicate data in different systems).
- They don't talk to one another (at least not without a fair amount of work on my part).

ConsultingPortal combines the core offerings of these products, eliminates the stuff I don't need, plus includes a handful of other features, into a single application. I'm going on about these silly details to give you some context for the subject of this post: Displaying end-user specific data.

Let's say I actually find the time to finish ConsultingPortal and I want to release it upon the world. It's web-based, and I'll (hopefully) have MILLIONS of end users. Since I'll be storing important, private data about my customers' businesses, it's important that an end user sees data for their business and ONLY their business. Here's the solution I've implemented to ensure this. It has three main parts:
1. Define a Preference
2. Set a value for this Preference at end user login
3. Limit data queried by this Preference value

I *could* use SecurityByValue, and I'll likely head down that path in the future, but for now, this solution meets my needs and it's very simple.

In my case, when I create a new end users account, I store their UserName in my AccountEndUsers table, which is a child of Account (which I populate upon creating a new account - this allows me to have multiple end users associated to the same account - i.e. employees to Company). When an end users signs on, I want to determine to which account they belong and then use a View condition to limit the data they query by AccountID. It sounds simple, and it is!

Step one: Define a Preference:

We're going to define a Preference for our AccountID. Doing this will allow me to create an in-memory OptionsFile setting Preference and assign it a value (AccountID), and then use the Preference/value as a view condition.

- Open Classic..Librarian..Define..Preferences. Enter a Section name (ConsultingPortal), SubSection name (CurrentUserAccount), Application (ConsultingPortal), and Preference (UserAccount).

Step two: Set a value for the Preference after login (but before launching any transactions):

- Write a function to set your Preference value in your in-memory OptionsFile. Here's what the logic in my function looks like:

READ FindUserAccountID WHEN
...AccountEndUsersEndUserUserName = &User

IF &ReadCount = 1
...CALL SetOption("ConsultingPortal",
.................."CurrentUserAccount",
.................."UserAccount",
..................AccountEndUsersAccountID)
...CALL CNSTLaunchConsultingPortal
ELSE
...CALL Error ("Unable to identify an account for this end user")
ENDIF

- Open the responsibility your end users will access. Right-click on the Responsibility name and select Advanced menu properties. Add your function to the "First actions" section. Upon logging in and selecting this Responsibility, this function will fire before anything else happens. Having done this, we can now limit the data our end users sees based on their AccountID.

Step three: Create a View condition for UserAccount..AccountID

- ConsultingPortal contains a single transaction, so this is quite simple to manage. Open BuildTransactions and query the transactions to which your end users have access. Click on the Views frame, right-click on the parent View name and select "Edit view details". Now, before we create our View condition, we obviously must be storing AccountID in all of our end user transaction's parent tables. Since that's the case, I just define a View condition as follows:
- Field: CompanyAccountID
- Operator: "="
- Preference: UserAccount

My end user will now only see the records associated with the CompanyAccountID to which his/her end user belongs!

How To: Display unique instances using "Limit within sort"

2007-11-02T05:50:00.000-06:00

Enterprise TenFold has a cool feature called "Limit within sort". This allows you to specify a field in a view and limit the number of duplicate instances displayed for that field.

For example, let's say you have a table called Guestbook. Visitors to your site can sign your guestbook and leave their contact information (name, email address, et cetera). You don't do any sort of validation on posts to your Guestbook, so it's entirely likely you have duplicate records - people sometimes forget and sign your guestbook more than once.

Now, however, you want to view a list of unique guestbook entries. Here's a case where Limit within sort is super handy. This is how you'd use it to display unique entries:
1. Decide the field you want to use to specify uniqueness - say EmailAddress
2. In BuildTransactions..Views, set your EmailAddress field's "Sort order" to some numeric value like 1.
3. Right-click on the View name and select "Edit view details"
4. Enter a value for "Maximum instances" - this is, stay with me now...the maximum number of matching instances you're going to allow to be displayed for the field you indicate.
5. Select the 1|EmailAddress field from the "Limit within sort" SmartPick.

When you open your transaction and query all the records in this table, it will now show only 1 instance for each Email address in your database, even if there are more than 1 instances of the same email address in the database.

How To: Round floating point decimal numbers

2007-11-02T03:23:00.000-06:00

This post originates from a message on the TenFold forum. I ran into it again yesterday, so I thought I'd put it up here. The original post had a bug in the sample code. I've included a fix for that here.

In short, the problem is that TenFold stores numbers as floating point decimals. That means if you have a product in your application with a price of $19.95 that TenFold actually stores it as 19.94999999999. This can be a problem if you want to use the price, say, in an email confirmation to a customer or to send to a third party for processing.

To compound the issue, TenFold Language's build in Round function doesn't really work on this. Therefore, you have to work around the issue yourself. Here's some code that'll do that for you.

Create a function that accepts a single input parameter and returns the good number. In this example, iBadNumber is my input parameter.

LET vBadNumber2 = Round (vBadNumber * 100,0)
LET vDecimals = SubString (vBadNumber2, -2, 2)
LET vBadNumber2 = Round ((vBadNumber2 - vDecimals)/100, 0)
LET vGoodNumber = vBadNumber2|'.'|vDecimals

RETURN vGoodNumber

This function strips the first two decimal places from my input number, rounds them, then creates and returns a new number string. You'd call the function as follows:

LET vPrice = FixRounding(ProductPrice)

How To: Use Phantom Tables in your application

2007-10-31T17:08:00.000-06:00

A "Phantom" table is a logical table that has no physical implementation. This means that you can't query or store data for a Phantom table because it doesn't really exist in the database.

You might wonder why you'd want such a thing, since you are probably building a database-driven application. There are a few reasons. Here's one example:

I'm currently working on an application for one of my customers that, upon entering a customer's ContactID, it sends an XML request to a third party which returns a list of that customer's current subscriptions.

We want to know, in real time, a customer's current subscriptions, and we only need to know the current subscriptions for the customer in question. There is no need to persist this information in the database, as it would require around-the-clock processing and would go largely unused (we only get the current subscriptions for a few customers each day, but we have thousands of customers in our system).

Therefore, we want to simply display a list of current subscriptions, but not store them in the database. This is a perfect scenario for using a Phantom table. Here's how it goes down:

1. Enter a customer id, which triggers the XML messaging to return their current subscriptions
2. ADD the returned list of current subscriptions to a Phantom table that exists in our set as a child of the Customer table
3. Review the customer's current subscriptions and go about our business

If we were to change some data in the Customer table, we'd persist those changes to the database, but the list of current subscriptions would simply disappear until the next time we triggered our XML call for the customer's current subscriptions.

To define and use a Phantom table, there are few things you should know:
1. In DesignDatabase, under the Advanced tab, check the "Phantom table" check box. This will prevent DesignDatabase from implementing this table in your physical database. In fact, you won't even have the option of creating it when you click [Change database]
2. Any time you add this Phantom table to a set, you want to be sure to prevent your transaction from attempting to Read or Save data to the table. To do this, use BuildTransaction to find the view in your set that's based on the Phantom table (Views frame). Right-click on the View field and select "Edit view details". Now check the "Do not read" and "Do not save changes" checkboxes and click [Save and return]. If you forget to do this, you'll get an "Error in reading" or "Error in add" failure. (As a side not, hopefully TenFold will automate this process. You would never want to attempt to Read or Save changes for a Phantom table. These checkboxes should be automatically checked (and be non-modifiable) for any view that's based on a Phantom table).

How To: Query without clicking [Query]...[Run query]

2007-10-30T15:26:00.000-06:00

Here's a neat little trick you can often use to query data without actually clicking [Query] and then [Run query].

For example, let's say you have an application to manage your customers and their orders. Whenever a customer calls you, the first thing you do is bring up their account. To do this, you ask the customer for their telephone number, or their email address, or anything that uniquely identifies them in your system. You enter this information, click [Run query] and voila, their data appears.

I'm lazy and I don't like to click buttons that don't need to be clicked. I also think that a good application doesn't require an end user to do stuff they shouldn't have to do. Wouldn't it be nice for them to be able to enter, say, the customer's email address and tab out of the field and have their account data query up automatically (no click of [Run query] necessary)?

Here's how to do just that.

First, we need to assume that you can query your customer's information based on the customer table's primary key. This should definitely be the case, but it's possible that getting the right ID for the right customer might be a little challenging.

Let's take the simplest case and just assume that you use your customer's email address as the primary key in your customer table. You will attach a Business rule Function to this field with a single input parameter mapped to the email address field. The function will call TransactionOperate() and perform a KeyQuery. For example, your function might look like this:

Function name: AutoQueryCustomerInformation
Parameters: iEmailAddress
Logic: Call TransactionOperate("KeyQuery")

When you compile the function you'll receive a warning that you never use the input parameter. No big deal. It's only there to trigger the function.

TransactionOperate("KeyQuery") performs a query on your parent table using the ID in your primary key field (in this case, email address). The end result is that you enter a customer's email address, tab out of the field, and the query happens automatically. With a bit more TFL logic, you can make it intelligent enough to only perform the query if the customer exists, and if they don't, allow you to just start entering their information straight-away. For example:

READ Customer WHEN Email = iEmailAddress

#If customer doesn't exist, let's search
#again or add them to our system
IF &ReadCount = 0
...CALL Information ("Customer does not exist. Please enter a different email address or enter this customer's information")
...RETURN
ENDIF

#If we made it here, we know the customer exists, and
#that only one customer exists since we're performing
#our READ on the primary key field (i.e. it has to be unique)
#Therefore, let's query the record up

Call TransactionOperate ("KeyQuery")

That's it!

How To: Simplify calls to ReportError

2007-10-30T13:52:00.000-06:00

I'm not ashamed to admit that I have only one EndUserMessage defined in most of my applications. The Message is simply '&1', which means that whatever literal string or variable (or combination thereof) I pass ReportError will appear in the message viewer at the bottom of my screen.

One day I decided it was a pain in the ass to always be typing something like:

Call ReportError ("BLAH0001", "Error", "Your database just caught fire and burned all your data!")

So, here's what I did: I created two functions, Information() and Error(). Both functions accept a single input parameter (the message I want to display in the message viewer). The functions then execute the full ReportError call. For example, Information() accepts an input parameter of "iMessage". The function's logic looks like this:

Call ReportError ("ENDW0001", "Information", iMessage)

Nowadays, instead of typing the whole ReportError call, I only need to type this:

Call Information ("This is sooo much better")

How To: Create a new directory using TFL

2007-10-29T17:21:00.000-06:00

There may be another, simpler way of doing this, but I threw this together in a hurry. It's simple and it works.

If you're on UNIX, it's very easy. You'll create a function with a single input parameter (in this example, "iDir"). Your logic will look something like this:

IF RunProgram("mkdir", "Forever", iDir) <> 0
...CALL ReportError ("ABCD0001", "Error", "Failed to create directory "||iDir)
ENDIF

For Windows, I simply created a batch file called mkdir.bat which contained "md %1". You run this the same as above, but specify the directory of your mkdir.bat file (i.e. c:\mkdir.bat).

Info: TFL Built-in Function of the week - InstanceCount()

2007-10-29T10:40:00.000-06:00

InstanceCount tells you the number of instances in a View. I find myself using this simple built-in function all the time. There are a couple of ways to use it:

1. LET vCount = InstanceCount (SetName, "ViewName")
This is a nice, straight-forward way of getting the number of instances in a view into a variable so that you can use it in your TFL function.

2. IF InstanceCount (SetName, "ViewName") > 10
I use InstanceCount in this manner all the time, especially when creating an array and doing an ADD with the result. For example:

IF InstanceCount (ViewTodaysOrders, "Orders") > 0
...LET i = 0
...LET vOrderID = NULL
...WHILE Orders
......LET i = i + 1
... ..LET vOrderID[i] = OrdersOrderID
...ENDWHILE
...ADD ProcessOrders
... ..OrdersOrderID = vOrderID
ENDIF

This makes it simple for me to ONLY create an array and ADD my records IF there are actually records to begin with. You can find the full documentation on InstanceCount in the "Set and Database Functions" document in DocuManage.

Idea: BuildFunction & DocuManage integration

2007-10-28T15:26:00.000-06:00

How many times do you need to stop writing a function to log into Documanage to refresh your memory of a certain built-in TenFold Language function call? For some reason, I can never remember the parameter order for StartTransaction()...

Wouldn't it be cool if TenFold integrated a web service into DefineFunctions that would allow you to find information like this without even having to leave your TenFold client?

Info: MySQL user lengths

2007-10-28T14:58:00.000-06:00

I ran into a MySQL issue today that's I've not experienced before. I was setting up a new environment, created from another environment's database export. I updated all the new environmentss implementation values (see below for the script to do this). When I started a new client session, I received a signon failure. I thought this strange since I knew my signon username and password was correct.

I checked my client log and found the following error message:

**** EnterpriseTenFold started
2007/10/28 11:33:47.390 | Error DBOD0005: From MYSQL: '[MySQL][ODBC 3.51
Driver]Access denied for user 'endwhiledict_pro'@'localhost' (using password:
YES)'
2007/10/28 11:33:47.390 | Error DBLG2073: End-user 'jason' was unable to sign
onto database instance 'ENDWHILEDICT_PROD:endwhiledict_prod' as physical user
'endwhiledict_prod'.
2007/10/28 11:33:47.390 | Error DBCN0558: Failed to signon to the vendor
database.
2007/10/28 11:33:47.390 | Error DBCN0507: Error during DB Sign On.
2007/10/28 11:33:48.046 | Error SC000038:You entered an invalid end-user name
or password. Please check your end-user name and password and try again.
Contact your system administrator if this problem persists.

After banging my head for a while trying to figure out why my client was attempting to sign on using endwhiledict_pro instead of (what I thought was) the correct MySQL user of endwhiledict_prod, I found that MySQL limits the length of MySQL users to 16 characters. I changed my DATABASE implementation to use a different user/pass and was able to move on.

Here's an example of a script to update the database implementation:

Update dd_data_implementations set implementation_data='/' where element_type='DATABASE';

Update dd_data_implementations set implementation_data=':' where element_type='DBINSTANCE';

Truncate table dd_preprocessed_data;

How To: Swap a child view foreign key reference

2007-10-28T13:16:00.000-06:00

This post describes how to change the reference used by a child view. This is pretty straight-forward using the classic tools, but I don't think it's supported yet in BuildTransaction.

Case example: You have a parent-child relationship, which you want to keep, but you want to change the reference used by the child view so that child records are returned based on a different foreign key. For example:

Parent table: Patient
Child table: PatientVisits
Current child view reference: PatientVisitsPatientIDToPatientPatientID
Join: PatientVisitsToPatient
From table: PatientVisits
From column: PatientID
To table: Patient
To column: PatientID

Let's say that you've found that you have duplicate records in the Patient table - perhaps due to mis-spelling a patient's name. This means that for the same patient you have multiple PatientIDs. However, let's assume that the Social Security Number for your patients is always correct. You'd like to see all a patient's records regardless of which of the duplicate patient records you might query. To do this, you can simply change the child view's reference to use a join based on Social Security Number (assuming, of course, that your PatientVisits table contains the patient's Social Security Number).

Step 1: Query your set in RefineSets
Step 2: Select the Views frame
Step 3: Right click on the PatientVisit (child) view's Reference and Zoom to "Reference"
Step 4: In the Links region, right-click on the From table field and Zoom to "RefineTables". If you already have the join you need defined, skip to step 14.
Step 5: Confirm that you have a Key defined for the Social Security Number column (if not, create one)
Step 6: Go back to RefineSets and right-click on the To table column and choose "RefineTables"
Step 7: Confirm that you have a Key defined for the Social Security Number field (if not, create one). Note the Key priority.
Step 8: Go back to the RefineTables instance for the PatientVisit (child) table
Step 9: Select the Joins frame.
Step 10: Click [New]
Step 11: Name your join, select the correct "To table" and select the "To key priority" that you noted above in step seven.
Step 12: Select the "From column" and "To column" values (in this example, SocialSecurityNumber)
Step 13: Click [Done]
Step 14: Go back to RefineSets..References, click [New]
Step 15: Name your reference
Step 16: Select the "From table"
Step 17: Select the name of the join you just created (or that already existed, if you're lucky)
Step 18: Click [Done]
Step 19: Go to the Views frame and select the name of your new reference in the child view's "Reference" field
Step 20: Click [Done]

You're finished. Re-open your transaction and query your records. You'll now have PatientVisits returned based on their SocialSecurityNumber rather than their PatientID.