Order By in Report built with code

[qberta01]

Hello,

I am not a FoxPro programmer, but have been given what I thought would be the simple task of creating an order by sequence in a report that is built through code. I am using version 7.....

Everything works fine until I get to the order by on the postal zone. What I would like to do in the postal zone portion is order by a bit field called renewal (in the LblData table) then the postal zone.

Please review..


* These next 40+ lines do nothing but put the data in a particular print sequence.

Wait Window 'Preparing Label Print Order
Sequence... One Moment Please' NoWait
Alter Table lbldata Add Column OrderBy I
Index On OrderId TO tmpIdx3

If File('TmpOrdBy.Dbf')
Erase TmpOrdBy.Dbf
Endif
Create Table TmpOrdBy (CustID i, OrderID i,
ProdList c(254))
Select LblData
Go Top


Private cProdCodeList, iOrderID, iSeqNbr
cProdCodeList = ''
Do While !Eof()
iOrderID = OrderID
iCustID = CustID
iCopies = Copies

DO While OrderID = iOrderID
iCopies = Max(iCopies, Copies)
cProdCodeList = cProdCodeList + Padr(Alltrim(ProdCode), 8, '.')
Skip
EndDo

cProdCodeList = Padl(Alltrim(Str(iCopies)), 3, '0') + cProdCodeList

Insert Into TmpOrdBy (CustID, OrderID, ProdList) Values (iCustID, iOrderID, cProdCodeList)
cProdCodeList = ''
Enddo
Select Custid, Orderid, ProdList, Len(Alltrim(ProdList)) From TmpOrdBy Order By 3 Into Cursor MyTemp
Select MyTemp

Set Relation To OrderID Into LblData
iSeqNbr = 0
Scan
Select LblData
iSeqNbr = iSeqNbr + 1
DO While OrderID = MyTemp.OrderID And !Eof()
Replace OrderBy With iSeqNbr
Skip
Enddo
Select MyTemp
EndScan
Select MyTemp
Set Relation To
Select LblData
Index On OrderBy To tmpIdx4
Wait Window 'Print Order Sequencing Complete. Now Gathering Address Data' NoWait


****************************************************************************************************************************
* Postal Zone Ordering

Wait Window 'Preparing Label Print Order Sequence By Postal Zone... One Moment Please' NoWait

Select LblData
If TYPE('LblData.PostalZone') = 'U'
Index On STR(Copies)+STR(OrderBy) To tmpIdxPst
Else
*Had to get a little tricky here...
*All multiples are supposed to be grouped together, then sorted by PZ...
*So we pad the index value for the zone with a space for singles.
Index On IIF(Copies>1,(IIF(Renewal = .F.,0,1)),' '+IIF(Renewal = .F.,0,1))+STR(PostalZone)+STR(Copies)+STR(OrderBy) To tmpIdxPst
EndIf
Wait Window 'Print Order Sequencing Complete. Now Gathering Address Data' NoWait

******************************************************

I get the following error:

Line Of Code.: Index On IIF(Copies>1,(IIF(Renewal = .F.,0,1)),' '+IIF(Renewal = .F.,0,1))+STR(PostalZone)+STR(Copies)+STR(OrderBy) To tmpIdxPst

Error Message: Operator/operand type mismatch.

If anyone can help I would really appreciate it! Also, please forgive the slopiness of the code.


*ENDIF

 

[DSummZZZ]

I'm a little confused but to start with, but your error is coming from trying to concatonating character with numeric values. But, you shouldn't try to jam all that stuff into an index statement.
If you're just trying to create a repot by PostalZone, index it like this:

INDEX ON PostalZone TAG PostalZone

Then run your report/label like this:
REPORT FORM whatever FOR renewal

That will only print where renewal = .T.



-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[qberta01]

Thanks for your response. The original offending line of code for postal zone was writen by someone else:

Index On IIF(Copies>1,PostalZone,' '+PostalZone)
+STR(Copies)+STR(OrderBy) To tmpIdxPst
----------------------------------------------

What the report does, I think , is group the data by copies. Once the data is grouped it needs to sort it by postal code then sort it by renewal. I don't want to print only where renewal is true. I want to print everything, but in the sequence I mentioned above. I don't want to lose the orderby part either. Should I replace the following:

Index On IIF(Copies>1,(IIF(Renewal = .F.,0,1)),' '+IIF(Renewal = .F.,0,1))+STR(PostalZone)+STR(Copies)+STR(OrderBy) To tmpIdxPst


