Workflow to generate Virtual Column

[Luke_Vancleave]

Is there anyway to configure a workflow to generate a custom virtual column? So from my understanding, AppSheet generates virtual columns for things like Related Rows from Refs and other things. Is there anyway I can configure something to automatically create a virtual column and control the expression that goes into it?
Long story short, I’m wondering if I can create some kind of Asset management system where I have an Assets table, Locations table, and Movement Log table. I’d like to have a workflow that triggers every time I make a new Movement Log and then creates virtual columns on the Asset I’m moving that say what Location and how many I’m moving according to the Movement Log. Any ideas?

[Bellave_Jayaram]

Why don’t you use data change workflow and actions to achieve this with real columns?

[Luke_Vancleave]

Because I’ll be hypothetically be adding many many locations. So I already made an app that does this by having columns for 20 different generic locations that I assign information to, but I can only have a maximum of however many columns I’ve already created. I’m trying to have these columns be automatically created for every location I add.

[MultiTech]

Hey Luke. The EAM industry is thriving, and AppSheet is a perfect fit for building things of this nature.

Sounds like you might want to consider a different data-schema. Sounds like you’ve gone with what I call a “long-form” type of table (where you create a column for each entry type of thing you’re doing). One of the down-sides of this structure is that you (as the developer) have to specify how many options will be available; if you ever want to add more, you need to add more columns into the table.

You can build things another way with a parent-child relationship using two tables (or more) and this gives you the ability to have 1 interface to maintain, with an infinite expansion of items possible.

Think about something like the projects of a client: I wouldn’t create a column in the client table for each project (project 1, project 2, etc.), I create a separate table (Projects) and link the records from it to the client using a ref connection.

Here’s a video I made awhile back about this topic (how to choose your data-schema), might help.

[Luke_Vancleave]

Thank you for your reply and that is an excellent video! I’ve run into a problem with this however. So not only am I trying to track where my assets are, I’m trying to track how many I have at each one of these locations. I’m dealing in things like generators and chainsaws but also things like hammers, wrenches, other hand tools and consumables too. So there are two variables that each asset needs to track. I may have 40 hammers in my inventory but I’m only moving five to location 1 and three to location two. Do you have any advice on how to structure tracking something like that?

So In my previous app, the way I got around this was having literally something like 140 columns after my details columns on my asset table. These were split up into “Quantity added to generic location 1” “Quantity Moved from generic location 1” “Quantity Used at generic location 1” Etc. These all had SUMIF equations in them that would look over at another table called movement records that would have look at another table called locations and I had a code for 20 different locations. So if the movement record said Code for location 1, move item 25, to location 3, it would SUMIF all those columns either adding to or subtracting from the total. It works… but it’s not elegant and like I said, I’m limited to however many generic locations I make (which I would then assign my OTHER table of clients to… it’s a bit of a mess)

[MultiTech]

Absolutely this is all possible. (BTW, one of my very first solutions for a client was built from a spreadsheet using the same sort of tracking magic-math you’re using. )

I would go with the parent-child relationship structure, but include more layers than just the main ones. The benefit of references is that you can layer things and add connection tables, so I would do the following:

1. The top tier of the database would be Locations (or maybe Clients, or regions, other possibilities for higher levels as well - it’s all about grouping).
2. Then I would have an Equipment table (holding a master list of all equipment).
3. I would create a separate table to hold the Equipment assignments - this table has two references, one to the location and the other for the equipment. (On the Master equipment table, you’ll want to create a virtual column that watches these assignment records - if it’s already assigned to a location, then it should be removed from the list of available equipment (which can easily be accomplished with a slice)).

Doing something like this (creating a joining table) allows you to have an easier interface with the data.

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so that your data makes sense.

[Luke_Vancleave]

I’m playing around with an example based on what you’re describing. I have my master table of items, my table of locations, and I have another table of Items deployed in which there are ref columns for what item, what location and a column for how many I want to be moving. I’ve made a few examples of rows in the table of deployed items, but I’m wondering how I can represent the total number of an item at a site since it currently just lists all the related rows from the deployment table and not like a “Total number of this item moved to this location” number somewhere.
For example, how to I combine these two rows to be “Total AAA Batteries moved to this site”

[MultiTech]

I"m not sure where the documentation is, but you can create summary columns of related things pretty easily.

The syntax is like this: [Related Movement Logs By To Location ID][Move Quantity]

