Grid Custom Control with sql_lookup

[aburningflame]

I'm having an issue which I think is a bug, or maybe im missing a line of code....anyways:

I have a very common situation: a foreign key in a grid which I want to display a useful value for: Instead of an id, id like to show some other datapoint.

Im building a timesheet/billing program and I have a rate table which holds employee rates. This table has a rateID as a primary key.

I also have a payment table which has a payment record and has a field called rateID which references the record in the rate table.
It seems that using the sql lookup, every row is using the same value..selwyns commenting says 'data parameter contains the value for each row'

I have 2 records, one has rateID: 1, the other has rateID: 7

Here is my custom code

Dim code as C
code=data.ratID

Dim rate as c
rate=sql_lookup("::name::mysql_billing", "rate", "ratID="+code,"ratRate");

'Write Xbasic code here that returns the HTML to be displayed by this control
ratID_render = <<%html%
<label>Code was: <%a5 ?code %></label>
<label><%a5 ?rate %></label>
%html%

When i look at the grid both records say Code was: 7.
I KNOW this is incorrect, why isn't it using the correct value for the row?

Any help or advice would be appreciated

[Sparticuz]

Re: Grid Custom Control with sql_lookup

did you ever get this working? i would like to know how to do this as well

[workaholic06]

Re: Grid Custom Control with sql_lookup

Hi Sparticuz,

We never got a response or workaround for this, and we haven't got back to working on this.

If you figure out a solution, we'd appreciate it if you could let us know what you did.

Thanks,

Robert

[mmaisterrena]

Re: Grid Custom Control with sql_lookup

sql_lookup works fine for me
heve you tried

rate=sql_lookup("::name::mysql_billing", "rate", "ratID="+[COLOR="Red"]quote[/COLOR](code),"ratRate")

(No need for that closing ';' on xbasic code)

[wmz]

Re: Grid Custom Control with sql_lookup

Hi

If I am not totally mistaken, the problem is that you try to output the value of the field using <%a5> tags.

While a custom control HTML is evaluated in each row, <%a5 ...%> tags are not, i.e. they are evaluated by the server once when the page is (re-)loaded. So, I assume what happens is, that it will evaluate all code values but take only the last one to send to the server evaluating the <%a5 tags.

You have to insert the value of the variable in the the custom control's HTML in a different way, for example

dim code as ...
code=...
dim rate as ...
rate=...
ratID_render = <<%html%
<label>Code was: [B][COLOR="Blue"]{code}[/COLOR][/B]</label>
<label>[COLOR="blue"][B]{rate}[/B][/COLOR]</label>
%html%
ratID_render = [B][COLOR="blue"]evaluate_string(ratID_render)[/COLOR][/B]

See evaluate_string() in the online help.

You can also easily verify this with the following examples:

This is using the <%a5> tag and it will fail to print ALL row numbers. Instead, only the last row number is shown for each custom control:

dim code as N

code=args.RowNumber
ratID_render = <<%html%
<%a5 ?code %></label>
%html%

This example uses evaluate_string() and evaluates the correct row number for each row:

dim code as N

code=args.RowNumber
ratID_render = <<%html%
{code}
%html%
ratID_render=evaluate_string(ratID_render)

cheers

Wolfgang

[Sparticuz]

Re: Grid Custom Control with sql_lookup

For some reason I'm getting an error.

This works in the interactive window:

?sql_lookup("::name::jkapp","Telemarketing.TelemarketingProspects", "ProspectID=1", "ProspectName")

This produces an error message:

dim prospect as c
prospect = sql_lookup("::name::jkapp","Telemarketing.TelemarketingProspects", "ProspectID="Data.Prospect_fk, "ProspectName")
Prospect_fk_render = <<%html%
<label>{prospect}</label>
%html%
Prospect_fk_render = evaluate_string(Prospect_fk_render)

ERROR:
Error in custom control xbasic: Variable type mismatch: attempt to pass data of type 'N' to argument 'filter ' is of type 'C'.

I've also tried convert_type(Data.Prospect_fk, "C")

[Jim Coltz]

Re: Grid Custom Control with sql_lookup

Try:

prospect = sql_lookup("::name::jkapp","Telemarketing.TelemarketingProspects", "ProspectID=" [COLOR="Red"]+quote([/COLOR]Data.Prospect_fk[COLOR="Red"])[/COLOR], "ProspectName")

[Sparticuz]

Re: Grid Custom Control with sql_lookup

Error in custom control xbasic: quote(Data.Prospect_fk): Argument is incorrect data type

btw, Prospect_fk is an int

[Jim Coltz]

Re: Grid Custom Control with sql_lookup

btw, Prospect_fk is an int

OK then, we need to use s_quote() for and integer. From the interactive window:

dim n as n

n =1

?"ProspectID=" +quote(n)
ERROR: Argument is incorrect data type

?"ProspectID=" +s_quote(n)
= "ProspectID=1"

[Sparticuz]

Re: Grid Custom Control with sql_lookup

OK then, we need to use s_quote() for and integer.

That did it! Awesome.

[workaholic06]

Re: Grid Custom Control with sql_lookup

Thank you all for the above notes - i am able to get this working on a grid.

However, I'm now trying to use Custom fields on the Form only grid.

I've got the following code for the custom field (this does work for the Grid):

Dim strGiven as C 
Dim strSurname as C 
Dim intPerID as N 
 
intPerID = Data.perID 
 
strGiven = sql_lookup("::name::mysql_billing","person","perID="+s_quote(intPerID),"perGiven") 
strSurname = sql_lookup("::name::mysql_billing","person","perID="+s_quote(intPerID),"perSurname") 
 
Dim strPersonName as C 
strPersonName = trim(strGiven + " " + strSurname) 
 
perID_render = <<%html% 
<label>{strPersonName}{intPerID}</label> 
%html% 
perID_render = evaluate_string(perID_render)  

As i click on the NEXT icon for the next record in the form view, I'm getting the same value for the name and ID.

The other textbox fields are correctly changing to show the values from the database.

Is there anyway to get the form to read the correct perID when moving through the records using the form only grid?

Thanks,

Robert

Anyone have any suggestions?

[frankbicknell]

Re: Grid Custom Control with sql_lookup

Maybe you need to refresh the row after the record changes like {grid.object}.RefreshRow(1) in the Javascript system events OnRowRender?

[christappan]

Re: Grid Custom Control with sql_lookup

I'm needing something very similar--when I make a change in the Detail View and submit it, the Grid row reflects the change but the Custom Control doesn't get refreshed. Did you ever figure this out and/or does anyone know how to call the custom control to refresh itself without having to refresh the entire grid?

Thanks...