How do I allot prizes to a person on different dates?

[janak vyas]

I tried using Rand() but I am failing in implementing something like this, where I randomly give the employees prizes on random dates while also having control and constraining the number of prizes an employee can get.
I have only used and seen/understand old foxpro codes so I will appreciate If any one can help with writing a function for this. Please.

( I have attached a picture just if I am not able to explain my question )

 

[Mike Lewis]

It would help to know where you are starting from. In other words, do you already have your data in tables (DBF files) or arrays? If so, what data do you have? And what are your rules for giving prizes? For example, how far in advance do you want to assign a prize? Can an employee get prizes on two consecutive dates? Is the amount of the prize also random, or is it always the same amount? And so on. (I'm not asking for the answers to those questions. I am just giving examples of the sort of rules that you must establish.)

But to get things started, let's assume that you have an employee table, like this:

ID     Name
==     ====

10     Andy
25     Bonny
36     Darren

Create a prizes table, like this:

ID    Employee_ID     Prize_Date   
==    ===========     ==========

Your code would look something like this:

* Asumes you want to give prizes up to 30 days in advance; 
* Prizes table has auto-incrementing ID; the amount
* of the prize is fixed.
SELECT Employee
SCAN
  INSERT INTO Prizes (Employee_ID, Prize_Date) ;
    VALUES (Employee.ID, DATE() + INT((RAND() * 30)))
ENDSCAN

This is only meant to give you a start. You will probably need to refine it according to the rules mentioned above.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[janak vyas]

I created both the tables, Employee and Prizes
=================
Employee table having
ID -> numeric -> 2
Name -> character -> 10
=================
======================
Prizes table having
ID -> numeric -> 2
Employe_ID -> numeric -> 2
P_date -> numeric -> 8
=======================

I populated the table Employee with data and tried to run the program but an error persist on

 INSERT INTO Prizes (Employe_ID, P_Date) ;
    VALUES (Employee.ID, DATE()+ INT((RAND() * 30)))]

showing DATA TYPE MISMATCH
I am not able to figure out what is the mismatch ?

 

[Mike Lewis]

The "Data type mismatch" happened because your P_date field is numeric. It should be a Date field. Change it to a Date data type, and that will get you past that error.

Regarding the "500 prize money", do you mean that each prize is worth 500? If so, there is no need to store the amount in a table. It will be the same for all employees.

In any case, my code was not meant to be a complete solution. As I explained at the start, we would need a lot more information to provide that. My aim was to give you a start, to help you to understand the general approach so that you can solve the problem for yourself.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[janak vyas]

Instead of randomly assigning numeric values or dates, how can i just allot a character like 'W' ?

 

[Olaf Doschke]

I don't know what you mean. As Mike already said about the amount of 500, it's not necessary to allot anything but the winning date to an employee, if the prize is fixed.

Later display W in a grid at the dates you have in data, ot 500, or whatever.

If you want that flexible, well, then add fields to the table and populate them as necessary. A char(1) field could store a 'W' or any other ANSI character in the DBF codepage. For amounts you can add an integer field, or currency.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[janak vyas]

I just want to clear things, if there are fields in prize tables as
-------------------------------------------------------------------
employee.id | one | two | three | four | and so on... to thirty |
-------------------------------------------------------------------
if I define a variable mWon = 'W' and write a function which which tells that the "W" character needs to appear 4 times in random fields like :

-----------------------------------------------------------------------------------------
employee.id | one | two | three | four | five | six | seven | eight | nine | ten | .....
001..............| W | ..........| W | ...........| W | |...| |...| .......| W | .....
-----------------------------------------------------------------------------------------

I wanted to convey and needed to understand how can I achieve this ( also tried to explain this same thing with a picture ) with a help of old foxpro codes!

I sincerely apologies if I am not clear in making my question more easier.
Hope this helps.

 

[Olaf Doschke]

Ok, that's a thing you wouldn't do knowing how to normalize your data, what you describe as data structure is 1:1 what you would want to display in a grid but not store that way, it's candidate for a record per cell.

Ynway, I can't guarantee you this works in legacy foxpro, but there is field so when you know the fields in question are field number 2 to 32 (because employee id is beforehand). then generate a number between 1 and 31 for the day and lookup field name with the field() function, field(randimnumber+1) will pick one of the field names of fields 2 to 32. And then put the value into that with REPLACE.

REPLACE (field(randomnumber+1)) WITH 'W'

for example.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Janak, as Olaf has pointed out, what you are now describing is a terrible data structure. It will be inefficient and difficult to query. However, if that is what you want, you can do something like this:

First, name all the fields (except Employee ID) something like F1, F2, F3, and so on, up to F30. Then do this:

SELECT Prizes
SCAN
  lcField = "F" + (INT(RAND() * 30))
  REPLACE &lcField WITH "W"
ENDSCAN

