Building data base aps with Rev (Jan Schenkel)
Jan Schenkel
janschenkel at yahoo.com
Mon May 19 13:43:02 EDT 2003
--- Sadhunathan Nadesan <sadhu at castandcrew.com> wrote:
> Jan,
>
> As someone else said, way kewl.
>
> Thank you!
>
>
> Now, since you are being so extremely helpful, can
> we prevail on you to
> help us get to the next step? A multi-record type
> form.
>
>
> Typical of course is a master-detail form with a one
> to many relationship.
>
> For example I want to build a phone number data base
> with 2 tables.
> One table for company name, address, some other
> fixed stuff, as the parent
> record, and then a child record with various people
> at that address.
> This 2nd table would have as its foreign key the
> primary key of the first
> table, and then fields like name, title, phone
> number, fax, email, etc.
>
> The reason being we would typically have many
> different contacts at
> the same customer, for different stuff (accounts
> receivable, technical
> contact, payroll coordinator, etc.) Nice to group
> them in this way.
> By customer.
>
> So, easy enough using your helpful examples to make
> a card with the
> company info at the top, then what would be nice is
> a 'table' field at
> the bottom, for the child record, with each column
> in the table being a
> data base column, so the user could scroll through
> the list of contacts
> at that company on one screen.
>
> Any hints on if that is possible, and if so how to
> go about it?
>
> And pardon me for taking advantage of your
> generosity.
>
> Regards,
> Sadhu
>
>
Hi Sadhu,
If you're doing things 'the scripting way', then
here's an approach I've used in the past :
- Below the 'main' record fields, you place a 'portal'
field on the card, from which you can select a line,
updating other fields for the 'sub' record.
- So you would put this in the card script :
on RefreshMain
-- populate the fields of the 'main' record
-- ...
-- make sure you have the 'main' record primary key
put fld "foobar" into tPrimaryKey
-- now populate the 'related' fields
send "RefreshList" && tPrimaryKey to fld "portal"
end RefreshMain
- Now put the following script in the 'portal' field
on RefreshList pWhichKey
-- I like to place things in custom props
put the uDBConnectionID of this stack into \
tConnectionID
-- read the fields to display, etc.
put the uSQLFieldNames of me into tSQLFieldNames
put the uSQLTableNames of me into tSQLTableNames
put the uSQLRelatedFld of me into tSQLRelatedFld
-- now merge the query
put merge("SELECT [[tSQLFieldNames]] FROM
[[tSQLTableNames]] WHERE [[tSQLRelatedFld]] =
[[pWhichKey]] ;") into tSQLQuery
-- retrieve the data from the database
put revDataFromQuery(,,tConnectionID,tSQLQuery) \
into tQueryResult
if char 1 to 8 of tQueryResult is "revdberr" then
answer tQueryResult
else
-- now put the primary keys of the portal into
-- custom properties
repeat for each line tLine of tQueryResult
add 1 to i
set the uPrimaryKeys[i] of me to \
(item 1 of tLine)
-- you can do some more processing here
end repeat
-- now select the first line
set the hilitedLines of me to 1
end if
end RefreshList
- You'll also want to react to the 'selectionChanged'
message to update the 'sub' fields ; this can be done
in a very similar way to how you populated the other
fields, I think ; a snippet of the code :
on selectionChanged
-- find the primary key for the selected line
put the hilitedLines of me into tLine
put the uPrimaryKeys[tLine] of me into tKey
-- now refresh the sub fields
-- ...
end selectionChanged
- As you may have noticed, MetaCard doesn't send a
'selectionChanged' message when pressing arrowkeys ;
so have a look at the code I posted on the RunRev
Documentation WIKI :
http://wiki.macitworks.com/revdocs/2674
If you wanted to go the 'non-scripting way', I can
have a look at it tomorrow ; don't have my 'database'
computer with me this evening.
Hope this helped,
Jan Schenkel.
=====
"As we grow older, we grow both wiser and more foolish at the same time." (La Rochefoucauld)
__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com
More information about the use-livecode
mailing list