Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to add multiple indexes in foxpro 13

Status
Not open for further replies.
Niki_S

Niki_S

Programmer
Jun 4, 2021
232
LK
Code: 
SELECT CI_QTY
INDEX on nStylecode TAG Stylecode
SELECT CI_QTY
INDEX on Style TAG Styles
SELECT CI_QTY
INDEX on cLotName TAG cLotName

SELECT _DelDtl 
SCAN
	IF CI_QTY.nCiQty <>_DelDtl.nCiQty then
		SELECT CI_QTY
		SEEK _DelDtl.nStylecode 
		SELECT CI_QTY
		SEEK _DelDtl.Style 
		SELECT CI_QTY
		SEEK _DelDtl.cLotName 
		
		SELECT _DelDtl 
		replace nCiQty WITH CI_QTY.nCiQty 
	ENDIF 
	
ENDSCAN

When I run my code it shows an error as ".temp is read only." How can I fix this?
Thank You
 
Sort by date Sort by votes
Even if a cursor is readonly, you can still index it. Makes me wonder. What is DBF("CI_QTY").
Or is it really the indexing that fails, is it not the REPLACE?

You're doing 3 SEEKS, do you reaalize a SEEK always starts at top and when you do 3 seeks as you do, you don't get the record with nStylecode, Style, and cLotname matching your criteria, you only can be sure about the last SEEK finding a record with_DelDtl.cLotname, but it will have any Style and nStylecode.

Chriss
 
Upvote 0 Downvote
I think I would specify the order before doing a SEEK (that's what I normally do) someone is going to tell me that
VFP will do that automatically now...

Code: 
SELECT CI_QTY
INDEX on nStylecode TAG Stylecode
INDEX on Style TAG Styles
INDEX on cLotName TAG cLotName

SELECT _DelDtl 
SCAN
	IF CI_QTY.nCiQty <>_DelDtl.nCiQty then
		SELECT CI_QTY
		SET ORDER TO Stylecode
		SEEK _DelDtl.nStylecode 
		IF !FOUND()
			SELECT CI_QTY
			SET ORDER TO STYLE
			SEEK _DelDtl.Style
			IF !FOUND() 
				SELECT CI_QTY
				SET ORDER TO cLlotName
				SEEK _DelDtl.cLotName 
			ENDIF
		ENDIF	
		IF FOUND()	
			SELECT _DelDtl 
			replace nCiQty WITH CI_QTY.nCiQty 
		ENDIF
	ENDIF 
	SELECT _DelDtl 
ENDSCAN

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​
 
Upvote 0 Downvote
Of course it could be you are looking for this instead:

Code: 
SELECT CI_QTY
INDEX on STR(nStylecode,8,0)+STYLE+CLOTNAME TAG Combined

SELECT _DelDtl 
SCAN
	IF CI_QTY.nCiQty <>_DelDtl.nCiQty then
		SELECT CI_QTY
		SET ORDER TO Combined
		SEEK (STR(_DelDtl.nStylecode ,8,0)+_DelDtl.Style+_DelDtl.cLotName )
		IF FOUND()	
			SELECT _DelDtl 
			replace nCiQty WITH CI_QTY.nCiQty 
		ENDIF
	ENDIF 
	SELECT _DelDtl 
ENDSCAN

or perhaps

Code: 
SELECT CI_QTY
INDEX on STR(nStylecode,8,0)+STYLE+CLOTNAME TAG Combined

SELECT _DelDtl 
SCAN
	SELECT CI_QTY
	SET ORDER TO Combined
	SEEK (STR(_DelDtl.nStylecode ,8,0)+_DelDtl.Style+_DelDtl.cLotName )
	IF FOUND()	
		IF CI_QTY.nCiQty <>_DelDtl.nCiQty then
			SELECT _DelDtl 
			replace nCiQty WITH CI_QTY.nCiQty 
		ENDIF
	ENDIF 
	SELECT _DelDtl 
ENDSCAN

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​
 
Upvote 0 Downvote
I have to select 2 cursors and make indexes.

First I have to get data from SQL and I did it like this.
Code: 
Use In Select('Clr_Name')

Select _DelDtl
Scan
	TEXT TO stra NOSHOW
	SELECT cColorName,nStylecode,clotname from vMER_Master_LotColors where nStylecode=?_DelDtl.nStylecode and clotname=?_DelDtl.cLotName GROUP BY cColorName,nStylecode,clotname
	ENDTEXT
	SQLExec(hndOps,stra,'_TempClr')
	If Not Used('Clr_Name')
		Select * From  _TempClr Into Cursor Clr_Name Readwrite
	Else
		Select Clr_Name 
		Append From Dbf('_TempClr')
	Endif
ENDSCAN


Use In Select('Clr_Des')

Select _DelDtl
Scan
	TEXT TO stra NOSHOW
	SELECT cLotColor as cColorName,nStylecd,clotname from mis.dbo.vInvFinal where nStylecd=?_DelDtl.nStylecode and clotname=?_DelDtl.cLotName and cLotColor=?_DelDtl.cColorName GROUP BY nStylecd,clotname,cLotColor 
	ENDTEXT
	SQLExec(hndOps,stra,'_TempDes')
	If Not Used('Clr_Des')
		Select * From  _TempDes Into Cursor Clr_Des Readwrite
	Else
		Select Clr_Des
		Append From Dbf('_TempDes')
	Endif
ENDSCAN

After that I have to compare Clr_Des and Clr_Name and check if there have same records in Clr_Des against to the Clr_Name in order to nStylecd,clotname,cLotColor something like this.
Code: 
Clr_Name 
cColorName       nStylecode         clotname
Black            1234               A1
White            1234               A1
Blue             1234               A1
Black            10                 Z2
Green            10                 Z2
Pink             10                 Z2

Clr_Des 
cLotColor        nStylecd           clotname
Black            1234               A1
Black            10                 Z2
Green            10                 Z2
Pink             10                 Z2

According to the above cursors there have missing records in Clr_Des. In that case I need to something like this.
Code: 
IF Colr_Desp.Tot_Desp>0 
     REPLACE Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_Desp.Tot_Desp*Colr_Desp.Tot_Desp,0)
