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…)
dan_R:
COUNT(SELECT(WM LOADS[Activities], IN([Activities], {“Drop”, “Arrive”, “Hook”, “Chain”})))
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.
dan_R:
If I leave filter in the expression, I get this> Function ‘FILTER’ should have exactly two parameters, a table name and a filter condition
Please include a screenshot (preferable) or a copy-paste of the exact expression that you’re using whenever stating that it gives an error.
