HowTo: Quickly and easily display the number of child instances

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

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

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

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

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

Update: READ using Date for a DateTime column

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

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.