Database - functionality for front end application
Melvin Cox
melcox at hotmail.com
Sun Nov 16 22:22:32 EST 2003
Jan:
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?
M.C.
----Original Message Follows----
From: Jan Schenkel <janschenkel at yahoo.com>
Reply-To: How to use Revolution <use-revolution at lists.runrev.com>
To: How to use Revolution <use-revolution at lists.runrev.com>
Subject: Re: Database - functionality for front end application
Date: Sun, 16 Nov 2003 09:52:03 -0800 (PST)
MIME-Version: 1.0
Received: from mail.runrev.com ([207.36.15.228]) by mc11-f3.hotmail.com with
Microsoft SMTPSVC(5.0.2195.6713); Sun, 16 Nov 2003 09:52:20 -0800
Received: from www.runrev.com (localhost [127.0.0.1])by mail.runrev.com
(Postfix) with ESMTPid 74B759300A7; Sun, 16 Nov 2003 12:50:42 -0500 (EST)
Received: from web60505.mail.yahoo.com
(web60505.mail.yahoo.com[216.109.116.126])by mail.runrev.com (Postfix) with
SMTP id 55A7D9300A0for <use-revolution at lists.runrev.com>;Sun, 16 Nov 2003
12:50:39 -0500 (EST)
Received: from [213.224.83.166] by web60505.mail.yahoo.com via HTTP;Sun, 16
Nov 2003 09:52:03 PST
X-Message-Info: QIy1oIULmHdATnwcsKwK1B0IDJfg6qG0
Delivered-To: use-revolution at lists.runrev.com
Message-ID: <20031116175203.64511.qmail at web60505.mail.yahoo.com>
In-Reply-To: <Law10-F729SLB1ygHZl0000d0a0 at hotmail.com>
X-BeenThere: use-revolution at lists.runrev.com
X-Mailman-Version: 2.1.1
Precedence: list
List-Id: How to use Revolution <use-revolution.lists.runrev.com>
List-Unsubscribe:
<http://lists.runrev.com/mailman/listinfo/use-revolution>,<mailto:use-revolution-request at lists.runrev.com?subject=unsubscribe>
List-Archive: <http://lists.runrev.com/pipermail/use-revolution>
List-Post: <mailto:use-revolution at lists.runrev.com>
List-Help: <mailto:use-revolution-request at lists.runrev.com?subject=help>
List-Subscribe:
<http://lists.runrev.com/mailman/listinfo/use-revolution>,<mailto:use-revolution-request at lists.runrev.com?subject=subscribe>
Sender: use-revolution-bounces at lists.runrev.com
Errors-To: use-revolution-bounces at lists.runrev.com
Return-Path: use-revolution-bounces at lists.runrev.com
X-OriginalArrivalTime: 16 Nov 2003 17:52:20.0304 (UTC)
FILETIME=[61C0C500:01C3AC6A]
--- Melvin Cox <melcox at hotmail.com> 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
else
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,
however.
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
http://antispam.yahoo.com/whatsnewfree
_______________________________________________
use-revolution mailing list
use-revolution at lists.runrev.com
http://lists.runrev.com/mailman/listinfo/use-revolution
_________________________________________________________________
MSN Shopping upgraded for the holidays! Snappier product search...
http://shopping.msn.com
More information about the use-livecode
mailing list