Loop through records and get a value for each record

[richard2007]

Hi, i need to loop through a table, filter the records an perform actions on the filtered records.

This is all ok but at the same time i need to get the value of one of the fields in table that the loop is based. The field is 'ContactID'. I am unsure how to get this value for each record in the loop and then use it in subsequent actions.

	'If case is interest only
	Dim cr_io as L
	cr_io = lookup("[PathAlias.ADB_Path]\progress.dbf","caseid = "+quote(session.caseid),"cr_intonly")

	if cr_io = .t. then
 
	dim tbl2 as P
	dim qry as P
	dim nrecs as N
	dim i as N
	
	tbl2 = table.open("[PathAlias.ADB_Path]\participants.dbf")
	filter_case = "caseid = " + quote(session.caseid)
	filter_type = "contacttype = 'Customer'"
	filter_recs ="("+filter_case +") .and. ("+filter_type +")"
	
	query.description = "Create IO Declarations to be signed by client"
	query.order = ""
	query.filter = filter_recs
	query.options = ""
	qry = tbl2.query_create()
	nrecs = qry.records_get()
	
	if nrecs=0 then  
	    end 
	else	
	   tbl2.fetch_first()
	   while .NOT. tbl2.fetch_eof()

                'create note and document link
                dim tbl as p
	        tbl = table.open("[PathAlias.ADB_Path]\activities.dbf")
			tbl.enter_begin()
                                [COLOR="#FF0000"]tbl.contactid = Contactid from the table the loop is performed on[/COLOR]
				tbl.caseid = session.caseid
				tbl.created_by = session.employeename
				tbl.date_created=date()
				tbl.activity_type = "Note"
				tbl.activity_subject="Compliance"
				tbl.activity_body="Interest Only Declaration Generated and saved to file"
				tbl.published = "No"
			tbl.enter_end(.T.)
			
			tbl.enter_begin(.T.)
				tbl.caseid= session.caseid
				tbl.created_by = session.employeename
				tbl.document_name="Interest Only Declaration"
				tbl.document_path = vroot3
				tbl.date_created = date()
				tbl.published = "Yes"
				tbl.document_extn = ".pdf"
				tbl.canesign = "true"
			tbl.enter_end(.T.)
		tbl.close()
			
		tbl2.fetch_next()
	end while
	qry.close()
	tbl2.close()
		
	end if

[Al Buchholz]

Re: Loop through records and get a value for each record

Richard

So you want to keep multiple contactids from the loop shown and use them in a later process?

3 ways
list
array
collection

List is probably the easiest to do.

dim MyList as C
MyList=""

inside your loop

MyList = MyList+tbl.contactid+crlf()

when you want to use MyList somewhere else

*for_each(contactid,<<some function call>>,MyList)

or a FOR EACH NEXT loop of code.

Look at List Processing Functions if you need to work more with the list values.

[richard2007]

Re: Loop through records and get a value for each record

Thanks Al,

Im never very good at explaining what im trying to achieve. I believe i only need 1 ContactID value at a time, i'm just not sure how to get it during the loop.

Participants table holds records of contacts linked to a case. i need to filter the Participants table on CaseID and then for every Contact attached to that case, grab the ContactID and create 2 records in the Activities table.

[Al Buchholz]

Re: Loop through records and get a value for each record

Thanks Al,

Im never very good at explaining what im trying to achieve. I believe i only need 1 ContactID value at a time, i'm just not sure how to get it during the loop.

Participants table holds records of contacts linked to a case. i need to filter the Participants table on CaseID and then for every Contact attached to that case, grab the ContactID and create 2 records in the Activities table.

tbl.contactid = tbl2.Contactid 'from the table the loop is performed on

I would also move the tbl.open() and tbl.close() outside of the loop of tbl2

No need to open and close the target table each time you loop through the other table.

Way too slow.

Also note that the values set when the first record is added will carry forward to the second record unless they are specifically over written by the code in the second enter_begin().

ie the contactid will be entered in both records since it was defined in the first enter_begin()

[COLOR=#ff0000][B]                'create note and document link
            dim tbl as p
            tbl = table.open("[PathAlias.ADB_Path]\activities.dbf")
[/B][/COLOR]
   tbl2.fetch_first()
       while .NOT. tbl2.fetch_eof()

            tbl.enter_begin()
                tbl.contactid = [B][COLOR=#ff0000]tbl2.Contactid[/COLOR][/B] 'from the table the loop is performed on
                tbl.caseid = session.caseid
                tbl.created_by = session.employeename
                tbl.date_created=date()
[B][COLOR="#00FF00"]                tbl.activity_type = "Note"
                tbl.activity_subject="Compliance"
                tbl.activity_body="Interest Only Declaration Generated and saved to file"
[/COLOR] [/B]               tbl.published = "No"
            tbl.enter_end(.T.)
            
            tbl.enter_begin(.T.)
                tbl.caseid= session.caseid
                tbl.created_by = session.employeename
                tbl.document_name="Interest Only Declaration"
                tbl.document_path = vroot3
                tbl.date_created = date()
                tbl.published = "Yes"
                tbl.document_extn = ".pdf"
                tbl.canesign = "true"
            tbl.enter_end(.T.)
            
        tbl2.fetch_next()
    end while

[B][COLOR=#ff0000]                 tbl.close()
[/COLOR][/B]

[richard2007]

Re: Loop through records and get a value for each record

Thanks Al,

so i have the script working to a certain extent except, it seems as the second and subsequent records in the loop are not being filtered by the field ContactID, it's as if the script is looping through the records but applying the contactid from the first record to all records.

[Al Buchholz]

Re: Loop through records and get a value for each record

Thanks Al,

so i have the script working to a certain extent except, it seems as the second and subsequent records in the loop are not being filtered by the field ContactID, it's as if the script is looping through the records but applying the contactid from the first record to all records.

Post your code and some sample data or send it to me directly to avoid have the public see data.

[MoGrace]

Re: Loop through records and get a value for each record

I am curious about this section of the filter you are using:

filter_case = "caseid = " + quote(session.caseid)

It looks like some sort of dot.variable?

Edit: I see this is a mobil app - sorry if my question doesn't apply