×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Someone please explain me the flow of this code.4

## Someone please explain me the flow of this code.

(OP)

#### CODE -->

CREATE CURSOR tmp (RD N(18))
INDEX ON RD TAG MAIN CANDIDATE
SET ORDER TO

RAND(-1)
LOCAL i, j
j = 29
ON ERROR ErrHand(@j)

FOR i = 1 TO 30 && Just do 100k for now
INSERT INTO tmp (RD) VALUES (RAND()*j+1)
NEXT
ON ERROR

PROCEDURE ErrHand(j)
if j>=30

else
j=j*1.05
endif
RETRY
ENDPROC 

what does this procedure does ? How is the loop working ? what role does CANDIDATE has? What does TAG does ?

### RE: Someone please explain me the flow of this code.

Overall this is creating non repeating random numbers with a less elegant strategy of retrying at failures.

CANDIDATE is the index type and is unique, thus storing the same number twice triggers an error. And the error handler in the end does retry causing another RAND value to be generated.

I'd say it's flawed, as it adjusts j, a value used for the range of random values in a way that doesn't make sense to me. As it stops adjusting when j becomes just 1 than initial you could just start right away with 30. It seems to be for larger ranges. And even then, where you start with j gives a bias. If you'd start very low you'd prefer lower numbers before you get them all and then extend the range, that's not really effectively randomizing the results.

A better strategy is already given to you in your older thread184-1803937: How do I allot prizes to a person on different dates?

You can pick N of M numbers simply by populating a list from 1-M and then draw 1 out of them and N times, while deleting it from the list. Simply like in the lottery. That does need exactly N random numbers to be generated.

The think with such random/retry on error strategy is, when you'd want to pick 999 of 1000 numbers, you'd get almost certain repeats and would generate perhaps 10s of thousands of random numbers to finally get them. Of course, in that case it's much simpler to determine 1 losing number instead, but this illustrated the case.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

Olaf has given you a good answer, but let me add my €0,02 worth.

You are creating a cursor with a single numeric field. You are then filling that field with 30 random numbers.

In theory, the first record will contain a random value between 0 and 2. That's because you are multiplying the result of RAND() by j+1, and j is initially 1. By the same logic, the second record will contain a random number between 0 and 3. And so on.

But because the field in the cursor is an integer, the value will be rounded to the nearest whole number.

The point of the candidate index is that it does not allow duplicate values. So, if you try to insert a value which already exists in an earlier record, you will trigger your error handler. The error handler slightly alters the value of j. It then retries the insertion that caused the error. Because j is different, you might get a different random number. But it will still be rounded to the nearest integer as before.

So that's what the code does. But I have to ask: if you don't know what the code does, clearly you didn't write it yourself. So why you interested in it? What do you expect it to achieve?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Someone please explain me the flow of this code.

The code includes the comment:

&& Just do 100k for now

This implies that the code originally did the loop 100,000 times. That would give you a much more interesting result. But I still don't understand what the point of it is.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Someone please explain me the flow of this code.

For what it's worth, I've just run your code. The result is a cursor with 30 records. Each record contains a different integer, from 1 to 2 and then from 4 to 31 (so 3 is missing). That's pretty much what I expected. Is that what you wanted?

By the way, I noticed you also asked about the role of CANDIDATE and TAG. As I mentioned earlier, CANDIDATE ensures that the keys are unique. If you try to insert a record where the key field (RD in this case) contains a non-unique value, it triggers an error.

TAG is used to give the index a name - in this case, the name is Main. You would use that name if you subsequently wanted to refer to the index, for example in a SET ORDER command. The name is not being used here, but you still need to specify it in the INDEX command.

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Someone please explain me the flow of this code.

J initially is 29, isn't it?

Otherwise, if you want to pick 5 random numbers from 1 to 30, just change it to calculate numbers in hat range rand()*30+1 independent of j, no need to modify that during the creation of the numbers. and let the loop run from i=1 to 5 to create 5 numbers. It'sll repeat some iterations due to the errhand RETRY, not by setting back i or such.

This will be okay when you only pick a short sample of a bigger group.

Come to think of it an easy way to pick N out of M numbers is put them in a cursor and then index on RAND(), that'll sort records randomly and then jut pick the first N records and they are all different because each record has a different number to start with.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

(OP)
Thank you Olaf and Mike for your valuable insights !
It helps me to understand the concepts one step further. Thank you for being there.