ELSE
     IF Lot_CI.Tot_CI=0 
         REPLACE Delvr_Pcs WITH 0
     ELSE 
         REPLACE Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_CI.Tot_CI*_DelDtl.nCiQty,0)
     ENDIF      
ENDIF
According to this is there have any missing records I need to do first replace command to the whole nStylecode and clotname something like this.
Code: 
_DelQty
cColorName       nStylecode         clotname        DelQty
Black            1234               A1              100
White            1234               A1              100
Blue             1234               A1              100

For this I did my code as below, but it is not working. Someone can please correct me?
Code: 
SELECT Clr_Des
INDEX ON cColorName TAG ColorName 
INDEX on nStylecd TAG Stylecd 
INDEX on clotname TAG lotname 

SELECT Clr_Name 
INDEX ON cColorName TAG ClrName 
INDEX on nStylecode TAG Stylecode
INDEX on clotname TAG lot_name 

SELECT _DelDtl 
SCAN 
	SELECT Clr_Name 
	SET ORDER TO ClrName AND Stylecode AND lot_name 
	SEEK (Clr_Des.cColorName+Clr_Des.nStylecode+Clr_Des.clotname) 
	
	_Clr="N"
	
	SCAN WHILE ClrName = Clr_Des.cColorName
	IF Colr_Desp.Tot_Desp>0 
		REPLACE _DelDtl.Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_Desp.Tot_Desp*Colr_Desp.Tot_Desp,0)
	
		_Clr="Y"
		
		EXIT 
	ENDIF 
	ENDSCAN
	IF _Clr="N"
		IF Lot_CI.Tot_CI=0 
	    	REPLACE _DelDtl.Delvr_Pcs WITH 0
		ELSE 
			REPLACE _DelDtl.Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_CI.Tot_CI*_DelDtl.nCiQty,0)

		ENDIF
	ENDIF 
	 
ENDSCAN

Thank you
 
Upvote 0 Downvote
If you have multiple conditions, use LOCATE with all conditions. But you better not use two cursors in the first place, get from SQL what you want in the first place.

