Can someone tell a query to fetch both the Name and Email values from the table in a single output, i.e., having two columns. I am using structure to store the output but I need to create two structures and run two queries, one to fetch all the names and other to fetch all the email values. Can anyone tell me how to do this using a single query and store in a single structure instead of two.
This is the query which I am using to fetch the name or email.
SELECT {FieldData}.[TextValue] FROM {FieldData} WHERE {FieldData}.[FieldId]=@FieldId
I am taking fieldId as an input parameter.
Hi,
First of all, it's recommended for this kind of operations use Aggregates, since the platform optimize them.
If, for any motive, you really need to made it trough SQL node, you can pull it with the two values like this:
SELECT {FieldData}.[Name], {FieldData}.[Email] FROM {FieldData} WHERE {FieldData}.[FieldId]=@FieldId .
You can create a structure with the two attributes and map it as the output.
Hope this can help you.
Best regards,
Ricardo
Hi instead of SQl you can use aggregate in server action/screen and you can add filter there
Google SQL pivot clause.
But the real question is why on earth you have a complex generic meta table just to store a person's name and email.
You are making it very hard on yourself on so many levels, for what purpose ?
I am using dynamic form which stores the information in this format only and I want to fetch the information and display some of the information on the screen.
Ok,
being a newby with 0 training points, that sounds like an extremely challenging thing to start with, so I wish you all the courage you will undoubtedly need.
I do get that you do this because you want to make a dynamic form, but that's just the question : why ? If scaffolding a real, static form based on a specific entity, only costs minutes, why get into all the complexity of dynamic forms ? It comes at a great cost, so what is the benefit you think you'll get from that ?
Of course, it can be an enjoyable thing as a developer to make something like that work, but is that really your business's requirement ? Or are you just trying something out for the learning experience ?
Anyway, as I said earlier : if you want to compact values from several rows into a single row with several columns, sql pivot clause is your friend.
But that's just te beginning of your challenges, since you have to tell OutSystems up front what the output structure of your SQL is, and you can't just have a structure with name and email in there, as you want this to be dynamic, you'll have to find a workaround, for example adding a FOR JSON in your SQL.
And then try to build a dynamic UI based on that JSON,...
Dorine
Can anyone tell the pivot query to show the table in such a way where 2 and 3 are the field Id. I need this as an output.
you should save emails and names in different attributes. It does not make any sense to save it in the same attribute. You are just making this entity so much harder to manage and mantain.
Hi in this case you should not use iD
use group by in aggregate
the output will be
I group the table by label and I got this. How to show it on the UI in tabular form the email and the name.