Using seeking in a table with 2 items/records at the same time.

[A_Radiolog_inFoxPro]

Dear All ,

I wish you a happy new year and all the best in life ,

I have an issue.

I am trying to find a record in a table with 2 values for example :
Looking at the order number and order type.

this config Used to work at another program with a different table.
SELECT <arbitrary table name>
SET ORDER TO 2
seek orderno+ordertype
orderno = some variable that contained the order number
ordertype = contained the tape of order.

and I can't seem to make it work with this table.

if someone could explain to me how this set order to 2 works and what it needs to have in the background that would be great.

the program is un optimized , no formes exist.

Thanks
The _idiot

 

[Mike Lewis]

You can only use [tt]orderno+ordertype[/tt] if the two variable - the order number and order type - are character variables. If they are not, you must convert them first. So, if orderno is numeric, you need [tt]TRANSFORM(orderno) + ordertype[/tt].

And that will only work if the table has an index based on that expression. You clearly have an index, otherwise you wouldn't be able to use [tt]SET ORDER[/tt], but is the index based on [tt]TRANSFORM(orderno) + ordertype[/tt]?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[A_Radiolog_inFoxPro]

Dear Mike ,

Thank you for your help , very much appreciate it.

Well I saw what the previous guy did(he had several indexes) and I added an index of my own under the table ( modify structure )
the order number in the table is as type : Number and the order type as Character
and in my program I use only Character variables so I presume this was the problem.

Now for me to understand what I have to do is under the index expressions to use :
str(ordernummer,7,0)+ ordertype ?

or I need to use transform function ?

also I can only seek character variables with seek so I can't convert the variables to numbers on the subroutine side , correct ?

Thanks ,

The idiot.

 

[Chris Miller]

Whether seek orderno+ordertype works ADN finds the data you expect depends entirely on whether the index you use covers this type of SEEK.

SET ORDER TO 2 just sets order to whatever is the second index, but 2 is no magic number that always means order number plus order type.

If you have no idea about indexes that means you'd need to learn them and in general only make use of code you don't just know worked previously, but know the details about what it does uses, depend on etc. All that is the reason both documentation and knowledge is important.

You make very clear you don't know nothing about VFP, so you made a first experience of dangerous half knowledge. And I don't think anybody here gave you or can give you all you need to know in one post so you understand and can use SEEK correctly in the future.

Instead I'd go for reading about LOCATE, your demand on finding a record based on two values can be fulfilled with code that you can moree easily understand. You can judge for yourself:

USE your.dbf
LOCATE FOR orderno='xyz123' And ordertype='abc'

I can tell you that knowing about SEEK And index searching is fine, but when you want fast results with the focus on being able to get your search idea into usable code, then first learn about LOCATE and its FOR clause, you can learn about indexes and their use as one of next steps, but I guess once you knwo how to put your ideas into LOCATES your next questions will be something else that's not even near the topic of data search.

You'll be fine with this and are able to modify and extend and apply it to totally different situations, too, much easier. Besides that, you can also later reuse that LOCATE code with one or many indexes that make it faster.

It should be clear that you only see the result when you have a browse window open. So I'll teach you another command that does LOCATE and display in one go at the same time:

USE your.dbf
BROWSE FOR orderno='xyz123' And ordertype='abc'

That'll show not just the first but all records with these two values. Or try

USE your.dbf
BROWSE FOR ordertype='abc'

To see a list of orders of that type

And then I personally would suggest the next helpful thing to you will be SQL-Selects, they even combine to open, search and show the data

Select * From your.dbf WHERE ordertype='abc'

Now we're at a point that once (in the 70ies) to allow management to query data. The first idea was even to establish something that would understand simplified english. You may or may not know the history since then, we're still at a point a non developer management person should ask an employed or hired developer for code that does what he needs..

Don't take me too serious, but I guess you'll see the truth of this yourself.


Chriss

 

[Mike Lewis]

Idiot, you asked if you have to use the TRANSFORM() function. No, you could just as well use STR(). The advantages of TRANSFORM() are (i) it converts any data type to a string, whereas STR() only converts numerics; and (ii) you don't have to specify the width of the string or the number of decimal places - with STR(), you either specify those values or you accept the default of ten characters and no decimals.

On your other point, it's not true that you can only SEEK character variables. You can seek any data type. But the values you are seeking must match the index's expression. If the index contains character strings, you can't SEEK a numeric value, or vice versa.

