Yet another!!

I have a function which based on selection criteria pulls a value from a table into a field.

This works fine, however for one set of criteria, there can be 3 values which could be pulled back. Basically the function always pulls back the first one it comes across, however I want it to pull back the one with the least occurances, hence I want to perform a count....

Any ideas?

Cheers

Try this:

....
select tablefield:value, count(tablefield):cntr
from table
where ......
selecteos
return
endselect
.....

where value and cntr are declared in the function

ok, just before I give this a go....

select tssma931.instr:value, count(tssma931.instr):cntr
where tssma931.instr = 007454
selecteos
return
endselect

Will I need to perform this for the 3 different values?

If so what I am guessing is that I write 3 functions to count each individual value and then write a function to compare the results?

cheers

This returns only the last occurrence and the total number of occurrences if there are no values, returns 0 and 0.
You have to call this function only one time.

function retrievevalues(domain yourdomain instr,
ref domain yourdomain value,
ref long cntr)
{
select tssma931.instr:value, count(tssma931.instr):cntr
from tssma931
where tssma931.instr = instr
selecteos
return
endselect
value = 0
cntr = 0
}

Ciao

I have given this a try, but I do not fully understand...

Here is the code I have used...

on the field section....

field.tssma931.instr:
before.input:
select.instructor()
check.input:
if tssma931.instr = "007532" or tssma931.instr = "007473" or tssma931.instr = "014530" then
retrievevalues()
endif


and the function ....


function select.instructor()
{
select tssma938.empno
where tssma938.crscd = :tssma931.crscd
selectdo
tssma931.instr = tssma938.empno
endselect

}

function retrievevalues(domain tccom.empno instr, ref domain long value, ref long cntr)

{
select tssma931.instr:value, count(tssma931.instr):cntr
from tssma931
where tssma931.instr = instr
selecteos
return
endselect
value = 0
cntr = 0
}

I did not know what domain to use for "value" so I have used long. When I compile this, it is giving me errors....

I don't understand what you are trying to do with retrievevalues(). Your function call has no variables and the select statement appears to be trying to get an instructor value even though it is using one in the where clause.

Are you trying to return one of those three instructor values listed? If so, perhaps try:

function retrievevalues(ref domain tccom.empno value, ref long cnt)
{
select tssma931.instr:value, count(tssma931.instr):cnt
from tssma931
where tssma931.instr IN ("007532" , "007473" , "014530")
group by tssma931.instr
order by 2
as set with 1 rows
selecteos
return
endselect
value = ""
cnt = 0
}

Ed

what you have said makes sense.

essentially one of the 3 instructor values needs to be returned.

the function that I have written which picks the instructor based on the course code entered, has 3 which it can pick from. It always picks the first one that it comes across.

I want it to use the one which has the least entries and where all have the same amount of entries pick the first it comes across also, if 2 of the 3 instructors have the same amount of entries then pick the first one it comes across.

I will give what you have put down a try...

Cheers

Ed

I have given it a try mate and it is giving me the error that "2 arguments expected for function 'retrievevalues'

I do not know what it means by this?

The function 'retrievevalues' Ed suggested, has 2 arguments, to be precisely 'value' and 'cnt'.

So, whenever you call this function, it should be something like
retrievevalues(my_value, my_cnt)

Kammie

Apologies, but I do not understand, could you elaborate please...

Cheers

How does your function call to retrievevalues(..) look like? You need to pass two arguments as Kammie explained.

Ahhhh now I see what you mean ....

currently here is the field statement...

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

field.tssma931.instr:
before.input:
if isspace(tssma931.instr) then select.instructor() endif
| check.input:
if tssma931.instr = "007532" or tssma931.instr = "007473" or tssma931.instr = "014530" then
retrievevalues()
endif

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

Cheers

I have had a further thought on this, I think that the function actually returns the amount of times that the instructor value has occured?

What I actually need the function to return is the Instructor value that it should be using....

This is an idea that I knocked together, perhaps anyone can see what is wrong with this?

The function that is called on the field, is assign.ct.instructor().

|function assign.ct.instructor()
|{
| count.007532()
| count.007473()
| count.014530()

| if count.007532() > count.007473() and
| count.007532() > count.014530()
| then tssma931.instr = "007532"
| endif

| if count.007473() > count.007532() and
| count.007473() > count.014530()
| then tssma931.instr = "007473"
| endif

| if count.014530() > count.007532() and
| count.014530() > count.014530()
| then tssma931.instr = "014530"
| endif
|}
|function count.007532()
|{
| select tssma931.instr:value, count(tssma931.instr):cntr
| where tssma931.instr = "007532"
| selecteos
| return
| endselect
|}

