Disagreeing with the Past

I've been trawling through past articles and snippets that I've written, and came across this letter to the new editor of Dialogue in which I disagreed with an article in the previous issue.

I don't remember ever sending this! But since I don't disagree with what I wrote then (this was April 2002), and since I still see DOS-like code permeating DfW and Ff apps, I'll print it now.

You'll have to imagine the article I was replying to, unless you have a copy of that issue.

Dear Editor,

I beg to differ with some points made in the article on page 39 of the Winter 2001 Dialogue discussing DfW procedure rules.

“Where possible, fields that sort or group should be at the top of the procedure.”

This is misleading.

The position of grouping in a DQL determines which form/record object set a field belongs. It is not merely a matter of positioning them “at the top”, but understanding how where they are positioned affects the QBM and body created.

For example:

for Invoice ;
list records
CustomerID in groups ;
CustomerName ;
Amount .

creates two containers – one for the grouped items, and one for the items that belong to that group. A previously – and maybe even currently – undocumented ‘feature’ is that all the fields above the grouped field belong to the group container.

Here the customer name will repeat for every item in that group, which I presume is not what the user wants.

The proper DQL is:

for Invoice ;
list records
CustomerName ;
CustomerID in groups ;
Amount .

This will position CustomerName in the container it belongs – the grouped items – and not in the individual records for that group.

In DOS, people were used to writing the DQL and then re-arranging the fields in the format. In DfW, you must write the DQL to position the fields in their proper container in the body.

For the same reason, listing in order should be done in the appropriate container. Thus:

for Invoice ;
list records
CustomerName ;
InvoiceDate in order ;
CustomerID in groups ;
Amount .

Putting InvoiceDate above CustomerID will result in that field appearing in the grouped container, and only showing the value of the first record in the group – again, not what the user wants.

“Ad-hoc relationships based on virtuals…”

One key problem with virtual fields is that many people don’t understand how to use them properly. In addition, whereas the DOS environment meant that even absurd coding using virtuals was usually ignored, the Windows environment brings with it new memory issues that allow for multi-tasking and the like, and cannot be ignored.

As I pointed out a few Dialogues ago, I found a vertical 4.53 application that I migrated for testing purposes to DfW. I found that it migrated fine, except for one problem – a virtual field derived as “lookup ThisRel Value”, where the virtual itself was the match in the relationship! DOS forgave this; DfW (rightly) objected.

Whilst I would say that one source of GPFs is poor use of virtual fields, I would also say that, used properly, virtuals belong in the DataEase application (see, for example, Chris Gadsby’s excellent article on project methodology in the same issue, p18, col 1, “Entity Level Data Dictionary”, in which he singles out as part of the process the identification of virtual attributes).

Thus my concern is that people are put off using virtuals “because Sapphire said so”. You were correct that they “may cause recursive calculations” … but then again, they may not.

I also think that the ‘recursive problem’ is not limited to ad-hocs, but applies to all relationships.

“Reference to System forms are invalid in DfW…”

Since when? That should be ‘system tables’, of course, but I know of no reason not to use them, as long as DfW has a corresponding table. After all, the DQL scheduler is just a pseudonym for “for system configuration with computer name = blank…”.

Of course, you have to check “show system tables” in the application preferences dialog. And maybe some of the things people do with system tables are dubious. But the above statement is in and of itself incorrect.

“The output command .. in many cases … can be dropped.”

Whilst it is correct to point out its redundancy, I’d say that in every case it can be dropped, though please give an example of where it can be kept if I’m wrong. I’ve never used it in a DfW application, since we can use nested fors and multiple list records instead.

I’m slightly dubious about the line “if a … procedure has multiple non-nested for loops, break it up into separate procedures”. Such constructions are possible, as long as one understands how the QBM/multi-view is affected. But I agree that ‘rambling’ DQLs that don’t respect the QBM/multi-view are likely to be problematic, and am minded that you group these under “other general rules”, especially since such considerations were not applicable to DOS, and therefore even the best programmer could code stuff that won’t work in DfW. Your recommendation in the next paragraph to “keep procedures to a single outer for loop” is good advice.

The article also continues to refer to ‘forms’. The longer the DataEase developer/user confuses forms with tables, the longer it will take to appreciate the true power of separating form and table design.

I agree with all the other suggestions and comments made in the article.

Sorry to sound so harsh, but I’ve tried in these pages and on the various forums to develop a true understanding for the readers of how DfW works, and to overcome some of the many mis-assumptions about the product.

Your turn to disagree...

Relatively Speaking, 2010

I’ve been thinking a lot about relationships recently, but writing a ‘Definitive Guide’ seems such an enormous task that I’ve decided to make this a parts work, and focus on one area per blog.

Here’s a rough guide to where I’m going, though.

