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?
Why donโt you use data change workflow and actions to achieve this with real columns?
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.
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.
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)
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:
Doing something like this (creating a joining table) allows you to have an easier interface with the data.
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โ
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])
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?
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.
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โฆ
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.
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.
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?
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.
User | Count |
---|---|
43 | |
27 | |
24 | |
22 | |
13 |