how to select one id from the list in Aggregate FIlter.

Hi,

I have the below scenario.

In Database table I have 3 rows . Example : ID1, ID2, ID3.

Now my requirement is to filter in Aggregate only ID1. Is there any functon I can use for this?


Thanks,
Saicharan

Hi, in aggregate switch to filters tab and  indicate your condition or use ListFilter action (for example tablename.id = screen input variable).

Hi Sai,


You can use the filter tab in your aggregate. The ID1 can be a hardcoded value, or it can come from an input parameter, or from a local variable, or from any other widget that is within the scope of your screen or action.


Regards

Nikolai Kaplin wrote:

Hi, in aggregate switch to filters tab and  indicate your condition or use ListFilter action.

Hi,

How do I top record from a particular table when we have 6 sources in the Aggregate. We cannot use list filter action in the our scenario.


Thanks,

Saicharan



Pedro Costa wrote:

Hi Sai,


You can use the filter tab in your aggregate. The ID1 can be a hardcoded value, or it can come from an input parameter, or from a local variable, or from any other widget that is within the scope of your screen or action.


Regards

Hi,

Records in the Table are fetched dynamically, we need a solution where the top record in that join condition.


Thanks,

Saicharan


You can use sort and max record property from your aggregate. In my example I am considering that I want to get the highest ID (Last created), but you can customize your ordering. 

e.g.:

sai charan wrote:

Nikolai Kaplin wrote:

Hi, in aggregate switch to filters tab and  indicate your condition or use ListFilter action.

Hi,

How do I top record from a particular table when we have 6 sources in the Aggregate. We cannot use list filter action in the our scenario.


Thanks,

Saicharan




maybe I do not understand your case, but why not?(6 sources)

Hi Sai,

You can use the sorting option available in the aggregate as what Pedro mentioned above.

Thanks....

I don't know if it's what you want to do. But if you want to do something like this, I suggest using SQL instead of aggregate or doing 1 aggregate before your main aggregate, which will return the first record of the entity you want to filter on.


note: there are several ways to write this sql

SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
    INNER JOIN (
       SELECT TOP 1 LineItems.Quantity, LineItems.Description
       FROM LineItems
       WHERE LineItems.OrderID = Orders.OrderID) LineItems2
    ON 1=1



In addition, you can set max records property to 1)

I don't know if I understand your case. You can add filter upon joining the tables to select only the desired record on the table you are joining with.


In this example, you only want to join Table1 with Id = 1 to Table2


Regards,

Jeff


I guess your only option is to do it through advanced sql to join only the Top 1 of a table.


You query may look like this: 


select [columns] from Table1 inner join (select top 1 * from Table2 [insert where condition] ) as T2 on table1.Id = T2.Id



Hope this helps :)