[Tuesday Topic] Using Mode Aggregation

Office Hours
1

This came up in Office Hours this morning with @jmlowden, @Amit, and Frank, and I thought it might be helpful to share!

Frank had a use case where an operator can be working on multiple work orders at once (for example, they run out of parts on one WO and then move to a new one) but each operator will only be working on one Part number at one time. He is using a User Table to tie a Work Order number and Part number to a user when they log in. He wanted to be able to then have the Work Order number show up in a different table once an operator finishes the assembly.

Here is how we did this:

  1. On the User Table, create a query to filter User and Part Number by app input, sort by latest created, and a limit of 1.

  2. Then do an aggregation on top of this using the “Mode” calculation on Work Order number. This allows us to pull the data from the query into an aggregation we can use in our app.

Note: you don’t want to use “Unique Values” aggregation here because that will return an variable array data type. When you are looking for just 1 single value, using mode is the way to go!

  1. Last, add the query and aggregation to your app and then you can use this aggregation in a trigger to add the Work Order number data to the new table!
2 Likes
2

The MODE aggregation a lookup function is one of my favorite hacks, and this is a great use-case for it.

I’ve documented some of the use-cases and solutions in this thread awhile back, and @thorsten.langner added a great alternative approach as well.

Here’s a quick video I made to show what this looks like when you try to build it in your instance:

If folks have other approaches or use-cases for the MODE aggregation I’d love to hear them!

2 Likes
3

Hi Beth,
Thank you for the session yesterday, I really didn’t know about the mode method in aggregations, only used Unique values each time, stored result in array and pop from array this single value, so, now I fixed one use case in one of my apps and it works great.
Beside that, thank you for the help with deleting unused attributes in machine, I didn’t notice this window until now.
And @jmlowden, I hope your use case of empty string is solved also, if not, please share the correct solution for that.
Thank you,
Amit Berku

1 Like