Run time badge

  

Hello

In a list based on an aggregate, is it possible at runtime to execute an sql?
The list has a link to a popup in which it is possible to register data in the X table (1 or more for each record in the list). The ultimate goal is to identify whether a record in the list has data in the X table without clicking the link.


Thanks

Solution

Hello Carlos,

One option (the easiest one), is to add a Web Block in the Table Records (screen) and pass to it the ID of the current line record and check in its preparation the associate entity to check if it is empty or not, or how may item it has But this means an extra hit in the database for each line of the table.

Personally, I would join this associated entity in the first one (in the page), and group by the elements of the main entity (like the ID and any other field you need to show), and count the number of associated items (using a Count in the ID column of the associated entity.

Cheers.

Solution

Eduardo Jauch wrote:

Hello Carlos,

One option (the easiest one), is to add a Web Block in the Table Records (screen) and pass to it the ID of the current line record and check in its preparation the associate entity to check if it is empty or not, or how may item it has But this means an extra hit in the database for each line of the table.

Personally, I would join this associated entity in the first one (in the page), and group by the elements of the main entity (like the ID and any other field you need to show), and count the number of associated items (using a Count in the ID column of the associated entity.

Cheers.


Hello Eduardo,
Thank you for your response.
I opted for the web block solution:
- in the preparation I put an input variable (list entry id)
- I execute a sql that makes a count to the table X relative to the input
- in the list I put the web block and I can see the count
But the final objective (sorry if I was not explicit) is to use a true or false, because the count is 0 or> 0. I can easily get the boolean, but in the list I can not use it. The purpose is to use the value of boolean so that the icon associated with the link is different from true or false.



Carlos Messias wrote:

Eduardo Jauch wrote:

Hello Carlos,

One option (the easiest one), is to add a Web Block in the Table Records (screen) and pass to it the ID of the current line record and check in its preparation the associate entity to check if it is empty or not, or how may item it has But this means an extra hit in the database for each line of the table.

Personally, I would join this associated entity in the first one (in the page), and group by the elements of the main entity (like the ID and any other field you need to show), and count the number of associated items (using a Count in the ID column of the associated entity.

Cheers.


Hello Eduardo,
Thank you for your response.
I opted for the web block solution:
- in the preparation I put an input variable (list entry id)
- I execute a sql that makes a count to the table X relative to the input
- in the list I put the web block and I can see the count
But the final objective (sorry if I was not explicit) is to use a true or false, because the count is 0 or> 0. I can easily get the boolean, but in the list I can not use it. The purpose is to use the value of boolean so that the icon associated with the link is different from true or false.




Hello
I opted for a solution that I do not like: assigning the obtained value to a session variable.
Just one more question:
is it possible to refresh the list when I close the popup?

greetings


Carlos Messias wrote:

Carlos Messias wrote:

Eduardo Jauch wrote:

Hello Carlos,

One option (the easiest one), is to add a Web Block in the Table Records (screen) and pass to it the ID of the current line record and check in its preparation the associate entity to check if it is empty or not, or how may item it has But this means an extra hit in the database for each line of the table.

Personally, I would join this associated entity in the first one (in the page), and group by the elements of the main entity (like the ID and any other field you need to show), and count the number of associated items (using a Count in the ID column of the associated entity.

Cheers.


Hello Eduardo,
Thank you for your response.
I opted for the web block solution:
- in the preparation I put an input variable (list entry id)
- I execute a sql that makes a count to the table X relative to the input
- in the list I put the web block and I can see the count
But the final objective (sorry if I was not explicit) is to use a true or false, because the count is 0 or> 0. I can easily get the boolean, but in the list I can not use it. The purpose is to use the value of boolean so that the icon associated with the link is different from true or false.




Hello
I opted for a solution that I do not like: assigning the obtained value to a session variable.
Just one more question:
is it possible to refresh the list when I close the popup?

greetings



In the meantime I have already prepared the solution for the refresh, I used popup_editor_notify.

Thank you


Hello Carlos,

Just as a comment, the session variable solution would not work for a list with more than one line unless you used a list that would degrade app performance.

Instead of notify the parent, why don't you use the icon inside the block?

This way, all the required logic is there and you don't need a complex logic to handle this.

But personally, I would use the other option I gave to you :)

Cheers.

Eduardo Jauch wrote:

Hello Carlos,

Just as a comment, the session variable solution would not work for a list with more than one line unless you used a list that would degrade app performance.

Instead of notify the parent, why don't you use the icon inside the block?

This way, all the required logic is there and you don't need a complex logic to handle this.

But personally, I would use the other option I gave to you :)

Cheers.


Hello Eduardo

Thank you for reply.
The solution with the session variable works fine and 
degradation is imperceptible given that the number of lines never exceeds 120.
I'll try to be more explicit. When the list is executed or refreshed, in each line a variable is passed to the web block, it assigns to the session variable the true or false value that is used in the list.
Regarding the popup, it is called at a different time, so it does not seem possible to use the web block.
As for the solution that you would use, I confess that I did not understand it properly.

greetings
Carlos Messias


Hum...

For my solution.

Imagine that I have entity A and Entity B that has a Foreign Key to A (A_Id).

If I want to list A, but one of the columns is an icon that shows if there are any associated record in B, i would do the following:

1. An aggregate fetching A, joined with B. 

2. Group by all the required fields from A that I want to show in the Table Records.

3. Do a Count in the B.Id column (if there are any associated record, it will be greater than 0, otherwise it will be 0)

Outside the aggregate I have the fields that I grouped plus the count. So, it is just a matter of using it in each line to show or not the icon or do whatever depends on the existence or not of records associated with A, in B.

Cheers.

Eduardo Jauch wrote:

Hum...

For my solution.

Imagine that I have entity A and Entity B that has a Foreign Key to A (A_Id).

If I want to list A, but one of the columns is an icon that shows if there are any associated record in B, i would do the following:

1. An aggregate fetching A, joined with B. 

2. Group by all the required fields from A that I want to show in the Table Records.

3. Do a Count in the B.Id column (if there are any associated record, it will be greater than 0, otherwise it will be 0)

Outside the aggregate I have the fields that I grouped plus the count. So, it is just a matter of using it in each line to show or not the icon or do whatever depends on the existence or not of records associated with A, in B.

Cheers.



Hello Eduardo

Got it. So is not preferable to use sql instead of aggregate?

greetings

Hi. Nope. Every time you can use an aggregate, you should use an aggregate :)

EDIT. There are exceptions, of course, but in general, its better in terms of performance, code clarity, etc.

Eduardo Jauch wrote:

Hi. Nope. Every time you can use an aggregate, you should use an aggregate :)

EDIT. There are exceptions, of course, but in general, its better in terms of performance, code clarity, etc.

Hello Eduardo

I tested your solution and I must confess that it was quite laborious. I had to change the whole list because everything changed. On the other hand I started to have a structure in which I do not have any ability to change the names, getting name1, name2, etc getting without knowing what their correspondence. To have some control I have to add attributes so I can name them.
Since I've been an sql man for over 25 years, I'm probably influenced by that fact.

Regards and thanks for everything

Hi Carlos,

Yes, when you need to change, require some effort. But in the long run, both in terms of maintainance as well as performance, it will be worth.
Regarding the name of the columns, you can change them. There is no need to create new computed names because of the name.

Cheers.