How to Execute a UDF using Grid Data and Display it's Product within the Grid?

[CJMcGrath]

In my project I have defined a function called Profile_Into_Line(Member_Id) which creates a punctuated user friendly sentence by selecting data from the member's table fields.
I want to display this sentence within the member's profile grid. (The function is compiled into the project's AEX file). The approach I've tried is:
1. I've created a calculated field in the grid called Intro_line which initially equals "This is a calculated field"
2. I've then tried to use the OnGridExecute function to assign the product of Profile_Into_Line(Member_Id) to the Intro_Line field.

The problem is I don't understand the syntax for communication between xBasic and grids. I've "looked around" inside the project's Xbasic produced by Alpha (as suggested in tutorials that stop short of connecting the dots) but it remains a frustrating mystery to me.

To get the Profile_Intro_Line function to execute on the Member_ID from the grid I've tried:
Profile_Intro_Line(e.tmpl.field_info[1].Member_id)
Profile_Intro_Line(e.tmpl.Member_id)
Profile_Intro_Line(e.Member_id)
Profile_Intro_Line(e.session.Member_id)
Profile_Intro_Line(e.rv.session.Member_id)
but none of these syntax attempts successfully retrieve the value of Member_id.

Similarly, I've tried to reassign the value of the field "Intro_Line" to "Hello" (in the absense of a working Profile_Into_Line() function) but no joy with the syntax there either. I've tried:
e.tmpl.field_info[20].Intro_Line = "Hello"
e.tmpl.Intro_Line = "Hello"
e.Intro_Line = "Hello"
e.session.Intro_Line = "Hello"
e.rv.session.Intro_Line = "Hello"

Any suggestions on what I should be doing? In the absence of any proper educational documentation on how to apply Xbasic within grids, are there any simple working examples I could look at?

[Jay Talbott]

Re: How to Execute a UDF using Grid Data and Display it's Product within the Grid?

The missing link is to get Xbasic and javascript to work together.
Look at the videos in section #56 on this page:
http://news.alphasoftware.com/V10Preview/videos.htm

If you haven't seen these videos, you should watch them. They will move you along the learning curve rapidly.
Jay

[Pat Bremkamp]

Re: How to Execute a UDF using Grid Data and Display it's Product within the Grid?

One of the problems with teaching and learning Alpha is that there are lots of different ways to accomplish anything. So, in trying to answer your question, I have to start out with "it depends". That's why solutions offered on this board usually start out asking a bunch of questions.

So, let me guess some answers to some questions and present one possible solution.
For some solutions, it will depend on the backend database...dbf or SQL. For this particular solution, it doesn't matter.
It matters if you want to save the value you enter in the grid or only display it. For this solution, the result is not saved.
It matters if you want to display in a grid or a detail view. This solution is for a grid.
It matters what your purpose is for what you are trying to achieve. In the case I'm showing, I have a long list of projects and I select one. This is to show which one is selected.
The picture shows the result.

To achieve this, I put this in the OnBeforeExistingRowsRender event. It uses some Xbasic, some html tags and some CSS styles. (Yes, there is a lot to learn!)

function OnBeforeExistingRowsRender as v (e as p)
if e.rv.session.__protected__jobno = 0
	vJobno =  "NONE"
else
	vJobno = alltrim(str(e.rv.session.__protected__jobno,8,0))
end if
e.html="<tr><td colspan=\"11\">Selected job number is <span style=\"font-size:11pt;font-weight:bold;\">"+vJobno+"_"+e.rv.SESSION.__PROTECTED__OWNER+"_"+e.rv.SESSION.__PROTECTED__CLIENT+"</span>&nbsp;<br /><hr></td></tr>"
end function

[Pat Bremkamp]

Re: How to Execute a UDF using Grid Data and Display it's Product within the Grid?

Here's another possibility.
This is in a detail section of a grid
It uses an SQL back end database
I don't save this value in the grid

In this case, I have a function defined in the SQL named Friendly_name that looks like (the p_ variables are the inputs):

 BEGIN
	DECLARE v_friendlyname VARCHAR(50);
	DECLARE v_nick VARCHAR(20);
	
	IF p_nickname IS NOT NULL AND p_nickname<>'' THEN
		SET v_nick = p_nickname;
	ELSEIF p_firstname IS NOT NULL AND p_firstname<>'' THEN
		SET v_nick = p_firstname;
	ELSEIF p_middlename IS NOT NULL AND p_middlename<>'' THEN
		SET v_nick = p_middlename;
	ELSEIF p_salutation IS NOT NULL AND p_salutation<>'' THEN
		SET v_nick = p_salutation;
	ELSE
		SET v_nick = '';
	END IF;
	IF p_lastname IS NOT NULL AND p_lastname<>'' THEN
		SET v_friendlyname = CONCAT(v_nick,' ',p_lastname);
	ELSE
		SET v_friendlyname = '';
	END IF;
	RETURN v_friendlyname;
END

So, in the SQL statement I have added

FRIENDLY_NAME(LAST_NAME, FIRST_NAME, NICK_NAME, MIDDLE_NAME, SALUTATION)  AS DISPLAY_NAME

Then, in the Label Properties for the field, put this CSS:

font-size: 10pt; color: #fafafa; font-weight: bold; background-color: #000080;