with -----

INDEX ON OrderBy TAG OrderBy
INDEX ON PostalZone TAG PostalZone
INDEX ON RenewalTAG Renewal


I hope I have expressed myself clearly. If not I apologize. Thanks for taking the time to assist me...Q

 

[KenWMurphy]

If I have a complex report to do, I simplify it using a SELECT statement. For example, if you have a series of tables to use (all related to eachother) and you need to set an index that goes across these tables, it becomes somewhat complex. Get the complexities out of the way using SELECT. For example,

SELECT Tbl1.*, Tbl2.*, ... ;
FROM Tbl1 ;
LEFT OUTER JOIN Tbl2 ;
ON Tbl1.Tbl2ForeignKey = Tbl2.KeyFld ;
INNER JOIN Tbl3 ;
ON Tbl1.Tbl3.ForeignKey = Tbl3.KeyFld ;
...
ORDER BY Tbl1.Fld1, Tbl2.Fld4, Tbl3.Fld5 ;
INTO CURSOR MyReportCursor

This gives you a simple flat file cursor that you can easily create a report over. (The complexities are all taken care of by the SELECT statement.) If you want to take this further, store your SELECT clauses in tables. For example:

lcSqlScript = [SELECT ]+ QryFlds.cFieldList + ;
[ FROM ] + QryJoins.cFromJoin + ;
[ WHERE ] + QryWhere.cWhere + ;
[ ORDER BY ] + QryOrder.cOrderBy + ;
[ INTO CURSOR ] + lcCursorName
&lcSqlScript

This gives you the ability to "mix & match" query components. Specifically, for this report, you could create one QryFlds and QryJoins record, any number of QryWhere records and any number of QryOrder records. You could then simply have the user select the filter and order from dropdown lists. One report - any number of sort orders and filters - too easy.

You can, of course make this quite a bit more generic if you want. See Andy Kramec's article on class based data management here:

http://www.tightlinecomputers.com

Ken

 

[qberta01]

Hmmm... So there is no possible way for me to make this simple by adding renewal somehow in the index of the original code, which does work. I just want to add one more sort level...nothing more...

Index On IIF(Copies>1,PostalZone,' '+PostalZone)
+STR(Copies)+STR(OrderBy) To tmpIdxPs

Thanks....Q

 

[KenWMurphy]

I think you missed Dave Summers reply. Your problem in this index statement is that you are concatenating numbers and characters - you cannot do this. Change the numbers into characters using the str() function (just as you did for postalzone, copies and orderby.

INDEX ON IIF(Copies > 1,;
STR(IIF(NOT Renewal,0,1),1,0),' '+;
STR(IIF(NOT Renewal,0,1),1,0))+ ;
STR(PostalZone)+ ;
STR(Copies)+ ;
STR(OrderBy) ;
TO tmpIdxPst.IDX

Ken

 

[qberta01]

Oh no, I did not miss his reply. I was thinking along a different path since he stated I should not be jamming everything on an index. Then I thought I really don't want to rewrite all this code. I guess what I really want to know is how to convert a logical value to a string.

I tried your code above, but still get the same error. I was wondering if I should use transform?

Thx...Q

 

[DSummZZZ]

One of the easiest ways to convert a logical to string is using something like this:

cStr = Transform(renewal, 'Y')

If renewal is .T., it will return 'Y'. If it's .F., it will return 'N'.
Another way is something like this:

cStr = Iif(renewal, "1", "0")

Here's how you would put it in your index:

INDEX ON IIF(Copies > 1,;
             Iif(renewal, "1", "0"),' '+;
             Iif(renewal, "1", "0")) + ;
             STR(PostalZone)+ ;
             STR(Copies)+ ;
             STR(OrderBy) ;
       TO tmpIdxPst.IDX

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[KenWMurphy]

Q,

After looking at that code, I would most definately want to rewrite it. At first blush, I think you could turn all of this into a single select statement. Much easier to maintain in the future - and NOW is always a good time to make code better.

Ken

 

[qberta01]

It didn't work at first, but I changed it a little:

INDEX ON IIF(Copies > 1,;
Iif(renewal, "1", "0"),' '+;
Iif(renewal, "1", "0")) + ;
IIF(Copies>1,;
PostalZone,' '+;
PostalZone)+ ;
STR(Copies)+ ;
STR(OrderBy) ;
TO tmpIdxPst.IDX

and it seems to be working okay. Will do more testing...

Thanks soooooo much!