Hi community,
I have an aggregate to show data in a table of requests. Each request can have many states. Then I added one column to the aggregate called UltimoEstado, and I have a server action to get the last state that should appear in the table for each request.
In the OnAfterFetch event of the aggregate I call the server action to get the values and assing the values to the column:
After this, the table is loaded fine with the column showing exactly what I want.
But when I try to apply a dropdown to filter the state,
when I select one value of the dropdown the table becomes empty...
What am I missing? How would you do this logic?
Thanks in advance!
Ok,
i couldn't really work with your oml, because it had lots of missing dependencies, but I think I understand what you need.
See attached oml, I would also go for an SQL node here rather than Aggregate. In yellow I emphasized that it is working with the id, not the label, as we discussed before.
If you really want to use aggregates, I think you can't really make that happen in a single aggregate, I don't think they support subqueries, but you could have an aggregate reading all, and then do some filtering afterwards. That is also something that I would do entirely on server side, but there is the downside of having to first retrieve everything, and only being able to apply your filter afterwards.
Dorine
Forgot to say : if you want this to work perfectly, you have to make sure no 2 states of a request exist with the same timestamp (so in crud wrapper, when updating the state, if that happens within the same second as the previous state, just add a second)
If you don't want to do any of this, you could also have a reverse reference of LastStateId in Request pointing to the last state. If you would do that (more fragile to make sure it is always updated correctly) you can stick to a simple aggregate to produce that overview.
Hi Diogo,
Is there any drop-down on change event running?
Please share the oml or elaborate your question.
Yes, it just refreshes the aggregate...
Hi @Diogo Barbosa ,
for understanding why it's empty, we would need to understand what exactly it is that you do to "apply a dropdown to filter the state". There is at least one odd thing about the dropdown properties : you use the label as value, but you store it in a value called id ??? If your states are in a static entity, I would expect the text to be label, but the value be id.
But even if you did this right, I'm suspecting there's more to fix :
For example, it is a bad idea to do this server action finding the latest state in that OnAfterFetch. It means that if you have for example 7 requests, you will do 8 server calls, one to retrieve the list, and 7 for each individual request to find the last state.
But I'm also not seeing how you could correctly apply a filter, unless you retrieve all requests in the database instead of only let's say 10.
Ideally, you write a better aggregate that also determines the last status in a single trip to the database, or if that's not possible, you do everything you need in a dataaction, with maybe an sql instead of an aggregate.
Share your oml, or your datamodel with some sample data, and we can help you with that.
Hi Dorine,
yes the first part is right, I really want to filter by the label.
It's a simple data model... Pedido can have many Estados, and I just want to show the last one in the table of Pedidos.
But as you said,
I think I need to do this with advanced SQL... I always try to do the most with aggregates, but I realize that they are still limited to some things :(
Thanks...
no, still don't get it, it is a dropdown to select a value from a referenced static entity, there is no reason to filter by that label, it is much more obvious to have a filter like
Estado.TipoEstadoId = filter value or filter value = empty, so the id of the selected TipoEstado, not the textual value.
As for your datamodel, it being portuguese, it is hard to understand what it represents, could you say what the functionality of these data is, and what the functionality you want in your screen ?
For example, you are talking about the last state, but i don't really see on what in your datamodel you are basing that. Aaaah, unless data means date, is it just the one with the highest value of data ?
Also, what exactly do you want to accomplish with that filter, you want only those rows that have the filtered value as last state ?
Hi Dorine!
Well, I built an oml for testing...
Basically, I wanted an aggregate similar to this, but I needed to add the Label of StateType also to the result, but I'm only able to add the DateTimeStateMax with the MAX() function.
I'll need an advanced SQL to this, or do you have any idea to complete this aggregate?
With an aggregate then the filter by state type would work... In my project, even if I add the id of the static entity to the dropdown it also doesn't work.
Thanks for your help!
Thanks Dorine!
Really helpful!
The SQL solution is great, but this last solution also sounds good! I'll try this I think...
As Dorine Boudry said I can also see in drop-down variable you are using id type variable as it's name suggesting-TipoEstadoId but in drop-down value you are passing lable.
Can you please check which attribute you are using to filter data from the aggregate hold same attribute value in drop-down variable.
Thanks,
Arun
1. Always Trim your aggregate attributes in the calculated attributes.
2. Check if it is = nulltextidentifier() or not in if condition.
Hope it helps
I am unsure of the proper solution for this, but please make sure you add all 3 server action combined inside OnAfterFetch. This may cause performance issues