Building data base aps with Rev (Jan Schenkel)

[Sadhunathan Nadesan]

Hi Jan,

Wow, I really appreciate all the time you are putting into these posts,
and unfortunately I am such a newbie that a lot of it is going over
my head.  But I'm trying to follow it!

Here are some of the things I am running into if you have time to respond,

1) I can't find a "table object".  I thought I did see that on the
tool palette once upon a time but it's not in the version of Rev I am
running now (release candidate, 2.0).  How do I create such an object?
There is no doc on it either, that I can find, how does one access the
cells of this object, can you refer to them like a spread sheet with row,
column indices?

I went to the current release candidate per Kevin because my previous
beta couldn't connect to the data base.  This one solved that.

I was wondering the same thing about a 'portal' field from your
previous post, is that actually a field type, or was I taking things
too literally?  I'm trying to follow your instructions like a cookbook
recipie and struggling.  


2) Using the query builder, there is very little doc on it so having
trouble.  I am going to pass on to support that When I enter a query
with a snytax error it crashes Rev.  What I was trying to do was put the
contents of a card's field into a query as a parameter?  Maybe you have
to script to do that, could probably figure that out but was hoping to
do it in the builder,

such as, select * from foo where foo.bar = field "one"

Obviously the above isn't going to work.

Maybe it should be $l or [fld 1] or something. ??

The goal is to link 2 tables. The first table is returned by a query
on it, filling up one set of fields.  I want the query for the second
table to refer to a result from the first query, so as to fill up the
matching fields for the second table.  ie, the equivalent to

select bar.a, bar.b, bar.c from foo, bar where foo.bar = bar.bar and
foo.bar = field "one"

Can the query manager handle this sort of thing?  Well, again if not,
the scripting approach as you lay out could .

3) Well as per your disclaimer, the functions in this and your
previous post do not seem to be in the published API.  I tried some
of them yesterday in the message box to see if they might do something
but nothing happened.  Maybe I don't have them?

4) I realize this is a lot to ask but I was wondering if you actually have
a working stack on some kind of a data base with two tables that are
related by a foreign key?  If so, could you send it to me?  Of course
remove the specific connection info ..  gee I think passwords should be
encrpted in the builder .. then I could either create a postgres database
to match your schema or, change the table names etc to match mine.

Sorry to be such a pain but I'm not quite getting it.

As an example, I launched a stack with a live data base
connection and then tried the first function call in your
example, I put this in the message box

 put the cREVDatabase["queryobject"] of fld 1


I got - script compile error, error description - handler
bad command

does that mean that "queryobject" was suppose to refer
to something else, it wasn't to be taken literally,
or does it mean,  I do not have this function, or????

Again, many thanks

Sadhu


| Hi Sadhu,
| 
| As promised, I had a look at how to accomplish the
| same thing without (too much) scripting. Here's what I
| found:
| 
| - use the query builder to create the same query twice
| (otherwise you get refresh issues) ; name them
| PortalQuery and DetailQuery
|   SELECT * FROM locations ;
| 
| - now create a new field and make it a table object ;
| use the database panel to hook it up to the
| PortalQuery and for columns pick "Show all"
| 
| - now create the other fields and uise the database
| panel to hook them up to DetailQuery and pick the
| separate columns.
| 
| - to round it all off, you'll want to update the
| DetailQuery fields whenever you select a different
| line in the PortalQuery table field ; so set its
| script to (as usual, watch for wrappings):
| 
|   on selectionChanged
|     put the hilitedLines of me into tNumber
|     put the cREVDatabase["queryobject"] of fld
| "DetailField1" into tObject
|     put revQueryLongName(tObject) into tObject
|     if exists(tObject) and the
| cREVGeneral["databasequeryobject"] of tObject is true
| then send "revGoToRecordOfQuery" && tObject & comma &
| tNumber
|   end selectionChanged
| 
| - of course you'll have to employ in your PortalQuery
| field the tricks I mentioned earlier to get the
| arrowKeys, etc.
| 
| Hope this helped,
| 
| Jan Schenkel.
| 
| > [snip]
| >
| 
| After I had hit the 'send' button, I realised I wasn't
| finished yet in terms of a master-detail form ; so
| there's one more thing to solve in the point-and-click
| scenario : synchronising the master and details.
| 
| DISCLAIMER: Pleas note that this is based on diving
| deep into the revDatabase functions, below the
| published API ; as such, it may not work in future
| versions !
| 
| What we need to do is alter the portal and detail
| queries dynamically upon moving along the master
| record cursor.
| 
| First we change the Portal and Detail queries with the
| query builder, so that they get the form
|   SELECT * FROM locations WHERE country = "BEL"
| 
| Note that "BEL" would be the primary key for the
| master records and the foreign key for the detail
| records.
| 
| One of the features of the buttons that are linked to
| queries, is that they get a mouseUp after having
| handled the record move.
| 
| So put the following script into the "Next" button
| (other buttons are analogous).
| 
| on mouseUp
|   -- fetch the new primary key
|   put quote & field "countryID" & quote \
|       into tPrimaryKey
|   -- fetch references to the query objects
|   put the cREVDatabase["queryobject"] of fld \
|       "Portal" into tPortalObject
|   put revQueryLongName(tPortalObject) into \
|       tPortalObject
|   put the cREVDatabase["queryobject"] of fld \
|       "Detail1" into tDetailObject
|   put revQueryLongName(tDetailObject) into \
|       tDetailObject
|   -- fetch the query and adapt it
|   put the cREVDatabaseQuery["sqlquery"] of \
|       tPortalObject into tSQLQuery
|   put tPrimaryKey into word -1 of tSQLQuery
|   -- now update the query objects
|   -- auto refresh + move to the first record
|   send "revSetSQLOfQuery" && tPortalObject & \
|       comma & tSQLQuery
|   send "revSetSQLOfQuery" && tDetailObject & \
|       comma & tSQLQuery
|   -- and select the first line in the portal
|   set the hilitedLines of fld "Portal" to 1
| end mouseUp
| 
| Admittedly, this piece of script and similar
| adventures are not for the faint-hearted, as it
| requires a good look around the 'revDatabase'
| frontScript.
| 
| Nevertheless, I hope this helped,
| 
| Jan Schenkel.
|