Can App "A" reference/ lookup a Virtual column from App "B"

[LLD]

Can an App lookup values from another app? 

I have Virtual columns in all the Apps I have created. Can I lookup/ reference the virtual column(s) created in one App, from another App?

For example, I have an "Employees" app that includes the "On Rolls" status of contractor teams.. i.e. persons who work with us per assignment. Every time their assignment ends their status is set to Off and back to On when they start a new one. These transactions are recorded in a separate table. From this table, the virtual column LOOKsUP the MAX (last entry - date wise) according to their ID code and returns the status as Off or On.

I have another "Stores" app through which spare parts, consumables and tools are issued to contractor teams. Only those who's current status is On will be issue stuff from Stores.

The Employees app has its own tables, accessible to select users. Similarly with the Stores app. While I can add/ share the base table of the Employee app with the Stores app, I am not able to reference any Virtual columns.

Am I missing something?

[1minManager · Accepted solution]

AFAIK you can use the Appsheet API to read virtual columns.  Though if I was doing this (as I'm NOT really good with the API) I'd simply have both Apps reading the same table.  Then to recreate [On Rolls] in the second app you have 2 options:

1. The simplest is simply to duplicate the virtual column in this App.  Though this may require you to add multiple tables to be able to do this
2. Or in the first app consider making [On Rolls] a standard spreadsheet column.  Then simply put the formula in as the App Formula, so it gets recalculated every time you edit that record.

If no2 above doesn't work right because the virtual column was being updated by a change in a different table, then you'll need a further step.  So lets say you need Employee[On Rolls] to recalculate every time a users adds to or edits the Stores table.  Create a column called Employee[ForceUpdate] as a DateTime column.  Then create an  action that writes Now() to to this column.  Next you'll need to create a "Execute an action on a set of rows" type action to be able to call this new action from the Stores table.  Finally in the Stores_Form view in the Event Actions option choose this second Action.

In simple terms then, when you create or edit a stores record, this will write Now() to any relevant Employee[ForceUpdate] columns.  In writing this timestamp it will force Employee[On Rolls] to be recalculated.

Simon@1minManager.com

[1minManager · Accepted solution]

AFAIK you can use the Appsheet API to read virtual columns.  Though if I was doing this (as I'm NOT really good with the API) I'd simply have both Apps reading the same table.  Then to recreate [On Rolls] in the second app you have 2 options:

1. The simplest is simply to duplicate the virtual column in this App.  Though this may require you to add multiple tables to be able to do this
2. Or in the first app consider making [On Rolls] a standard spreadsheet column.  Then simply put the formula in as the App Formula, so it gets recalculated every time you edit that record.

If no2 above doesn't work right because the virtual column was being updated by a change in a different table, then you'll need a further step.  So lets say you need Employee[On Rolls] to recalculate every time a users adds to or edits the Stores table.  Create a column called Employee[ForceUpdate] as a DateTime column.  Then create an  action that writes Now() to to this column.  Next you'll need to create a "Execute an action on a set of rows" type action to be able to call this new action from the Stores table.  Finally in the Stores_Form view in the Event Actions option choose this second Action.

In simple terms then, when you create or edit a stores record, this will write Now() to any relevant Employee[ForceUpdate] columns.  In writing this timestamp it will force Employee[On Rolls] to be recalculated.

Simon@1minManager.com

[LLD · Accepted solution]

Hello Simon and thank you for your really detailed answer.. let me try out what you say. Regards,