Help with Query

[jjfcpa]

The below code fragment is supposed to skip through 3 records in the parent table (t_ftps) and find the corresponding child record in t_ftpinfo.

However, I can't get it to skip through the parent records, even though it indicates that there are 3 matching records as verified by q_ftps.records_get().

	t_ftps = table.open("app_ftps")
	t_ftpinfo = table.open("ftp_info")
	q_ftps = t_ftps.query_create("","pk_app ="+s_quote(pk_app2update))
	q_ftpinfo = t_ftpinfo.query_create("","pk_ftpinfo = "+s_quote(t_ftps.Pk_ftpinfo))
	n_ftps = q_ftps.records_get()
	msgbox("FTP sites assigned to this app:  "+convert_type(n_ftps,"c"))
	p_ftps = q_ftps.find_first()
	for i = 1 to n_ftps
		q_ftpinfo.find_first()
		ui_msg_box("Sites","Site #"+convert_type(i,"C")+" to update is "+alltrim(t_ftpinfo.desc),ui_information_symbol)
		q_ftps.find_next(p_ftps)
		ui_msg_box("APP_FTPS","Next site is "+t_ftps.Pk_ftpinfo)
    next

The line q_ftps.find_next(p_ftps) does not appear to be moving to the next matching record in the parent table. What am I doing wrong??? I wish there were better examples in the help file.

[Tom Cone Jr]

Re: Help with Query

John, this line looks wrong:

q_ftps.find_next(p_ftps)

I don't think this method takes an argument. Shouldn't it be

q_ftps.find_next()

??

If that doesn't get you going then take a minute and rewrite the sequence in english, without using Alpha Five commands or statements. I'm having trouble discerning what your goal is as you step through the loop.

[Keith Hubert]

Re: Help with Query

Hi John,

If you are trying to find all child records that belong to a parent then you would need to have a nested loop. I have shown the code here that I needed to give a row number to all child records before the application went live. While this code itself is not relevant to you at this time, it demonstrates how it works its way through the parent table, then works its way through the child table and processes each corresponding child record.

Note the use of the While and End While loop.

'This script is designed to be used as a global script and should be run once from the 
'Control Panel. Create a numeric field in the invoice2 table called line_number

dim invh as p
dim tbl as p
dim linecount as n
dim idx as p
dim invnum as n
dim qry as p
dim nummy as n
dim recs as n

invh=table.open("temp2_2") 'the parent table in the set - if you ran this outside of the set
                                              'it would renumber all invoice line items 
on error goto errors 
invh.fetch_first()
while .not. invh.fetch_eof()
 linecount=0 'Reset the linecount field everytime another parent record is fetched
 
 nummy=invh.job_number 'Populate variable with invoice number fields from the invoice_header table
 'ui_msg_box("",(nummy)+"") 'Can be commented out. Only shows the Invoice_Number value of the Invoice_header table
 tbl=table.open("supp2")
  query.filter="sup_id=var->nummy"
  query.order="sup_id"
 query.options="N"
 query.decription="Number Item Lines"
 qry=tbl.query_create()
 recs=qry.records_get()
 'ui_msg_box("",str(recs)+"") 'Can be commented out. Shows how many child records related the the Invoice_header->Invoice_number field
    tbl.fetch_first()    
  while .not. tbl.fetch_eof()
   linecount=linecount+1     'Start the count at one first time through the while loop
    tbl.change_begin()    'Put child table into change mode
      tbl.line_number=padl(ltrim(str(linecount,2,0)),2,"0")  'populate the value of the line_number field
     tbl.change_end(.t.)    'end the change mode     
    tbl.fetch_next()     'fetch until last matching child record has been fetched
 end while  
tbl.close()   'close the child (invoice_items) table  'fetch the next parent record
invh.fetch_next() 'fetches the next record from the parent table
end while
invh.close()

on error goto errors 
end 
errors:
err_msg = error_text_get(error_code_get())
line = error_line_number_get()
script = error_script_get()
ui_msg_box("Error", err_msg+" Error occurred at line "+alltrim(str(line,4,0))+ " in script: "+script)
end

Hope this gives you a different look at what you are trying to achieve.

[jjfcpa]

Re: Help with Query

Keith

Thanks for the code segment. One thing I noticed is that you didn't use a pointer for the query. The help file for "query.find_next(ptr)" leads one to believe that you have to use a pointer so that (presumably) the query knows where it left off.

I'm really just trying to navigate through the parent table which has been filtered based on a key value. Then the parent table needs to lookup additional data in a child table which is not filtered at tall.

I'll try your code and see if it works. Thanks again.

[Tom Cone Jr]

Re: Help with Query

John, I find it useful to use nested loops and no queries in this situation.

In pseudo code I will

   - open both tables
   - set the primary index in each to the link field that relates the two tables
   -fetch the first record that matches the desired link field value in the 
            parent table
          - then loop through the related child table records, fetching one at a 
             time
          - stop when the fetched child rec has the wrong key or you've 
            fetched past eof
    - then loop back to get the next primary table record
    - stop when the next primary table record has a key that doesn't match 
          (or - you fetch past the eof)
    - close both tables

Of course the nested loop is not needed if the link is one to one.

[jjfcpa]

Re: Help with Query

John, I find it useful to use nested loops and no queries in this situation.

In pseudo code I will

   - open both tables
   - set the primary index in each to the link field that relates the two tables
   -fetch the first record that matches the desired link field value in the 
            parent table
          - then loop through the related child table records, fetching one at a 
             time
          - stop when the fetched child rec has the wrong key or you've 
            fetched past eof
    - then loop back to get the next primary table record
    - stop when the next primary table record has a key that doesn't match 
          (or - you fetch past the eof)
    - close both tables

Of course the nested loop is not needed if the link is one to one.

In this case, I don't think of this as a query. It's more a filter of the records to avoid using "if" statements to eliminate records that I don't want. But I can see why you might avoid using a query. The problem is, don't you have to in the case of a report that should only include filtered records?

[Tom Cone Jr]

Re: Help with Query

In this case, I don't think of this as a query. It's more a filter of the records to avoid using "if" statements to eliminate records that I don't want. But I can see why you might avoid using a query. The problem is, don't you have to in the case of a report that should only include filtered records?

Maybe. Depends upon your needs and your goal, both of which remain a mystery to me.