How to calculate field based on results of an SQL query?

[WindForce]

In a classic Orders/OrderLines scenarion, i would like to calculate the price for a line item upon selection. When selecting the product id, i would like to run a SQL query in the price list table, get the correct value and put it in the OrderLines BasePrice field.
Can someone point me to the right direction where to run the select etc...?

Thanks
Jaime

[kingcarol]

Re: How to calculate field based on results of an SQL query?

I don't know if this will help but here is some sample Xbasic code I refer to when needing to do something similar to what you are describing.
I quite often will first execute a count query to determine if a record is found before trying to set the value in a field to the result, but the code below does not include the count query.

function functionName as c (e as p)

dim ParentFieldValue as c 
ParentFieldValue = e._currentRowDataNew.fieldname

dim cn as sql::connection
dim cs as c 
cs = whatever you do to connect

dim flag as l 
flag = cn.open(cs)
if flag = .f. then 
	dim msg as c 
	msg = "Could not connect to database. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	FunctionName = jscmd
	exit function 
end if 
	
sql = "select fieldName from tableName where fieldName = :whatCriteria"
dim args as sql::arguments
args.add("whatCriteria",ParentFieldValue)

flag = cn.Execute(sql,args)
if flag = .f. then 
	cn.Close() 
	dim msg as c 
	msg = "Could not execute select query. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	FunctionName = jscmd
	exit function 
end if 
	
dim rs as sql::resultset
rs = cn.ResultSet

dim result as n
result = rs.data(1)  'this works if only one record value will be returned by select query
e._set.calculatedFieldName.value = result

cn.close()
FunctionName = "alert('The function is done.');"   'or whatever
end function

[WindForce]

Re: How to calculate field based on results of an SQL query?

Wow this is heavy for a noob like me :-) I thought there was some way to point to the parent's field name somehow...Even if i do not go that road, there is plenty to learn from your post, thanks!!
Jaime

[Jay Talbott]

Re: How to calculate field based on results of an SQL query?

You should also check out sql_lookup()
Jay

[Davidk]

Re: How to calculate field based on results of an SQL query?

Not sure that you need to actually run a SQL Select yourself. When you select your ProductId, use the Property - Javascript - onChange Event.
Add a new Action and choose "Lookup and Fill-in Fields" and follow the prompts.

If you're just looking to get a price based on a product then this might do it for you.

[WindForce]

Re: How to calculate field based on results of an SQL query?

Hi
I need to do this basic SQL operation in 2 different issues in the same Grid, please help me as where to put the sql code and how...

Issue 1: field validation based on SQL query. I have a db of services, and a services child table with days of week when those services are available. After i select a service in my order grid, i select a date, and in the "validation rules" property, i just entered for now ctod(currentValue) > date(), but i would also like to run a SELECT on the services child table and see if the selected date's week day is available for this service ( my dream would be to bold available dates in the date/time picker, but didn't find any way to do so)

Issue 2: the final service rate is based on the selected service in the order part, and some more fields entered in the orderlines child grid. I think i should build a function that would check if all the needed parameters are in place, and if yes, run the SELECT and get the price for that orderline. I just don't know where to define that function, how can it run the SQL and where do i call it from the affected fields...

I hope this is clear enough and you guys are not turned down by the length of my post :-)

Thanks,
Jaime