#### Quote:

if you don't know what the code does, clearly you didn't write it yourself. So why you interested in it? What do you expect it to achieve?

Well I have come a long way from my previous thread where i asked stating an example of allotting prizes randomly.
The main purpose was different, I just needed a right concept to work around from there and you all helped me in that!

Now I am just a little mark behind to complete that program, the thing that I am facing is that the numbers that are being generated are from 0 to 30. What I am not able to figure out is, I just want the number to be generate from 1 to 30.

How do I work around that ?
It would be a great help in completing a long program that me and my uncle have spent days and nights on.

### RE: Someone please explain me the flow of this code.

(OP)

#### Quote:

from 1 to 2 and then from 4 to 31 (so 3 is missing). That's pretty much what I expected. Is that what you wanted?

Well, when I run I do not see the 3 missing.
Are you talking about the end result having 3 missing in it ?

### RE: Someone please explain me the flow of this code.

(OP)

#### Quote:

CANDIDATE ensures that the keys are unique. If you try to insert a record where the key field (RD in this case) contains a non-unique value, it triggers an error.

Also can we use this CANDIDATE on several fields in a table ? What would be an example code like ?

### RE: Someone please explain me the flow of this code.

Glad it was of some help, Janak.

But here is an easier way of doing it:

#### CODE -->

CREATE CURSOR temp (key I, randnum B)
INDEX on randnum TAG MAIN
FOR lnI = 1 TO 30
INSERT INTO temp (key, randnum) VALUES (RECNO(), RAND())
ENDFOR 

This will give you a cursor containing 30 records. The first field will contain the numbers 1 to 30 in random order. The second field contains a random number in the range 0 to 1, but you can ignore that. It is only present for the purposes of sorting.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Someone please explain me the flow of this code.

#### Quote:

Well, when I run I do not see the 3 missing.

That's right. Remember, these are random numbers. They will be different every time. What I see in not necessarilly what you will see.

#### Quote:

Also can we use this CANDIDATE on several fields in a table ? What would be an example code like ?

Yes we can. Just execute the required INDEX commands. For example:

#### CODE -->

INDEX ON RD TAG MAIN CANDIDATE
INDEX ON AnotherField TAG Somethign CANDIDATE
... etc. 

The effect will be to prevent duplicates in each of the fields specified. So, in this example, you will not be able to put duplicate values into either RD or AnotherField.

Just to be completely clear: With my alternative code, you don't need a CANDIDATE.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Someone please explain me the flow of this code.

You can define multiple candidate indexes, yes.
You already have the sample code to do so, it's the command.

#### CODE

INDEX ON field TAG indexname CANDIDATE

In itself a CANDIADATE index will not prevent double data, it will error in the moment you try to store something already in the table as it finds it in the index.

