• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

Loop through a DBF

I want to select my location.dbf table

now I want to loop through it in this order

co, loc, dept

What I will do is get the co, then loc, then dept, then email

some location have a blank dept since they are a small branch so I hope that does not matter

This is where I am not sure how to get all 4 fielda and build into a loop so I can gather other
info from other tables then email to that location
then

table is:
lcco         lcloc     email                              lcdept
1             2         me@domain.com            
1             4         user1@domain.com        shi
1             4         user2@domain.com        shi2
1             4          user3@domain.com        har
2             5          user5@domain.com      


* Open database for Location table
C:\MyFolder\MyData
* Select table to get location info
select location.dbf shared

* Loop through by co, loc, dept and get email
	FOR lnI = 1 TO ALEN(location).....

Open in new window

0
jaymz69
Asked:
jaymz69
  • 6
  • 5
  • 2
2 Solutions
 
Olaf DoschkeSoftware DeveloperCommented:
Looping a dbf is SCAN...ENDSCAN, not FOR...ENDFOR. ALEN() is the length of Arrays, in SCAN...ENDSCAN you don't need to know the number of lines/records, the loop is over all records anyway.

Looping in a certain order needs a sorted cursor or an index. You could SQL-Select in the order you want if an index is missing or create an index eg INDEX ON BINTOC(co)+BINTOC(loc)+dept TAG sortorder.

SQL would be:
SELECT co,loc,dept, email FROM location ORDER BY 1,2,3 INTO CURSOR curMailings
SCAN
  ? co,loc, dept, email
  * whatever you want to do with each record.
ENDSCAN

Bye, Olaf.
0
 
jaymz69Author Commented:
As I do that then what is the best practice on scan through each one then?
I need to make sure I get it by co, loc, dept, email
*do my selects by the co, loc, dept
*gather info and group it up
*email accourind to the email from the scan
NEXT SCAN

SELECT lcco, lcloc, lcdept, lcemail FROM location ORDER BY lcco, lcloc INTO CURSOR curMailings
SCAN
 * Get History 
		Select slitem As e, Count(*) As ErrorCnt , sldesc As Type ;
		From cSHline, cSHhead ;
		Where ((shodat) Between ldBeginLastWeek And ldEndLastWeek ) And slco=shco And slloc=shloc And slord_=shord_ And slrel_=shrel_  ;
		Group By slitem Into Cursor shistory



* Get Open 
		Select olitem As e, Count(*) As ErrorCnt , oldesc As Type ;
		From cOOline, cOOhead Where ((ohodat) Between ldBeginLastWeek And ldEndLastWeek ) And olco=ohco And olloc=ohloc And olord_=ohord_ ;
		And olrel_=ohrel_  ;
		Group By olitem Into Cursor oline

		Select shistory.e, shistory.ErrorCnt  ;
		From shistory Union all Select oline.e, oline.ErrorCnt ;
		From oline Group By e, ErrorCnt Into Cursor Final

		Select Final.e, Sum(ErrorCnt) As Total ;
		From Final Group By e Into Cursor Total
ENDSCAN

Open in new window

0
 
jaymz69Author Commented:
Also I am not sure of the best way to gather the email from the scan and add it to a variable for
the email address to send to
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
CaltorCommented:
Hi jaymz69,
I think I can see what you are getting at here. If I understand it correctly you want to loop through the company locations dept table and email a report for that location to the email address against that record. If that is not the case please let us know.

With this in mind I would say you need something like.
SELECT lcco, lcloc, lcdept, lcemail FROM location ORDER BY lcco, lcloc INTO CURSOR tDepts
SCAN
  lcCompany = lcco
  lcLocation = lcloc
  lcEmail = lcemail
  *!* or SCATTER MEMVAR as previously advised by other experts

  * History
  SELECT ..... FROM ... WHERE slco = m.lcCompany AND slloc = m.lcLocation ...

  * Open
  SELECT ... FROM ... WHERE olco = m.lcCompany AND olloc = m.lcLocation ...

  * SendEmail
 ... 
ENDSCAN

Open in new window


This is pretty much what the other experts have already advised.
Please be aware this is air code and you might need to check for typos etc.

If all departments for a location receive the same report you might be able to optimise the performance by generating a list of locations using your first SQL statement and looping through that cursor. Then you could have an inner loop through the email addresses for that location. Any time savings would be dependent upon how many depts there are at each location and how long it takes to generate the report for that location. Let me know if you want help with that approach.
0
 
jaymz69Author Commented:
Yes you are right on what I am looking for to do.

I will need to scan for dept. too in the scan
0
 
CaltorCommented:
OK, great it sounds like you are well on your way. Let us know if you need more help.
0
 
jaymz69Author Commented:
this part I would just add lcDept = lcdept after my lcloc then... And again I will debug and test my numbers thanks for the assitance.


SCAN
  lcCompany = lcco
  lcLocation = lcloc
  lcEmail = lcemail
0
 
CaltorCommented:
Yes. Note that you might like to change the name of the memory variable to lcDepartment to make it obvious whether you are referencing a field or memory variable. Where there is any change of ambiguity (namely anywhere you refrence a memory variable to the RHS of an equals sign) you should prefix with m. to make it clear.
0
 
jaymz69Author Commented:
Going back to this

index is missing or create an index eg INDEX ON BINTOC(co)+BINTOC(loc)+dept TAG sortorder.

SQL would be:
SELECT co,loc,dept, email FROM location ORDER BY 1,2,3 INTO CURSOR curMailings
SCAN
  ? co,loc, dept, email
  * whatever you want to do with each record.
ENDSCAN


I never have doen this befor or seen it.

do you do the INDEX ON
then the
SELECT *.....?
0
 
CaltorCommented:
That's 2 separate things. The INDEX ON applies to the source table (locations) but not the SQL generated cursor curMailings. The ORDER BY is all that is required for the SQL - SELECT to create a cursor in the right order. Why do you want to create the index?
0
 
jaymz69Author Commented:
Really I don't
I am going to go about it a different way I figure
I am trying it out on my own
And see what happens

Select my table into array
Then pass the Parms
And loop thru
0
 
CaltorCommented:
Just use the code provided by us and forget about index on. The ORDER BY clause of the SQL - SELECT will generate a cursor (temporary table) in the correctly sorted sequence for you. You then SCAN (loop) through that cursor doing the work for each record.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Just to answer your question about the index: With the index you can sort the table, you don't need to create a curosr with ordered data then. So that's alternatively to SQL-Select.

You'd only need to create the index once, then you open the table, SET ORDER to the index and scan through the table instead of selecting into a cursor.

I wonder why you make things even more complicated with an array. Just to have variables? If you scan through either the table or the cursor, you can adress values of the current record by locations.fieldname eg locations.co, loctions.loc or curMailings.co, curMailings.loc etc.

You can also use these single field references in subsequent SQL inside the scan to group the data for each mailing.

And on the bigger scale I bet you could do one bigger SQL to create one cursor with all mails instead of a cursor for each mail.

Eg something like

Select locations.co, locations.loc, locations.dept,slitem As e, Count(*) As ErrorCnt , sldesc As Type ;
            From locations, cSHline, cSHhead ;
            Where (shodat Between ldBeginLastWeek And ldEndLastWeek ) ;
                And slco=locations.co And slloc=locations.loc ;
                And slco=shco And slloc=shloc And slord_=shord_ And slrel_=shrel_
            Group By co,loc,dept,slitem Into Cursor shistory

Then each mail would be created by a portion of that cursor with the same co,loc,dept. It's much faster to do the data processing at once and seperate the data for each mail afterwards.

Bye, Olaf.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now