Hi all, 

i am just doing some task about SQL and aggregate filter.

As i created a page to show different status of the project with a table, and i drop some expression to count the totally number of each project status above the table.

i generate the number successfully on the preparation:

Aggregate -> SQL -> assign the result to the local variable (structure).

that make me show the total number of each status at the beginning.

but i added the search function on the top to filter the data showing on the table.

The problem is appeared that when i filtered the data, the total number (SQL) did not change.

for example, without filtering the total number of all status showed on the table is 8 and SQL can count that is 8..... with filtering the total number of all status showed on the table is 2 but SQL also count that 8.

i added a action on the search button with refresh aggregate (that should be filtered) -> refresh SQL -> assign -> Ajax refresh the expression with a container.

is it my SQL issue or the function logic issue?  SQL can be generate the number with the filtered aggregate?

Thanks all.

Hi, 

Try to debug it, then you'll find why the results are not the same.

regards

Hi Chin,

if possible can you share the OML file,

think your structure which you are assigning was only contain total number of count which you did while preparation. its not getting update based on your filter values,

Manikandan K


Manikandan K wrote:

Hi Chin,

if possible can you share the OML file,

think your structure which you are assigning was only contain total number of count which you did while preparation. its not getting update based on your filter values,

Manikandan K


Dear Manikandan, 

Sorry i have created a demo with same logic for you coz the original one have some confidential data.

The problem is that when i search "111", that show 1 record on the table but the number still the original one.

i also think that may be the problem what you mention, but i stuck on it for a long time.

Thanks a lot for the reply.


Solution

Hi Chin,

As far as I can see what you created should work (I just did a quick glance), but I do have some observations:

  1. You shouldn't use SQL unless absolutely necessary. In this case, you could create two new Attributes in an Aggregate that select the "Draft" and "Done" status, and Count those.
  2. I would just group on the StatusId and use a Count, so you have the totals of all statusses. Since you only have two of them, the total is a simple sum of those, so you don't need to have the query output it.
Solution

Kilian Hekhuis wrote:

Hi Chin,

As far as I can see what you created should work (I just did a quick glance), but I do have some observations:

  1. You shouldn't use SQL unless absolutely necessary. In this case, you could create two new Attributes in an Aggregate that select the "Draft" and "Done" status, and Count those.
  2. I would just group on the StatusId and use a Count, so you have the totals of all statusses. Since you only have two of them, the total is a simple sum of those, so you don't need to have the query output it.

Dear Kilian, 

Thanks for the advise, i can do that if i use your method.

although i feel a little bit pity that can't do that using SQL but at least i can do that now.

Thanks a lot!

Hi Chin,

Glad I could be of help. Note that you can do it with SQL, but using OutSystems you should really avoid SQL, so no SQL is better :).

Kilian Hekhuis wrote:

Hi Chin,

Glad I could be of help. Note that you can do it with SQL, but using OutSystems you should really avoid SQL, so no SQL is better :).

Got it:)! Thank you very much