Table(X) column to show the latest status based on timestamp from table(Y)

[junjie8709]

Hi, 

Need some assistance here.  I'm creating an apps for my front user to report faulty equipment to maintenance team and both tables are dynamic where users will update accordingly.

I'm currently struck with below.

• A Reports[Status] - Update this column with latest Maintenance Q[Work Status] based on Maintenance Q[Date/Time]
• B  Related Maintenance Q to show the latest entry timestamp

Cant get my head around what formula and how?  Max(Select)) 

Appreciate any guidance. Thank you in advance

UX card view for Reports data

Updates, Adds & DeletesUpdates, Adds & Deletes

 Updates, Adds & DeletesUpdates, Adds & Deletes

[mateo]

Hi @junjie8709 

I am not sure whether I understand clearly but in one of my apps I had to create something similar I guess.

What about creating new ref_type virtual column where you will be storing the latest row ID string using MAXROW() formula and then use it to retrieve needed values by dereferencing [latest_rowID].[needed_value]?

This is how statuses under different operations are being handled in my app. Thanks to this I have whole history of status change.

If I am wrong please correct me guys! 😊

[junjie8709]

Hi Mateo,

Yes you are spot on changing the statuses regards to the same operation item. 

Did you use MAXROW(SELECT())? 

[mateo]

Take a look at MAXROW() formula syntax

[junjie8709]

Hi Mateo,

I managed to get MAXROW() but not sure how to dereference in table reports- column status. 

 

[mateo · Accepted solution]

Hi @junjie8709,
Maybe try to place [Latest_Maint_ID] in Reports table and then [Latest_Maint_ID].[Work Status] dereference expression in Reports [Status] app formula (but make it virtual to always be calculated). However, if you need this [Status] value to be stored in your database, you can create a [Trigger] column to "refresh" the record manually or a kind of automation using actions to make it happen automatically. Hope it helps!

[junjie8709]

Hi Mateo, 

Great! Works like magic now.  Thank you 🙂

[mateo]

I am glad it helped! You're welcome 😀