[Pat Bremkamp]

Re: How to Execute a UDF using Grid Data and Display it's Product within the Grid?

Now, let's say you do want to save the value.

In this case, I have an SQL stored procedure that determines the next project number. This is in an Add only grid. In the OnInitialValueCalculate event, put

function onInitialValueCalculate as v (e as p)
dim OK as L
dim vErrorMsg as c
dim cnx as SQL::Connection
dim crs as SQL::ResultSet
dim vSelect as c
vSelect = "call get_next_project_number(@nextnum)"
OK = cnx.open("::name::nfn")
OK = cnx.execute(vSelect)
crs=cnx.ResultSet
e.newValues.project_number=crs.data(1)
cnx.close()
end function

So, these three examples should help you get started.

[CJMcGrath]

Re: How to Execute a UDF using Grid Data and Display it's Product within the Grid?

Thanks for those examples Pat. They're a bit advanced for my initial "baby steps" right now, but I hope to be referring back to them in detail very soon.

Jay, those tutorial videos are very helpful, but I've struck a snag trying to implement my first example test taken from videos 3 & 4 (my first "baby step"):
I've used the Freeform layout Row Property for my calculated field Intro_Line to position an element within the grid:
Test ELE2 is: <span id="ELE2"></span><br>

I able to use a button to execute an Xbasic function declaration...

function testIt as c (e as p)
e._setElement.ELE2.value = "ELE2 has been set by button"
end function

...which works fine. But when I try to achieve this automatically using Server-side Events...

function OnGridInitialize as v (e as p)
e._setElement.ELE2.value = "ELE2 has been set OnGridInitialize"
end function

...or...
function OnGridExecute as v (e as p)
e._setElement.ELE2.value = "ELE2 has been set OnGridExecute"
end function

...nothing happens. Where am I going wrong?
(My grid is a basic form to just display information with no Search or Detail View)

[Jay Talbott]

Re: How to Execute a UDF using Grid Data and Display it's Product within the Grid?

I believe that the problem is that you are mixing up server side events with client side events.
Javascript works with client side events and Xbasic functions work with server side events.

I do not believe that Xbasic from Events (which is what you describe above) can be used directly with spans.

However, since an Xbasic callback function can return a javascript action, the same script that goes on your button should work on the Xbasic script.
Make sure that your function returns the javascript like this:
testIt = "document.getElementById('ELE2').innerHTML = 'ELE2 has been set by Xbasic';"

You probably don't need the quotes around ELE2 in the span id, either.

Hope this helps.
Jay

[CJMcGrath]

Re: How to Execute a UDF using Grid Data and Display it's Product within the Grid?

Hi Jay, I copied that syntax verbatim from the video tutorial.
Test ELE2 is: <span id="ELE2"></span><br> placed in any free form area (above, below or within the grid) works fine when a button is used to execute the Xbasic function testIt described above (which is what the tutorial demonstrated). What I'm trying to do now is find out where to place the function contents so that it is executed automatically when the grid loads (without having to press a button, which wasn't demonstrated in the tutorial).

Where would you place e._setElement.ELE2.value = "ELE2 has been set." so that "ELE2 has been set." is already displayed when the grid appears?

[Jay Talbott]

Re: How to Execute a UDF using Grid Data and Display it's Product within the Grid?

In the event called OnInitialValueCalculate, put in something like:
e.newValues.cfname = "Alpha"

which would stuff Alpha in the first name field.
You will have to modify this to your field name. You can hard code the value to go into the field, or use a variable derived from something else.
Hope this helps.
Jay

[CJMcGrath]

Re: How to Execute a UDF using Grid Data and Display it's Product within the Grid?

Hi Jay, nope, no help because OnInitialValueCalculate is not available for a Read only grid. (Remember my grid displays profile information only as a single record form with no Search and no Detail View).
My Server-side Events are limited to OnGridInitialize and OnGridExecute and few others that don't look applicable because there is no rows, search etc.

However, tonight I discovered (by chance) that you can place Xbasic directly into Free form areas anywhere on your grid (did I hear that mentioned way back on a video tutorial, but didn't get it?). The remarkably simple answer (but no doubt not the only one) to my original question above is:
Step 1: was correct, ie: create and insert the calculated field Intro_Line as a place holder on the grid in the required location for the sentence produced by the UDF Profile_Intro_Line()
Step 2: In the Row Properties Freeform template of the Intro_Line field, type in the following Xbasic:

<%A5
?Profile_Intro_Line({data.Member_id})
%>

and presto! my first UDF (36 lines of code no less) came to life inside my grid! (My discovery of {data.Member_id} was a stab in the dark by clicking the Data radio button in the Freeform template editor)

On reading the Ajax Callback Function Prototype I thought I should have been able to use e._currentRowDataNew.Member_id in the above code, but it didn't work. Why?
Any references or examples on e.things and how and where you can use them? (The simple solution I discovered hasn't advanced me at all with my understanding of grids and their e.things)

[Jay Talbott]

Re: How to Execute a UDF using Grid Data and Display it's Product within the Grid?

you can place Xbasic directly into Free form areas anywhere on your grid

Now that you mention it, it makes perfect sense, because you can do the same thing on an a5w page.
Thanks for reminding all of us. This is another great tool for our toolbox.
Jay