updating table with a .cdx

[Koen Piller]

Hi,

Griff constgructed an indexroutine

Function HIERACHY
	Parameters m.KeyField
	PRIVATE m.KeyField,m.String, m.WordCount, I 
	m.Wordcount = GetWordCount(m.KeyField,".")
	m.String = STR(m.WordCount,3,0)
	For I = 1 to m.WordCount
		m.String = m.String + STR(VAL(GetWordNum((m.KeyField,I,".")),3,0)
	Next
	Return(m.String)

which works perfectly.
This procedure will create a .cdx file. When updating my table it errors "The nonstructural CDX file should be closed"
Is there a construction/coding possible to have this indexroutine as index part of the table to overcome this error?

Stay healthy,
Koen

 

[Olaf Doschke]

multiple warnings occured performing...."

Well, I think this just means the CDX was so unusable at that stage, that just changing the expression in the table designer didn't help. What it would do in detail is dropping the tag as it was and creating it with the new expression. But if you just try the first step of that in a CDX that leads to the message "The nonstructural CDX file should be closed", then I think you either have two CDXes and confuse VFP or me or both or you have a situation like it is with a broken CDX that the best thing you can do is DELETE TAG ALL. The second best is to delete the CDX, but then what happens is there is a rest information about indexes in the DBC and you get in trouble with even more messages about the invalidity of the indexes or dbf or even the dbc.

Anyway, as far as it looks, you made it, I'd just double ensure the call is doing what it should do. The way you posted Griff's function has an r missing, maybe your PRG is actually differing from what you reposed here, doesn't matter much.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,
1) I am sorry, I can't find the type / missing R. It could be that I made a mistake making the function in retyping here in order to make it more readable since Griff originally posted in all UPPER.
Please show me where I went wrong.
2) This is interesting, you give as comment on line
m.Wordcount = GetWordCount(m.KeyField,".") && will be 1, no "." is present, which means the whole string counts as one word
however Keyfield has only 3 possible values:
1) nothing
2) "1"
3) "1."followed by a next increment - this is checked on input.
in case 1) nWordCount has 0
in case 2) nWOrdCount has 1
in case 3) nWordCount has the value of the words
Your conclusion, hierarchy("parenteelid") is wrong, it can't do it's job, it isn't passing in the parenteelid value but always the constant string literal "parenteelid", sorry I can't follow. The more the shown effect in my screenshot, does that not count to be the proof of the pudding?
Please understand me correctly I don't want to argue here, just want to be 100% sure I do understand what is going on with Griff's hierarchy function.
The error message about multiple warnings, is one of those mystic things. What I do know is that when I pass with quotes it works.
Stay healthy,
Koen

 

[Olaf Doschke]

Koen,

in the first place: Your data sorting shows it works, and I assume that's because you have a modified version of what you posted.

Where is the R missing, simply in the function name: HIERACHY (wrong spelling) vs calling HIERA[highlight #FCE94F]R[/highlight]CHY("parenteelid"). It's actually the first sign you use something else.

But you reject and ignore the very obvious, when you make a call HIERARCHY("parenteelid") you pass in the string "parenteelid", not the field parenteelid. You just have to execute what I posted to see this
a) it works even without a table present, that has a parenteelid field.
b) It just literally works on the string "parenteelid": pee ayy are ee, en tee ee ee el ay dee. The letters.
c) Both me and Griff pointed that out, and it's a simple fact. I don't know how you can still not see the wood for the trees. I would really like to know what's happening in you, when you still ignore something that has been pointed out 4 or five times. Just because a field name or variable with the same name exists, a string of that name is still a string of that name and doesn't turn to the variable or field value. You're pointed to the string value you pass in not working with the function as posted. You don't need to show us the composition of the values of your parenteelid field, you don't pass these values in.

There is an obvious way you overall get the sorted result and no error: You use modified code to take this passed-in field name and evaluate it and you didn't post that code. That would just be overcomplicating things, as the index expression has access to all the fields directly, passing in the field name to then evaluate it in the function code is just an extra step costing extra time. So even in that case, just mend the function to NOT need to evaluate a passed in field name, just pass in the field value and work with that, as Griff's original function does.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[GriffMG]