Code: 
SELECT mcol.cColorName, mcol.nStylecode, mcol.clotname from vMER_Master_LotColors mcol
inner join mis.dbo.vInvFinal inv 
on   mcol.nStylecode = inv.nStylecd
and  mcol.cColorName = inv.cLotColor 
and  mcol.clotname = inv.clotname
where mcol.nStylecd= ?_DelDtl.nStylecode 
and mcol.clotname=   ?_DelDtl.cLotName 
and mcol.cColorName= ?_DelDtl.cColorName

Ideally you could also inner join _DelDtl instead of iterating all _DelDtl records you got from SQL beforehand, I think.

Just don't do your workload in VFP if all data you query from is in SQL Server, just query what you actually want in the first place. As far as I know you're new to both VFP and SQL Server and SQL, you don't get the best learning effect from writing simple queries to SQL Server and then trying to do some aftermath and merging or filtering in VFP. Just leanr straight forward T-SQL (the MS SQL Server SL dialect) and get what you need in one query.

Chriss
 
Upvote 0 Downvote
Really thank you for the advices.
When I take only one cursor I think I can't get all the records in vMER_Master_LotColors. So I have to use two cursors. Within 2 cursors how can I do my code?
 
Upvote 0 Downvote
Finally you want the list of colors both cursors have, right?
Then there is no point in querying all oolors first. There are all colors in the database, right? That won't change just because you only fetch some of them by the record(s) in _DelDtl.

Whatever you need can be done with SQL on SQL Server.

Chriss
 
Upvote 0 Downvote
I don't think I have ever seen this used

Code: 
SET ORDER TO ClrName AND Stylecode AND lot_name

I'm pretty certain that can't be done... again, unless someone wants to enlighten me

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​
 
Upvote 0 Downvote
Mike Yearwood,

Would it be possible for you to take part in a technical discussion without constantly resorting to negative language, personal attacks on other forum members, and unnecessary attempts to stir up controversy?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
I'm afraid I have to point out there's even something worse than the multiple SELECT CI_QTY, that is only the last SEEK determines on which record you end. SEEKs are not cumulative.
What I'm saying is best shown in an example: Say you have data about persons with their name and city, then first setting order to an index on city and seeking "Washington" and then setting order to lastname and seeking "Miller", you most likely will have seeked to a Miller that's not living in Washington. No matter where you are in a dbf, a SEEK seeks in the index and finds the first match in the index, that's usually the lowest recno that has that seeked value, and likely not a Washington citizen.

As I said earlier:
myself said:
If you have multiple conditions, use LOCATE with all conditions.
Click to expand...

but than also:
myself said:
But you better not use two cursors in the first place, get from SQL what you want in the first place.
Click to expand...

The inner join and where clause I propose are using the indexing in the sql server database both for the inner join and the where clauses. The result will be minimal in terms of network load, for example.

The other thing is directly after an INDEX the latest tag is determining the sort order. So you can eliminate one SET ORDER by indexing on the field you want to SEEK first. That doesn't help, though, you can't go on with another SEEK and keep the condition the first SEEK implies. So, that's also to you , Niki, your idea does not work at all. You would need an index that combinaes all three fields and that likely fails on being far too long, so you'd rather use SQL Select and then you can also do that in SL Server already.

Chriss
 
Upvote 0 Downvote
I did it like this. All are going good but the updates value is same. It means, it takes only the last value of the cursor and used it for all the records. The records are not separating by their cColorName ,nStylecode and cLotName . How can I fix that issue?
Code: 
Use In Select('Clr_new')

Select _DelDtl
SCAN
TEXT TO stra NOSHOW
SELECT mcol.cColorName as cColorName , mcol.nStylecode as nStylecode, mcol.clotname as cLotName  from vMER_Master_LotColors mcol
inner join mis.dbo.vInvFinal inv 
on   mcol.nStylecode = inv.nStyleCD
and  mcol.cColorName = inv.cLotColor
and  mcol.clotname = inv.cLotName
where mcol.nStylecode= ?_DelDtl.nStylecode 
and mcol.clotname=   ?_DelDtl.cLotName 
and mcol.cColorName= ?_DelDtl.cColorName   
ENDTEXT
	SQLExec(hndOps,stra,'_TempNew')
	If Not Used('Clr_new')
		Select * From  _TempNew Into Cursor Clr_new Readwrite
	Else
		Select Clr_new
		Append From Dbf('_TempNew')
	Endif
