How to Sort or Filter List by Custom Column

Hello Honeycode Community!

Is there a way to filter and sort a column list by a custom column that's not in the tables?

To explain my specific situation:

I have a table named Students, and I am trying to create a column list based on it. The column list is to include a column for the number of lectures attended without payment. For the sake of explanation, let's suppose this column is called Must Invoice Count.

The issue is, the formula involved in the computation of this Must Invoice Count value is rather complex, and I was hoping to have it execute at view-time, rather than have it as a proxy column in the table, which slows down any writes to the table in other sections of the app.

I have succeeded in creating this column list, but only by including ALL rows in the Students table in the list, which is not ideal.

Is there a way to design such a column list such that only students with a Must Invoice Count > 0 are shown? It would also be a nice bonus to have this column list sort by this custom column, Must Invoice Count.

Would really appreciate all ideas you can offer!

P.S. I should note that I have already tried FILTER(Students, "% > 0", *{insert formula for Must Invoice Count}*), which doesn't seem to work.

Hello @Eric, so if I understand correctly you want to show only students with a score of 0 or do you need an extra formula? Can you take a screenshot with what you want to understand better?
Note that my post is translated, sorry if it contains grammatical errors

Hey @Stph-1188 ! Thanks for the reply :))

Basically, I want to show all students with a Must Issue column greater than 0. Must Issue is evaluated as the number of rows in the filter of another table containing the attendance of students.

I found a work-around to achieve this without significant processing delays, which is to implement a manual 'Refresh' button which evaluates these values and stores them in the table as plain numbers. This of course, isn't really ideal from a user's perspective.

Hello @Eric
Try this:
= FILTER (students, "students [Must Invoice Count] =% AND students [Must Invoice Count] =% ORDER BY student [Must Invoice Count]", "> 0") This will also sort them for you in order
I do not know the name of your table I deduced from what I saw, do not hesitate to give a screenshot of the table in question if it does not work
Note that my post is translated, sorry if it contains grammatical errors

Hi @Eric-1086 :honeybee:

For your case, you can use FILTERDIFF() to filter out the difference between all Students table rows, and the Student table rows that are 0. The formula would look something like this:

=FILTERDIFF(FILTER(Students),FILTER(Students, "Students[Must Invoice Count]=0"))

Then if you wish to also add a sorting order, you can use ORDER BY to achieve this (this idea was also in @Stph-1188 's reply). This is how it would look if you added ORDER BY to my example formula above to sort the order of invoice count in a descending order:

=FILTERDIFF(FILTER(Students, "ORDER BY [Must Invoice Count] DESC"),FILTER(Students, "Students[Must Invoice Count]=0"))

Hope that helps! :slight_smile: Let us know if we can help any further. :honey_pot:

1 Like

Hey @Stph-1188 and @Alyssa - thank you for the reply!

Both are valid formulas, and I thank you for your input. However, the core of the problem here is that I am unable to store the column, [Must Invoice Count] in my table, as that significantly slows down my app.

The value of [Must Invoice Count] must be evaluated during app execution on the frontend, rather than the backend.

1 Like

Hi @Eric :honeybee:

Could you share your formula and any other details on how the value of your [Must Invoice Column] is calculated during run-time?

Hey @Alyssa, certainly!

[Must Invoice Count] is evaluated by the following formula:

=ROWS(FILTER(IndividualLessonEntries,"IndividualLessonEntries[Student]=THISROW() AND NOT(IndividualLessonEntries[Invoiced]) AND NOT(OR(IndividualLessonEntries[Generated Completed Datetime]=0,ISBLANK(IndividualLessonEntries[Generated Completed Datetime])))"))

In words, the above formula traverses through the Individual Lesson Entries table (which has a couple thousand rows) to find the number of lesson entries which:

  1. Correspond to the student of interest
  2. Have not been invoiced yet
  3. Has been completed

Below is a sample row from the table:

Thank you for your help!

Hi @Eric :honey_pot: :honeybee:

Got it, thank you for those details! You are able to calculate this in Tables, that way the app will only display the result in the column. You can also just use FILTER, since in the app it would display only the number rows as ROWS does. This is how it would look:

Here's a table of lesson entries; I set it up as you described where some students may have completed lessons, but have not yet been invoiced yet:

Now in the table of students, for the Must Invoice column, I put the following formula:

=FILTER(LessonEntries,"LessonEntries[Student]=[Name] AND LessonEntries[Invoiced]=FALSE AND NOT(ISBLANK(LessonEntries[Completion Date]))")

This is saying for each of these students in the given row, return the rows where they have completed the lesson (by looking for the completion date) but have not yet been invoiced.

Now, if you were to display the column in the app, it will return the number of invoices that need to be sent to the student.

Let me know if that helps! :slight_smile:

Thank you for the quick reply and suggestion, @Alyssa!

Like I mentioned in the original post, however, evaluating these filters in the Tables slows down any process dealing with Students significantly, once the number of LessonEntries reach the order of thousands (which is virtually every process, in the case of my main app).

That's why I was hoping to evaluate this filter in the app, rather than in the tables, but using the same formula which works in the Table doesn't seem to work in the app.

The two % parameters are identical and is equivalent to the filter formula used above by you.

Hi @Eric :honey_pot:

Thank you for sharing your formula; the slowness you are seeing may be due to the complexity of your formula. You will want to simplify the formula to remove any redundancies or calls you may not need. For example in your formula, you would not need any ROWS functions as the FILTER formula would return the same numeric result on a screen.

For additional guidance and ideas to keep in mind when writing formulas, you can check out this article from our team: Best Practices for Optimizing Performance.

The FILTER formula I provided would achieve the same goals you are looking for and will run efficiently. You can use that filter or a similar filter in either tables or in run-time (perhaps just with some slight tweaks to match any variables on the screen).

Hope that helps clarify! Let us know though if we can assist further. :slight_smile: :honeybee:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.