Filtering a table based on a combination of most current date and status

Hi community,

We've got a requirement we haven't yet resolved. The concept is use of a field pairing on "effective date" and "effective status" to determine whether a row of data can be used currently.

Our working model is a Hello World application. It's based on a Phrase Table. Phrase Table has four fields, with three representing keys.

Code is the language code, a key
Effective Date is a key; it is the date this row of data is effective
Effective Status is a key; it is the status of this row of data (Active or Inactive) as of the Effective Date
Phrase is text.

The business logic says that you can't go back and update a row once it is in the table. This way rows can be used for past transactions representing active values as of the date of that transaction. To make a change, one can insert a new row for the Code, with a new Effective Date and Active Effective Status plus a new Phrase, meaning on that date the new phrase will become available, OR insert a new row for the Code with a new Effective Date, Effective Status of Inactive, and the current Phrase, essentially indicating on this new Effective Date there is no available row for use.

Use English as a reference to illustrate this requirement:

On Jan 1, the phrase "Hello, World" is available for use, as it has an Active status
On Jun 1, the phrase "Hi World" is available for use as it has Active status, and the prior "Hello World" is no longer available.
On Jul 2, there should be no English phrase available for use as the latest dated row is Inactive.

We're confident we can make various pages to effect this requirement, but we're stuck getting the data filtered the right way. In a traditional database, we'd write a view of the Phrase Table, which would find the most current row - by Effective Date - for each code, then only include from these the rows that have Effective Status of Active.

We've built a table with some formulas - Phrase_Summary. It has three fields: Code, Count, and Phrase.

This table has a formula on field Count: =filter(Phrase_Table,"Phrase_Table[Code]=Phrase_Summary[Code] AND Phrase_Table[Effective_Status]=%","Active")

It also has a formula on field Phrase: =FINDROW((FILTER(Phrase_Summary[Count],"ORDER BY Phrase_Table[Code] DESC, Phrase_Table[Effective_Date] DESC")))[Phrase]

Unfortunately, the logic gets us all the Active Rows and then gets us the last of these by date. We end up showing two rows - English and Tamil - that should not be shown per the requirement:

Here's a spreadsheet view showing the two tables side by side, plus indications of which rows should appear and which rows do appear:

{Sorry I can't show more screens, still too new a user to add more}

We're using Phrase_Summary as our "view" and I think if we could reverse the logic to first filter for the latest row per code by date, then find the rows that have an active status, we'd be good. I haven't been able to make that happen yet.

Help?

Hi @DevC-79e7,

Thank you for your question!

Here is how you can create your request in Honeycode:


Formula to Show all Active Rows

=filter(Phrase_Table,"Phrase_Table[Code]=THISROW()[Code] AND Phrase_Table[Effective_Status]=%","Active")

Formula to Show All Active Rows sorted by Effective Date

=filter(Phrase_Table,"Phrase_Table[Code]=THISROW()[Code] AND Phrase_Table[Effective_Status]=% ORDER BY Phrase_Table[Effective_Date] DESC","Active")

Formula to Show Latest translation with Active status and has Latest Effective Date (Final Formula)

=FINDROW(Phrase_Table,"Phrase_Table[Code]=THISROW()[Code] AND Phrase_Table[Effective_Status]=% ORDER BY Phrase_Table[Effective_Date] DESC","Active")[Phrase]

Make the formula handle error condition

=IFERROR(FINDROW(Phrase_Table,"Phrase_Table[Code]=THISROW()[Code] AND Phrase_Table[Effective_Status]=% ORDER BY Phrase_Table[Effective_Date] DESC","Active")[Phrase],"N/A")

This is how it will reflect in the app:

Here is an article with more information if needed: Filter()

I hope this answers your question! If not please provide some additional screenshots from within Honeycode.

We are also hosting office hours, so if you would like to sign up you can here.

Thank you!
Evelyn