Create a aggregate at runtime

Create a aggregate at runtime

  

Hello 

I'm not sure if this make any sense ! 

I would like that a enduser could select an entitie form a list (for example in combobox), and as a result we could display a tableRecord with all the attributes.


I do not know how I can create a aggregate at run time using the select source!! 


Any help is appreciated !!


TIA


Hi Alberto,

This is possible but very tricky regarding security / SQL injections.

Is there a reason why you want this and how is your application going to be exposed?

Kind regards,

Remco Dekkinga

Hello Remco

Tanks for your comment.


Yes. The application in cause is a kind of toolbox that help the maintain other applications. This is for internal use and what we pretend is selecting one entitie from a list, view the content of the entitie.


 

WHy don't you use the database management tooling for that? Toad, SSMS, Oracle client or similar?

in fact we use it, but in other context.


We need it for other reasons

Hi Alberto,


Please find attached one modified version of the AdvanceQuery forge component, that achieves a similar functionality of what you are trying to achieve. See Dynamic WebPage:

Access via direct link: http://<server>/AdvanceQuerySample/Dynamic.aspx

I plan to contact the owner of the forge component to make it public available in the near future.


Hope it helps you.

Best regards,

Daniel Martins

Thank you. Daniel


I'll check.It look promising.


I will give feedback

Hi All,We can use OS table which is having the list of all table name and we can show in combo and based on selection we can design SQL Query.

Thanks Jitendra.


Can you give one example of second part of your solution !!


TIA

Hello Daniel


I tried your solution and its works. The problem is that oblige to use one extention. That turns as a not accepted solution because of our organization rules.

I prefer a solution based only in OutSystem "default" code... without extentions or forge modules.


Thanks anyway. Let see if someone have a diferente approch.

Solution

HI Alberto,

Thank you for your feedback,

Some times it will be difficult or even impossible to avoid the usage of extensions. I don't understand your organization policies regarding forge components.

Nevertheless, you can't take a look into OSSYS_ENTITY (System-> Entity) and OSSYS_ENTITY_ATTR (System-> Entity_Attribute) as suggested previously by JitendraYadav in this post.

You can build your own logic and build an dynamic html table as described here, making use of the previous system tables. However you will need to use advance queries with expand inline parameters, as described here by Remco. This is a trade off and I would personally prefer to this inside of one extension.


Best regards,

Daniel Martins.

Solution

Hello Daniel


Thanks for your clear and quick answer. It seem like a good solution (and probably with a bigger performace tradeoff). I understand your comment about policies but i will not comment it.


Thanks, I will give feeback. Meanwhile I will mark as a solution

Daniel

One more aspect. If I decide use an Advanced SQL what i must use as the Output.... it will change from entity to entity !!! 


You will need to have a limit of for example 20 attributes, and will be all text. You will need to output to all the attributes for the same structure.


Alberto Ferreira wrote:

Daniel

One more aspect. If I decide use an Advanced SQL what i must use as the Output.... it will change from entity to entity !!! 



Ok Daniel


I see the picture. Thanks again

Hi Alberto,

I am following your issue on how to display in real time an entity.

I don't know how many entities do you want users to chose from, and I am wondering about how performant would be to advance query  from system entities. 

I attached a very clumsy solution. Only my two cents:

Use a switch on a webblock preparation with all entities you want to chose from. Switch arm wil only be rendered on input (from static entity with entities names maching your entity to display).

meanwilhe you have all your tables with display property being true if input matches the source entity.

Thanks Helena


I will try and give feedback


Nice to be part of a so active community !!!!! :)

Thanks Helena

for your contribution... but it is not what I pretend. I need something more dynamic.  

Hi Alberto,

No problem, I'm eager to see  your developments on this and learn from it :-)


Hi Alberto Ferreira,

I did something similar a feel weeks ago.

After getting Entity, I look for the fields in Entity_Attr, concatenating each of them so that in the end, in the query that I will use them, return only one column, and together I am concatenating a special character that I am sure will not exist in the content of the fields. I use both Entity and the fields concatenated in an Advanced Query, where the query returns only a Structure Text. To separate the results, I use the String_Split using the special character I passed in the previous steps, and so I have my dynamic columns.
Remember, if you use this method, you will have warnings for this the Advanced Query used, since the select clause will be a text.

This was the rule to concatenate each field (Oracle DB):

SyntaxEditor Code Snippet

"CONCAT({"+Replace(Replace(GetEntitiesByName.List.Current.Table_Name,"""",""),"SMO.","") +"}.[" + GetEntitiesByName.List.Current.Column_Name + "],"+ If(GetParamCampos.List.CurrentRowNumber = (GetParamCampos.List.Length - 1),"''","'"+ Site.DelimiterChar +"'") +")"

 

And bellow is the Advanced Query:



If help, let me know.

Douglas Lima

Thanks Douglas.

Only to get it clear. You use all the attributes of an entity ? and "upload" all to the SQL Output?

Alberto Ferreira wrote:

Thanks Douglas.

Only to get it clear. You use all the attributes of an entity ? and "upload" all to the SQL Output?


Yeah, 

I'll give you an example:

Entity: Car

Entity_Attr: Id, ModelName, ManufactureName, Price

@Select: 

CONCAT({Car}.[Id],'^') || CONCAT({Car}.[ModelName],'^') || CONCAT({Car}.[ManufactureName],'^')|| CONCAT({Car}.[Price],'')

@From: {Car}


With this, I'll get all the fields in one colunm.


Cheers

Thanks Again. Douglas