Process Column in Aggregate

Hello everyone,

In my aggregate I have one column that looks like this. I want to delete everything that comes after the last slash (since - as you can see - it is repeated information).

This would be easy using regex or even a substring + indexOf with 3 arguments. However, when I create a new attribute in the aggregate, I don't have these abilities. Do you have any workaround for me to do this?

Hi Catarina ,

You are right In aggregate it has limited functions to use. But you can remove the duplicate value while binding to the table cell  and drop down some thing like below 

Would it be possible to post your OML?

Hi Catarina,

Can you give a bit more context? Do you need it inside an aggregate for any particular reason? Or can you workit outside of the aggregate?


Hello Laura,

I am displaying my aggregate as a table in my screen. I will also use this aggregate for a dropdown filter in my screen. For both my table and my dropdown, I want the data in this column to not have whatever comes after the last slash. But seems like the built-in functions are limited for new attributes in an aggregate (see below comment).

Thank you!

You do have access to built-in functions for your new column in your aggregate. This should help you achieve what you are looking for.

Hello Asher, I thought so too, but the Index function when calculating new attributes doesn't allow me to have 3 arguments which I need to be able to search from the end to the beginning.

Did you try inputting the function that I supplied (with the Substr) and changing the elements to reflect what you want? My Index function only uses 2 arguments (though it can use 3)

If you want to do this for all rows of a particular column then you can create an new attribute in the aggregate itself. There you can use string split method. In that method you can get substring after spilt from last occurrence of "/".

For this you can use indexOf in built function and in that pass SearchFromEnd true. So it will start searching from end and once get index of first "/" from end. You can get substring from 0 to index of "/". This substring will be available as a new column in the aggregate and you can use directly any where.


Hi Vikas! That's exactly what I intended to do. However, it seems like the functions for new attributes are limited. It only lets me add 2 arguments for the Index function.

Even if my 3rd argument is something else, it just tells me "Too many arguments specified for function Index".

This seems silly, since on the right side it is literally telling me that I can have 3 arguments, but on the left side it shows Index(t, search) and only lets me add 2.

Hi Catarina ,

You are right In aggregate it has limited functions to use. But you can remove the duplicate value while binding to the table cell  and drop down some thing like below 

Hi Devendra, thanks for your help, I think I can work with that for my table, but for my dropdown I still have some issues.

My dropdown is getting a list of options from a DataAction, which has a SQL query that gets values from my aggregate column (the one that has the repeated information, with all the slashes and everything).

After my SQL returns a list, is there any way where I can apply a regex function or some kind of function to my list to remove all that duplicate value?

Hi Catarina,

You can iterate through the list returned by SQL and then you can use ListAppend to create a new list with the remove duplicate text some thing like below

Or may be you can do it in SQL itself by below expression 

left({Questions}.[Question], len({Questions}.[Question]) - charindex('/', reverse({Questions}.[Question]) + '/'))

I think in this case, you can do one trick. Create one server action with input parameter String and output parameter as substring (for required string). Set this server action as Function true. Then use this server action in  your aggregate directly.

In above server action (Action 3). you can write all the logic of substring and index and get final result for aggregate column value.


Hi Vikas,

Everything was going just fine until I got this šŸ˜„

Hello Devendra, what you suggested worked for me, so:

* Replace the column in my table with an expression

* Change my DataAction that generates a list for my dropdown: change the SQL expression

Thank you very much for your help.

For this case, we can use data action instead of aggregate to fetch the list of printers. Once list get fetched, can assign complete list of printers to the output parameter of data action of type List (structure (printer entity, 1 attribute to hold the printer value) using append All method. In that append all method while mapping can do that string operation part.

Then use this output list of data action as per your requirement anywhere in the screen.


Hi Catarina,

My recommendation is to create a single server action that calls your aggregate, processes logic on the aggregate's output, and then returns the compound type (Structure).

Use this server action inside your Screen Data Action, and the output of the data action can be bound to a dropdown or a table.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.