Help required for newbie on looping through records

[Sooty57]

I am creating a new system that uses data from an Access database that has been in use for over 10 years.

Because of this, I am limited in what I can do with regard to restructuring the tables etc.

Some of the tables have become very large over time, with much of the information now redundant. While this information is not required on a day-to-day basis, we cannot delete it in case it is required in the future.

There are 4 tables:

Activities - Correspondence - Clients - Client_Contacts


I have created duplicate tables (Activities_archive, Correspondence_archive, etc) and, with the guidance of other forum members (thanks Mike & Stan), have used action scripting to allow the user to select a client and move records from the main tables into the archive tables, and back again if required.

My problem is as follows....

The Activities, Clients and Client_Contacts tables all contain the Client_ID field, making it easy to select and move records based on the value of this field. The Correspondence table is only linked to the Activities table via the Activity_ID field.

I guess what I need to do is read the Activity_ID field value for each record in the Activity table where the Client_ID field matches the selected client (currently placed in a variable), and then search for records in the Correspondence table where the Activity_ID field value matches.

a/ Is this the best way to go about it, or is there a better way (maybe based on a table set)?

b/ Any script examples would be REALLY appreciated.

Thanks in advance

[Stan Mathews]

Re: Help required for newbie on looping through records

This jewel in the online store has a section on Duplicating Records in a Set which will make your life much easier. The actual implementation of moving the desired records to the archive tables and recalling them later will be somewhat different than the duplication processs but the looping principles illustrated should be the same.

Available as a pdf for download and easily searchable.

(Peter, we must speak about commissions.)

[Tom Henkel]

Re: Help required for newbie on looping through records

You just need to do a double loop.

First, get the activity_ID from the Archived Activities table, then, you have the Activity_id that you need. Once you have the ID, search through the correspondence table lookingfor records that match the activity_id.

I am assuming that you want to take non-current records out of the tables and move them to archive tables.

sample would look something like this:

dim actbl as P
dim ccntact as P
dim ccnt_arc as P
acttbl = table.open("activities_arc")
ccntact = table.open("Client_contact")
ccnt_arc = table.open ("client_contact_arc")
ccntact.index_primary_Put("Activity_id")   
     'Assumes there is an Activity ID index
actbl.fetch_first()
while .not.cltbl.fetch_eof()
     ccntact.fetch_find(acttbl.act_id)
     while .not.ccntact.fetch_eof()
          ccnt.arc.enter_begin()
               ******  add each field from client contact to a corresponding 
               ******  field in client contact _arc
          ccnt_arc.enter_end(.T.)
          ccntact.fetch_next()
     end while
     acttbl.fetch_next()
end while
ccnt._arc.close()
ccntact.close()
acttbl.close()

I am not saying that this is completely executable code, but it should give you a reasonable idea.

Tom

[Sooty57]

Re: Help required for newbie on looping through records

Thanks for your help guys.

I have been doing some experiments, and am now completely confused.

I use an xdialog box to allow the user to select the client, whereupon the client_ID is place in a shared variable vClientID - Works fine

The following code allows moves all records associated with that client from the client_notes table to the client_notes_archive table without any problems.......

********************************
dim tbl as P

tbl_source = table.open("client_notes")

result = tbl_source.move_records_to("client_notes_archive","Client_Id = Var->vClientID")

ui_msg_box("Result", word(result, 1) + " Client Notes moved to Archive. " + word(result, 2) + " Records not moved.")

tbl_source.close()
********************************


In order to move records associated with the client from the correspondence table to the correspondence_archive table I have to get the Activityid value for each record in the Activities table associated with the customer by checking the Custid value. I then need to move any records with the same Activityid value.

I have tried to use the following code to do this...........

*********************************
dim actbl as P
dim indx as P
dim cortbl as P
dim count as N
dim vActID as C

actbl = table.open("activities")
cortbl = table.open("correspondence")
query.filter = "Custid = Var->vClientID"
indx = actbl.query_create()

actbl.fetch_first()
while .NOT. actbl.fetch_eof()
vActID = actbl.Activityid

result = cortbl.move_records_to("correspondence_archive","Activityid = Var->vActID")

actbl.fetch_next()
end while

indx.close()
cortbl.close()
actbl.close()
*************************

This gives an error message that a function cannot be used with this data.

If I remove the line that should move the records, and write the results to the Trace window instead, I get the correct Activityid printed out 92 times (once for each record) exactly as I would expect.

What am I missing please?

[Tim Kiebert]

Re: Help required for newbie on looping through records

Try dimming vActid as shared or leave it as is and constructing the filter like so:

        result = cortbl.move_records_to("correspondence_archive",[COLOR=Red]"Activityid = " + quote(vActID)[/COLOR])

[Tom Henkel]

Re: Help required for newbie on looping through records

We've had issues with variables in filters. I think that that is what is happening.

Try this:

dim filt as C
filt = replace_parameters("Activityid = [VarC->vActID]",local_variables())
 
'then...

result = cortbl.move_records_to("correspondence_archive",filt)

This SHOULD insure that the variable is replaced in your expression.

Tom

[Sooty57]

Re: Help required for newbie on looping through records

Ok guys, thanks for your suggestions, but I still can't get this to work.

I have used trace.writlne(variablename) to try and see what is happening.

The variables appear to contain the correct info (trace shows what I expect), but the records where the Activityid field matches the variable are not being moved to the archive table.

I only need to do this once, as I will ad a field for the client ID to the table and force any new entries to write this info into any new records, so if anyone can think of another way to accomplish this one-off task, that would be great.

[Tom Cone Jr]

Re: Help required for newbie on looping through records

Paul, whenever I compare field values to variables I force myself to remember the possibility that each character field in my table probably includes trailing blank spaces. These blank spaces are part of the field value in each record. Are you comparing your variable with untrimmed field values?

[Sooty57]

Re: Help required for newbie on looping through records

Hi Tom,

I don't think this is an issue as the fields are both 11 chrs and the ActivityID is an auto-increment field that is always 11 chrs long

[Tom Cone Jr]

Re: Help required for newbie on looping through records

Paul, a good way to get specific assistance is to upload a working copy of the database, after sanitizing the data to protect privacy. Maybe it's time for you to do this so we can see where you're stuck. Include a representative sampling of dummy data, and a narrative description of the steps we must follow to find and trigger the fault.

[Sooty57]

Re: Help required for newbie on looping through records

Thanks Tom.

I will look at doing that now (VERY sensitive info, so I have to create dummy data first)

[Sooty57]

Re: Help required for newbie on looping through records

OK, now I'm going to start throwing toys out of the pram..............

After I removed the fields that contained sensitive data, and tested again before uploading, everything worked fine.

More investigation required, I think

[Sooty57]

Re: Help required for newbie on looping through records

:D As with just about everything in life, there was another solution to the problem......

I managed to find another table from the original Access database that contained both ActivityID and CLientID. I created a new field for the Client_ID in both the Donations and Correspondence tables, and created table sets for both with this new table as the child.

It was then a simple matter to open the default browse for each table set, select all records, and use the "Update Record" command in the menu to copy the Client_IDs into the parent tables.

I can now use the 'Move_Records_to' command to send all client records to the respective archive tables..... and without those horrid nested loops that take SOOOO LOOOONG (over 60,000 records in each table)

Rest assured that I shall be ensuring that all future records created by the Alpha5 system automatically insert the Client_ID into the newly created fields.

A big thank you to everyone for their suggestions :)