Notice there’s no dot between each part, the square brackets are right next to each other.

This tells appsheet that you want to create a list of all the values in the [Move Quantity] column for each record in the related records list. Continuing from your example, that means you’d end up with {5, 2} - if you wrap the whole formula with a SUM(), everything should come together.

SUM([Related Movement Logs By To Location ID][Move Quantity])

[Luke_Vancleave]

I got cha, but if this is to be my list of items at the site I only want there to be one row in that list that is being displayed.
So looking at that picture, for there to be just one row called “AAA Batteries” and for there to be our now combined “7” next to it?
Can I do this with a slice at all or would this be done through an expression?

I made a virtual column at the end of the movement log that sums up the number of that asset that match that location. But how to I get it to show only one row of the Item if I have more than one record of moving it?

[MultiTech]

Hmmm… more difficult. I’m curious how others would solve this, but I would try the following.

You’ll need to create a slice that holds only the most recent movement logs - individualized by location and item type.

Then you can change the formula for the Related Movement logs from the standard:
REF_ROWS(“Table”, “Reference Column”) , to using the slice instead:

REF_ROWS(“Slice”, “Reference Column”)

This switches the ref_rows from pulling rows from the original table, to pulling from the slice. Since the slice contains only the most recent records, your related movement logs will only contain the most recent.

Like I said, complicated.

[Luke_Vancleave]

Funny enough, this was actually the sticking point with what my org was last using for inventory management, was that you just couldn’t split quantities of things between locations. It’s what got my started using app sheet and while I’ve solved the problem for now it still seems to be a challenge for databases.

The other idea I was thinking about was to clone the Item row and give it a new location and a new quantity and have that do some data changes on the main row. So the Movement Log requests that an item move locations. A workflow triggers a behavior that copies that row and changes the data to now match what that Movement Log was requesting. Then I run into the same problem as before where there’s now another row that I somehow have to combine or make a slice that filters that but then I have to somehow trigger making another slice…

[MultiTech]

As I’m processing your last remark, it got me thinking… AAA batteries are a consumable, whereas a television is not.

Perhaps you could create a consumables table (batteries, light bulbs, etc.), things that will go to a location and never come back.

These would differ from your typical equipment (phones, tvs, computers, etc.) - these would be moved from one location to another and stay there until moved again. With this model you could create a virtual column on the equipment table that pulls in the MAXROW() of the movement logs (for that equipment) - keeping note of where it is. When it’s moved to another location, the MAXROW() will hold the newest location.

But batteries will go to a location and be disposed of. So maybe create a consumables table where you can select the type of consumable that’s being sent to whichever location.
To get an individual sum (like Total_AAA_Batteries_This_Year)… you’d still need to create a system of isolating item-location dependent records and counting/summing etc.

[Luke_Vancleave]

In my org’s use case though, consumables might get sent to a location and some of them may be used there but then some of them may be sent to another location and used there.

Then on the asset side, I have some assets that I have in the system that I track individually with it’s own unique ID (Generators and Chainsaws) but I also have small hand tools that while I don’t consume, I also don’t give each their own unique ID in the system and instead give it a quantity.
So I have Consumables that I need to be able to split between locations and consume.
I have hand tools that I need to be able to split between locations
And I have Assets that need to individually go to locations.

By the way I really appreciate your assistance trying to brainstorm this! It’s been a long road here.

[Mike_T]

I’m interested in using the parent child table structure. However, I’m adding a bunch of extra ‘taps’ to the user’s data entry process. In my instance I want the user to count the number of items in a display. I’m limiting myself to 20 brands (20 columns) in the form. If I use the parent child structure, I can add as many brands as I want but the user has to ‘tap’ Add, ‘tap’ the Brand field, scroll to select the brand or type in their own brand (which leads to it’s own problems later). ‘tap’ save and then go back to the original form.

Any suggestions how to get around this?

[MultiTech]

Mike_T:

However, I’m adding a bunch of extra ‘taps’ to the user’s data entry process

Indeed - this is something you’ll have to manage as your apps get more and more complex.

This is where getting to know how to use view events, composite actions with conditional navigation actions, maybe some data change actions thrown in to accomplish some stuff in the background so users don’t have to, etc. comes in handy.

Implementing an in-app custom menu greatly helps with this - you can create your own system of navigation throughout your app, as you like.