Automatically creating linked records in two tables?

[Sooty57]

My client is currently using an access database (10 years old), so I am stuck with the table structure used there to a great extent.

In order to keep track of correspondence they first create an Activity (in Activities table). If they then send a letter an entry is made in the Correspondence table that is linked to the record in the Activities table. Obviously this is a very time-consuming process - If they send out a mailshot to 200 people, they have to manually input 400 records.

I have created a table that stores addresses etc so that the client can create a mailing list. Using this, I can create the records for the Activity and Correspondence tables.

My problem is how to link the two. i.e.: Each record in the Correspondence table MUST be linked to a record in the Activities table by means of the unique ActivityID field. If I use a script to create the Activity record (ActivityID field is AutoInc) how can I then place this auto-generated ActivityID into the corresponding Correspondence record?

Because this needs to create multiple records in both tables I am assuming that I need to use some for of do-loop routine i.e. create the Activity record, read the ActivityID field, create the Correspondence record with ActivityID field, move on to next.

Any help would be really appreciated on this one - it's driving me nuts.

[Bill Parker]

Re: Automatically creating linked records in two tables?

Paul,

This is straightforward to do in xbasic, and your script description is good. This fleshes the outline a little more. I assume there is a person table.

tPerson = <open person table>
tActivity = <open activity table>
tCorresp = <open correspondence table>
<query tPerson for the contacts for this activity>
tPerson.fetch_first()

while .not. tPerson.fetch_eof()
  tActivity.enter_begin()
  ... <activity id is automatically set by field rule>
  tActivity.enter_end(.t.)

  tCorresp.enter_begin()
  tCorresp.activity_id = tActivity.activity_id
  ...
  tCorresp.enter_end(.t.)

  tPerson.fetch_next()
end while

Bill.

[Sooty57]

Re: Automatically creating linked records in two tables?

Thanks for the tips Bill. I will start to play with this.

My problem is mainly lack of knowledge of the functions available and their syntax.

You have hopefully pointed me in the right direction.

[Sooty57]

Re: Automatically creating linked records in two tables?

Once again, my lack of knowledge of xbasic has become glaringly obvious. Despite many hours of experimentation I am no closer to a solution.

I have attached a much simplified version of the database in the hope that someone may be able to add some code to the button.

The form is for a set. Browsing the mailing lists (mailing list table) shows the recipients (mailshot table). What I need the code to do when the button is clicked is to create two records for each recipient - first an activity record with the data from the mailshot record, and then a correspondence record with the auto-generated ActivityID from that record.

I realise I am asking a lot, but I am sure that once I have an example to work from I will be able to crack it.

Thanking some kind soul in advance.

[Stan Mathews]

Re: Automatically creating linked records in two tables?

When the record is created in the correspondence table, what should go in the corrid and type fields?

My attachment creates the suggested records, I think to your specifications. Currently the corrid and type fields are filled with hard coded values.

[Sooty57]

Re: Automatically creating linked records in two tables?

Thanks for the quick response again Stan.

I'm afraid I didn't make things as clear as I should have. I need to create the two records for each of the records in browse1 (recipients), not just the selected one.

Don't worry about the fields in the correspondence table - this info is taken from fields I forgot to include in the example, but your code has given me a good pointer to the method I need to employ to do this.

[Stan Mathews]

Re: Automatically creating linked records in two tables?

tbl = table.current(2)
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
	tbl_dest = table.open("activities",FILE_RW_EXCLUSIVE)
	tbl_dest.enter_begin(.T.)
	tbl_dest.Funderid = tbl.funderid
	tbl_dest.Contact = tbl.contact
	tbl_dest.Address1 = tbl.address1
	tbl_dest.Address = tbl.address2
	tbl_dest.Town = tbl.town
	tbl_dest.Postcode = tbl.postcode
	tbl_dest.enter_end(.t.)
	act_id = tbl_dest.activityid
	tbl_dest.close()
	
	tbl_dest2 = table.open("correspondence",FILE_RW_EXCLUSIVE)
	tbl_dest2.enter_begin(.T.)
	tbl_dest2.Activityid = act_id
	tbl_dest2.corrid = "ABC"
	tbl_dest2.TYPE = "DEV"
	tbl_dest2.enter_end(.T.)
	tbl_dest2.close()
	tbl.fetch_next()
END WHILE

[Sooty57]

Re: Automatically creating linked records in two tables?

Thanks Stan, that's absolutely fantastic.

I thought that I needed to create a query to filter the records in the mailshot table; I didn't realise that tbl.fetch_eof() would only use the records in the embedded browse.

Just a quickie.... What is the "2" for in table.current(2), does it tell Alpha that 2 tables are open?

[Stan Mathews]

Re: Automatically creating linked records in two tables?

I didn't realise that tbl.fetch_eof() would only use the records in the embedded browse.

Effectively, yes. Practically, it uses the records linked to the current parent.

What is the "2" for in table.current(2)

table.current() or table.current(1) is the parent of a set. The other tables are referenced by their vertical position in the set structure.

[Sooty57]

Re: Automatically creating linked records in two tables?

Thanks Stan.

I'll get the hang of this xbasic lark one day ;-)

[Stan Mathews]

Re: Automatically creating linked records in two tables?

Looking at Bill's recommendation, which I should have studied before I posted, the code can be improved thusly.

tbl_dest = table.open("activities",FILE_RW_EXCLUSIVE)
tbl_dest2 = table.open("correspondence",FILE_RW_EXCLUSIVE)
tbl = table.current(2)
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
	tbl_dest.enter_begin(.T.)
	tbl_dest.Funderid = tbl.funderid
	tbl_dest.Contact = tbl.contact
	tbl_dest.Address1 = tbl.address1
	tbl_dest.Address = tbl.address2
	tbl_dest.Town = tbl.town
	tbl_dest.Postcode = tbl.postcode
	tbl_dest.enter_end(.t.)
	act_id = tbl_dest.activityid
	
	tbl_dest2.enter_begin(.T.)
	tbl_dest2.Activityid = act_id
	tbl_dest2.corrid = "ABC"
	tbl_dest2.TYPE = "DEV"
	tbl_dest2.enter_end(.T.)
	tbl.fetch_next()
END WHILE
tbl_dest.close()
tbl_dest2.close()

I tend to knock things together without regard for best practices and hope to imporve them later.