--- Bill Humphrey <[EMAIL PROTECTED]> wrote: > In case anyone else wants to use this great thing > extensively then when > designing your databases always use unique names for > every column even > when in different tables because if you refer to a > column as > tablename1.shipID and another one as > tablename2.shipID even though SQL > is happy with this the database query builder update > feature will choke. >
[Sorry for the late reply, jsut cleaning the backlog] You're touching on one of the main drawbacks of SQL databases: their relational syntax makes it easy to combine data from multiple tables in a meaningful way -- but what if you want to update the data ? In the above example, the shipID field might be the primary key for both tables, and there might be a one-to-one relationship between them. But it is far more likely that it is the primary key for tablename1, but only part of the primary key for tablename2 -- or it could be just a foreign key. This means that while the revDatabse frontscirpt can farily easily update the database when you're displaying data from one table and telling it the primary key of that table ; but updating combined data from multiple tables is a different game entirely ! You would have to provide the primary key for each table involved in the query and may therefore have to add fields in order to have the key columns. Note that this is also the main reason why it is near impossible to provide a 'Create record' functionality in that frontscript: if there's more than one table involved, where should it create new records? In all tables? In some of them? In only one of them? Database solutions like FileMaker Pro have ready access to the full database schema, including the relations between the tables, and may use some heuristics to figure out what it needs to create. But it is very tricky to get this right without inner knowledge of the database structure. Which is why I usually tell people to do the SQL-stuff themselves, and employ the database-linked controls mainly for viewing data and minor editing. You can easily piggy-back on the existing query conection data by using the other calls in the revDatabase frontscript like : -- revExecuteWithQuery <query name>,<sql query> -- Hope this helped, Jan Schenkel. Quartam - Tools for Revolution <http://www.quartam.com> ===== "As we grow older, we grow both wiser and more foolish at the same time." (La Rochefoucauld) __________________________________ Discover Yahoo! Use Yahoo! to plan a weekend, have fun online and more. Check it out! http://discover.yahoo.com/ _______________________________________________ use-revolution mailing list [email protected] http://lists.runrev.com/mailman/listinfo/use-revolution