That will place a W in a random field for each employee. It won't deal with any constraints, such as the maximum number of prizes one employee can win, or whether prizes can be awarded on consecutive dates.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

You said you are using old Foxpro code. I don't know how far back you are going, but if your version doesn't support SCAN / ENDSCAN, then do this instead:

SELECT Prizes
GO TOP
DO WHILE NOT EOF()
  lcField = "F" + (INT(RAND() * 30))
  REPLACE &lcField WITH "W"
  SKIP
ENDSCAN

Essentially it's the same thing, but just with a different way of looping through the table.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Just to illustrate you don't need 30 fields when you want to sparsely populate 30 columns:

Create Cursor crsLottery (winnercolumn int)
Insert into crsLottery values (Int(Rand()*5+1))
Insert into crsLottery values (Int(Rand()*5+1))
Insert into crsLottery values (Int(Rand()*5+1))
Insert into crsLottery values (Int(Rand()*5+1))
Insert into crsLottery values (Int(Rand()*5+1))
Insert into crsLottery values (Int(Rand()*5+1))

Browse fields ;
w1=Iif(winnercolumn=1,'W',' '),;
w2=Iif(winnercolumn=2,'W',' '),;
w3=Iif(winnercolumn=3,'W',' '),;
w4=Iif(winnercolumn=4,'W',' '),;
w5=Iif(winnercolumn=5,'W',' ')

You can concentrate on the essential data, just the column number, the date or whatever is necessary to know what column needs to display something.

Sparse data like yours is actually a reason for specific sparse column data structures in NoSQL databases.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[janak vyas]

Thank you Olaf and Mike for your valuable insights!
Because of your codes I got a start

CLOSE ALL

SELECT A

        USE MTHDES
        GO MTO
        FL= 'S' + MTH_NO+SUBSTR(COCD,1,3)+ TDYEAR+ '.DBF'
        USE  '&FL'

SELECT B

        USE PRIZES

 
SCAN

REPLACE b->employe_id WITH a->emp_no

endscan


scan



FOR mCounter = 1 TO 5 STEP 1

 nFIELD=Int(Rand() * 33 + 3)  &&... Random number between 1 and 31

REPLACE (field(nFIELD+1)) WITH 'A'    

*EXIT
*LOOP
ENDFOR 

ENDSCAN 
CLOSE ALL


USE PRIZES

BROWSE FONT 'ARIAL BOLD',11

This is where I am right now

It randomly allots 'A' to only 5 fields. Basically what I wanted to get started with.

What weird is happening is that I sometimes get an syntax error ( what's weird is it only comes a few times and when it comes it messes with the table allotting 6 or 7 'A') error line being :

 nFIELD=Int(Rand() * 33 + 3)  &&... Random number between 1 and 31

I cannot see what is the syntax error is here?
can someone please point it out to me?

 

[Olaf Doschke]

RAND itself is between 0 and 1, guaranteed not exactly 1, so the number you generate are from 0 to 32, then you add 3 and generate numbers between 3 inclusive up to 36 exclusive, so effectively 3 to 35.

If you want numbers 1 to 31 you have to use INT(RAND()*31+1), if you then need an offset of fields before the first one you want to allot randomly, then add that offset again, but don't multiply RAND()*33 if you want a range of 31 numbers.

If you don't want surprises because of leaving valid numbers just ensure the range is correct by making a lot more experiments:

* inint min with out of bound numbers:
minnum = 9999
maxnum = -9999

for experiment = 1 to 10000
   number = Int(Rand() * 33 + 3)  && put in your guess of the correct formula
   if minnum > number
      minnum = number
   endif
   if maxnum < number 
      maxnum = number
   endif
endfor 

? "numbers generate d are from ", minnum," up to ", maxnum

I can tell you your factor and offset bring you from 3 to 35. As said, you may need a range starting at 3 or higher, depending on what number the first field has, you want to allot values to. Your definition of the table does not have the first field of your range of randomizable fields as field number 1, that has to be adjusted. But no matter what that offset is, the factor of RAND(U) will always be the count of different numbers you want to generate. multiply by 33 and you get 33 different numbers. In theory even 10000 experiments won't guarantee you to find the max and min, but given a small enough range and how random numbers of RAND() are distributed this should give you a hint, if it's hard to imagine the extreme values of a formula to you.

The final truth about tests is reveled here: They can reveal a flaw, they can't guarantee clean or working code. So better learn the effective outcome of your formula: INT(RAND()*N)+1 will go from 1 to N, when you need N different numbers starting from 5 instead of 1 then you want the range 5 to N+4 and that will still mean multiply by N, only add 5 instead of 1, only the offset changes.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Janak, regarding your syntax error. Are you sure this is a syntax error? What is the actual message that you see? The reason I ask is that you would not normally expect a syntax error to be intermittent. If the syntax is wrong, the statement can never be executed. (An exception to that would be if the statement contains a macro, but that's not the case here.)