Relationships are one of the jewels in the DE-Ff crown. The simple connection of one table to another by common values yields many possibilities. Yet these have been little explored in writing, and there are myths from the DOS days still floating around.

Relational Groups

Think of relationships in two groups: structural and operational. Structural ones arise out of your entity relationship modelling. Operational ones, on the other hand, may appear to break modelling rules, but have many uses.

The Relationships form essentially consists of three (well, 3 ½) main areas: the two tables to be matched; the match fields; and the names.

(The half is referential integrity, which I’ll look at much later.)

You can link two different tables, or you can link the same table to itself.

The fields you specify either uniquely identify that table, or they don’t. If they do, that side by definition is the ‘one’ side. If not, it must be the ‘many’ side.

We’ll tackle the thorny issue of ‘fields that identify’ later.

There is one exception to the above – when there are NO match fields! I’ll definitely be exploring this more!

Name Your Relationship!

Finally, there is the relationship name. There are many different types of relationships, and the name should reflect this.

The name is an alias for how one side sees the other. You can think of it as being a property of that side, so that “Customer.SeeInvoices” (to borrow notation from object oriented programming) shows each customer has a collection of zero or more invoices.

My naming suggest the type of relationship, and I also try to make it as ‘English’ as possible. However, we are restricted by a maximum of twenty chars in DE 6.x, and since I have not yet confirmed that there are no issues with longer names in Ffenics, I still tend to stick to that limit. That can be tricky, of course, depending on the name and nature of the tables involved, so there may have to be a compromise for practical reasons.

By giving it a meaningful name, it should be quite clear what you can do with that relationship. ‘SeeInvoices’ means there may be a sub-form that displays all customer invoices. GetCustomer shows that the Invoice table can reference details in its one and only customer. Note that I use not only a prefix key word, but also the full name of the table (if the character count permits), and singular or plural.

One last comment: my preference is to put the ‘one’ side on the left and the ‘many’ on the right. This makes searching relationship records a lot easier!

Getting Round The Crash

I'm sure you know this one, don't you?

If, in any Windows version of DataEase or Ffenics, you are happily developing away and you get the dreaded "Exception" message to tell you that all the work you just didn't save is heading down to Rio, then:

1. Curse
2. Curse again
3. Click the DataEase or Ffenics screen behind the message BEFORE you click ok, and
4. Go File -- Save/Save As, etc
5. Now click the GPF ok button to bomb out of your application.

You might even find you can continue working for a bit with that dialog box on screen ... but you might not get a second chance, so save, bomb out, and jump back in again.

Can 't guarantee it will always work, but as you only lifeline when this happens, always worth trying!

You live and Learn

Just found out something I didn't know re scripting.

If you are in the object about which you are scripting, you can omit the object name.

So, for example, if this is the ValueChanged event for FieldA, you DON'T need:

FieldA.Fill.Color.Red := 255 . etc

But can get away with:

Fill.Color.Red := 255 .

Show () .

Which saves some typing!

(I will also save some typing by NOT going back to old articles and blogs to adjust code. Sorry!)

More importantly, it means that a lot of scripts can be generic, as long as they reference the properties and methods of the current object, and that you are pasting them into objects that have those properties and methods.

Time at the Piano in New York

Just uploaded a new article to my website about my time in New York working on the World Trade Center disaster, and developing a new application for the Office of Chief Medical Examiner.

I think this is the third version I have written. The first appeared in Dialogue Magazine circa Winter 2001/2002, and I have no idea how to get hold of a copy.

The second was included in a volume of writings post 9/11 called 'On The Ground After 9/11' (ISBM: 789029073).

In this version, amongst other things, I compare DataEase and Ffenics to a piano, and the consultant to a pianist. A piano can't do everything. But compare the solo piano version of 'Rhapsody in Blue' to the full orchestral score, for example, and it is clear that on its own it does a pretty good version.

Similiarly, the piano is not every instrument, but is still a damn good solo instrument for beginner and virtuoso alike.

So it is with Ffenics. In the hands of a good solo performer, you can put together a pretty decent application to solve your small business needs.

Even in the hands of a rank amateur, does it not make a pleasing sound?

The devil's in the DQL...

I'm probably going straight to hell, but ...

Our Ffenics,
Who art F7,
Relationships be named.
Thy index come,
F10 be done
In DOS as it is in Windows.
Give us this day our DQL
And forgive us our table views,
As we forgive those who table view against us.
Lead us not into temp form
But deliver us from Access.
For thine is the function,
The aspect and the query.
For ... ; enter a record
.end

Suggestions? Improvements? Absolution?

Letters, Loopers and Some Very Strange Relationships

Time for some daft, ‘out there’ ideas … and a bit of fun.

