Combine SQL and Aggregate filter's

Combine SQL and Aggregate filter's

  

Hi All,


Definition: I have SQL query and aggregate in the preparation where "SQL" has structure entity with Start Date, End Date & Contract Document Status Filter's and "Aggregate" also has same entities as SQL structure with Start Date & End Date filters.


Solution Required: I need to remove the Start Date, End Date Filter's form the "SQL" query and the date filter's should pick from Aggregate(Get Contract Documents). The SQL should have only Contract Document Status Filter and Aggregate should have Start & End Date filters, so how to combine SQL and Aggregate filters. Also this is to inform that Table Source Record List in referenced to "SQL" query not the aggregate.


We know that we can either have only "SQL" or "Aggregate" to build this logic, but still i need to split the filter's  between SQL Query and Aggregate and final combine them.


Attached is the OML file for workings.


Regards,

Vinod

Hi Vinod,

As we know we can use only SQL with DB Entities & for Local Entities we have Filter as a best option under Aggregates.

I have created below filters & it is working perfect to get the records Today,Last Month, Last Week

Notifications is my Local Table:


Today:
Notifications.notification_date = FormatDateTime(CurrDate(),"yyyy-MM-dd")


Last Month:
Notifications.notification_date >=
NewDate(
If(Month(CurrDate()) =1,Year(CurrDate())-1,Year(CurrDate())),
If(Month(CurrDate())-1 > 0,Month(CurrDate())-1,12),
1)
and
Notifications.notification_date < NewDate(Year(CurrDate()),Month(CurrDate()),1)


LAST WEEK:
Notifications.notification_date >= NewDate(Year(AddDays(CurrDate(),-DayOfWeek(CurrDate()))),Month(AddDays(CurrDate(),-DayOfWeek(CurrDate()))),Day(AddDays(CurrDate(),-DayOfWeek(CurrDate()))))



Thanks,

Assif

assif_tiger wrote:

Hi Vinod,

As we know we can use only SQL with DB Entities & for Local Entities we have Filter as a best option under Aggregates.


I have created below filters & it is working perfect to get the records Today,Last Month, Last Week

Notifications is my Local Table:


Today:
Notifications.notification_date = FormatDateTime(CurrDate(),"yyyy-MM-dd")


Last Month:
Notifications.notification_date >=
NewDate(
If(Month(CurrDate()) =1,Year(CurrDate())-1,Year(CurrDate())),
If(Month(CurrDate())-1 > 0,Month(CurrDate())-1,12),
1)
and
Notifications.notification_date < NewDate(Year(CurrDate()),Month(CurrDate()),1)


LAST WEEK:
Notifications.notification_date >= NewDate(Year(AddDays(CurrDate(),-DayOfWeek(CurrDate()))),Month(AddDays(CurrDate(),-DayOfWeek(CurrDate()))),Day(AddDays(CurrDate(),-DayOfWeek(CurrDate()))))



Thanks,

Assif


Hi Assif,


I have already created date filters under aggregate, but i need combine the SQL filter (Status) and Aggregate filter(dates).

At any cost i need have status filter under SQL, since some logic is built for Status.


Regards,

VINOD

Solution

I am still a bit unclear as to why you are separating these into an Aggregate and an SQL query, but to combine the two lists you would use the ListAppendAll action from (System) and map the aggregate entity to the structure you are using in the SQL query.

Like this:

Solution

Jordan Welch wrote:

I am still a bit unclear as to why you are separating these into an Aggregate and an SQL query, but to combine the two lists you would use the ListAppendAll action from (System) and map the aggregate entity to the structure you are using in the SQL query.

Like this:

Ok Jordon,

Should i need to use this listappend action in preparation or in the search action. If its in the preparation i need to use after sql & aggegate rite.



Vinod Kumar R wrote:

Jordan Welch wrote:

I am still a bit unclear as to why you are separating these into an Aggregate and an SQL query, but to combine the two lists you would use the ListAppendAll action from (System) and map the aggregate entity to the structure you are using in the SQL query.

Like this:

Ok Jordon,

Should i need to use this listappend action in preparation or in the search action. If its in the preparation i need to use after sql & aggegate rite.



Yes it would come at the end of your preparation after both SQL and Aggregate.

You would then call ListAppendAll again any time you call a [Refresh Data] on the aggregate and SQL query.

Jordan Welch wrote:

Vinod Kumar R wrote:

Jordan Welch wrote:

I am still a bit unclear as to why you are separating these into an Aggregate and an SQL query, but to combine the two lists you would use the ListAppendAll action from (System) and map the aggregate entity to the structure you are using in the SQL query.

Like this:

Ok Jordon,

Should i need to use this listappend action in preparation or in the search action. If its in the preparation i need to use after sql & aggegate rite.



Yes it would come at the end of your preparation after both SQL and Aggregate.

You would then call ListAppendAll again any time you call a [Refresh Data] on the aggregate and SQL query.

Thanks Jordan,


I will give a try with listappend.


Regards,

Vinod