Problem with list control with Grouping

[edsicard]

Hi,
I am trying to get a list view to group by the ‘Day Name’ of a date field. (Microsoft Access database)

The query uses Alpha’s DayName function in an expression and works correctly in the query wizard but fails when running actual application with a ODBC error ‘Two few parameters’.

The Portable SQL is: DayName(vxSchedule.Scheduled) AS Dname
The Native generated is: {fn DayName(vxSchedule.Scheduled)} AS Dname

Has anyone run into this problem or have a work around?

Thanks.


Portable SQL
------------
SELECT vxSchedule.id, DayName(vxSchedule.Scheduled) AS Dname, vxSchedule.ProposalID, vxSchedule.Scheduled, vxTask.Description AS TaskDescription, SalesCategories.Description AS Sales, vxSchedule.JobNo, vxSchedule.CName, vxSchedule.Address1, vxSchedule.City, vxSchedule.LeadInst, vxSchedule.Tech1, vxSchedule.Tech2
FROM (vxSchedule vxSchedule
LEFT OUTER JOIN vxTask vxTask
ON vxSchedule.Task = vxTask.id
INNER JOIN (ProposalHeader ProposalHeader
LEFT OUTER JOIN SalesCategories SalesCategories
ON ProposalHeader.SalesCategoryID = SalesCategories.id )
ON vxSchedule.ProposalID = ProposalHeader.ProposalID )
WHERE vxSchedule.Scheduled > {2016-06-01}
ORDER BY vxSchedule.Scheduled

Native SQL
----------
SELECT vxSchedule.id, {fn DayName(vxSchedule.Scheduled)} AS Dname, vxSchedule.ProposalID, vxSchedule.Scheduled, vxTask.Description AS TaskDescription, SalesCategories.Description AS Sales, vxSchedule.JobNo, vxSchedule.CName, vxSchedule.Address1, vxSchedule.City, vxSchedule.LeadInst, vxSchedule.Tech1, vxSchedule.Tech2 FROM ((vxSchedule vxSchedule LEFT OUTER JOIN vxTask vxTask ON ( vxSchedule.Task = vxTask.id )) INNER JOIN ProposalHeader ProposalHeader ON vxSchedule.ProposalID = ProposalHeader.ProposalID ) LEFT OUTER JOIN SalesCategories SalesCategories ON ( ProposalHeader.SalesCategoryID = SalesCategories.id ) WHERE vxSchedule.Scheduled > #2016-06-01# ORDER BY vxSchedule.Scheduled

[Davidk]

Re: Problem with list control with Grouping

You don't mention which version/build of Alpha you're using. Also, is your Group server-side or client-side?

I would try something simpler first. This is the SQL Statement for a List based on the Alpha-included Northwind Access database.

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShipName, ShipCity,  DayName(OrderDate)  AS DName 
FROM Orders

Using the current release of Alpha and Client-side Grouping... this works.

The error you're getting I know well from years of dealing with Access error messages. I always got this error somewhere in an Access Query when either Access didn't like something or the Query was set up wrong.

Is your SQL Statement pulling data from Queries? Or purely from Tables?

[edsicard]

Re: Problem with list control with Grouping

I am using latest version 4.2 against a table not a query.
Tried your case using NorthwindSQLserver and it works until I turn on group breaks in list's properties on the expression field DName, DayName(OrderDate). Also tried DayName([OrderDate]).
Tried Group breaks on the OrderDate field without DayName function works fine.

Thanks

[Davidk]

Re: Problem with list control with Grouping

Not sure what NorthwindSQLserver is. I've set up an AlphaDAO Connection string to the Northwind MSAccess database that ships with Alpha.

There are 2 methods for adding a Group Break:

1. is server-side, defined in the List Data Source tab. I believe this is where you are. I can re-produce the Access error here.
2. is Client-side, defined in the List Properties tab under Client-side Filter/Order, and Grouping.

Using a Client-side Group DayName(OrderDate) works.

You have to be careful and clear out any settings you have in the Server-side Group (e.g. the Break field name) because if you don't, the List is empty.

Your SQL statement could also use format(OrderDate,'dddd') AS DName when using Client-side Grouping... because you're using an Access connection.

[edsicard]

Re: Problem with list control with Grouping

Tried both methods, no success.
I will add a list for user to select the day of week and use that selection to filter second list.
I have to do a lot of this kind of stuff for workarounds in alpha.

Thanks David

Oh by the way, Thanks for the client side filtering example you did for another person on this board.
I modified it to do multiple keyword searches from one text box. (3 terms)

var lObj = {dialog.object}.getControl('list1');
{dialog.Object}.setControlDisplay('CONTAINER_1_2', false, 'v');


// Get keywords
var filter1 = {dialog.Object}.getValue('SEARCH_1');

// Split single textbox into keywords ( 3 fixed keywords )

var s = filter1.split(" ");

var reFilter1 = RegExp(s[0], 'i');
var reFilter2 = RegExp(s[1], 'i');
var reFilter3 = RegExp(s[2], 'i');

lObj.setFilter(function(data){
var f1 = data.De == null?"":data.De;
var f2 = data.De == null?"":data.De;
var f3 = data.De == null?"":data.De;
if(f1.match(reFilter1) && f2.match(reFilter2) && f3.match(reFilter3)) return true; // yes, show this row
else return false; // no, hide this row
});

[Davidk]

Re: Problem with list control with Grouping

Shouldn't have to work around this... I don't see an issue.

List_DayName.PNG

To run this component, just create an AlphaDAO Connection string named northwind which points to the MSAccess database installed with Alpha.

This is my connection string...

{A5API=Access,FileName='C:\Program Files (x86)\a5V12_3702\MDBFiles\Northwind.mdb'}

This runs under 3670_4684 and 3727.

[edsicard]

Re: Problem with list control with Grouping

After restarting Alpha it worked.