Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Sorting a table using a formula column

Topic Labels: Formulas
607 3
cancel
Showing results for 
Search instead for 
Did you mean: 

We have a base that we use for our accounts receivable process. We created a column that calculates how many days old the invoice is with the following formula: DATETIME_DIFF(TODAY(),{Date of Delivery},‘days’) . This takes today and subtracts the day of the invoice and returns a total number of days old. We have the base grouped by company. We want the company that has the highest number for days old (Meaning most overdue invoice) to be on the top of the base. We are not getting this result when we group by the calculation, it is only arranging within the grouped field by this calculation. How do we get the whole base to continue to sort by this field every time an order is marked as paid, and therefore removed from the base?

3 Replies 3

You’re probably better off sorting by the formula field, first by 9-1 viz. the formula field, i.e. the highest number at the top, then by company. If you’re able to post a screenshot though it might be easier to advise you here :slightly_smiling_face:

image

Here is a screenshot of the base. The column with the pink hue in the background is the formula showing how many days old, and each set is grouped by company. You can see the company on top has an invoice 133 days out, the next company has 11 days, the next 41 etc… How do I get it, that the whole company section will rearrange when orders are marked as paid, and therefore removed from the base?

Each individual order would need to be marked as “paid” for the whole company section to be removed from the base. So in the top two records showing in your screen shot, both would have to have been marked as “paid”. You will need a filter applied so that only records for which {Paid}=“No” are shown. Then anything for which {Paid} changes to “Yes” will disappear from the view.