Also, are you sure it is that statement that is causing the error? Off-hnad, I can't see anything wrong with its syntax.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike,

there is the possibility of an intermittant syntax error in a line using (field) as name expression, when field for an n outside the range of actually field count doesn't error but results in an empty string or NULL.

Then actually the syntax of the effective code line is having NULL or a missing name, only then, and causes an intermittant syntax error.

The essence is you need to use the right random number formula to remain in the right range of fields.

Just checking an example:

Create Cursor onefield(f1 i)
Append Blank
Replace (Field(1)) with 42
? f1
Replace (Field(2)) with 42

Yes, using field(2) in a single field workaea leads to an intermittant syntax error and no compilation error. VFP is extremely forgiving about macro substitution and name expressions and does not even do the simplest checks at compiletime. In this case the compiler can't see and know that the active workarea will have only one field at runtime. Indeed you could see this cursor just generated 4 lines above the replace of (field(2)) means this will lead to a syntax error at runtime, but the compiler does not use AI intelligence to deduct that.

Using macro substitution makes it even less predictable to the compiler.

Anyway, there is your intermittent syntax error, this exists and is a possible runtime error, not just a compile time error, due to both macro substitution and name expressions. They can go wrong at runtime.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Olaf, I can't reproduce the error you described. I created a cursor with three fields, added a record, and then tested the contents of FIELD(1), FIELD(12), FIELD(0), FIELD(2.5) and FIELD(-1). I couldn't see a syntax error in any of the cases. I then repeated the test with a single-field cursor - as in the code you posted. Again, no syntax error.

I did get an error when passing NULL to FIELD(). But that was an invalid argument type rather than a syntax error.

Still, I take your point that the error was intermittent. I didn't think that was possible in the case of a syntax error, as the statement would have been rejected at compile time. But I'm willing to admit I might be wrong.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[janak vyas]

CLOSE ALL
SELECT A

        USE MTHDES
        GO MTO
        FL= 'S' + MTH_NO+SUBSTR(COCD,1,3)+ TDYEAR+ '.DBF'
        USE  '&FL'

 SELECT B

        USE PRIZES

 
SCAN
 REPLACE b->employe_id WITH a->emp_no
endscan

*///////////////////////////////////////////////////////////////

scan

FOR mCounter=  1 TO 4

nFIELD=Int(Rand() * 34+4) 

lcField = nField


minnum = 4
maxnum = 34


*//////////////////////////////////////////////////RANDOM CONTROLS////////////////////////////////////////////

if lcfield>=maxnum 
      lcfield=maxnum 
endif


*////////////////////////////////////////////////RANDOM CONTROL OVER////////////////////////////////////////



WAIT WINDOW lcField

REPLACE (field(lcfield)) WITH 'A'  

ENDFOR 

ENDSCAN 
CLOSE ALL

USE PRIZES

BROWSE FONT 'ARIAL BOLD',11

REPLACE ALL D4 WITH ' ',  D5 WITH ' ',   D6 WITH ' ', D7 WITH ' ', D8 WITH ' ', D9 WITH ' ', D10 WITH ' ', D11 WITH ' ', D12 WITH ' ', D13 WITH ' ', D14 WITH ' ', D15 WITH ' ', D16 WITH ' ', D17 WITH ' ', D18 WITH ' ', D19 WITH ' ', D20 WITH ' ', D21 WITH ' ', D22 WITH ' ', D23 WITH ' ', D24 WITH ' ', D25 WITH ' ', D26 WITH ' ', D27 WITH ' ', D28 WITH ' ', D29 WITH ' ', D30 WITH ' ', D31 WITH ' ' , D32 WITH ' ' , D33 WITH ' ' , D34 WITH ' '

Having read Olaf answer and changing the offset I now got rid of the syntax error!
What I think was the reason ( with my small lay-man knowledge ) of the syntax error was the lcField going out of bound, having no structure of that number was letting the syntax error.

What I am failing now is that although the FOR loop runs 4 times whenever the lcField is duplicate (i.e. there are two same random numbers ) it only allots
three A's.
Is there any way to control this type of behavior ?
I read something about SYS(2015) function is this of any use here ?

 

[Mike Lewis]

I read something about SYS(2015) function is this of any use here ?

I doubt it. SYS(2015) returns a name that is guaranteed to be unique within your session. This has a number of uses, but it has nothing to do with generating random numbers.

I don't know what you read or where you read it. I'd suggest you refer to the official VFP help file, which contains the definitive information.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[janak vyas]

So how do I go around the duplicate numbers?
If in the iterations duplicate numbers are there there would only be 3 results while The iteration runs for 4 times.

I needed 4 results every time whether there be a duplicate or not.

 

[janak vyas]

the rand() should not generate same numbers, it should generate every time a unique number between range 4 to 34 ! how to work this? please help