Virtual vs Non-Virtual Columns with Ref

I have two tables for tracking tool inventory/locations. “Tool Transfer” is filled out as a form to track when and to where the tool is being transferred. I then have a “Current Tool Locations” table where I want to just show where each tool is currently.

Using several virtual columns, I have been able to get the most recent transfer date and the current location of the tool. The problem is, I would really like to have this information in my Google Sheet so the data can be viewed there. Whenever I have tried to use non-virtual columns with the same formulas, it shows the correct information in “test” but doesn’t show in the app (or in my spreadsheet).
Is it possible to have non-virtual columns and still have this work so that my data can be reflected in my spreadsheet?

Here’s the formulas for the columns I would like to be non-virtual:

Most Recent Transfer:
MAXROW(Tool Transfer, Timestamp, [Computed Tool ID] = [Tool ID])

Current Location:
LOOKUP([Most Recent], “Tool Transfer”, “Timestamp”, “Transferring to”)

The problem with your idea is this… your data is changed in another table and if you want to read that with a normal column, it won’t work because app formulas with the normal column will be recalculated only, when you open and save the record. If you would like to see this happening, you would need to open every “Tool” record and save them somehow… and you probably don’t want to do that.

What you could do… every time when you add a new “Tool Transfer”, you could update the “Tool” itself with an Event action. Check this sample app “EventAction” from this link… https://www.appsheet.com/portfolio/531778

3 Likes

very useful examples, thanks

I was able to get this to work! Thanks so much.

1 Like

You’re welcome