Here’s the goal: let’s imagine an environment, such as a busy, messy factory floor, where gloves and mess make it impossible to use a keyboard.

So you have a touch-sensitive screen, covered with a suitable protective layer, and want to give users the ability to look up something by typing in a reference number or keyword.

You need an on-screen keyboard, and because you love doing things in Ffenics or DataEase, you’re determined to create that keyboard using your favourite software.

Something like this:

So, we have a label, and field into which a value can be typed. There’s a funny arrow button to the right which is masquerading as a backspace button, a rough QWERTY keyboard plus integers, a blue bar representing the space bar, and a button labelled ‘Go’ which could be used to start a report that returns whatever it is supposed to return.

I’m using a couple of tricks here, including one that I’m surprised I’ve only just started to use regularly.

If you’re looked at other articles by me in which some set of buttons with letters from the alphabet is used, you should know that I have not wasted time and tightened every muscle in my back by laying out 36 buttons, each with their own set of scripts, to represent the keyboard. You should guess that I’m using a subform (just for the QWERTY letters and numbers; everything else is on the main form). And that the subform is build on my swiss-army-knife-of-a-dataset, Looper.

Or maybe you haven’t a clue what I’m on about. So let me explain.

Looper is a table of integers. To be precise, it is a table of ordinal numbers -- that is, integers in order. Why I call it Looper is my own business.

The area on screen is a 10 x 4 subform. Each record in that subform is a number from 1 to 40, the first 40 records in Looper.

The object with the appropriate letter displayed is a ‘layout-only virtual’, that is, a field that only exists on this document. It happens to be called vLetter, is one character long, and is derived:

midc ( "1234567890QWERTYUIOPASDFGHJKL ZXCVBNM " , LoopNo , 1 )

That odd string is the set of characters from your QWERTY keyboard, starting with the numbers across the top, then each row of upper case letters. The spaces towards the end help with the indenting of the keyboard.

LoopNo is our actual field in the form, the one containing the ordinal integer. The derivation basically finds the corresponding letter for the key. For example, the letter ‘R’ is in the second row and is four columns over on the keyboard; the ordinal number behind that record will therefore be 14, and the 14th letter in my string is the letter ‘R’.

As I said, these are records in a subform. But what’s the nature of the relationship? Well, this is one of my new tricks: the main form and Looper match based on … NO match fields.

Yes, they match on nothing. Nada. Zilch. And as a result, ALL records in the subform display. Try it and see with any old forms.

Now, Looper probably contains several thousand integers. But because I have removed the scrollbar from the subform, we only see the first 40 records. (Actually, if I had used a button instead of a text object for the letters, we’d have to deal with a minor issue about scrolling because the button can get focus, but let’s worry about that some other time.)

The letter field has two scripts: a clicked event for adding to the string of text in the main form’s ‘code’ field, and a ValueLoaded event to hide the field if its value is a space (to make the layout look a bit more like a keyboard).

The clicked event script is:

define "t" number .
Customer.FirstName.Value := concat ( Customer.FirstName.Value ,
  if ( getarray ( 1 ) = "SpacePending" , " " , "" ) ,  vLetter.Value ) .
t := setarray ( 1 , "" ) .
Customer.FirstName.Show () .

In a recent blog post I covered how to update values between sub and main forms using scripts. Basically, we are adding the value of the letter to the end of the value already in our main form field.

Spaced Out
The getarray bit obviously needs explaining. It turns out that when you concat a space to the end of a field, the value in the field seems to immediately be trimmed of the trailing space.

The dark blue bar, as I mentioned above, is the space key. When this is clicked, this script:

define "t" number .
t := setarray ( 1 , "SpacePending" ) .

justs sets a flag, using array #1, to indicate that we are expecting a space. When a letter is clicked, we can then first insert such a pending space before the letter, and get around the trimming of spaces described.

Each letter also resets array #1 to blank, effectively meaning that the space is no longer pending.

The valueloaded script on the letter field is:

if vLetter.Value = " " then
  vLetter.Hide () .
else
  vLetter.Show () .
end

Two other things need mentioning. The first is that I found issues with Ffenics if the record contained no actual fields from the form. So Looper’s LoopNo is actually on the record, scrunched up in the top left corner. It has its new display option ‘hide’ checked, which means that it is never actually made visible. DataEase for Windows scripts didn’t need underlying fields present.

The other item is the backspace button. This deletes one character at a time from the string. Its script is:

Customer.FirstName.Value := firstc ( Customer.FirstName.Value ,
  length ( Customer.FirstName.Value ) - 1 ) .
Customer.FirstName.Show () .

Of course, you might want to have more characters than I’ve chosen. I’m sure someone could have some fun working out how to put the display into upper or lower case, or to imitate the keyboards found on some PDAs and smart phones. You could also add a ‘Clear All’ button. And another intriguing problem would be to have more than one field for data-entry. That’s your homework.

