Database - functionality for front end application

Melvin Cox melcox at
Sun Nov 16 22:22:32 EST 2003


MANY THANKS for your prompt and very thorough response.

I have successfully implemented each of your examples.  The only 
modification I needed to make was to add quotes around the value of the 
"affiliation" field in Problem #1 (my database requires the use of quotes as 
string delimiters).

My code for the group is therefore:


on mouseUp
   put "SELECT * FROM Business WHERE Bid=" \
       into tSQLQuery
   put quote & field "affiliation" & quote after tSQLQuery
   revSetSQLOfQuery "AffiliatedBusiness", tSQLQuery
end mouseUp


After implementing your code for Problems One and Two, I noticed that the 
desired functionalities worked flawlessly EXCEPT when making updates to the 
affiliation field on the form.

I have worked around this by adding an on closeField statement on the 
affiliation field which combines the code addressing Problems One and Two.

Am I correct in the assumption that the code for a field has prescedence 
over the code for the form?


on CloseField
  put "SELECT * FROM Business WHERE Bid=" \
      into tSQLQuery
  put quote & field "affiliation" & quote after tSQLQuery
  revSetSQLOfQuery "AffiliatedBusiness", tSQLQuery

  put the date && the long time into tDate
  convert tDate to dateItems
  put item 1 of tDate & "-" & \
      item 2 of tDate & "-" & \
      item 3 of tDate & " " & \
      item 4 of tDate & ":" & \
      item 5 of tDate & ":" & \
      item 6 of tDate into field "last_update"
end CloseField


My sole remaining problem (for the moment) is how to force capitalization of 
the affiliation field, while at the same time retaining the other 
functionalities, as above.

Adding your on keyDown statement to the script for the affiliation field 
results in data entry being CAPITALIZED, but the update of the timestamp and 
display in the CompanyName field are not done.

What am I doing wrong?


----Original Message Follows----
From: Jan Schenkel <janschenkel at>
Reply-To: How to use Revolution <use-revolution at>
To: How to use Revolution <use-revolution at>
Subject: Re: Database - functionality for front end application
Date: Sun, 16 Nov 2003 09:52:03 -0800 (PST)
MIME-Version: 1.0
Received: from ([]) by with 
Microsoft SMTPSVC(5.0.2195.6713); Sun, 16 Nov 2003 09:52:20 -0800
Received: from (localhost [])by 
(Postfix) with ESMTPid 74B759300A7; Sun, 16 Nov 2003 12:50:42 -0500 (EST)
Received: from 
([])by (Postfix) with 
SMTP id 55A7D9300A0for <use-revolution at>;Sun, 16 Nov 2003 
12:50:39 -0500 (EST)
Received: from [] by via HTTP;Sun, 16 
Nov 2003 09:52:03 PST
X-Message-Info: QIy1oIULmHdATnwcsKwK1B0IDJfg6qG0
Delivered-To: use-revolution at
Message-ID: <20031116175203.64511.qmail at>
In-Reply-To: <Law10-F729SLB1ygHZl0000d0a0 at>
X-BeenThere: use-revolution at
X-Mailman-Version: 2.1.1
Precedence: list
List-Id: How to use Revolution <>
<>,<mailto:use-revolution-request at>
List-Archive: <>
List-Post: <mailto:use-revolution at>
List-Help: <mailto:use-revolution-request at>
<>,<mailto:use-revolution-request at>
Sender: use-revolution-bounces at
Errors-To: use-revolution-bounces at
Return-Path: use-revolution-bounces at
X-OriginalArrivalTime: 16 Nov 2003 17:52:20.0304 (UTC) 

--- Melvin Cox <melcox at> wrote:
 > I am evaluating Runtime Revolution, but have been
 > growing increasingly
 > frustrated in attempting to build the shell for a
 > front end database
 > application.
 > Although I have been successful in connecting to a
 > local database via ODBC,
 > I have run into numerous difficulties implementing
 > certain on-form
 > functionalities.

