ok.  I know this is probably a newbie question....

3 Tables.   Person, Address and PersonToAddress (to accomodate 1 to many relationship).  GUID's are used for keys.
I.E.

TPERSON
======
personkey GUID
name        string[40]
active        boolean

TADDRESS
=======
AddressKey  GUID
Street          string[40]
active           boolean
..
..


TPERSONTOADDRESS
==============
personkey  GUID
addresskey GUID   
addresstype string[40]
active          boolean
...
...


I have a form that essentially allows editing a person and address that uses a datagrid that only has the name column to select the person. Then there is a panel that has a dropdown listbox to
choose which of the addresses to edit for that person. Then there are DBAware editboxes to edit the street, etc for the address for the type of address selected.  

Query1's SQL will select all people with active being true.  The results are put into the datagrid to choose the person.

Query2 will select all address types for the person selected.  The results is stuffed into a drop-down listbox.

Query 3 will select the address based on the address type of the person selected from the drop-down listbox.

The data aware edit boxes are set to Query 3.

So far, no problem.  I can select and view the data without issue.

I placed a button on the form to allow changing the datasource to go into edit mode.   No problem. I placed a label and I can see that I'm in edit mode when I select that.

I placed a button on the form to save the changes.

Now comes the question(s).

What is the appropriate/best/easiest way to make those changes.

I did the following, which did save the changes, but kept the 3rd query in edit mode and did not switch it to browse.

function UpdateAddress(mykey:GUID);
var
tb: tdbisamtable;
begin
 tb.session:=sessionname;
 tb.database:=databasename;
 tb.tablename:='Address';
 tb.open;
 tb.Locate('AddressKey', mykey,[]);
 tb.edit;
 tb.fieldbyname('Street').AsString:= dbeditStreet.Text;
 tb.post;
 tb.close;
end;

Lance R wrote:
> ok.  I know this is probably a newbie question....
>
> 3 Tables.   Person, Address and PersonToAddress (to accomodate 1 to many relationship).  GUID's are used for keys.
> I.E.
>
> TPERSON
> ======
> personkey GUID
> name        string[40]
> active        boolean
>
> TADDRESS
> =======
> AddressKey  GUID
> Street          string[40]
> active           boolean
> .
> .
>
>
> TPERSONTOADDRESS
> ==============
> personkey  GUID
> addresskey GUID   
> addresstype string[40]
> active          boolean
> ..
> ..
>
>
> I have a form that essentially allows editing a person and address that uses a datagrid that only has the name column to select the person. Then there is a panel that has a dropdown listbox to
> choose which of the addresses to edit for that person. Then there are DBAware editboxes to edit the street, etc for the address for the type of address selected.  
>
> Query1's SQL will select all people with active being true.  The results are put into the datagrid to choose the person.
>
> Query2 will select all address types for the person selected.  The results is stuffed into a drop-down listbox.
>
> Query 3 will select the address based on the address type of the person selected from the drop-down listbox.
>
> The data aware edit boxes are set to Query 3.
>
> So far, no problem.  I can select and view the data without issue.
>
> I placed a button on the form to allow changing the datasource to go into edit mode.   No problem. I placed a label and I can see that I'm in edit mode when I select that.
>
> I placed a button on the form to save the changes.
>
> Now comes the question(s).
>
> What is the appropriate/best/easiest way to make those changes.
>
> I did the following, which did save the changes, but kept the 3rd query in edit mode and did not switch it to browse.
>
> function UpdateAddress(mykey:GUID);
> var
>  tb: tdbisamtable;
> begin
>   tb.session:=sessionname;
>   tb.database:=databasename;
>   tb.tablename:='Address';
>   tb.open;
>   tb.Locate('AddressKey', mykey,[]);
>   tb.edit;
>   tb.fieldbyname('Street').AsString:= dbeditStreet.Text;
>   tb.post;
>   tb.close;
> end;
>
>
>

Hi Lance,

You haven't posted the query so why would it change mode? Looks like
you've created a temporary DBISAMTable to do the update and have left
the query in edit mode?

Tony.

Here's essentially the code side of the sample project I'm doing.
Any suggestions to more efficiently do this is very welcome.

