Platform
Learn
Community
Support
Sign up
or
Log in
Profile
messages
Messages
logout
Logout
Platform
Home
Downloads
IPP
Licensing
Project Sizing
Learn
Training
Documentation
Evaluation Guide
Community
Jobs
Home
Forums
Forge
Ideas
Badges
Members
Support
Partner Guide
Resources Library
Opportunities
Account Management
Technical Proof
Sign up
or
Log in
Home
Jobs
Forums
Forge
Ideas
Members
Badges
Dear ,
What would improve your OutSystems Community experience? Let us know by taking this 2minute survey.
Pick up the survey
Community
›
Forums
›
Technology & Integration
Sorting an Aggregate with business rules
Aggregate
Sorting
Community
›
Forums
›
Technology & Integration
Sorting an Aggregate with business rules
Aggregate
Sorting
New Post
New Post
Martijn Habraken
Posted on 20150911
Martijn Habraken
Rank: #241
Posted on 20150911
Hi all,
I have an aggregate that gets products. One of the attributes of the products is the amount.
In my screen I have a parameter of the requested amount. The following thing I want to do:
Sort the result of the aggregate to:
 First show all the products with amount is equal to the requested amount.
 Secondly show all the products which have an amount that’s equal to n*requested amount (n is an integer)
 At last all other amount sort from small too big.
An Example:
I search for a particular kind of nail and I want a box of 10. This should be the result:
Nail in a box of 5 (should come 3 in the list)
Nail in a box of 10 (should come 1 in the list)
Nail in a box of 20 (should come 2 in the list)
Nail in a box of 25 (should come 4 in the list)
Nail in a box of 35 (should come 5 in the list)
How can I do this?
Curt Raddatz
Posted on 20150911
Curt Raddatz
Rank: #123
Posted on 20150911
Solution
You'll have to use an advanced query using the Union syntax, something like the following.
SELECT AMT
FROM YOURENTITY
WHERE AMT = {PARAM}
UNION
SELECT AMT
FROM YOURENTITY
WHERE (AMT / {PARAM} = 0) AND (AMT NOT = {PARAM})
UNION
SELECT AMT
FROM YOURENTITY
WHERE NOT (AMT / {PARAM} = 0) AND (AMT NOT = {PARAM}))
Note that the syntax might not be 100% correct as I didn't test it. I would try each Select separately to make sure it works as expected and then combine them using the Union syntax as shown.
Hope this helps,
Curt
Solution
leonardo.fernandes
Posted on 20150911
leonardo.fernandes
Rank: #20
Posted on 20150911
Solution
Hi Martijn.
Let's rewrite your business logic, but speaking a little math lingo.
Your products have an
Amount
field, and the aggregate will run in a context where you have a
RequestedAmount
. Both are integers greater or equal to zero.

First show all the products with amount is equal to the requested amount.
This is a case which
Mod(
Amount,
RequestedAmount) = 0
, and
Amount
=
RequestedAmount
.

Secondly show all the products which have an amount that’s equal to n*requested amount (n is an integer).
In this case,
Mod(
Amount,
RequestedAmount) = 0
and
Amount
>
RequestedAmount
.

At last all other amount sort from small too big.
These are the cases where
Mod(
Amount,
RequestedAmount) > 0
.
Now, in a first attempt you could query all products, and order by
Mod(
Amount,
RequestedAmount)
*. This will make the first two cases appear on top, and the third case below. But it won't distinguish between the first two cases.
You can add a second order by
Amount
. It will make the products with Amount equal to RequestedAmount to appear on top, and those with greater amounts below, thus distinguishing the first two cases.
This is just a start, though. To get your requirements exactly right, you will need to solve the following situations:
 Products whose Amount is zero will be the first results on the list, and from your requirements they should be on the top of the third set.
 Products on the third set will be sorted first by
Mod(
Amount,
RequestedAmount)
. This will mean, in your example, that a box with 13 nails will appear first than a box with 5 nails.
If you cannot relax your requirements, get back to me, and we will make things a little more complex.

* To order by a function, you'll need to
add a calculated column
, and then order by this new column.
Solution
Martijn Habraken
Posted on 20150911
Martijn Habraken
Rank: #241
Posted on 20150911
Solution
Hi Leonardo,
When I try to do the
Mod(Amount, RequestedAmount)
in a calculated column I get the following message:
'mod' function cannot be executed in the database, so it can't receive any attributes from the aggregate as parameter.
The way you described it would match my requirements though.
Solution
leonardo.fernandes
Posted on 20150911
leonardo.fernandes
Rank: #20
Posted on 20150911
Solution
Oh well, that goes for not testing it! You will have to resort to an advanced query then. In this case the solution given by Curt seems more appropriate and easier to understand.
Solution
Login to reply
New Post
Available Forums
Technology & Integration
News and Announcements
Forge Discussions
Meta
Community Quick Guides
Usability
Installation Troubleshooting
Personal Environment Troubleshooting
Forum Notifications
Email Digest Settings
Loading...
(hidden) New Badge Popup