Hi Melvin,

Let's see if we can get you back en route :-)

 > Problem One: Posting of results from "lookup" query
 > -------------------
 > Whenever a new row is displayed (via Query, Next or
 > Previous calls), or
 > whenever an entry or modification is made to the
 > form's Affiliation field, I
 > wish to automatically populate the form's (display
 > only) CompanyName field
 > with the results of the following SQL query:
 > select Org_Name from Business where Bid = "<value of
 > the form's currently
 > displayed affiliation field>"
 > How can this best be accomplished in Revolution?
 > [Note: I do not understand the process by which the
 > currently value of a
 > field is captured and used as a variable within a
 > SQL query].

The easiest way to refresh a related query, is the
following :
- I'll assume that the card field "affilitation" is
linked to the database field of the same name
- to make it easier for yourself if you would like to
add more fields from the affiliated Business record,
make a new query "AffiliatedBusiness"
- connect that query to the database, and set its SQL
query to :
   SELECT * FROM Business
- if you don't have a field "CompanyName" on the card
yet, create it, and link it to the new query and its
Org_Name field
- group the record navigation buttons (first / prev /
next / last)
- now set the script of that navigation group to :

- from now on, whenever you click on one of the
buttons ion the navigation group, any fields you have
on the card that are linked to the AffiliatedBusiness
query, will be automatically updated.

 > Problem Two: Placing the current time into a field
 > ------------------
 > Just prior to an insert or update of any row, I wish
 > to populate the form's
 > Last_update field with a datetime value reflecting
 > the current date and time
 > (ie. "2003-11-14 13:17:39.250").
 > How can this best be implemented?

Okay, it's not down to the millisecond, but try the
following tactic :
- whenever the content of a field is cha nged, a
closeField will be sent ; so to update the timestamp
field with the right info whenever that happens, put
the following in the card script :
on closeField
   put the date && the long time into tDate
   convert tDate to dateItems
   put item 1 of tDate & "-" & \
       item 2 of tDate & "-" & \
       item 3 of tDate & " " & \
       item 4 of tDate & ":" & \
       item 5 of tDate & ":" & \
       item 6 of tDate into fld "Last_Update"
end closeField
- now when you do an update of the record to the
database, you can simply include the content of the
field in your Update and Insert query.

 > Problem Three: Forcing All Caps
 > --------------------
 > To insure data integrity, my application design
 > requires entry of data into
 > the "Pid" field in UPPER CASE format.
 > While I have been successful in limiting the length
 > of the data entered in
 > this field to ten (10) characters or less, my
 > attempts to force
 > CAPITALIZATION have to date failed.  The following
 > script is currently
 > applied to the field:
 > ===================================
 > on keyDown theKey
 >   if the length of me >= 10 then beep
 >   else pass keyDown
 > end keyDown
 > ===================================

Try the following script instead :

on keyDown pWhichKey
   if the length of me > 10 then beep
     send "Capitalize" to me in 5 milliseconds
     pass keyDown
   end if
end keyDown

on Capitalize
   put the selectedChunk of me into tChunk
   put upper(me) into me
   select tChunk
end Capitalize

Beware that the above script doens't fix 'pasted'
text, nor text that gets into your field via
'drag-and-drop'. There are ways of handling that,
Have a look at the Transcript Dictionary entries for
the 'pasteKey' and 'dragDrop' messages.

 > Any assistance in these areas is most sincerely
 > appreciated.
 > Melvin Cox
 > BCM Productions

Hope this helped ; don't hesitate to poke the list for
more information ; there are plenty of very smart
people out here :-)

Jan Schenkel.

"As we grow older, we grow both wiser and more foolish at the same time."  
(La Rochefoucauld)

Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
use-revolution mailing list
use-revolution at

MSN Shopping upgraded for the holidays!  Snappier product search...

More information about the Use-livecode mailing list