Tuesday, 26 January 2010

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

Suggestions? Improvements? Absolution?

Wednesday, 20 January 2010

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 () .
  vLetter.Show () .

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.

Tuesday, 12 January 2010

... 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.

Tuesday, 5 January 2010

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.