|function count.007473()
|{
| select tssma931.instr:value, count(tssma931.instr):cntr
| where tssma931.instr = "007473"
| selecteos
| return
| endselect
|}

|function count.014530()
|{
| select tssma931.instr:value, count(tssma931.instr):cntr
| where tssma931.instr = "014530"
| selecteos
| return
| endselect
|}

Cheers

Further to my last post, I have attached the errors I received when I compiled the script with the above function....

Andy,

If you go back to the retrievevalues() function you had before, just call it like this: retrievevalues(instructor, inst.count) where instructor is the name of the variable you want to receive the value of the instructor with the lowest count, and inst.count is the variable you want to receive the actual lowest count. If you notice the definition of the function, the arguments are declared with ref. This means that they are declared by reference and will have their values "returned" from the function. This is one way to have more than one "return" value from a function.

I hope this makes sense. I think your last idea is too much work.

Ed

I may be going completely off track here, let me show the code, and I have attached the errors that I am getting....


***************declarations*****************

extern domain long cntr
extern domain tccom.empno value

**************fields************************


field.tssma931.instr:
before.input:
if isspace(tssma931.instr) then select.instructor() endif
check.input:
if tssma931.instr = "007532" or tssma931.instr = "007473" or tssma931.instr = "014530" then
| assign.ct.instructor()

retrievevalues(value, cntr.count)

endif

*************functions***********************

function retrievevalues(ref domain tccom.empno value, ref long cntr)
{
select tssma931.instr:value, count(tssma931.instr):cntr
from tssma931
where tssma931.instr IN ("007532" , "007473" , "014530")
group by tssma931.instr
order by 2
as set with 1 rows
selecteos
return value
endselect
value = ""
cntr = 0
}

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

Cheers

Try these changes:

In your retrievevalues(...) call, change the second argument to cntr.
In the select statement in your function, change your return line to be just return -- this function does not explicitly return a value (other than through the by ref arguments as I mentioned before).

Let me know if this helps.

Ed

Here is the function call, and the function itself.

Its all compiling with no errors now, however it is not working, the session is still picking up the first of the 3 instructor values that it comes across...

***********************************************
field.tssma931.instr:
before.input:
if isspace(tssma931.instr) then select.instructor() endif
check.input:

if tssma931.instr = "007532" or tssma931.instr = "007473" or tssma931.instr = "014530" then

retrievevalues(value, cntr.count)

endif
************************************************

function retrievevalues(ref domain tccom.empno value, ref long cntr)
{
select tssma931.instr:value, count(tssma931.instr):cntr
from tssma931
where tssma931.instr IN ("007532" , "007473" , "014530")
group by tssma931.instr
order by 2
as set with 1 rows
selecteos
return
endselect
value = ""
cntr = 0
}
***********************************************

Any ideas?

Many thanks:)

Ed

Could you explain what....

field.tssma931.instr:
before.input:
if isspace(tssma931.instr) then select.instructor() endif
check.input:

if tssma931.instr = "007532" or tssma931.instr = "007473" or tssma931.instr = "014530" then

retrievevalues(value, cntr.count)

endif

...the "retrievevalues(value, cntr.count) actually does?

Is the instructor value that is being picked actually being returned to the field?

I do not understand exactly what is going on here?

Cheers

The retrievevalues(value, cntr.count) function call launches the function and passes the two arguments (value and cntr.count), which initially are meaningless, to the function. The function then executes. Since both variables are called by reference, when the function exits via a return or reaching the ending '}', the value of the arguments specified in the function declaration (in this case value and cntr) are "returned" to their counterparts in the function call. Perhaps this example will be a little clearer:

my.function(a, b, c)
...
function my.function(ref long x, ref domain tcdsca y, long z)
{
...
}

When my.function is called, the variable x is populated with a's value, y is populated with b's value, and z is populated with c's value. When the function exits, since x and y were defined with the ref keyword, their values are passed back so that a gets x's value and b gets y's value. Since z is not defined as by reference, its value is not passed back to c (i.e., c remains unchanged).

So, in your code, the function determines the instructor with the lowest count and that count and passes them back (by reference) to the arguments used in the function call.

I hope that helps.

Ed

This makes sense, so when I call the function ...


check.input:

if tssma931.instr = "007532" or tssma931.instr = "007473" or tssma931.instr = "014530" then

retrievevalues(value, cntr.count)

endif

do I need to add something which says that the field = the value?

eg

tssma931.instr = retrievevalues(value)

Is that correct?

Cheers