Dereferences don't update on edit?

Hello folks.

Question; Do deferences only only when adding a new row?

I have reference and deferences sep for several of my tables.

Here’s what I have in my app and how it looks in my tables.

I set up my sheet to record the referenced and the dereferenced value.

If a add a new row, the dereference is automatically added, as expected.

However, if I change a reference field in a row already in a table, the dereferenced value is not changed to its corresponding one.

Can someone help me figure out what I’m doing wrong, or Is this expected?

I think the answer based on what you have shown is that it is the expected behavior. Here’s why.

Looking at your table image, it looks like you have the deferences in the “Initial Value” property of the columns. This means the column value is only set when the row is initially created. It will not change with rows edits unless it is changed manually by the user.

If you are not intending these to be user edited columns and want them to change along with the referenced value, then you’ll want to move them to the “App Formula” property.

An observation: it looks like you have a lot of “normal” columns that are saving copies of the dereferenced values. Is there a reason you don’t want to use Virtual Columns? Virtual Columns ARE updated anytime the reference column changes and are re-evaluated when a Sync is performed. More importantly you would not need all of the extra columns in your datasource - meaning it will be cleaner…and smaller.

@WillowMobileSystems
The dereference formulas are in the formula column in fact. Nothing is in the “Initial Value” column(I would’ve hated myself if the answer was that simple).

Re: Observation. I have many reasons why I’m recording them actually.

The main one is I need to sort using the “Member” name sometimes, so i need the actual name for reference.

Second is I like to review the spreadsheet periodically, to filter or make bulk edits. Having the actual values makes this much easier.

Virtual columns can not enter data in the sheet, hence why I’m using this setup.

Trust me, i would love for this to sync as faster than it is right now (it’s about 15 seconds), but this is a necessary evil for what I need.

Try having a VC for the deref and a physical column that equals the VC.
I’m on my phone now otherwise I would have tested before posting.

1 Like

@AlexM Interesting idea Matt,

But wont it add to the sync time?

I have about 4-5 dereferences each across my 20 tables. So I’m reluctant to rely on this method if it will increase sync time.

I coded a function in Google Apps script to catch mismatched entries, so I’ll be ok if it can’t be solved with Appsheet. But i was just curious if this is actually how dereferences are supposed to work, which feels limiting to me.

But I’ll give your suggestion a shot, see what happens

1 Like

Let us know.
It’s more like a test idea to see how it behaves.
Something tells me it should do it.

Only VC columns update immediately.
If you have to use “real” columns, you have to Add an Action at the End of the Form.
image
This Action has to edit the row with your deref formulas. For example increase the value of a [counter] column. With this, the formulas would get recalculated.

1 Like

@Mic_L_Angelo The difference between normal and virtual columns… only virtual column is updated when you sync the app. App formula in normal column is not recalculated unless you open it or change the value with an action as @Fabian proposed.

3 Likes

@Aleksi @Fabian @AlexM @WillowMobileSystems

Thanks for all the answers fellas. I suspected they don’t update dynamically but wanted to verify.

Unfortunately, I’m using about 40 - 50 references across my app. I don’t think setting up the action for each dereference is practical.

The Virtual column workaround @AlexM suggestion does work. But I’m worried about potential performance issues to cover all those deferences.

So Google App Scripts it is! Thanks, yall

2 Likes