Most recent referenced value

KRejko · 06-03-2020 09:39 AM

Hello,

I am trying to get the most recent value referenced from my current record. Currently, I am using the formula:

ANY(
     TOP(
          SELECT(
               [Related Invoices][Status], 
               [Due Date]=MAX([Related Invoices][Due Date]), 
          ),
          1
     )
)

I have used a nearly identical formula elsewhere in the same app and this approach has worked successfully. However, in this case, the formula returns an empty value for the [Related Invoices][Status].

To try and debug this I have pulled out the ‘MAX’ part of the formula into its own virtual column and can validate that this is picking up the most recent value.

Also, this value returned from the max function does match the most recent invoice date in the invoice table directly above:
—image redacted due to new user limitations—

Thinking maybe this was a formatting error, I have also tried wrapping these dates in a DATE() function, but this did not solve the issue.

  DATE([Due Date])=DATE(MAX([Related Invoices][Due Date]))

I have also seen others use the MAXROW() function to achieve a similar outcome. However, I am not sure that this works in my case due to needing records related to the current invoice.

Any help or suggestions on how to fix or debug this issue would be greatly appreciated. Thanks in advance!

Steve

For debugging, try creating a virtual column that uses just the SELECT() expression, without TOP() and ANY(). What does that give?

KRejko

Thanks for the quick response, Steve.

Unfortunatly, removing the TOP() and ANY() functions still yeilds an empty response.

Steve

Then either (a) the Related Invoices column is empty when the expression is evaluated; or (b) the invoice with the newest due date has a blank Status column value. That’s all I can think of.

KRejko

To try and validate that the issue was related to the date condition, I tried removing it from the select entirely and replaced the second parameter with TRUE:

 SELECT(
           [Related Invoices][Status], 
           TRUE
      )

This did validate that the result was not empty by producing a comma-separated status list:

Although what’s interesting is that the statuses in this list don’t match the list of statuses in the [Related Invoices] table on the same record:
— See next post for [Related Invoices] table screenshot —

How can this be possible? If [Related Invoices] contains two different lists of statuses, that would certainly explain why the max date doesn’t match. Any thoughts as to what might be going on here?

Steve

Wow! I’m certainly confused by this, too! Unfortunately, I think this will take looking at your app, and I don’t have permission to do that. I’m going to have to refer you to support@appsheet.com. Good luck!

KRejko

Here’s the list of [Related Invoices]:

KRejko

Thanks, Steve. I will forward this post to the support team.

To provide supplimental information, I have added a new SELECT() function to report [Due Date] instead of [Status]:

     SELECT(
           [Related Invoices][Due Date], 
           TRUE
      )

This was an attempt to try and confirm that the records were, in fact, different. However, while the statuses are different from the [Related Invoices] table, the due dates do seem to match:

Putting the weirdness with the statuses aside for a moment, if the dates are the same and both the table and select statements return 23 records then it seems to confirm that at least they are both using the same data source. However, if these records are the same, then this also begs the original question: why does the SELECT() condition [Due Date]=MAX([Related Invoices][Due Date]) return an empty result?

tvinci

https://www.appsheet.com/samples/Initialize-forms-with-most-recent-value?appGuidString=18b39921-b8a8...

KRejko

Thank you, Tiffany. I have seen this demo. However, this project uses the MAXROW() function which best I could tell can only point to a table, not a set of related records.

KRejko

I do think I found an acceptable workaround, although it requires two virtual columns instead of one.

Instead of using the MAX() function with date comarison to find the most recent date, instead I used the ORDERBY() function to get the most recent invoice which then filters out all others but the most recent with TOP() and ANY():

ANY(
     TOP(
          ORDERBY(
               [Related Invoices],
               [Due Date],
               TRUE
          ),
          1
     )
)

This will get the most recent ref, so the second virtual column pulls the status out of this value:

 [Most Recent Invoice][Status]