>>>>Yikes, I've asked this before; but how do I make one column in a grid, which is otherwise bound to a table show the value of a user defined function, eg grid.controlsource= myfunction(mytable.fieldname)
>>I can't seem to make it work...>>
>>
>>Even if you do get it to work, its going to be *REALLY* slow (that UDF is firing for every cell on every refresh). Might I suggest you reference an object property (vs the UDF) which is updated by your UDF. That way the UDF is called only when some (legitimate) trigger event calls it, as opposed to being called dozens, hundreds, maybe thousands of times even though the result doesn't change.
>
>
>>>Thom,
>>>Could you please elaborate more on your idea?...
>
>>>I didn't notice significant delay on the tables with 25000 thousand records,
>but when I try to use scrollbar in the grid, it seems not responding properly,
>may be it's related.
>>>Basically, I want to show "Edited","Bad","Duplicate", etc., instead of "E","B","D".
>
>
>Certainly.
>
>You may be familiar with the old Fox addage: "Never use a UDF in a SQL statement".
>
>If you had a 25,000 record table with a status field (values "E", "B", "D", etc) and ran a query
>
>SELECT MyTable.*, GetDesc(MyTable.APN, "APN") AS FullStatus ;
>FROM MyTable
>
>the GetDesc() function's going to fire 25K times.
>
>If instead you did something like:
>
>SELECT APN AS Status, SPACE(25) AS FullStatus ;
>FROM MyTable ;
>GROUP BY Status ;
>INTO TABLE ShowStatus
>
>UPDATE ShowStatus ;
>SET FullStatus = GetDesc(ShowStatus.Status, "APN")
>
>SELECT MyTable.*, ShowStatus.FullStatus ;
>FROM MyTable, ShowStatus ;
>WHERE MyTable.APN = ShowStatus.Status ;
>INTO CURSOR Disp2Grid
>
>Then GetDesc() will fire less than a half dozen times. Additionally, you can optimize the queries
>for better performance.
>
>My personal best was an app I did a couple of years ago where I was calling a legacy UDF in a SQL query. During a test run, I gave up after 20 minutes and soft booted the machine. I rewrote the query by making an intermediate cursor (like the ShowStatus cursor in the above example) and did a join between the main cursor and the intermediate cursor. The query was done in 1.2 seconds.
>
>OK. This is pretty well established SQL theory, but it applies equally to objects where you've got
>lots of instances... like the cells in a grid.
>
>In the case of a grid, there are a number of ways to prevent iterative calls to a UDF. I prefer to alter
>the values in the cursor/table driving the grid, or set a relationship between the grid's primary
>cursor and a child cursor (which is also displayed) with a SET RELATION.
>
>Regards,
>Thom C.


Thanks for the reply, Thom. I agree with that you're saying, but for me it would mean to create additional Status table and use Relations in a grid (it could be done in a view too). I'll think about it, but leave it the way it is for now...