===========================
procedure TForm1.btnSaveChangesClick(Sender: TObject);
begin

   //Do I do this?
   DBISAMQuery3.FieldByName('NAME').AsString:= dbeName.Text;
   DBISAMQuery3.FieldByName('STREET').AsString:= dbeStreet.Text;
   DBISAMQuery3.FieldByName('CITY').AsString:= dbeCity.Text;
   DBISAMQuery3.FieldByName('STATE').AsString:= dbeState.Text;
   DBISAMQuery3.FieldByName('ZIP').AsString:= dbeZip.Text;
   DBISAMQuery3.UpdateRecord;
   DBISAMQuery3.Post;

   // Or this
   UpdateDB;
   DataSource1.DataSet.Close;
   DataSource1.DataSet.Open;
   // Or...???

end;

procedure TForm1.UpdateDB;
var
 tb: tdbisamtable;
 PersonGuid: TGUID;
 AddressGuid: TGUID;
 newperson: boolean;
 newaddress: boolean;
begin
   tb:=tdbisamtable.Create(nil);
   try
      tb.SessionName:='mysession';
      tb.DatabaseName:='ADDR';

      // Update the name record
      tb.Tablename:='PERSON';
      tb.Open;
      if tb.Locate('PersonID', dbeSelectedPersonID.Text,[]) then begin
         tb.edit;
         newperson:=FALSE;
      end
      else begin
         tb.Insert;
         CreateGuid(PersonGuid);
         tb.FieldByName('PersonID').AsString:= GuidToString(PersonGuid);
         newperson:=TRUE;
      end;
      tb.FieldByName('NAME').AsString:= dbeName.Text;
      tb.Post;
      tb.Close;

      // Update the address record
      tb.Tablename:='ADDRESS';
      tb.Open;
      if tb.Locate('AddressID', dbeSelectedAddressID.Text,[]) then begin
         tb.edit;
         newaddress:=FALSE;
      end
      else begin
         tb.Insert;
         CreateGuid(AddressGuid);
         tb.FieldByName('AddressID').AsString:= GuidToString(AddressGuid);
         newaddress:=TRUE;
      end;
      tb.FieldByName('STREET').AsString:= dbeStreet.Text;
      tb.FieldByName('CITY').AsString:= dbeCity.Text;
      tb.FieldByName('STATE').AsString:= dbeState.Text;
      tb.FieldByName('ZIP').AsString:= dbeZip.Text;
      tb.Post;
      tb.Close;

   finally
      tb.free;
   end;
end;

procedure TForm1.DataSource1DataChange(Sender: TObject; Field: TField);
begin
   DBISAMQuery2.Close;
   DBISAMQuery2.SQL.Clear;
   DBISAMQuery2.SQL.Add('SELECT * FROM PERSON_ADDRESS WHERE
PERSONID='''+dbeSelectedPersonID.TEXT+'''');
   DBISAMQuery2.Open;
end;

procedure TForm1.DataSource2DataChange(Sender: TObject; Field: TField);
var
   sqlstr: string;
begin
   DBISAMQuery3.Close;
   DBISAMQuery3.SQL.Clear;
   sqlstr:='SELECT PERSON.Name,PERSON_ADDRESS.AddressType,
ADDRESS.STREET, ADDRESS.CITY,   ADDRESS.STATE,   ADDRESS.ZIP '+
   ' FROM   PERSON INNER JOIN PERSON_ADDRESS ON (PERSON.PersonID =
PERSON_ADDRESS.PersonID) ' +
   ' INNER JOIN ADDRESS ON (PERSON_ADDRESS.AddressID = ADDRESS.AddressID) ' +
   ' WHERE ' + ' '#9'(PERSON.PersonID = ''' + dbeSelectedPersonID.Text +
