Change value in multiple sheets? Have a global variable column

[TY_Car]

My data source is a google sheet with multiple tabs, each tab has a table which has its own slice, all the tables contain a column called “Next Area”. This column is a Enum type, a user would choose the next area that the product will go to, and this is displayed in the deck.

The issue is that this change only is shown in one slice, so if the product is in another slice this information is not shown. I need to be able to update this column in every sheet once it has changed in one slice so we know where the product is.

I tried to use an action however this doesn’t update the location automatically (i.e. without user intervention). Is there a way to do it?

[Aurelien]

Hi @TY_Car

Welcome to the community !

Is it possible to use virtual column for your needs ? As it is calculated on the fly, there won’t be any need to create an action just for update purpose.

For reference:

help.appsheet.com

Virtual Columns

Check out how you can use virtual columns to add more information to your app.

[TY_Car]

How would that work?

[Aurelien]

I invite you to read the document in my previous post.

In short:

• you remove your real column from the sheets
• regenerate structure (Data/Columns/yourTable ==> “Regenerate structure”)
• click on “add a virtual column”, set the name you had before, and set an app formula in order to get the information you need.

[TY_Car]

Sorry I may have not explained the problem very well. The new location has to be inputted by a user from a list. To update the information a user shouldn’t do anything else. That is why I wasn’t able to do it with an action as it required an action by a user to update.

From my understanding the virtual column can only be a formula and not a Enum, so I don’t this it would work

[Aurelien]

You explained it very well, I misunderstood.

TY_Car:

From my understanding the virtual column can only be a formula and not a Enum, so I don’t this it would work

Correct.

What I still don’t get, is:

• why do you use various table and various slices ?

Slices, based on the same table, are each just a part of that table.
Someone modifying an information (status…or area) will impact every slice, provided these are based on the same table.

Can you tell us more about your table/slice structure ?

[TY_Car]

The product goes through multiple processes, so like inspections, repairs, quality control and so on. So each table and slice corresponds to a process. The only thing that the tables have in common is the product ID

[Aurelien]

OK.

Just asking: what about an action that will update product location instead ?
This way, you retrieve it in each slice with a virtual column, with the expression:
[Product ID].[Location]

[TY_Car]

I’m not sure if I understand. The product location would be updated by an action in every slice? So the user will have not input for it?

[Aurelien]

Well, there are always various ways to achieve the same thing, but I was thinking about something like this:

Did you know....that there IS a pop-up Form available using the INPUT() function? Tips & Tricks 💡

Based on a post by @Joachim_Mund … …I just learned that there is a way to present a pop-up Form to collect additional input from a user. It is with the INPUT() function. I never knew this existed and I don’t ever recall it talked about in any of the posts I’ve read (I don’t read everything ). I thought there is probably a good chance others weren’t aware of it either so I thought I’d pass it along. Basic Usage Create a “Set the values” action specifying the target field…

[TY_Car]

This may work, but how can I implement it so that it updates all the slices at once?

[Aurelien]

This is it: it isn’t about updating all slices, but just about updating one value in the table Product.
And then, use a virtual column in every slice to read the value currently stored in the table Product, through one VC per table.

I think I’m facing an issue due to barrier language, my explanation doesn’t seem to make sense.

I allow myself to call on @Steve, @Marc_Dillon, @Suvrutt_Gurjar on rescue: maybe my idea is not correct, or I don’t succeed into explaining what I have in mind properly

[Marc_Dillon]

If you’re keeping a record of the same “thing” in multiple spots, then I would say you need to rethink your whole data structure.

To solve your current issue, you’ll need to execute a group of “reference actions” (Action of type “execute an action on a set of rows”), one for each other Table, where in the “referenced rows” you’ll provide a FILTER() expression that retrieves the matching record from the particular Table.

You’ll also need an Action on each Table that sets the appropriate values. I’d recommend using INPUT() for each of these Actions, so you can pass the origin value through each reference Action.

You can execute this Group with a Bot, or with a Form Save.

As Aurelien said directly above, this has nothing to do with Slices.

But again, I highly recommend re-thinking your data structure.

[Aurelien]

Thanks @Marc_Dillon

[TY_Car]

Thanks @Marc_Dillon and @Aurelien for your suggestions