Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Loop through a DBF

Posted on 2010-11-11
13
Medium Priority
?
560 Views
Last Modified: 2012-05-10
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
Comment
Question by:jaymz69
  • 6
  • 5
  • 2
13 Comments
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 1000 total points
ID: 34112405
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
 

Author Comment

by:jaymz69
ID: 34112521
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
 

Author Comment

by:jaymz69
ID: 34112665
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Assisted Solution

by:Caltor
Caltor earned 1000 total points
ID: 34119177
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
 

Author Comment

by:jaymz69
ID: 34120754
Yes you are right on what I am looking for to do.

I will need to scan for dept. too in the scan
0
 
LVL 3

Expert Comment

by:Caltor
ID: 34120839
OK, great it sounds like you are well on your way. Let us know if you need more help.
0
 

Author Comment

by:jaymz69
ID: 34120894
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
 
LVL 3

Expert Comment

by:Caltor
ID: 34120946
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
 

Author Comment

by:jaymz69
ID: 34145930
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
 
LVL 3

Expert Comment

by:Caltor
ID: 34150902
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
 

Author Comment

by:jaymz69
ID: 34150959
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
 
LVL 3

Expert Comment

by:Caltor
ID: 34238115
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 34245858
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
In this article, the configuration steps in Zabbix to monitor devices via SNMP will be discussed with some real examples on Cisco Router/Switch, Catalyst Switch, NAS Synology device.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question