''') AND ' + ' '#9'(PERSON_ADDRESS.ADDRESSID = ''' +
dbeSelectedAddressID.Text + ''')';
   DBISAMQuery3.SQL.Add(sqlstr);

   DBISAMQuery3.Open;
end;

procedure TForm1.DataSource3StateChange(Sender: TObject);
begin
   case DataSource3.State of
      dsInactive: btnSaveChanges.Enabled:=FALSE;
      dsBrowse: btnSaveChanges.Enabled:=FALSE ;
      dsEdit: btnSaveChanges.Enabled:=TRUE ;
   else
      btnSaveChanges.Enabled:=FALSE ;
   end;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
   self.DataSource1.DataSet.First;
end;


===========================

I guess this may be a bit unrelated to the question, but why would you have
a separate table to hold the links in a 1-to-many relationship.  Why not
include the personkey in the address table.  It just looks like you have a
lot of redundant information and we're only seeing partial tables.  If each
address can belong to one and only one person, then there's no need for
another table.  I think the only time you would need the relation table is
when you have a many-to-many relationship.

Just my 2c,
Bill

"Lance R" <lance@lance.ws> wrote in message
news:63F17CAB-7E7B-46F6-97EB-390A370EC3C5@news.elevatesoft.com...
> ok.  I know this is probably a newbie question....
>
> 3 Tables.   Person, Address and PersonToAddress (to accomodate 1 to many
> relationship).  GUID's are used for keys.
> I.E.
>
> TPERSON
> ======
> personkey GUID
> name        string[40]
> active        boolean
>
> TADDRESS
> =======
> AddressKey  GUID
> Street          string[40]
> active           boolean
> .
> .
>
>
> TPERSONTOADDRESS
> ==============
> personkey  GUID
> addresskey GUID
> addresstype string[40]
> active          boolean
> ..
> ..
>
>
> I have a form that essentially allows editing a person and address that
> uses a datagrid that only has the name column to select the person. Then
> there is a panel that has a dropdown listbox to
> choose which of the addresses to edit for that person. Then there are
> DBAware editboxes to edit the street, etc for the address for the type of
> address selected.
>
> Query1's SQL will select all people with active being true.  The results
> are put into the datagrid to choose the person.
>
> Query2 will select all address types for the person selected.  The results
> is stuffed into a drop-down listbox.
>
> Query 3 will select the address based on the address type of the person
> selected from the drop-down listbox.
>
> The data aware edit boxes are set to Query 3.
>
> So far, no problem.  I can select and view the data without issue.
>
> I placed a button on the form to allow changing the datasource to go into
> edit mode.   No problem. I placed a label and I can see that I'm in edit
> mode when I select that.
>
> I placed a button on the form to save the changes.
>
> Now comes the question(s).
>
> What is the appropriate/best/easiest way to make those changes.
>
> I did the following, which did save the changes, but kept the 3rd query in
> edit mode and did not switch it to browse.
>
> function UpdateAddress(mykey:GUID);
> var
> tb: tdbisamtable;
> begin
>  tb.session:=sessionname;
>  tb.database:=databasename;
>  tb.tablename:='Address';
>  tb.open;
>  tb.Locate('AddressKey', mykey,[]);
>  tb.edit;
>  tb.fieldbyname('Street').AsString:= dbeditStreet.Text;
>  tb.post;
>  tb.close;
> end;
>
>
>

Bill,

Your point is well taken.  I support having it set as a many-to-many
gives me the flexibility, but doesn't really give any referential integrity.

In this schema, it does allow me to reduce the # of rows when, for
example, Billing and Shipping addresses are the same.  By using the
Intersection table to point to the same address record, it reduces rows.

Lance



B Miller wrote:
> I guess this may be a bit unrelated to the question, but why would you have
> a separate table to hold the links in a 1-to-many relationship.  Why not
> include the personkey in the address table.  It just looks like you have a
> lot of redundant information and we're only seeing partial tables.  If each
> address can belong to one and only one person, then there's no need for
> another table.  I think the only time you would need the relation table is
> when you have a many-to-many relationship.
>
> Just my 2c,
> Bill
>
> "Lance R" <lance@lance.ws> wrote in message
> news:63F17CAB-7E7B-46F6-97EB-390A370EC3C5@news.elevatesoft.com...
>> ok.  I know this is probably a newbie question....
>>
>> 3 Tables.   Person, Address and PersonToAddress (to accomodate 1 to many
>> relationship).  GUID's are used for keys.
>> I.E.
>>
>> TPERSON
>> ======
>> personkey GUID
>> name        string[40]
>> active        boolean
>>
>> TADDRESS
>> =======
>> AddressKey  GUID
>> Street          string[40]
>> active           boolean
>> .
>> .
>>
>>
>> TPERSONTOADDRESS
>> ==============
>> personkey  GUID
>> addresskey GUID
>> addresstype string[40]
>> active          boolean
>> ..
>> ..
>>