Time to implement this from scratch: less than one hour. Click title for PDF version from my website.

On DE/FF Ears: Looking Up

On DE/FF Ears: Looking Up

... And Looking Down

Following from my last post we should consider how you use script to point from an object in a main form to a specific row in a subform.

Here, life is not so rosy. Yes, this script will copy main form value to subform:

MySubform.MySubformField.Value := MainFormField.value .

MySubform.MySubformField.show () .

The trouble is, this will only update the first visible subform row. Now, if you're only displaying one subform record, that might not be an issue. But otherwise, whatever is the top row currently visible is the one that is targeted. Not the first in the dataset, note: the first visible. Scroll the list so that the top one pops off and you'll now be affecting the value in the row that was previously below.
 
Now, I'm not entirely sure why one would want to do such a thing in the first place, but no doubt someone will want to do something like this. I therefore post for completeness.
 
If the goal was to change the value in all sub records, you could do this using a procedure instead, followed by use of the RefreshScreen action (all existing records will magically be updated). You could also include the field you want to copy to the subform records in a relationship, and use that relationship for the subform.
 
Thoughts, anyone?

Looking Up

Here's a quick Ffenics scripting snippet, on my mind since it just came up in a bit of support. Should also apply to DataEase for Windows 6.52 and 7.x.

You have a sub-form, and you want to copy a value to your main form:

MainForm.MyTargetField.Value := MySourceField.Value .

MainForm.MyTargetField.Show () .


This script could be placed in a button's clicked event, or in the clicked event of a virtual field. Just make sure in either case that this is an object on your subform.

Note the 'dot notation' to navigate from the outermost object inwards to locate the target field. MainForm is the name of the form object that wraps the entire document. This will be the same as the name of the actual form/table on which the document is based, whose name you cannot change. Just bear in mind we are talking about the object on screen, and not the form/table defining and containing your data.

MyTargetField is a field object on MainForm. Again, the name is likely to be the same as a field in your form/table, but, again, we are talking about the textbox object on screen, and we are setting its value property.

The ':=' assignment is sufficient to perform the data change (if you only had this line of code, clicked the button in user view and then attempted to close the document, you'd get a 'save data changes' message). However, it does not cause the screen to be repainted, and therefore without the '.show()' method, it would appear as though nothing has happened.

The source value resides in a field object MySourceField in your subform, and on that same subform will be the button with this code. For that reason, you don't need any dot navigation to get to the appropriate field, and this code will update the main form value with whichever is the appropriate value in the subform.

As with many scripting examples, it seems to take a lot of words to describe what is going on. The constant key confusion with scripting, I find, is that people mix up the concepts of the data with the objects on the form. Hence why you have to refer to the value property of the field object, and not simply the field name itself as you might in a field derivation or DQL.

Doing the scripting the other way -- from main to subform -- is, however, problematic. I'll look at that in a later post.

The Model Is King, Pt 1

In DataEase and Ffenics, the data model -- that thing that is represented by a little diagram in the query by model dialog -- is king. Whenever a change in a procedure, say, sends your procedure body to the waste disposal, you can bet you made a change that affected the data model.

I was reminded of this in a support call this week, where the user had a pride-and-joy procedure with a beatiful, bouncing layout ... and they wanted to make what seemed like a simple change. They added a line of code before the main 'for' loop that checked to see that a value in the data-entry form was set:

if data-entry MyValueToCheck = blank then exit . end .

Why would such a simple addition bring the roof down? Because it changes the data model.

Basically -- and this is another golden rule -- for every document, there can only be one parenting table. Perhaps less obvious is that this 'if' command required something to parent it ... and in these instances Ffenics and DataEase inserts its standby DQL Scheduler. Try it and see:

1. Mock up a simple for/list records
2. Accept the default body
3. Move one of the fields just to highlight that it is changed
4. Add an if command above the for (try 'if year ( current extended date ) = 2010 then exit . end .')
5. Compile and accept the default 'merge changes' for the new layout.
6. Note than previously moved field is back to its default position.
7. Go to Document -- View Data model to see that the DQL Scheduler table has been inserted at the left.

I'm working on an article to document what types of changes will kill your body/layout.

In the meantime, there are several workarounds (other than start again...):

1. Add the sense of the 'if' to the selection criteria for the main 'for' ('for MyTable with data-entry MyField not = blank' )
2. Add the 'if ... exit ... end' statement as is immediately after the 'for MyTable'
3. Add some logic (e.g. make the field required) to the data-entry form/interactive header to do the check for you in the first place.

Note that 1 and 2 will likely incur a small performance overhead depending on what else you are doing and on the number of records in MyTable -- you'll have to be the judge of what is acceptable.