Solved

Loop through a DBF

Posted on 2010-11-11
13
482 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 29

Accepted Solution

by:
Olaf Doschke earned 250 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
 
LVL 3

Assisted Solution

by:Caltor
Caltor earned 250 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 29

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now