Search by start and end date

Search by start and end date

  
Hi, Can anyone help me how to Search A record or list by start and end date?
Hello Sathish,

Implementing a search using dates should not be different from searches using other data types.

Could you please share with us what you have already tried, or a more specific difficulty that you're having?
How do i give the filter in my aggregate on Search action?

I gave as
 "%" + Session.PurchaseFrom + "%" >= Purchase.PurchaseDate

and

 Purchase.PurchaseDate <= "%" + Session.PurchaseTo + "%" 


But its showing error.

Rrefer the images
Refer this Image too
Hello again Sathish,

The issue here is that you are using the "%" character, which is an SQL wildcard, and that only makes sense in SQL queries involving columns of type text (you can read about the SQL wildcard "%" here, for example).

To compare dates, the syntax is actually much simpler:

Session.PurchaseFrom >= Purchase.PurchaseDate

Purchase.PurchaseDate <= Session.PurchaseTo

I hope this helps!
Paulo Ribeiro wrote:
Hello again Sathish,

The issue here is that you are using the "%" character, which is an SQL wildcard, and that only makes sense in SQL queries involving columns of type text (you can read about the SQL wildcard "%" here, for example).

To compare dates, the syntax is actually much simpler:

Session.PurchaseFrom >= Purchase.PurchaseDate

Purchase.PurchaseDate <= Session.PurchaseTo

I hope this helps!
 Yeah It helped but I got another problem.

My new question is "How to Search, Filter and Display the data in charts by start date and end date.

Refer the oml.

I'm trying to get the report of purchased products during a particular time period using From and to dates.

But I'm getting a error like Session variables can't be bound in SQL which I've used in Show action."
 
Hi Satish,

Charts are bound to lists, which are the result of queries. There shouldn't be any difference between your previous question and this one. I don't have time to take a look at the OML, but when do you get the error? And what is the exact text of the error? Is the error triggered by the Platform, or is it a JavaScript error in the browser?
This is the Error I'm getting.
Hello,

The easiest way to do what you're after is using an Aggregate with a filter:



The main steps to do this are:
  • Create the Aggregate with the filters in the Preparation of the screen;
  • On the Search button, refresh the Aggregate so that it fetches new information depending on the filter;
  • Finally, Ajax refresh the list that displays the results of the Aggregate.
I've uploaded a new version of the OML file so that you can have a look.
Hi Satish,

The errors you were getting, "XXXX" could not be bound, means that you've used something in your query that the database doesn't know what they are. In this case, you are using "Session.PurchaseFrom" and "Session.PurchaseTo" directly in the SQL, but that's not possible. If you use the SQL widget (formerly "Advance Query, hence the "Error in advanced query") instead of an Aggregate, you need input variables for each variable you want to use. If at all possible, use Aggregates, as Paulo suggested, it'll make your life easier.
Paulo Ribeiro wrote:
Hello,

The easiest way to do what you're after is using an Aggregate with a filter:



The main steps to do this are:
  • Create the Aggregate with the filters in the Preparation of the screen;
  • On the Search button, refresh the Aggregate so that it fetches new information depending on the filter;
  • Finally, Ajax refresh the list that displays the results of the Aggregate.
I've uploaded a new version of the OML file so that you can have a look.
 You are right. But the problem is I'm trying implement the result of the report in chart widget. Does this logic suit for that too?
Hi Satish,

Yes, it does. But given that you had to ask, it seems to me you are trying advanced features while not having down the basics. I'd advise you to go back to the tutorials, and try to make sense of the Platform, before trying things that seem out of your league.
Kilian Hekhuis wrote:
Hi Satish,

Yes, it does. But given that you had to ask, it seems to me you are trying advanced features while not having down the basics. I'd advise you to go back to the tutorials, and try to make sense of the Platform, before trying things that seem out of your league.
 
 But I have a Project where I need to implement the reports using charts. Thats why I started with it. I have no idea how to proceed now. Thats why seeking your help.
Sathish,

The problem is, that this is all rather basic, and you are asking rather basic questions. Of course we're willing to help, but we can't do your project for you! If you're not up to the task, you mustn't proceed with the project, but pass it on to someone who actually knows something of the platform.
Kilian Hekhuis wrote:
Sathish,

The problem is, that this is all rather basic, and you are asking rather basic questions. Of course we're willing to help, but we can't do your project for you! If you're not up to the task, you mustn't proceed with the project, but pass it on to someone who actually knows something of the platform.
 
 I must do this task to learn this platform. I can't give it someone else when it was given to me for my own practice. And to be clear again I'm not asking anyone to do my project. Just seeking for a guidance. Anyway as you suggested I will learn the basics and continue this work later. Thanks for you help.