Sort by Calculated Value

Continuing the discussion from Toolbox Plugin: Sort Repeating Group by Expression's Value:

Has anybody cracked this yet? I need to sort results of an RG based on a calculated value.

Specifically,

Data Types:

Contact (name, etc.)
Transaction (amount, Contact)

I need to display a list of Contacts, sorted by the :sum of amounts in a list of Transactions.

I can’t figure out a workaround here.

Also, @emmanuel Is a solution to this up for sponsorship? Group by, with sort?

Could really use some help here – aggregation of data and displaying top results is critical to the app.

Thanks All!

–Ken

3 Likes

The only workaround I know is to add a number field to the Contact table. Anytime a Transaction is created, the transaction amount is added to that number field.

Also, in the Contact table, you should add a “List of Transactions” field. This way, you don’t have to search through every Transaction just to get one specific Contact’s Transactions.

Hope this helps,
Daniel

1 Like

Thanks @dbevan. I may be stuck with that, although it just kills me. Just too easy for things to get out of synch. I’ll have to build some kind of refresh that spins through everything and updates the values.

4 Likes

@mebeingken Yes, this is killing me too, as mine is out of synch now, and is providing the wrong information. I can’t believe this sorting by calculated result is not a built-in function of a repeating group.

4 Likes

The new :group by modifier gets us one step closer…I have to assume that sort will be added.

I think you can achieve this by “Search for transactions” “:grouped by Contact” “Aggregation Sum”. Then you can add the “:sorted by” and the aggregation should show up as a sort function. Let me know if this work!

4 Likes

Hey @rosshelton Yes, it is now supported!

You could probably use getstream.com to store transaction activity and create a feed out of it. Seems like it’s a really powerful solution and can address some of the most complicated types of sorting for RGs, including real-time, aggregated, ranked, and personalized feeds.

2 Likes

Is there a working way to achieve this yet?

I have an RG that calculates prices of Things on the database based on other factors that are inputted by the user on the page itself.

I’m trying to sort those calculated values from low price to high price within the repeating group, but the values are not ‘fixed’ values in the database, they are calculated based on the users inputs.

1 Like

I think so. Use a second repeating group. It’s data source is the 1st repeating group (which you can hide by unchecking this is visible on page load). Then add :sorted by expression on the end.

3 Likes

@anil check this out.

Hi Robert, Could you explain this please? I created the 2nd repeating group. I made the list of items in the 1st repeating group the source of the 2nd repeating group. Then I added :sorted at the end. I was hoping to see the Expression in the “Sort By” drop-down of sort options. I did not. I’m I missing a step?

No you are not. I re-read the question and I think my initial proposed solution is wrong.

However, I think there are 2 possible solutions.

#1 BDK plugin - i used it for a similar RG sorting/calculation problem, and it worked

#2 - A clunkier workaround would be to have calculated values save to the database based on a change of the input value (your variable), and have the RG sort based on these values.

1 Like

Thank you Robert. #2 looks intriguing. How would you implement it? Here’s how I attempted it:
(1) Make the sort button change the state of the RG before proceeding to sort the RG
(2) Add a condition to the Expression element such that When the state of the RG is a certain input, the condition evaluates an expression that tries to assign the value of the Expression element in the RG to the sort field.

Problem is in (2), the expression in the condition reads like “sort field value” is “Expression value”. I used ‘is’ because ‘=’ is not available. As such, the value assignment didn’t really work.

How would you implement it?

I installed the Plugin and was able to extract the results of the Expression element in the RG as a list. I would like to store the list in the sort field of the RG. How do I do this? If that’s not the way to approach it, how did you use the Plugin for sorting?

Thank you Robert. #2 looks intriguing. How would you implement it? Here’s how I attempted it:
(1) Make the sort button change the state of the RG before proceeding to sort the RG
(2) Add a condition to the Expression element such that When the state of the RG is a certain input, the condition evaluates an expression that tries to assign the value of the Expression element in the RG to the sort field.

Problem is in (2), the expression in the condition reads like “sort field value” is “Expression value”. I used ‘is’ because ‘=’ is not available. As such, the value assignment didn’t really work.

How would you implement it?

It’s an old project, but I remember using the plugin to extract the calculated values. Don’t think I sorted them, but it seems if it is possible to extract the calculated values from the RG, sorting should also be possible…perhaps with a step in between.

So I made a test…shopping cart style…to see if this could work. The key is to create a “line item” data type that holds the item, quantity, and total in the same record. Then you can sort by the total.

There are 3 datatypes:

  1. Widget (item) - fields: Name (text) and Price (number)
  2. Line Item - fields: Widget (widget), Quantity (number), Total (number), and Purchase Order (Purchase Order)
  3. Purchase Order - fields: Line Item (list of line items), PO Number (number)

The workflow must calculate the total in the database so it can be sorted.

here is a link to the editor, so you can see how it works:

And it does work. The repeating group updates automatically based on the total amount (sorted by this in the do a search for)

Thank you Robert. Really appreciate your work on this. The calculation occurs when the RG is loaded, so real time somewhat. That’s why Expressions are optimal. I agree - sorting with the extracted calculated values require an intermediate step. I think that step involves storing the extracted values (list) to the database - a field of the data in the RG, for the sort function. Would appreciate any idea on doing the storing

I tried “Make Changes to Things”. I was able to reference the extracted list but could not make index number variable to update the data points of the RG field with the corresponding member of the extracted list