Filtering by calculated variable

Hi,

I have a list of records to be displayed on screen. The records are fetched from an Aggregate. But I also have to show an additional column to display "status" which needs to be calculated for each row. I am displaying that using a web block (Placed the logic for calculation in web block).

Now the problem is, I need to filter the records by "status" (a filter functionality for user). 

Is it possible to calculate values inside the same aggregate (without using webblocks). I tried implementing this logic through Function and assigning the ouput of that function to each values. But it says function cannot be called inside DB.

Any other ideas to implement this feature?  

Any help would be appreciated.


Thanks

Angel Saxena wrote:

Hi,

I have a list of records to be displayed on screen. The records are fetched from an Aggregate. But I also have to show an additional column to display "status" which needs to be calculated for each row. I am displaying that using a web block (Placed the logic for calculation in web block).

Now the problem is, I need to filter the records by "status" (a filter functionality for user). 

Is it possible to calculate values inside the same aggregate (without using webblocks). I tried implementing this logic through Function and assigning the ouput of that function to each values. But it says function cannot be called inside DB.

Any other ideas to implement this feature?  

Any help would be appreciated.


Thanks

Hi Angel,


Can you please elaborate more, what do u mean by status. Is it calculated based on output?


Thanks.


JitendraYadav wrote:

Angel Saxena wrote:

Hi,

I have a list of records to be displayed on screen. The records are fetched from an Aggregate. But I also have to show an additional column to display "status" which needs to be calculated for each row. I am displaying that using a web block (Placed the logic for calculation in web block).

Now the problem is, I need to filter the records by "status" (a filter functionality for user). 

Is it possible to calculate values inside the same aggregate (without using webblocks). I tried implementing this logic through Function and assigning the ouput of that function to each values. But it says function cannot be called inside DB.

Any other ideas to implement this feature?  

Any help would be appreciated.


Thanks

Hi Angel,


Can you please elaborate more, what do u mean by status. Is it calculated based on output?


Thanks.


Hi Jitendra,


Yes the "Status" field which needs to be displayed is calculated seperately for each record.


Angel Saxena wrote:

JitendraYadav wrote:

Angel Saxena wrote:

Hi,

I have a list of records to be displayed on screen. The records are fetched from an Aggregate. But I also have to show an additional column to display "status" which needs to be calculated for each row. I am displaying that using a web block (Placed the logic for calculation in web block).

Now the problem is, I need to filter the records by "status" (a filter functionality for user). 

Is it possible to calculate values inside the same aggregate (without using webblocks). I tried implementing this logic through Function and assigning the ouput of that function to each values. But it says function cannot be called inside DB.

Any other ideas to implement this feature?  

Any help would be appreciated.


Thanks

Hi Angel,


Can you please elaborate more, what do u mean by status. Is it calculated based on output?


Thanks.


Hi Jitendra,


Yes the "Status" field which needs to be displayed is calculated seperately for each record.


Ok, so you can add the new attribute in aggregate with the name "Status" and create the formula for that.


JitendraYadav wrote:

Angel Saxena wrote:

JitendraYadav wrote:

Angel Saxena wrote:

Hi,

I have a list of records to be displayed on screen. The records are fetched from an Aggregate. But I also have to show an additional column to display "status" which needs to be calculated for each row. I am displaying that using a web block (Placed the logic for calculation in web block).

Now the problem is, I need to filter the records by "status" (a filter functionality for user). 

Is it possible to calculate values inside the same aggregate (without using webblocks). I tried implementing this logic through Function and assigning the ouput of that function to each values. But it says function cannot be called inside DB.

Any other ideas to implement this feature?  

Any help would be appreciated.


Thanks

Hi Angel,


Can you please elaborate more, what do u mean by status. Is it calculated based on output?


Thanks.


Hi Jitendra,


Yes the "Status" field which needs to be displayed is calculated seperately for each record.


Ok, so you can add the new attribute in aggregate with the name "Status" and create the formula for that.


For calculating that "Status", the logic has to loop through 2-3 more aggregates, with some filters, joins etc. So the logic can be implemented in some function.

Also this logic can't be placed in preparation as while displaying each row it has to compute "Status" for that row also.


In that case, you should use Advanced SQL...Thanks

Hello Angel,


If you want to be able to filter on status, you should indeed avoid doing that outside/after the aggregate, since aggregates might not return all available rows in the database, and than you would only be filtering on a subset of your data.  

You could still do it though, if you are certain that you always retrieve the full list.  No need to use a webblock for that, just loop through your aggregate result (either in the onAfterFetch for Reactive Web or in a node below the aggregate in Traditional Web) and call the action that calculates the status field for each one.  After that, you can apply the filter.

But I think it's always a better option to filter inside your aggregate (or as Jitendra said for complex queries inside the SQL tool) if that is possible.  As for needing to incorporate the result of other aggregates into your logic, it should be possible by extending your main aggregate with the joins and filters and tables involved.  Can you share something about your datamodel and calculation of status so we can help see if this is possible inside one aggregate ?  (maybe with fake entity and attribute names to protect your business)

Another route you could explore : if a piece of information has a complex calculation and is dependent on a lot of other information and is time consuming to determine, it might be interesting to store it in the database as an attribute rather then calculating it every time to show it to the user.  In that case, you could for example have a single action 'CalculateStatus' that gets called in every place of your system where data gets changed that might be of influence on your status.  And then your aggregate to show and filter on it becomes a vanilla one.

Dorine

Hi Angel,

Can you please share minimal oml of the logic so that we can suggest better. 

Other than this from your previous comments Advance SQL looks like the only solution.

Hi, friend.

You can create a structure with the same atributtes of your entity plus the value you want to show (wich is external from your entity or a calculated value).

Then create a variable on your screen with the type "Entity Structure List". With a list variable you're abble to set as a source list to your list/table records. 

The last step will be populate this list variable with the values from the aggregate.


Best regards