Ken this is exactly what I am looking for. I was parsing the lookup
key for departments from the employee cursor into a text string, and
then using that as a lookup for my department records, so that I ended
up with a cursor of department records that had matching values in the
employee cursor. That way I can get the department description and the
manager etc. right from the department table without replicating data.
The idea is to be able to page through large data sets by setting a
limit on the number of records I return in the parent cursor, and then
only return records in the "relational" cursors whose keys match the
parent. I don't want to use the SQL relational system, because frankly
writing rev code to build highly complex queries to return data from
multiple tables into one cursor on the fly is more trouble than I want
to deal with. I wish there was a way in SQL to set up linked cursors
for relations, but alas it is what it is.
So I am building into my application a more "traditional" approach to
relations, where I return separate cursors for every child table, and
then have a navigation function that looks up the records in the child
tables each time I navigate to a new record in the parent table. It
may seem a more complex way to do things, but the advantage is the
queries for each table are quite simple, and the mechanics for what
data gets returned are mostly under Rev code and not a long and highly
complex SQL statement. Think of what this would mean for an invoicing
app that would only return invoice detail for the current invoice in a
separate table that could be presented to the user as a list.
So using your method, my queries for a table of 25 employees with the
associated departments data would look something like this:
-- employee data
put "SELECT employees.deptid FROM employees LIMIT 25" into
mEmployeeQuery
-- department data
put "SELECT * FROM departments WHERE departments.deptid IN (" &
mEmployeeQuery & ")" into mDepartmentQuery
-- color added just because I can :-)
In this example I use as my subselection the exact same query I used
for the parent table. That would ensure that the children ALWAYS
contained the records in the parent cursor no matter how I paged my
data.
When I am done with this, I will have more than just an employee
database or an inventory system. I will have a Revolution database app
builder. Every card represents a table in the SQL database you connect
to. I have a "Field Dropper" stack that will read the SQL database and
present the user with columns for each table. It will then create a
field on a card for any selected columns with everything it needs to
work with the database app. If you drop a field from a table other
than the one for the card you are working with, it allows you to
select a column from the parent. It then sets up the relation in a
custom card property, and configures the field as a related table field.
Whenever I enter a card or navigate to another record I call a
function that reads the relations and queries (or requeries as
necessary) all the tables in the relations property for the card. I
have record and table locking and update checks already built in so I
never overwrite another user's updates if they got to the record
first. I have it reading and writing to the database now. I just need
to add search functions and field validation functions and away I go.
Thanks a bunch to everyone for their help. I will post a version as
soon as it becomes functional enough to use without any major bugs.
Bob Sneidar
IT Manager
Logos Management
Calvary Chapel CM
On Feb 4, 2009, at 9:02 AM, Ken Ray wrote:
The other thing to note is that "IN" can be used both for checking for
matching value in field(s) in another table with a "subselect", or
even for
a simple string comparison.
For example, you can do this (this is admittedly a bad example
because you
don't need to do the subselect, but it's more FYI):
SELECT * FROM People WHERE PhoneLink IN
(SELECT RecID FROM Phones WHERE PhoneType='Fax')
And you can also do this:
SELECT * FROM Phones WHERE PhoneType IN ('Fax','Work','Home')
etc.
The reason I mention this, is that when I first went to look for
this a
while ago, I thought it would be under SELECT, but now I know better.
:-)
Ken Ray
Sons of Thunder Software, Inc.
Email: [email protected]
Web Site: http://www.sonsothunder.com/
_______________________________________________
use-revolution mailing list
[email protected]
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