How about this instead

use names exclusive
alter table names add column hiercode c(25) not null
replace all hiercode with hierarchy(parenteeid)
index on hiercode tag hiercode

Whenever you add or mod a record, remember to

replace  hiercode with hierarchy(parenteeid)

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. ​

 

[Koen Piller]

Griff,
That is now exactly what I did not want to do! I did have this field ParenteelIndexID (grr for the very long name) which has e.g. a value of 10102111 for the parenteeeliD 1.1.2.11.1 I had a STP filling this record on every update, works fine. I could use as an index. But I have to extend my dbf with 5 more different parenteelID (for partner, for father, for mother, for stepfather and for stepmother) so I figured it would me better and easier to maintain to substitute these 5 extra fields with 5 different index expressions.
I did not manage to convert my convert my conversion into an index expression, your coding and your advise to put this as a function into my dbf index works as a charm, it is even extremely fast.
It works and I am more than happy, Ippyee!
Now I only don't understand why Olaf is complaining it cannot work and I should not place the name of the field into quotes. Microsoft seems very happy with it, and is not happy at all when I leave out the quotes.
Thanks for helping me.
Stay healthy,
Koen

 

[Koen Piller]

Olaf,

your

when you make a call HIERARCHY("parenteelid") you pass in the string "parenteelid",

I believe you are mixing here the effect of a the function hierarchy when applied to an index.
Indeed the function itself will work without the quotes, so
tcParenteelID = '1.1.2.1.4'
? hierarchy(tcParenteelid) will return '1 1 2 1 4'
and hierarchy('tcParenteelID') will return '1 0' && you may figure out why via the debugger.
however the function as expression in the index field will return for every value of parenteelID the converted content.
There is a difference between a function and a expression containing a function.
Rest assured I did not change anything in Griff's function, I merely changed the Upper to normal and made the calling to variables more consequent to my other functions.
Stay healthy,
Koen

 

[Olaf Doschke]

hierarchy('tcParenteelID') will return '1 0' && you may figure out why via the debugger.

I single stepped this just in my mind. And I showed this, yes. So at least we agree on that.

There is a difference between a function and an expression containing a function.

If there is a difference between an expression you set to something, like the IIF expressions often used for grid column dynamicbackcolor, then you put the whole expression into quotes, you set dynamicbackcolor to the expression as a string, which VFP then evaluates. You quote the whole expression or nothing. But you never just set one parameter in quotes. Just all or nothing. Quoting a name would only make sense in a context where name delimiters are a thing, that's not part of VFP, though. And the situation differs when you set daynmicbackcolor in the context of the properties window, you then set the expression without quotes as the property window already knows this is a string property containing the expression, or you make use of a specialty of the property window and set it to ="expression" with a preceding = and the full expression in quotes. But then, again, it's the full expression, not just parts ot ir. And this effectively puts just the string into the property, with the potential to make it one level more complicated and use an expression that returns an expression.

None of both things is necessary in the index tab of the table designer. If you use the fields tab of the table designer, enter your field there and simply specify an index order (pick ascending or descending) directly there and then change to the index tab, you see just picking an index order you created an index, and the index expression there simply is the field name, without quotes. So no quotes necessary. Why on earth would anyone put quotes around a field name? SQL Server has that concept of name delimiters, but there it also allows spaces in names and that make such name delimiters necessary just like VFP has many string delimiters, SQL Server allows double quotes and square brackets as name delimiters, which VFP knows as string delimiters, And that's a reason people struggle when they want to specify just a string, for which SQL Server only allows single quotes. Funny, ain't it?

There are a lot of detail things to know to not become a victim of any pitfalls. But when you quote an expresion to set it as the expression and not a value resulting from the expression, then you quote the whole expression, not just parts of it or field names. You're pointing out a rule, but use another. One that would hold true for another database. You're explicitly saying it doesn't work when you don't put the name in quotes, that's a simple utter lie. It's lying I can't stand, sorry. This will work, if the function works on field values.

