Database - functionality for front end application

[Jan Schenkel]

--- Monte Goulding <monte at sweattechnologies.com>
wrote:
> > People Table
> > -----------------
> >
> > Pid  <unique primary key>
> > FirstName
> > LastName
> > Affiliation
> > CompanyName < a display only field>
> > Last_update
> >
> >
> > Business Table
> > -------------------
> >
> > Bid <unique primary key>
> > Org_Name
> > Address
> > City
> > State
> > Zip
> > Last_update
> >
> > =======================================
> >
> >
> > 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?
> >
> With one properly formed SQL query instead of 2.
> Something like:
> 
> SELECT
> Pid,FirstName,LastName,Org_Name,People.Last_Update
> FROM
> People,Business WHERE Affiliation = Bid
> 
> Cheers
> 
> Monte
> 

Of course we can count on Monte to come up with the
more elegant solution -- on the other hand, the above
tactic will work fine as long as the number of related
records in Business is <= 1.
The tactic Melvin implemented on my advice, has the
advantage that it will also work for Master-Detail
views, where the number of related records might very
well be > 1.
Hmm, I still need to fix up that sample stack and post
it to the User Contributions...

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