Enumlist help

dan_R · 11-19-2020 08:56 PM

Hello all.
Okay, I’ll start with the disclaimer… I’m a stupid truck driver.
That being said, I have very little experience with working with computers, let alone appsheet.
However, after a couple of days, I have finally finished my project except one thing. I want to calculate the values entered by my enumlist.

Example:

Enumlist Selection

Drop Drop
Hook Hook
Live Load
Live Unload
Arrive Arrive

These items are listed potentially up to 6 times per load.
I’m looking for a way to calculate how many times I do each selected item in a week.
21 Drop
22 Hook
21 Arrive
etc.

I would like it to populate on my “load information” view, along with the other data entered into the load sheet. Is this possible or have I been banging my head for nothing? This is literally the main thing I was wanting to accomplish with this program.
Any help or prayers would be appreciated.
Thanks!

Marc_Dillon

Might need some more information about your whole setup, but maybe something like this:

COUNT( FILTER( table , IN( “Drop” , [enumlist column] ) ) )

dan_R

Wow, thanks for the quick response.

Not sure what else I can provide that will make it easier for you to help me. Again… very new at this.

Essentially, what I have is records of my loads.

Trip number
order number
Truck number
Trailer Number
Store numbers and information
and activities.
the activities is where I am wanting the help. Again, they are selected in an enumlist.
Arrive
Arrive Drop
Live Unload
Live Load
Etc.

Each trip has multiple “activities” that are performed. They all show up on the individual load records properly. What I would like to do is extract that data, calculate each individual item, and tally them on to a new view that shows each item quantity for the week.

Load # 554662
Order # 6584666542
Truck # 17-1005
Trailer # 32554
Arrives = 25
Hooks = 24
Live Load = 9

Etc.

I don’t know if that helps or not.
I’ll check out the suggestion you provided.

Thanks again!

dan_R

I have no idea what I’m doing here. GRRRR

I used the formula you put and changed the enumlist name to the one i have, “Activities”. I also changed the name of the table to “Load Data”, which is the name of the table “Activities” is located. I get a blank result when I review the data. I put this in the Valid If section of the builder.

I’m not sure this will work well for me anyway, because I will likely have multiple values from each cell. One cell could potentially have 3-4 items in it from a list of about 20 potential items.
For instance, I could show up at a store (=1 arrive), then I have to unload (= 1 Live Unload), then I will depart, (=1 depart). I might then go to another store, call it, Stop #2. There I would have an “arrive”, “live unload”, “drop”, and potentially a “undock trailer”. The cell that is populated by the Enumlist looks like, “Arrive, Live Unload, Un-dock Trailer, Depart”. Comma separated

What I would like to be able to do is calculate each activity throughout my multiple stops for the day. The reason this is important is that they all pay differently and have to be matched to what the computer in the truck shows, (which is never right).

Any further help would be appreciated, but I would totally get it if you told me to go pound sand.

Marc_Dillon

That is not the correct spot. The expression I provided would be for the App Formula of a Virtual column somewhere. And you’d need as many of these expression as the number of possible items in the EnumList.

I’d further suggest that you create an Activities table. Each row in this table would hold one possible EnumList selection, let’s name the column [Activity]. Then you can add a Virtual Column to this table with the provided expression, slightly changed to refer to the [Activity] column instead of the hard-coded activity value (the “Drop” from my initial example):

COUNT( FILTER( “Load Data” , IN( [_THISROW].[Activity] , [activities] ) ) )

I’d also advise thinking about how you might re-structure your data to better achieve the functionality that you’re looking for.

dan_R

Okay, thanks. I have the activities in a sheet, all on their own. The activity name is the key, being as it can’t be edited and there are no relations, other than when they are selected.
I’ll try to figure out the virtual column thing and let you know what I find. Thanks!
It’s interesting getting into all of this stuff, for a new guy. I appreciate all of the help!

dan_R

Okay, so this almost worked. COUNT(SELECT(WM LOADS[Activities], IN([Activities], {“Drop”, “Arrive”, “Hook”, “Chain”}))) However, I can’t seem to get the right combination.

The rows I am working on are as follows:

Stop 1…Activities1…Stop2…Activities2…Stop3…Activites3…Stop4… etc up to 7

2342…Drop, Hook…1608…Live Unload, Arrive…2532…Drop/hook…2432
No need to show the store numbers, just the calculated activities

The numbers under the “stop” are store numbers. The activities listed are activities that are populated from an enumlist that refers to another table to gather the contents of the list. There is a selection of up to 21 possibilities, but usually only 2-4 are selected at any given “stop”.
I am hoping to get something that shows how many of each activity were done.
5 arrive
4 live unload
3 Hook
Etc.
If I leave filter in the expression, I get this
Function ‘FILTER’ should have exactly two parameters, a table name and a filter condition
Is this possible or am I trying to hard?

Marc_Dillon

Oof! Having multiple EnumLists to check against in one record makes a very large difference in this scenario. Up until now I was under the impression that there was a single [Activites] EnumList per record.

As a just a bit of general advice, when your data requires repeated sets of the same information (like “stop” and “activities” repeated multiple times), it is often much better for this to be put into a child table, where the user can enter in any number of entries, which are all “grouped” by the parent record.

I think the best that I can advise, without having to redo your whole data structure as per the above, is to add in additional columns to the WM LOADS table, one column per Activity type (so 1 column for “drop” 1 for “load”, etc…). Each of these columns can be assigned an App Formula expression that’ll count up the number of those activities per record, like this:

IFS( IN( "Drop" , [activities1] ) , 1 ) +
IFS( IN( "Drop" , [activities2] ) , 1 ) +
IFS( IN( "Drop" , [activities2] ) , 1 ) +
...

Replace “Drop” with whichever activity each column is for. These new columns should be Number type, and they’ll give a count of the number of times each Activity is done per WM LOAD record. Name these new column as “xxx Count”, where “xxx” is the Activity Type.

Then we can go back to wherever you’re calculating the activities per week, and modify my previous expression a bit, to this:

SUM( SELECT( WM LOADS[Drop Count] , [date] = xxxxx ) )

The “[date] = xxxx” part is just an example for a condition that I imagine you’ll need to fill in the get the count per week, but that depends on where these are being calculated and how you have other tables setup. To simplify this for now, we can just use this expression to sum up all occurrences of each Activity for all time:

SUM( WM LOADS[Drop Count] )

You’ll repeat this expression for each Activity Type, changing the “[Drop Count]” portion of the expression to the appropriate column (e.g. [Load Count] , [Hook Count], etc…)

I think you were a bit confused about this expression here. The SELECT/FILTER condition was supposed to be IN( xxx , [Activities] ) , where “xxx” was to be replaced by a single activity, and you would create 1 expression per activity type.

Please include a screenshot (preferable) or a copy-paste of the exact expression that you’re using whenever stating that it gives an error.

dan_R

It’s working!!! I think I will keep and use this one for a while, while I work on figuring out how to reference the numbers instead. I would love for them to show up as a referenced table rather than being included in the main screen data area.
I have plenty of other fields that are ref, but figuring out how to add referenced data to this is blowing my mind for some reason. I can’t wrap my head around it. I guess that’s my bodies way of saying “ENOUGH FOR NOW”
Thanks!!!