Change SQL select statement in a list control

[canc]

Hi everyone

I need to dynamically change the initial SQL select of a list control , dependent on which grid its called from

My “main” filter is

SELECT Booking_id, clerk_added, Enquiry_expiry_date, Booking_date, Client_ID, Lead_surname, Adults, Children, Infants,Date_of_Travel, Status, Duration, Destination, Enquiry_cost FROM View_bookings WHERE Status = 'Enquiry'

I then need

SELECT Booking_id, clerk_added, Enquiry_expiry_date, Booking_date, Client_ID, Lead_surname, Adults, Children, Infants,Date_of_Travel, Status, Duration, Destination, Enquiry_cost FROM View_bookings WHERE Status = 'Enquiry' and client_id = :client_id

And

SELECT Booking_id, clerk_added, Enquiry_expiry_date, Booking_date, Client_ID, Lead_surname, Adults, Children, Infants,Date_of_Travel, Status, Duration, Destination, Enquiry_cost FROM View_bookings WHERE Status = 'Enquiry# and clerk_added = :clerk_added

Is this possible ?

If I select arguments in the properties of the button calling the UX I dont see them appear unless I add them as primary keys .. however if I do that then the relationship with a child grid does not work

thanks on advance

John

[madtowng]

Hi John,
I do this often with view boxes but haven’t tried with lists.
My guess is that it can be done.

[WayneH]

Hello John,

I may be missing something critical (really possible), but I am not seeing why this wouldn’t work with arguments, since it appears your select statement is uniform in its columns. I have setup several arguments before like I think you are describing and set them based on what I want from the list. Step 1 is figuring out why your arguments aren’t showing when you call the UX. Then step 2 is ensuring that your select statement will blank out for one’s you don’t want. I use Navicat to play with that, then move it over.

If you must change the underlying sql statement, one path would be to use a view, then call different stored procedures or programmability to change the view, leaving your ux intact with the same view each time. I’ve never done that, but heard rumors and folk tales of people doing it :)

If you want to post up the ux being called, I am sure someone here can quickly look at it and see if there is a reason arguments aren’t showing.

Wayne

[canc]

Hi Wayne,

Yeah it is a puzzle at the moment I am filtering it client side but it shows all the records first then executes the client side filter

The UX is called from a Grid , on the grid there is the My Enquiries button , this is where I need to add argurments etc

I have attached both

Thanks and hopefully someone can illustrate the error

cheers

john

[gianfior85]

Typically there is a way around this without changing the entire SQL statement, but if you do in fact want to merely append some different filter to a list dynamically, you can use the list's "Server side beforeQuery event" property. The Xbasic function prototype they provide is helpful, but basically you'd write something like:

e.sql = e.sql + "your new WHERE condition"

. You don't have to append to you original query (represented by e.sql), you could change the entire thing if you want.

[canc]

Hi
That looks great will give it a go
thanks for the advice
cheers

john

[canc]

Hi sorry

I need to add the where clauses as session variables, so can I use arguments in the xbasic for the session variables, or do I need to add them in the where clause

if so do you know the syntax , not done that for ages

thanks

[WayneH]

Hi John,

I quickly took a look. Good stuff in alot of your code. Looks like you are pushing towards the list and away from the grid a bit and doing in phases. Good call.
The reason all the arguments were not showing is due to some being set to be populated by variables. Thus Alpha figures you dont need to change that on open.
When I changed another one to set at runtime, it shows up as well. I would encourage the use of state variables vs session variables, but that just my preference.
If you PM me your email address, I will send you a UX I have based on a sql northwinds that has a couple decent examples of pushing values around state and to arguments, as well as quickly seeing those values for debug.
On a side note, I would encourage the use of aliases on your UXs that are opened. DefaultAlias does indeed work and Alpha will then set it for you, but there are other circumstances down road as you keep pushing further down the rabbit hole that you may want to call them from the parent and such that having the habit of giving the alias is a better mode.
I just use standard naming conventions for ease. Such as EMBED_UX_DISPLAYENQUIRIES or maybe what it is and where being called. My two cents.

Args.png


Grid UX Button.png

[canc]

Hi Wayne sent over
let me know if you don’t receive it
thanks
john

[madtowng]

I know there's been a lot of time in between but today was busier than usual at the office.
I believe the code below accomplishes what you wanted.
You are free to use any xbasic if/then/else or select/case code to customize your sql query.
This example uses the NorthWinds customer table but can easily be adjusted for your needs.
Don't forget to change the sql connection.
I'm a bit jealous about the options available for the list control that I don't believe are available for the viewbox.
I have several debug(1)/debugger statements throughout the code to give you a chance to checkout the variables.
Feel free to holler if you have any questions.

Gregg

' OnDialogInitialize code
function onDialogInitialize as v (e as p)
dim odiCN as sql::Connection
dim odiARGS as sql::Arguments
dim itsOpen as L
dim itWorked as L
dim sqlCode as P
dim queriedData as C
dim js as C
dim returnJS as C

sqlCode.loadNWcusts = <<%txt%
select CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax
FROM northwind.Customers
<where statement>
%txt%
js = <<%txt%
debugger;
{dialog.object}.stateInfo['nwData'] = <stateData>;
initializeLC('listA','nwData')
debugger;
%txt%


itsOpen = odiCN.Open("::name::connection2")
queriedData = odiCN.ToJSON(strtran(sqlCode.loadNWcusts,"<where statement>",chr(0)))

returnJS = strtran(js,"<stateData>",queriedData)
'debug(1)    
e.javascript = returnJS
end function
​

// javascript functions code
function initializeLC(lc,stateVar){
/* Plan on adjusting the switch(lc) function to
match your needs for this project.
The code already here is for example only
*/
debugger;
var sourceData = {dialog.Object}.stateInfo[stateVar];
var lcPTR = {dialog.Object}.getControl(lc);
var msg;
switch(stateVar){
case 'nwData':
msg = 'nwData data has been loaded.'
break;
case 'asnsVB':
msg = 'ASNs have been loaded.'
break;
}
lcPTR.populate(sourceData,false,false);
//<listObject>.populate(data [,flagKeepValue [,animate [,flagFireEvents]]])
displayTransMessage(msg,'invisible',750,'no')
};

// The code below here IS NOT required to populate the list control
function displayTransMessage(message,control,duration,showclose){
var _getElement = function() {return {dialog.Object}.getPointer(control);};
var _ele = _getElement();
//var _html = '<div id="showmessage" style="line-height:25px; display:inline-block;"></div>';
var _html = '<div id="showmessage" style="line-height:25px; display:inline-block;">'+message+'</div>';
var _options = {} ;

if(showclose.toUpperCase() == 'YES'){
_options ={"close": {"show": true },"duration": duration, "animation": {"show": {"type": "none"},"hide": {"type": "none"} }};
} else {
_options ={"close": {"show": false },"duration": duration, "animation": {"show": {"type": "none"},"hide": {"type": "none"} }};
}

{dialog.object}.transientMessage(['dropdown',_ele],_html,_options);
}/* end of displayTransMessage function */