The problem with your code was that you were adding an order number to an order type. If the order number is a number and the order type is a character, you would see a "data type mismatch" error.

By the way, you said in your first post that you "can't seem to make it work". In general, to solve a problem, it helps us to know in what way something doesn't work: whether it produced an error message, or just gave an incorrect result, or what. That's always the case, not just for this particular problem.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[A_Radiolog_inFoxPro]

Dear Sir ,

Thank you SO SO very much for all the help ,

Well is not that I am blindly reusing code I just have issue with dbs the rest I can manage since I have an extensive knowledge on VB 6.0 , some Java , I just can't get around how fox-pro sometimes works / behaves because really it's quite an animal ( most probably the dev took the dos software and didn't optimice it 1 bit for windows 10.. )

Nevertheless is there a let's say crash course guide to get my head around how all these open files / dbs work and how can indexes be used and how many tricks I can play with ?

Nevertheless I prefer to feel that I know nothing than to rather say that I know something. Half knowledge as you said is dangerous and I am very aware of that. But when you are in a bind you are really out of options.

Thanks

The Idiot

 

[A_Radiolog_inFoxPro]

Dear Mr. Mike ,

Well the failure issue was that after the IF FOUND I got --> I found nothing.

So I check my index ( the index was correct I was converting the order number from number to character with SRT + the order type
and I had set at some point my order to my index name
and still i got the same error ( No record found )
and I know I am passing the proper data type i even tried to trim the variables I tried to pass the order number as a number .. still no record found and the sub-routine was failing.

then I was playing around with values and expressions and I couldn't find a solution I looked also online and still to no avail.

That's why I wanted to expose my idiocy Here Both literally and figuratively

And I was always running it through the debugger and having a watch an all variables and the table output and still no avail.

Thank you

The Idiot

 

[Chris Miller]

First, it's good to know you're not learning from scratch.

Data types are not the only topic. As their lengths and in case an index is about two fields, how they are combined. You can find out about the expression indexed:

SET ORDER TO something
? KEY()

asssume this tells you the index is on orderno+ordertype, then to find something it doesn't only matter whether you use STR() or TRANSFORM, but how long the orderno field is. If you see

[pre]orderno | ordertype | ...
-----------+---------------+-------
xyz123 | abc | ...[/pre]

And then you SEEK 'xyz123abc', do you really expect to find that record? Didn't you forgot something important?

Chriss

 

[A_Radiolog_inFoxPro]

Dear Mr.Miller

I forgot to trim the table and the try to match with my values ?

Thanks
The Idiot

 

[Chris Miller]

I'll give you another hint: Use the table, Set index order, manually navigate to the record you expect to find.

Now

? '>>'+Evaluate(key())+'<<'

What do you see?

Chriss

 

[A_Radiolog_inFoxPro]

Dear Mr.Miller ,

I get >> 0 <<

if I browse last

and re-run your command I get
>> 123456TYPEE<<

I guess there is 1 space character in front of the order number because the size of the filled in the table is 7 characters and the actually number currently is 6 ?

Damn.

Thank you really ... I wish I knew all the secrets of the command window and how to test stuff before you try to use them again and again until you get the result you need <3

Thank you

The Idiot

 

[A_Radiolog_inFoxPro]

Dear Mr.Miller

now refining the index after this revelation :

ALLTRIM(STR(ordernumber,7))+ordertype

And it just works
with seek ordernumber+ordertype

Just perfect thank you for everything really

also are there somewhere the shortcuts for the command window ?
like modify report / modify command , etc etc ?

Thanks

The Idiot

 

[Chris Miller]

I forgot to trim the table and the try to match with my values ?

I think you're on topic, but still in the inverse logic.

If you have char(n9 fields, they are char, no matter if you trim values before storing them or not, they become char with length n, padded, not trimmed.
And then because of that you also don't trim the values you search for.

ALLTRIM(STR(ordernumber,7))+ordertype

And it just works
with seek ordernumber+ordertype

Well, well, not so fast.

Is ALLTRIM(STR(ordernumber,7))+ordertype what you tried before or what you got from ? KEY()
I'm not in your computer nor your mind, sorry.

If the index is on the simple expression (= KEY()) ordernumber+ordertype, then this is simple string concatenation of the fields. The untrimmed data values stored in them.
SEEK ordernumber+ordertypeworks once you are on the record you seek you just stay there.

What still won't work is when you have 2 inputs 123 and ABC and just add them together.

and re-run your command I get
>> 123456TYPEE<<

Now we're nearly there, Now I'd still need to know and not just guess which part comes from which field, I could have asked to look at the single fields themselves, too:
? '>>'+orderno+'<<'
? '>>'+ordertype'<<'

The index expression field1+field2 concatenates what's stored in the fields, not necessarily the trimmed values you initially only stored by INSERT or textbox controlsource. The values usually are padded to the right, when you have spaces in front, the values are stroed with spaces, that's not a result of automatic padding to the field length. And now finally, what value you need to seek, depends not only on the expression but also the field ddefinition including their lengths, because that plays a role in what ordernumber+ordertype becomes.

And here's something to feed your interest: What happens with an index when you ALTER a table and extend the field length? What stays the same?
How would all that play out if the index expression would be STR(orderno,5)+Substr(ordertype,5,1)? Would it be better to index that or not, would it make indexes more stable or not? Need code changes of current code SEEKing data? How would it impact LOCATE or SQL WHERE clauses? So what do you really need to be aware of too make good use of an index in VFP. NOte: In other databases you merely index fields as is, mayhave indexes that contain multiple fields. In VFP you always index an expression, even the single field name is an expression, the simplest expression but it's not just the field, it's an expression that has the field name.

So what happens if you take two tables SET RLEATION from table1 to table2, somehow (look up what that means), and INDX ON fieldx with fieldx being a field of table 2, while the index key becomes an index of table1?

Chriss

 

[A_Radiolog_inFoxPro]

Dear Mr.Miller

So before my index was set like this :
STR(ordernumber,7)+ordertype
and the result of your command was this:

>>_123456TYPEE<< (_ this was a space character)

so because my DB file had the field length as 7 characters, I had to trim that in order to accomodate a rate case of an order number less than 6 or 7 digits.

after altering the index expression from : STR(ordernumber,7)+ordertype to
ALLTRIM(STR(ordernumber,7))+ordertype

I got the following result :
>>123456TYPEE<<

Also my subroutine does not alter any of these values. It replaces other values in other empty fields in the table so I am not really concerned what the old DEV did in other parts of the program and to be honest as long as it works I ain't touching anything structure wice.

now I just look for that combination once I find it I add another 2 numbers on other fields of the table. the rest remain unchanged.

? '>>'+orderno+'<<'
? '>>'+ordertype'<<'

these commands gave me a operator mismatch. now clue why.

But the structure of the table is:
--> ordernum as Number 7 digits and 0 decimals

--> Type as Character 5 char long

So what happens if you take two tables SET RLEATION from table1 to table2, somehow (look up what that means), and INDX ON fieldx with fieldx being a field of table 2, while the index key becomes an index of table1?

I don't want to blow hot air up your you know what , I have no clue what would happen or if it even happens/ takes place in this software.

plus quite a lot of unknown vocabulary for me

Thank you in advance

The idiot

 

[Mike Lewis]

Hey Mr. Idiot - I really don't want to call you that.

I agree. Please consider signing your posts with your real name. Or, if you prefer to remain incognito, choose a nickname that we can address you by.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I second the Mikes, and address you as "Management" for a while. You're not only doing this on you and it's not even a good running gag.

I had to trim that in order to accomodate a rate case of an order number less than 6 or 7 digits.

No, you have to pad, not trim. trim removes spaces, padding adds, as the index has a leading space for order numbers below 100000 (a million), you have to pad it by using STR(no,7).

Just another vocabulary thing.

But in short, if you're not interested in the details, don't change the index and risk existing code to break, change your way of using it by using the index expression on your start values, ie an order number as a number, not a string. then this will also format the same way as the index expression and be found by SEEK.

The baclkbone of that recommendation is your app worked all the time, now it doesn't, not because something is wrong with the way it indexes things, but your usage of that.



What you did with changing the index is not a good solution. For one thing, you have no simple way of changing any code that still relies on the index being STR(orderno,7)+ordertype

The index couldn't be on orderno+ordertype if orderno is N(7), so that's another quirk in your description, but no harm done.

Indexes should have fixed length, so an expression like STR(ordernno,7) is surely much better than AllTrim(STR(ordeno)) and the only problem you get is when you go above 10 million orders.
in fact orderno could be integer, only use 4 bytes instead of 7 and still allow numbers up to a billion (actually about 4 billion different values, half of them negative, still 8 digits are free to be 1-9 and that's covered by the integer range.

You can make indexes on ALLTRIM(something) and it doesn't break nor will ever, but it's still recommended to have fixed length indexes by the expression used not by VFP being clever about it. If you create an index on ALLTRIM(field) of an char field, index nodes will have field length anyway, no index node will have leading spaces, the padding then happens at the end, instead, but it happens. VFP doesn't support variable length index nodes. It's one of the reasons indexing memos isn't allowed. Index values are limited to 240 bytes, so an expression (potentially) longer can't be indexed. It even get's worse when you use a collating sequence, then you can only use up to 120 characters, as they always become double byte characters by collation.

The old index expression is fine, just mend what you're seeking.

Chriss

 

[A_Radiolog_inFoxPro]

Dear All ,

Thank you for the constructive input with my peculiar situation.

Yes I shall change who I sign my post with : TECH_FOX

Dear : myearwood > Your offer is quite tempting. **BUT** I would respectfully decline. since the data is quite sensitive and also the dev is still alive and around but not active in development. I really don't want to get into a court battle for some "stupid" program.
Me adapting it , maintaining it is one thing, taking his work and making it "open source" I don't think is right at all.

Dear : Chris Miller

I see where you are going with it.

As A rule of thumb I always used DO NOT TOUCH anything the old dev has and works. Just ADD on top of that. Do not alter / modify in any way / shape or form. You are in for an unrecoverable disaster.

So I always add my own parts without altering the past.

Regarding fixed length indexes ,

Yes I agree when we will reach 10 million we will be in a world of pain. because 7 digits are not enough. (9,999,999)

but currently the number is somewhere at 140 000 so this software was implemented first around 30 years ago.

So in its whole life cycle it will never reach that number at this rate of expansion. SO it's safe to say that that won't be needed. ( hopefully )

Regarding padding the value. I would disagree because I don't normalize the number that I get from my subroutine to match the leading spaces I trim that too that's why trimming the index works

because My subrune does a LOT of regular expression stuff ( String concatenation ) and dealing with CSVs and text files from other more advance / newer better softwares that need to intergrade with this old dog.


I hope I gave some idea to what is what in my situation.

Thank you in advance

TECH_FOX

 

[Chris Miller]

I don't see how padding your search value for a SEEK is a no go because at other times you need to trim them.

As A rule of thumb I always used DO NOT TOUCH anything the old dev has and works

You use an index on ALLTRIM(STR(ordernumber,7))+ordertype now, is that correct? That doesn't break the rule?

Also, you say you SEEK ordernumber+ordertype, right?

Well, what's actually the value you seek, ordernumber can't be numeric, VFP would give you an operator/operand type mismatch error.
So your ordernumber I guess is converted to a string (a small crime inn itself, if you store something as numeric and then in other places handle it as string), and trimmed it?

Like I say all the time, code doesn't reveal everything about something, especially if out of context. I can surely give you examples of SEEKs you expect to work failing no matter what expression you use to index or seek, but it's really not just a rule of thumbs or best practice and not only practice to have indexes on expressions that result in fixed length only. It's the only way to ensure your experience will be consistently good.

Chriss

 

[dbMark]

Just a side note here... In the quote above, you specified transforming a number to character for up to 7 digits. Years from now a problem could arise where the number could grow to more than 7 digits, such as going from 9,999,999 (7 digits) to 10,000,000 (8 digits). Then your code would break. Avoid that by making the possibly expanding value, even years into the future, large enough to anticipate growth yet not be unreasonably large.

Example, a couple decades ago I maintained an FP/DOS app used by several agencies. One day one site called saying their message logging portion of the app wasn't working. What changed? That morning their tracking number had crossed from 999999 to 1000000 and that line crashed since it expected only 6 digits. It didn't crash anywhere else since all other code expected up to 7 digits. It was a quick fix to correct that one line, recompile, test and upload a new EXE. But somewhere in the past a programmer hadn't anticipated crossing a million log count. Think long term.

 

[A_Radiolog_inFoxPro]

Dear Mr.dbMark

As I mentioned above. that number managed to reach 140.000 in a span of 30 years.

The whole life cycle of the system is not that long anyway.

if I even manage to reach 9999999 would be a miracle.

The old dev made this arrangement. Also this is not the only thing that needs to be changed.

All the dbs and associated tables the field for this entry is sized at 7 digits.

So if this will break that index will be the least of my problems.

All tables , and associated textboxes and counters , control files need to be altered. So if it will need be to be overhauled I will do it holistically this small part is the very small tip of the iceberg.

But nevertheless thank you very much for the remark. Taken into account.

Thank you in advance

TECH_FOX