Building data base aps with Rev (Jan Schenkel)

[Jan Schenkel]

--- 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