ENDSCAN



SELECT Clr_new
INDEX on STR(nStylecode,8,0)+CCOLORNAME+CLOTNAME TAG Combined

SELECT _DelDtl 
SCAN
	SELECT Clr_new
	SET ORDER TO  Combined
	SEEK (STR(_DelDtl.nStylecode ,8,0)+_DelDtl.cColorName+_DelDtl.cLotName )
	
	_Clr="N"
	
	SCAN WHILE cColorName= _DelDtl.cColorName AND nStylecode=_DelDtl.nStylecode AND cLotName=_DelDtl.cLotName  
	IF Colr_Desp.Tot_Desp>0 
		REPLACE _DelDtl.Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_Desp.Tot_Desp*Colr_Desp.Tot_Desp,0)
	
		_Clr="Y"
		
		EXIT 
	ENDIF 
	ENDSCAN
	IF _Clr="N"
		IF Lot_CI.Tot_CI=0 
	    	REPLACE _DelDtl.Delvr_Pcs WITH 0
		ELSE 
			REPLACE _DelDtl.Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_CI.Tot_CI*_DelDtl.nCiQty,0)

		ENDIF
	ENDIF 
	
ENDSCAN

Thank you
 
Upvote 0 Downvote
Browse Clr_new after the first endscan. Isn't that already the result you want?

All the REPLACES you do in the code after that might be what you need or not, I have no way to tell that. But each REPLACE works in one record, yes. You replace data in _DelDtl, not inSQL Server. No idea what you want or need, so either tell more or nobody can help you with your further needs.

You have no way of seeing how many records are actually changed, if you don't single step through the code and observe whether it does what you intend or at least have some count variables. Logging also is a method. But we as developers also use no magic to see what our code does, we use the debugger. You get experienced and can do a lot more without single stepping through it, but it's always a good idea to see what happens. in which records of what workarea.

To get into the debugger, double click at the left side of a line of code and a red point (a breakpoint) appearss. You can also have SUSPEND in your code and the program halts. Then you can start the debugger, or you can start it before you run your code. It's in the Tools menu second to last menu item.

And then you can see what line of code is done next in the Trace windwow. You can now step through the code line by line with F8 and do many more things. Read about the debugger in the help and you'll get to know one of the best things a developer can have. There's little documentation, especially no screenshots and exampes, but here's one helpful entry page to the topic:
Chriss
 
Upvote 0 Downvote
Code: 
#DEFINE theKey STR(nStylecode,8,0) + Style + cLotName
SELECT CI_QTY
INDEX ON theKey TAG main

SELECT _DelDtl 
SET RELATION TO theKey INTO CI_QTY
REPLACE ALL nCiQty WITH CI_QTY.nCiQty FOR NOT EOF("CI_QTY")
SET RELATION OFF INTO CI_QTY

--
Rick C. Hodgin
 
Upvote 0 Downvote
Okay.
First I got records from SQL into cursor Clr_new. After that I want to see if their have same records in _DelDtl in the same group. If there miss any record in that group I want to use my first replace command.

Code: 
Clr_new
cColorName       nStylecode         cLotName 
Black            1234               A1
Black            10                 Z2
Green            10                 Z2
Pink             10                 Z2 

_DelDtl 
cColorName       nStylecode         cLotName 
Black            1234               A1
White            1234               A1
Blue             1234               A1
Black            10                 Z2
Green            10                 Z2
Pink             10                 Z2

According to the above tables one is missing two records. But their nStylecode and cLotName are same. By considering those cColorName, nStylecode, cLotName I need to do the rest of my code. I used yes/no variable to see that the records are exist or not.
The issue is the records are updating with the same value. They are not updating according to the cColorName, nStylecode, cLotName.
Now I need to fix that issue.

Thank you
 
Upvote 0 Downvote
Hello Niki.

There are probably many ways of getting the result. Here is a possibly way, by using SCAN . . . ENDSCAN to read all the records on your _DelDtl table and finding which ones are missing in Clr_New, then adding them in.

Code: 
USE Clr_new