Be aware you don't need to repeat this, you'll only trigger the security prompt asking whether you want to recreate the index. An index file in a VFP DBF is storing multiple index tags (that's where TAG comes from), so you don't have a bunch of IDX files, just one CDX, that's actually also available again for free and old tables, nothing new. And different from IDX, that only update with data changes when they are actively SET index, the main CDX having the same stem name ie month.dbf/month.cdx will automatically be in sync with any data changes, it belongs to the table just like the FPT file storing memo and some other variable length field types.

I often see code indexing everytime, that's a waste of time. The only reason to do so is with old style single index IDX files and when you store index tags into secondary CDX files. Usually unnecessary, as the capacity limit is usually hitting the FPT or DBF file earlier than the CDX and you can still only set one order at a time.

Just be aware each candidate index checks along rows only, specifically the indexed column in all rows, indexing multiple columns will not check whether they are unique along single rows, if you expect that.

You usually only need a unique primary key, candidate can be used for secondary keys. It also is available to free/legacy/fox26 DBFs which don't support a primary key, there is where the name comes from, as it's uniqueness makes indexed columns candidates for primary keys proving their uniqueness.

Mike has picked up the idey to simply generate 30 records in random order and then pick any count of numbers you need by starting from TOP. You could SCAN NEXT 5, or do a for loop from 1 to 5 and with SKIP 1 each time after picking the current row. And his example would also work, if you'd put in any other group of numbers, say only week days, no weekends.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

(OP)

#### CODE -->

CREATE  CURSOR tmp (RD N(18))

INDEX ON RD TAG MAIN CANDIDATE

SET ORDER TO
RAND(-1)
LOCAL i, j

j=100

ON ERROR ErrORHand(@j)

FOR i = 1 TO 100

INSERT INTO tmp (RD) VALUES (RAND()*j)

NEXT

INDEX on rd TO asdf
brows

ON ERROR
PROCEDURE ERRORHAND(j)
IF  j>=101

else

j=j*1

endif
RETRY

ENDPROC 

This is what runs, I just wanted that the outcome does not include 0 !
Is this possible with the code I have mentioned above ?

### RE: Someone please explain me the flow of this code.

You've been given why you get 0 if you don't add 1, RAND() is a number from 0 to 1, <1, multiply that with J and you get the range ß to j, but guaranteed never j itself, as the factor always excludes 1, you can only get close to j, very close, but it's always j-delta, delta>0.

Also, notice storing a float number into an int field this is always cutting off digits, rounding down, never up. Therefore you then end up with 0 up to j-1, no matter how close the number will be to the next higher.

The formula for numbers 1...max is INT(RAND()*max+1) or INT(RAND()*max)+1. ie it doesn't matter if you add one before rounding down or first round down and then add 1, but add 1, if you don't want to start at 0.

And just by the way, as INDEX ON does not only create an index but also sets order to it, your browse will always sort the random, you don't set order you'll see how random the number are generated.
So simply write SET ORDER TO before BROWSE, setting no order means not specifying an index file or tag name in SET ODER TO.

And last not least, all the error handler needs to do is RETRY, nothing more, nothing less. no change in j.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

(OP)
Thank you so much Olaf !!
Thanks a ton!

### RE: Someone please explain me the flow of this code.

It's okay,

In the cases you use, with i going up to the same number as j, the generation will take longer and longer, because that strategy only works well if the upper limit for i is much lower than j, the max random number value you want to generate.

When you have 99 of the range 1...100 randomly creating the missing number will eventually happen, but it might take long, longer than 100 retries. So it still is only a good idea of you say want to generate 5 numbers between 1..30 only, not all 30.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

To anybody this interest, the retry strategy already takes a long time when you want to cover all 1000 numbers in the range 1-1000, so when you actually only randomized the order of all 1000 numbers.

A retry strategy really is bad when you go for computing the full range of numbers, as the last one will take the most retries, of course.

I wrote up some code to measure this:

#### CODE

Clear
Rand(-1)
Public gnRandCounter
gnRandCounter = 0

Local lnT0, lnReccount
lnReccount=0
t0 = Seconds()
Randomize("tmp",500,500)
lnReccount = lnReccount + Reccount("tmp")
Randomize("tmp",500,500)
lnReccount = lnReccount + Reccount("tmp")
Randomize("tmp",500,500)
lnReccount = lnReccount + Reccount("tmp")
Randomize("tmp",500,500)
lnReccount = lnReccount + Reccount("tmp")
Randomize("tmp",500,500)
lnReccount = lnReccount + Reccount("tmp")
? gnRandCounter," random numbers generated for "+Transform(lnReccount)+" records using your code."
? Seconds()-t0," seconds needed overall."

lnReccount=0
gnRandCounter = 0
t0 = Seconds()
Randomize2("tmp",500,500)
lnReccount = lnReccount + Reccount("tmp")
Randomize2("tmp",500,500)
lnReccount = lnReccount + Reccount("tmp")
Randomize2("tmp",500,500)
lnReccount = lnReccount + Reccount("tmp")
Randomize2("tmp",500,500)
lnReccount = lnReccount + Reccount("tmp")
Randomize2("tmp",500,500)
lnReccount = lnReccount + Reccount("tmp")
? gnRandCounter," random numbers generated for "+Transform(lnReccount)+" records using Mike's code."
? Seconds()-t0," seconds needed overall."

lnReccount=0
gnRandCounter = 0
t0 = Seconds()
Randomize("tmp",5,30)
lnReccount = lnReccount + Reccount("tmp")
Randomize("tmp",5,30)
lnReccount = lnReccount + Reccount("tmp")
Randomize("tmp",5,30)
lnReccount = lnReccount + Reccount("tmp")
Randomize("tmp",5,30)
lnReccount = lnReccount + Reccount("tmp")
Randomize("tmp",5,30)
lnReccount = lnReccount + Reccount("tmp")
? gnRandCounter," random numbers generated for "+Transform(lnReccount)+" records using your code."
? Seconds()-t0," seconds needed overall."

lnReccount=0
gnRandCounter = 0
t0 = Seconds()
Randomize2("tmp",5,30)
lnReccount = lnReccount + Reccount("tmp")
Randomize2("tmp",5,30)
lnReccount = lnReccount + Reccount("tmp")
Randomize2("tmp",5,30)
lnReccount = lnReccount + Reccount("tmp")
Randomize2("tmp",5,30)
lnReccount = lnReccount + Reccount("tmp")
Randomize2("tmp",5,30)
lnReccount = lnReccount + Reccount("tmp")
? gnRandCounter," random numbers generated for "+Transform(lnReccount)+" records using Mike's code."
? Seconds()-t0," seconds needed overall."

Browse Nowait

Procedure myrand()
gnRandCounter = gnRandCounter + 1
Return Rand()

Procedure Randomize()
Lparameters tcAlias, tnCount, tnMax
tnMax = Evl(tnMax,30)

Local lcNormalErrHand, lnCount, lnRetries, lnT0

If tnCount>tnMax
Error "tnCount can't be higher than tnMax"
Endif

*      If tnCount>tnMax/2
*         Error "Warning: Only use for tnCount much smaller than tnMax"
*      Endif

Create Cursor (tcAlias) (irand I)
Index On irand Tag crand Candidate
Set Order To

lcNormalErrHand = On("Error")
On Error Retry

For lnCount=1 To tnCount
lnT0 = Seconds()
lnRetries = gnRandCounter
Insert Into (tcAlias) Values (myrand()*tnMax+1)
lnDuration = Seconds()-lnT0
lnRetries = gnRandCounter-lnRetries

If lnDuration>0.1 Or lnRetries>100
? "It took "+Transform(lnDuration)+" sec. and "+Transform(lnRetries)+" retries to get the "+Transform(lnCount)+"th number"
Endif
Next

If Empty(lcNormalErrHand)
On Error
Else
On Error &lcNormalErrHand
Endif
Endproc

Procedure Randomize2()
Lparameters tcAlias, tnCount, tnMax
tnMax = Evl(tnMax,30)

Create Cursor crsRandomizer (irand I, randorder B)
Index On randorder Tag Main
For lnI = 1 To tnMax
Insert Into crsRandomizer (irand, randorder) Values (lnI, myrand())
Endfor

Select Top tnCount irand From crsRandomizer Order By randorder Into Cursor (tcAlias)
Use In crsRandomizer
Endproc 

Better use Mike's code even though in a case of small count within large range it computes more records and random numbers, it'll run fast in all cases. Your retry based code only works better in case count<<max.

What astound me is that actually computing a large amount of random numbers doesn't take long, the usage of Mik'es code to compute 500/500 numbers 5 times shows this also creates 2500 random numbers every time, but in split seconds. The most time must be consumed in throwing an error and in the retry.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

2
There is another reason to use my code: It doesn't interfer with your normal error handling.

In most serious applications, you would use ON ERROR to set up a global error-handler that stays in force throughout the session. If you also use ON ERROR locally, as in your original code, Janak, then you lose the benefit of the global handler.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Someone please explain me the flow of this code.

I mended that in my version, the original really will bite you in the long run, as any further error causes a retry from then on. So it's extremely important to put it back to how it was.
Since TRY..CATCH handling exists, this could be handled with that and decrementing the loop counter to repeat the insert.

The only elegance in RETRY is actually just redoing the insert. But regarding my time measurements, preventing the error in the first place must save time, because if 2500 Rand() calls take split seconds it's not the major time spent in the usual 15000 retries needed to finish 500 of 500 numbers without repeats with his code instead of yours. What's also strikingly better is that it runs fast in all cases and is shorter, too. So overall trickery here with RETRY isn't worth it. You can do without that has I said here:

#### Quote (myself)

I'd rather turn the for loop counter back 1 and repeat the loop once more, whenever field(X) already has its 'W' allotted, so check that before the REPLACE.

What's also nice with your code: When you keep the cursor you build up, creating a new set you just need to REINCEX and get a new order. It's just you need a random number for ever record to finally see which are the top N, every new order number has the chance to put a record in the top N.

The urn model would perform slightly better, as it just picks N and then reduces it with ADEL. But I haven't measured that...

#### CODE

Local array laRandnumbers[5]

Local lnCounter
clear
Randomize3(@laRandnumbers,5,30)
For lnCounter = 1 to 5
? laRandnumbers[lnCounter]
EndFor

Procedure Randomize3()
Lparameters taResult, tnCount, tnMax
tnMax = Evl(tnMax,30)

Local lnCounter, lnElement

Local Array laUrnNumbers[tnMax]
For lnCounter=1 to tnMax
laUrnNumbers[lnCounter] = lnCounter
EndFor

Dimension taResult[tnCount]

For lnCounter = 1 To tnCount
lnElement = Int(Rand()*tnMax)+1
taResult[lnCounter] = laUrnNumbers[lnElement]
Adel(laUrnNumbers, lnElement) && swaps last element with deleted, so array ends in all deleted .f.  elements.
tnMax = tnMax - 1 && therefore next time pick from one fewer element, spare time to redimension.
Endfor
Endproc 

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

(OP)
Thank you Olaf!
Your code could come out to be a major point in our program. We are consistently suffering in getting things done lately ( having little to no knowledge of the concept of vfp ) But are and have been determined since 1995 working our way out here in India with the help of foxpro by running our small humble business. Well any ways, the things is I am just lost I know it's all concepts that i am missing in my program, but I am just giving up. With no technical guidance and no thought given into the engineering of this program, I am going no way.

I apologies for being such a mess..

What I want was to look into my personalized error that I why I am running into, providing you with the necessary files. If it could me any help.

Thank you with all my heart!

### RE: Someone please explain me the flow of this code.

No, for this to make sense as knowledgebase in the long term, we can't now go into "private mode". Because, notice, attachments are not stored forever, posted code is.

Please post problematic portions of your code and errors you encounter. Also notice your setting up of an error handler will keep VFP silent about ANY other errors, so better not use your own version of computing random number with RETRY. Just don't. You'll not get messages about errors and the handler will RETRY any line of code causing an error, which usually will cause an endless loop and render VFP unusable. This can only work, when you know the code actually is okay and like in the case of that INSERT of random numbers will eventually work, with another RAND() number.

Usual code does not differ in the next execution, it will not depend on random numbers or time, so it will error again. That's illustrating the importance of Mike's last post.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

#### Quote:

Well any ways, the things is I am just lost I know it's all concepts that i am missing in my program, but I am just giving up. With no technical guidance and no thought given into the engineering of this program, I am going no way.

Janek, if you are saying that you are planning to give up the whole application, I would be very sorry to hear that. You and your uncle have put a lot of effort into this project, and it would be a great shame not to see it through.

Could it be the application that you inherited was simply too ambitious for your level of knowledge? Would it be worth stepping back from it, and spending a little time learning the fundamentals of VFP. There are plenty of resources available to help with that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Someone please explain me the flow of this code.

Maybe this will help:

The functions I have designed are not to be called once to generate aq group of n records up to a max range.
So just run the code of my previous post and see what you get there.

You make ONE call and get 5 numbers. Shouldn't this clear up what the parameters mean?

In the first parameter you pass in the array the function should populate, in the second parameter you specify how many different numbers you want, and in contrast to a normal random number generated they will guaranteed to be different, non repeating. But this can also only be assured, because they are not generated separately, one per call, but all numbers are generated in a single call. That, by the way, was also done by your own function.

Thinking of a function generating 5 values, in each in 5 calls without being signalled when to start the series isn't possible, I already told you that, and that's not a question of the programming language.

Now, since VFP can't RETURN r1,r2,r3,r4,r5, as Python could, the only way to return 5 values at once is using a cursor or array. Your function used a cursor, my last one now uses an array.

So the loop determining 5 field names should be like this:

#### CODE

Local array laRandnumbers[5]
Local lnCounter

Randomize3(@laRandnumbers,5,30)
SELECT yourtable
For lnCounter = 1 to 5
? FIELD( laRandnumbers[lnCounter])
EndFor 

And again, and I already repeatedly said so, when your 30 fields don't start at field number 1, then add an offset to this. I remember you have tow fields before you series of 30 or 31 fields, so add an offset of 2 to skip those two fields not belonging to the "calender" columns.

#### CODE

Local array laRandnumbers[5]
Local lnCounter, lnHowManyDifferentNumbers, lnNumberRandeFrom1ToThis
lnHowManyDifferentNumbers = 5
lnNumberRandeFrom1ToThis = 30

Randomize3(@laRandnumbers, lnHowManyDifferentNumbers ,lnNumberRandeFrom1ToThis )
Local lnFieldNumberOffset
lnFieldNumberOffset = 2 && add to all laRandnumbers elements to shift the range from 1...lnNumberRandeFrom1ToThis to 1+lnFieldNumberOffset...lnNumberRandeFrom1ToThis+lnFieldNumberOffset
* for example to change from 1...30 to 3..32 add 2

SELECT yourtable
For lnCounter = 1 to lnHowManyDifferentNumbers
lnFieldnumber = laRandnumbers[lnCounter] + lnFieldNumberOffset
? FIELD(lnFieldnumber)
EndFor 

I can't give you exactly what you need, for example, whether you want to cover the range 1..30 or whether that will depend on the number of days the current month has, that's up to you. But it's all adjustable.

Bye, Olsf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

(OP)
It surely did !!!

I simply have no words.. the explanation of what does what helped me to understand more and having that "lnFieldNumberOffset" was icing on the cake !
I had given up but not my uncle, he is stubborn in making things to work and I think that is the attitude needed because of which things have worked since 1995.

Well, a milestone in this program is reached and I whole heartedly thank Olaf and Mike for this. You guys are a blessing!

I continue with the program for new featured to be added..

### RE: Someone please explain me the flow of this code.

(OP)

This is the result what we were working for.
According to the red marked circle we wanted to allot 'A' that much times.

and that's what we achieved with the help of you guys.

What we now are trying to figure out is, to allot 'P' on the basis of the blue marked field.
for that I searched around if I could find something and I got Olaf's answer on some previous threads

#### CODE -->

select 0
use mytable in 0 exclusive
if type("mytable.mynewfield") # cTypeexpected
alter table mytable add mynewfield ...
endif 

where to check if a specific field is empty, if not replace it with a 'P'.

I do not know if this approach is correct or not plus

#### CODE -->

# cTypeexpected
seems to give an error that cTypeexpected is not found. I know it is just a placeholder for me to write according to my needs, but I am not able to figure out it.
Any help ?

### RE: Someone please explain me the flow of this code.

The # simply means "not equal to". It is another way of writing <>.

The TYPE() function returns the data type of the specified field (in this case, mynewfield in mytable). It returns a letter to indicate the type, such as C for character or D for date.

So you are comparing the type of the field with the letter stored in the variable, cTypeexpected, whatever that is. If it is not the same as that letter, you are adding the field to the table.

So that's what the code does. But, as before, you have to think about why you want to do it. I'm not aware of where you found this code, so I can't really comment on that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Someone please explain me the flow of this code.

(OP)
Okay... so it checks the type of that field. I get it now.

What if I wanted If the specified field is empty or not ?
if it is replace it with something.

Is !empty would work ?

### RE: Someone please explain me the flow of this code.

(OP)

#### CODE -->

LOCAL Absentornot
Absentornot = 'A'
if field("ATTEST.D1") = Absentornot
MESSAGEBOX('Contains')
ELSE
MESSAGEbox('Is empty ')
endif 

I tried this and it seems to work but it shows "Is empty" even if the field contains an "A" .
Why is this happening ?

### RE: Someone please explain me the flow of this code.

(OP)

#### CODE -->

if  D1#'A'
REPLACE   d1  WITH 'P'
endif 

This is working for me.

#### CODE -->

if  D2#'A'
REPLACE   d2  WITH 'P'
endif 

But what if there is no field 'D2' it would throw an error, how to find it there is a field D2, and if there is then only replace.

### RE: Someone please explain me the flow of this code.

(OP)
How can this be possible, to check if from field number 3 to whatever field I want to replace all the empty records with 'P' !?

### RE: Someone please explain me the flow of this code.

The FIELDS() function gives you a field name by field number, not a field number by field name.

The way you use the field function would determine if the D1 field is present at all in the DBF, you only get the input returned as output, if the field of the given name exists. This isn't ADODB.Recordsetfiel("name).value.

In no way will you get tat a field value with the field function, you a) get name of a column number by FIELD(1), for example and b) empty value for FIELD('dfhkjhdfkjdfs'( will just tell you that a field dfhkjhdfkjdfs doesn't exist, as that returns empty.

To get at the field value as you know you can write IF D2#'A' or IF NOT EMPTY(D2) or such, but that's not applicable to FIELD(x) no matter whether x is number or name. Use EVAL(FIELD(x)) to get the value of the field name.

#### CODE

? FIELD('D2')
? EVAL(FIELD('D2'))
? D2 

See? The first call just gives back D2 again, output=input. FIELD is never giving you values of fields, it gives you field names only, no matter how you call it.
The last line shows you how easy it is to access fields simply by their name when you already know which name you want to address. Your situation is, that you need to be able to address a field by number, so there's a need to go the route EVAL(FIELD(randomnumber+2)), for example.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

#### Quote (How can this be possible, to check if from field number 3 to whatever field I want to replace all the empty records with 'P' !?)

As far as I understood you wanted to prevent random field numbers o address the same field twice, Randomize already guarantees you get 5 different random numbers. You don't need to check this now anymore. The Randomize function already solves that there will be no same field number twice.

You only need to ensure the records you process start out empty, then the loop over the laRandnumbers array you get from randomize already ensures you address 5 different fields.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

#### CODE

if type("mytable.mynewfield") # cTypeexpected
alter table mytable add mynewfield ...
endif 

I recommend you don't do such table alterations in the regular process itself.
Keep database changes as administrative separate tasks, ensure your databases are in the structure needed for your software version and then process the datqa inside.

I would also leave out any code checking for a field to exist despite the unusual situation of a software release prereleased to a database update, that can start working once the update exists, but even then ideally you'd just need to heck one database version number stored somewhere in one table of it, for example.

It's all very over the top terminology, if your database is a bunch of DBF files and your software consists of single scripts, but this still applies. The need for exclusive access then also disappears at runtime of the software, only the scripts concerned with the database structure expansion are eating with checking the existence of fields or not. And you can even spare that yourself, if you already know which alter table scripts you ran and which not. You're overcomplicating things here by wanting to do everything at once and ensure things work or mend themselves as necessary, that's a strategy leading to maintainable unclean code.

Software version X having a SELECT a,b,c from D has to be able to rely on table D existing and having fields a,b,c or your code will never get to the point of really acting.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

(OP)

Have a look at this image.
What I am trying to achieve is that the empty records that Are left, I want them to be replaced by ‘P’.

### RE: Someone please explain me the flow of this code.

The value in Present is the number of the field, the day number? This isn't self-explanatory.

If so, use it as the field number instead of the random value, the rest of the REPLACE command stays the same, doesn't it? The only thing that changes is where the number of the day comes from, isn´t it?

Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

I don't know what exactly you want, please rephrase your question, you really don't make it understandable what exactly you want. What does the blue outlined column has to do with it?

If I try to make sense of "empty records that Are left," and replace "records" with "fields". Then the simplest solution would be to start by setting all fields to 'P' before changing some to 'A', wouldn't it?

If you don't like that this puts them to the wrong letter temporarily, well, you initiate all fields empty, that's also a wrong value, isn't it? So that's not the problem as long as out end result gets right, or is it?
Why make it hard on you, just initialize all with P and you don't need to care, the non flipped fields will stay P and all D1-D30 are set.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

### RE: Someone please explain me the flow of this code.

Okay, another of my famous triple posts. Just look at the length of this thread and realize how you actually introduce the main problem of handling your data by wanting this structure of 30 fields.

One rule of data normalization is to avoid such data "arrays" inside a record. Just let that sink in and you may find much simpler ways that won't depend on field numbers to set fields. Look back to what Mike and I initially recommended as data structures in thread184-1803937: How do I allot prizes to a person on different dates?.

You do repeat employee ids, yes, but you then would have combinations of employee_id, date and type (A for Ad, P for Present) and ensure you don't store double information about a date by a candidate index you do on both employeeid and date field, for example. A database only gives you tools to look at multiple rows of the same table for restricting them, or counting them, like such indexes, COUNT(), SUM(), AVG() etc. You don't have such functions for columns of a row. So you always struggle with such structures.

If you design them for easier display, that programming by wrong virtues. A visual representation of data can differ from the storage of it and usually will, the concerns for data storage are wildly different and your mental idea, human readablity or such have nothing to do with that. They are a separate issue when going for display or report of data.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!