TableRecords containing both attribute data and calculated attribute data

Hi! 

I am building a Traditional Web App, and have a screen containing a TableRecords widget, which uses an aggregate comprised of several joined entities (which are imported from an external SQL database using an extension). There is a calculation to perform on some of the attributes in this aggregate. I currently "Group By" one column, then Sum another column based on that grouping. The calculation works correctly, but the aggregate only outputs the calculated attributes. OutSystems documentation indicates this is the intended behavior, but I need the TableRecords widget to have columns with the original attributes AND the calculated attributes.

I attempted to create a duplicate of the original aggregate and then perform the calculations on the copy, but couldn't get the two aggregates to output the same rows in a single TableRecords.

How does one create a TableRecords widget containing both attribute data and calculated attribute data from an aggregate?

Thank you!!!

Hey Isaac,

On the aggregates you can only use the columns that are in "blue" like example below. Because you made a group by for one column, you need to add the other atributes also as "group by" or with a calculation attribute (Max, Min, Count, Sum... ) for use it in the TableRecords.


You can also create a Structure and do a SQL query to get that data.

Did my explation helps you?


BR,

Luis

Hi Isaac,

As Luis already explained that, if you are using groupby then you can only use blue columns as an output of aggregate. If you want to use grey columns in your table, then you have to use another aggregate in a webblock with all the joins and filters that you have added in your first aggregate. 

Add one more filter in webblocks aggregate to filter the data bases on groupby attribute. Use that web block in your table and pass an ID of groupby attribute.

Check the attached oml, I have shared a small example.

Hope it helps to solve your issue.

Regards 

Seema

Hi Luis,

Thank you very much for your explanation! 

I attempted to include the other columns that I need using Groupby, but doing this changed the Sum column that was based on the original Groupby. I could not find a way to include the additional columns in the "blue" without changing other calculated attributes.

As for using Structures and the SQL Tool: I've honestly tried to avoid taking this approach, because I don't fully understand the OutSystems SQL Tool, and the documentation cautions against using it if possible... Is there a specific implementation of this approach you would recommend?

I did come up with this statement which performs the correct calculations, but struggled to put it into a Structure and feed it input data:


SELECT Product, SUM(Product_Price) FROM {Sample_Product}
GROUP BY Product_CategoryId


I will include an oml file with some example data in my next post.

Thank you!!

Isaac

Hello Isaac,

Please upload the oml file and I Will help you to build the query on the aggregate or Sql.

Br,

Luís 

Hi Seema,

I can't thank you enough for your help here! I inspected the file you attached- using the same technique, I was able to achieve much of the basic functionality I was looking for. However, there is an additional constraint I failed to mention in my post: I must make a column where a calculated attribute is divided by a different, non-calculated attribute. To better illustrate the idea, I'm attaching an example OML which uses essentially the same data model as my actual app. 

2/3 columns in the table are now correctly filled out (Hurray!) but I still can't really access or modify the data from the web blocks. Also, for those 2 columns currently working, I found it necessary to create 2 separate web blocks- might there be a better way I can implement this?

Again, thank you so much!

Isaac

Solution

Hello Isaac,

I have a solution for your issue: You can use actions (as functions) instead of webblocks and I think that way is more easy to do this. I also make a function to calculate the percentage column.

Please see attachment OML with my solution and see if it solved your problem with actions.

I suggest you to use this actions instead of List records in webblocks because you only need to get 1 record for each row in the table records. So, you don't need to use list records in the webblocks.


This option is not the best perfomant. The best option is to do a SQL query to get all the data ( with subquery).

Please see here an example (Simple Subquery to Calculate Average):  https://www.essentialsql.com/get-ready-to-learn-sql-server-20-using-subqueries-in-the-select-statement/


BR,

Luis

Solution

Isaac Owades wrote:

Hi Seema,

I can't thank you enough for your help here! I inspected the file you attached- using the same technique, I was able to achieve much of the basic functionality I was looking for. However, there is an additional constraint I failed to mention in my post: I must make a column where a calculated attribute is divided by a different, non-calculated attribute. To better illustrate the idea, I'm attaching an example OML which uses essentially the same data model as my actual app. 

2/3 columns in the table are now correctly filled out (Hurray!) but I still can't really access or modify the data from the web blocks. Also, for those 2 columns currently working, I found it necessary to create 2 separate web blocks- might there be a better way I can implement this?

Again, thank you so much!

Isaac

Hi Isaac,

Check the attached oml. Made some changes on GetProducts Aggregation. 

Seema, Luis,

Thank you both very much. These approaches both solve my problem. I will experiment to learn about them.

Happy Holidays!

-Isaac