function use with table

[John]

I have a field that needs to list an approver for the purchase order.
The dba provided a scalar valued function that returns the approvers
ID. One of the connected tables to the front end (back end is sql
server 2005) is the Employees table. The function can not be modified
to return the name as it is used else where. He is also reluctant to
do another (I was lucky to get this one). I have never worked with
functions and tables interacting with each other like I might need
here. First thing I thought of was some type of a select statement.
So, if any one out there can help out on this, please do.
Thanks for reviewing.
John

 

[John W. Vinson]

I have a field that needs to list an approver for the purchase order.
The dba provided a scalar valued function that returns the approvers
ID. One of the connected tables to the front end (back end is sql
server 2005) is the Employees table. The function can not be modified
to return the name as it is used else where. He is also reluctant to
do another (I was lucky to get this one). I have never worked with
functions and tables interacting with each other like I might need
here. First thing I thought of was some type of a select statement.
So, if any one out there can help out on this, please do.
Thanks for reviewing.
John

You cannot display the result of a function in a Table.

You can, however, base a Query on the table, and put a call to the function in
a calculated field in the Query. To do so in the query grid put a name for the
new field and call the function:

Approver: MyFunctionName(parameter, parameter, parameter)

Since you don't give any information about how the function works, what
arguments it takes, what kind of value it returns, or how it should be called
it's hard to be more specific.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John]

You cannot display the result of a function in a Table.

You can, however, base a Query on the table, and put a call to the function in
a calculated field in the Query. To do so in the query grid put a name for the
new field and call the function:

Approver: MyFunctionName(parameter, parameter, parameter)

Since you don't give any information about how the function works, what
arguments it takes, what kind of value it returns, or how it should be called
it's hard to be more specific.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi. Thanks for the reply. I am not trying to display the function's
result in a table. The scalar function returns the ID of the
approver. The Employees table has that ID in it. So I need to be
able to take the return of the scalar-valued function and match it
against the Employee table to get the name of the approver. The
function only needs to have the purchase order number passed in, such
as FUNCTION(ponumber). The actual function is rather lengthy and I
could not begin to explain how it works. As I mentioned, I thought
about a SELECT statement of some kind using the function's return as
the WHERE part. Something like;
SELECT Fullname FROM Employees WHERE id = Function(ponumber). The
approver's fullname would then be displayed in a textbox on the
form.
You might be wondering why passing the ponumber instead of the user
number, well, that story keeps getting more and more over the top
after each weekly meeting. I made the mistake of making light of it
all at a meeting saying something about if the purchase order needs
approval if there is a full moon on the second wednesday during a
month with an 'R' in it, and so on. Because it is getting that
confusing and they didn't take kindly to the ribbing. I've been with
4 other much larger organizations that had authority levels and none
of them were as convaluted as this is getting. Sorry about the
venting. I chuckle about it now.
Anyway, thanks again for the help on this.
John

 

[Mike Painter]

Hi. Thanks for the reply. I am not trying to display the function's
result in a table. The scalar function returns the ID of the
approver. The Employees table has that ID in it. So I need to be
able to take the return of the scalar-valued function and match it
against the Employee table to get the name of the approver. The
function only needs to have the purchase order number passed in, such
as FUNCTION(ponumber). The actual function is rather lengthy and I
could not begin to explain how it works. As I mentioned, I thought
about a SELECT statement of some kind using the function's return as
the WHERE part. Something like;
SELECT Fullname FROM Employees WHERE id = Function(ponumber). The
approver's fullname would then be displayed in a textbox on the
form.
You might be wondering why passing the ponumber instead of the user
number, well, that story keeps getting more and more over the top
after each weekly meeting. I made the mistake of making light of it
all at a meeting saying something about if the purchase order needs
approval if there is a full moon on the second wednesday during a
month with an 'R' in it, and so on. Because it is getting that
confusing and they didn't take kindly to the ribbing. I've been with
4 other much larger organizations that had authority levels and none
of them were as convaluted as this is getting. Sorry about the
venting. I chuckle about it now.
Anyway, thanks again for the help on this.
John

I hope you're getting all these requests in writing.
It soulds like you are working with a program that is not well written and
probably not well designed.
There should be NO need for a function to return the Employee ID if the
tables are propery designed.
Returning the name of teh employee for any given PO should be a trivial
matter of relating the PO header record to the employee table.

 

[John]

I hope you're getting all these requests in writing.
It soulds like you are working with a program that is not well written and
probably not well designed.
There should be NO need for a function to return the Employee ID if the
tables are propery designed.
Returning the name of teh employee for any given PO should be a trivial
matter of relating the PO header record to the employee table.- Hide quoted text -

- Show quoted text -

The approver may not be the user's immediate supervisor, depending on
authority levels and type of po it is. There are 5 new tables created
for this new approval process (groups, po authority levels, etc) which
the function takes into consideration for when it returns back the id
of the appropriate approver form one of the new tables. Which is why
I will need to get the fullname of the approver from the employees
table from the id the function returns.

 

[John W. Vinson]

I thought
about a SELECT statement of some kind using the function's return as
the WHERE part. Something like;
SELECT Fullname FROM Employees WHERE id = Function(ponumber). The
approver's fullname would then be displayed in a textbox on the
form.

You've got a couple of choices; perhaps the simplest would be just to use
DLookUp:

SELECT DLookUp("Fullname", "Employees", "[ID] = " & Function([PONumber])

Another would be to use the function as a clause in a JOIN clause:

SELECT <stuff from PO table>, Employees.Fullname
FROM [PO table]
INNER JOIN Employees
ON Employees.ID = Function([PONumber])

The query will probably not be updateable since it's joining on a nonindexed
function call rather than a field.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Mike Painter]

The approver may not be the user's immediate supervisor, depending on
authority levels and type of po it is. There are 5 new tables created
for this new approval process (groups, po authority levels, etc) which
the function takes into consideration for when it returns back the id
of the appropriate approver form one of the new tables. Which is why
I will need to get the fullname of the approver from the employees
table from the id the function returns.

"The scalar function returns the ID of the approver. "
If teh approver is indepentant of the employee then the approver's ID should
be stored with the header record.

Your five new tables shold relate in such a manner that everything should eb
accessible with one query based on the PO header record.