Good morning!
Let's say I have an aggregate with a set of records.
Then, in a foreach loop I need to iterate each record to check a condition.
In the end I want an aggregate with the same structure but without the records that don't match the condition.
What's the best approach to this?
Thank you!
Well,
I did it with SQL inside a data action, so this is it:
In text if anyone else needs it:
select ID, NumProc, Num, Ano, Objecto, Tipo, DataEntrada, Gestor from {V_CONTR_ESCR_ABERTO} VCEA where ((DateDiff(dd, VCEA.DataEntrada, GETDATE()) - DateDiff(ww, VCEA.DataEntrada, GETDATE())*2) <= 9 and @Estado = 'Verde')OR(((DateDiff(dd, VCEA.DataEntrada, GETDATE()) - DateDiff(ww, VCEA.DataEntrada, GETDATE())*2) <= 15 and (DateDiff(dd, VCEA.DataEntrada, GETDATE()) - DateDiff(ww, VCEA.DataEntrada, GETDATE())*2) > 9) and @Estado = 'Amarelo')OR((DateDiff(dd, VCEA.DataEntrada, GETDATE()) - DateDiff(ww, VCEA.DataEntrada, GETDATE())*2) > 15 and @Estado = 'Vermelho')OR(@Estado = 'Todos')
Thank you for all your suggestions!
Hello Diogo
First, why can't you add the condition to the Aggregate filters?That way you would have only the records that match that condition.
If, for some reason, that's not possible, I would check the ListFilter action. It returns a NEW list with only the items that match the condition (you give it the original list and condition).
If, for some reason, the ListFilter is not a possibility, and you really need to iterate the aggregate's result, you have some constraints.It's NOT possible to remove records from a list you are iterating, so you would have to duplicate the Aggregate's list first, iterate the dupplicated list and remove the items that do not match the criteria.In that case, I would iterate backwards with an ad hoc looping (using IF), instead of a for each, as it would be much easier to know which records to remove from the original list (the aggregate's list).
Hope it helps.
Cheers.
I tried the ListFilter, but it's very slow :(
Here is what I need:
The commented code works fine! It calculates the difference between 2 dates, with DiffDays.
But then I need to count only the weekdays, and I built a function for that. But as you see my function is not allowed... So, that's my problem...
Any ideas to solve this issue?
@Eduardo Jauch In fact, I would not ListDuplicate + ListRemove, but a ListAppend for each record that you do want. That prevents duplicating items that you do not want, only to remove them later. Also, it saves you having to keep track of the indexes that you need to remove, as the destination list keeps getting smaller and hence with every remove you need to decrease the index as compared to the source list.
Hi Diogo,
Those conditions look like conditions that you could add as Filters in your aggregate. I would definitely do that, instead of post-processing.
I agree, but how can I use Filters, if it is not allowed to use my Functions?
What do your functions contain? Is it difficult logic, or just a simple date diff? If it's really logic you cannot convert to an aggregate, then indeed either ListFilter or looping + testing + building a new list is all you can do, no matter if it's slow or not.
Much better approach, indeed :)
User Actions are allowed in aggregates, but ONLY if they can be evaluated to a value BEFORE sending the query to the database. Only a handful of built in functions can be used passing attributes as arguments, as they have an SQL equivalent.
Question.
The logic that is used by your function, can it be adapted for the aggregate filters?
Maybe using an SQL tool instead of an aggregate would allow you to implement the WHERE clause in a way that works directly on the query.
I'm not sure...
My function is on the left in this image. And it uses the Reactive\WorkingDays from the Forge (I show it in this image also).
And that's all, in the end it's just a function that returns an integer :)
If possible you can share the oml file so that it will be better to figure out why do you have to use for each even though we have the filters in aggregates and list filter options.
Regards,
Shree
You can use ListFilter action instead of looping the list. You shoud be able to get it on Manage Dependencies>(System)>ListFilter