I dare you to do this just in code and see wha sorting you get when you browse in that order

INDEX ON hierarchy("parenteelid") TAG hierarchy2

We're at a point you just don't want to cooperate anymore, then I'll stop here. I'll likely now stop posting anything in any forum anymore. It's just so frustrating and times are frustrating enough.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,
That is just your own decision, which I respect but do not understand.
What gave you the impression I do not want to cooperate? I simply donot understand why you believe my Griff's coding, which shows a correct result, is not correct that's all. And please do not make simple things complicated with an example of dynamicbackcolor, we are here talking about an index expression, which is completely different. Your assumption I did change change something to Griff's excellent coding is wrong. I did not, I can assure you.
We differ in opinion all right, is that enough to get frustrated?
Stay healthy,
Koen

 

[NasibKalsi]

you can try

index on str(len(alltrim(PARENTEELID))) + PARENTEELID tag HIERARCHY


I tried to test your code and found that I can not call 'user defined functions' with Field Name(s) for creating indexes.
This will not work: Hierarchy(PARENTEELID) or Hierarchy(FIELDNAME). There is an error when running the code

This will work: Hierarchy("PARENTEELID") or Hierarchy("FIELDNAME"). The result will be the same for both calls, ie it does not do anything.

In your posted code you have 4 left parenthesis and only 3 right parenthesis.

Function HIERACHY
	Parameters m.KeyField
	PRIVATE m.KeyField,m.String, m.WordCount, I 
	m.Wordcount = GetWordCount(m.KeyField,".")
	m.String = STR(m.WordCount,3,0)
	For I = 1 to m.WordCount
		m.String = m.String + STR(VAL(GetWordNum((m.KeyField,I,".")),3,0)
	Next
	Return(m.String)

 

[Koen Piller]

Hi,

You are correct there is one parenthesis too much, the correct code looks like:

m.String = m.String + Str(Val(Getwordnum(m.KeyField,m.I,".")),2,0)

For your remark 'it does not do anything', I will produce a working example and upload here.
Stay healthy,
Koen

 

[NasibKalsi]

This may be helpful

set talk off
set echo off
clear
close all 
delete file t-name.*
create table t-name free (FIELD1 c(10), RESULT c(20))

insert into t-name (FIELD1) values ("1.2.3")
insert into t-name (FIELD1) values ("1.1")
insert into t-name (FIELD1) values ("1")
insert into t-name (FIELD1) values (" ")
insert into t-name (FIELD1) values ("2.1")
insert into t-name (FIELD1) values ("2.1.1")
insert into t-name (FIELD1) values ("1.1.1")
browse fields Field1, Result title "Before call"

go top

browse fields ;
	AsIs=FIELD1,;
	QuotedField    = Hierarchy("FIELD1"), ;   && Call 1
	QuotedAnything = Hierarchy("ANYTHING"), ; && Call 2
	UnQuotedField  = Hierarchy(FIELD1), ;     && Call 3
	QuotedField2   = Hierarchy2("FIELD1"), ;  && Call 4
	Result title "After Call"

* Added in editing 
index on Hierarchy(FIELD1) tag FIELD1
brow
* End Addition

Function Hierarchy
	Parameters m.KeyField
	PRIVATE m.KeyField,m.String, m.WordCount, I 
	m.Wordcount = GetWordCount(m.KeyField,".")
	m.String = STR(m.WordCount,3,0)
	For I = 1 to m.WordCount
		m.String = m.String + STR(VAL(GetWordNum(m.KeyField,I,".")),3,0)
	Next
	Return(m.String) 
		
Function Hierarchy2
        * Missing &
	Parameters m.KeyField
	PRIVATE m.KeyField,m.String, m.WordCount, I 
	m.Wordcount = GetWordCount(&KeyField,".")
	m.String = STR(m.WordCount,3,0)
	For I = 1 to m.WordCount
		m.String = m.String + STR(VAL(GetWordNum(&KeyField,I,".")),3,0)
	Next
	Return(m.String)