*  Create an index on Clr_new to allow subsequent searching
*  to see if it is missing any records.
INDEX ON cColorname + STR(nStyleCode,4) + cLotName TAG Idx1
SET ORDER TO TAG Idx1
USE _DelDtl IN 0
LOCAL lKey, lCountNew
lCountNew = 0

SELECT _DelDtl
*  Read through all the records in _DelDtl.  If we find any that
*  are not also present in Clr_new, add those records into Clr_New
SCAN
   lKey = cColorname + STR(nStylecode,4) + cLotName
   SELECT Clr_New
   SEEK lKey
   IF !FOUND()
      INSERT INTO Clr_New VALUES (_DelDtl.cColorname, ;
           _delDtl.nStyleCode, _DelDtl.cLotName)
      lCountNew = lCountNew + 1
      ENDIF
   ENDSCAN

MESSAGEBOX(IIF(lCountNew > 0, ;
      STR(lCountNew,2) + " record(s) were added into the Clr_new table.", ;
      "The Clr_new table already included all the records present in _DelDtl"))

SELECT Clr_new
USE
SELECT _DelDtl
USE

Hope this helps - Andrew
 
Upvote 0 Downvote
Mike L.,

I too have similar questions for M.Y. But I don't ask since I KNOW the response will be nothing but some type of useless slam. Thank you for your "bravery"! Hope it helps. M.Y. could be a valuable contributor instead of criticizer-in-chief.

Steve
 
Upvote 0 Downvote
So you want to know which _DelDtl rows are missing from Clr_new?

Well, that's easy, in the loop with the SQLExec, everytime SQLExec(hndOps,stra,'_TempNew')returns an empty _Tempnew cursor, that's missing in SQL Server data.

So:

Code: 
Use In Select('Clr_new')

Select _DelDtl
SCAN
TEXT TO stra NOSHOW
SELECT mcol.cColorName as cColorName , mcol.nStylecode as nStylecode, mcol.clotname as cLotName  from vMER_Master_LotColors mcol
inner join mis.dbo.vInvFinal inv 
on   mcol.nStylecode = inv.nStyleCD
and  mcol.cColorName = inv.cLotColor
and  mcol.clotname = inv.cLotName
where mcol.nStylecode= ?_DelDtl.nStylecode 
and mcol.clotname=   ?_DelDtl.cLotName 
and mcol.cColorName= ?_DelDtl.cColorName   
ENDTEXT
	SQLExec(hndOps,stra,'_TempNew')
	If reccount('_TempNew')=0
        	* do what you need to do here, add the record of _DelDtl to Clr_new, for example.
	Endif
	If Not Used('Clr_new')
		Select * From  _TempNew Into Cursor Clr_new Readwrite
	Else
		Select Clr_new
		Append From Dbf('_TempNew')
	Endif
ENDSCAN

Just notice: Clr_new will have all the records you find in SL Server, if it should contaon all of them in the end, there is no point querying all data into Clr_new then Clr_new is just a copy of _DelDtl. There mustbe something you want to do in SQL Server data, if you don't find a recor for a _DelDtl record. Well, "do what you need to do here" is the place to do that.



Chriss
 
Upvote 0 Downvote
Remember Mike Lewis's lesson about m.

I generally select a table, then select an order. Sometimes someone else designed the database and
used the same tag for two tables....

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​
 
Upvote 0 Downvote
Well, stop wasting time on me

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SitesMasstec
  • Locked
  • Question
Create a table with Tag indexes 1
Replies
13
Views
917
TamarGranor
TamarGranor
ravicoder
  • Locked
  • Question
Plotting position of text in an image in visual foxpro 1
Replies
9
Views
855
tomk3
tomk3
mmerlinn
  • Locked
  • Question
FoxPro button status
2
Replies
32
Views
2K
Scott24x7
Scott24x7
Dronner
  • Locked
  • Question
How to display photos from Visual Foxpro 6? 2
2
Replies
23
Views
614
sedenter
sedenter
ravicoder
  • Locked
  • Question
OCR with visual foxpro
Replies
3
Views
565
Gerrit Broekhuis
Gerrit Broekhuis

Part and Inventory Search

Sponsor

Back
Top