Hello! I am having trouble mastering a particular workflow template. I will explainโฆ
I have a start function that gathers many rows from a table. I want these rows to each be shown in a workflow, but I want them grouped by a common column value. For example, If I have 4 rows that have [Store #]=โ123โ, and 6 rows that have [Store #]=โ456โ, then I want it to look like this
Store 123
Row 1
Row 2
Row 3
Row 4
Store 456
Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
I cannot figure out how to do this though. I did look at the documentation for a while, and was still left confused. HERE is a link to my template. Scroll down to the second page, this is the new version that I am working on. The area that needs help is highlighted in yellow. Any help would be much appreciated.
Solved! Go to Solution.
Try this:
<<Start: ORDERBY(FILTER("Previous Weeks Report",AND(([_ROWNUMBER]= MAX(SELECT(Previous Weeks Report[_ROWNUMBER],(LIST([Store #],[Store Name])= LIST([_THISROW-1].[Store #],[_THISROW-1].[Store Name]))))),OR([Company]=โGonnella",[Reference Company]=โGonnellaโ),CONTAINS([_THISROW].[Store Lists To Include In Report],[List]))),[Today])>>
Well, I should clarify. Not every store has a #. Some are marked by a #, and some are marked by a Name. Some are marked by both. Every store has at least 1 of the 2. I just want the rows that correspond to the store # and Name to go below it. Thanks in advance!
Unfortunately we donโt have grouping functionality with templates at this moment. What you can doโฆ
Iโm assuming that you know all possible store namesโฆ if you donโt know them in advance, you need to change that procedure somehow. If you have for example 10 different store names, you need to use IF: & EndIf expression for each store.
<< IF: [Store]=โStore 123โ >>
Store 123
<< Start:โฆ>> << [Column] >> << End >>
<< EndIf >>
Thanks! Actually I have 3000 stores, so that probably wouldnโt be the best option. So there is no workaround? Isnโt that what a parent child relationship is? I thought that in the reports documentation, the examples were able to do this. Is there a difference in what the examples did vs what I am trying to accomplish? What is a deference? Thanks again!
Both Store Name and Store # can be blank?
Some stores have only # but not name filled out
Some stores have only name but not # filled out
Some stores have both # and name filled out
Try this (untested):
<<Start: ORDERBY(
FILTER(
"Previous Weeks Report",
(
[_ROWNUMBER]
= MAX(
SELECT(
Previous Weeks Report[_ROWNUMBER],
AND(
OR(
([Company] = "Gonnella"),
([Reference Company] = "Gonnella")
),
CONTAINS(
[_THISROW].[Store Lists To Include In Report],
[List]
)
)
)
)
)
),
[Store #]
)>>
<<CONCATENATE(
[Store Name],
IFS(
ISNOTBLANK([Store #]),
(" - #" & [Store #])
)
)>>
<<Start: ORDERBY(
FILTER(
"Previous Weeks Report",
(
LIST(
[Store #],
[Store Name]
)
= LIST(
[_THISROW-1].[Store #],
[_THISROW-1].[Store Name]
)
)
),
[Today],
TRUE
)>>
<<IFS(
([Reference Company] = "Gonnella"),
("Visited By " & [Company])
)>>
<<[Store #].[Visit Details]>> <<IFS(
[Needs Follow Up?],
"Needs a Follow Up!"
)>>
<<End>>
<<End>>
โErrorsโ: โError: Workflow rule โPrevious Week Reportโ action โPrevious Week Reportโ Body template. Found 2 unmatched โEndโ. They are: <p class=โc0โ><span class=โc2โ><>
โ, '<p class=โc0โ><span class=โc2โ><>โ,Try removing the extra line breaks and spaces I use to format my expressions. I think templates are kinda sensitive to that.
I got the same error.
Im going to make sure that I followed your formula first though. Maybe that is my error
I have a different error now. I fixed my page to follow your formula exactly, and tried again, but received this error:
โErrorsโ: โError: Workflow rule โPrevious Week Reportโ action โPrevious Week Reportโ Body template. Expression โ[Store #].[Visit Details]โ is invalid due to: Error in expression [Store #].[Visit Details] : .โ,
I received this error last night trying to do this on my own. It confused me because that tells me that there is an error, and it doesnโt tell me what the error is
Is the Store # column of the Ref type? Does the table it references have a column named Visit Details?
It is all one spreadsheet. I use a slice to narrow it down to the rows that have been added in the last 7 days, and the filter filters it down even more to have only the rows with โGonnellaโ in either of the first 2 columns and only the rows that have a certain list that is specified when I sumbit a form from another table
Try changing this in the template:
[Store #].[Visit Details]
to just this:
[Visit Details]
It only contains 1 row when I do this. There should be 13. The row shown is the last row with โShawโsโ as the [List] Value.
Hmmmโฆ What does this do, from your original template?
CONTAINS(
[_THISROW].[Store Lists To Include In Report],
[List]
)
When a user fills out a form from a seperate table, that column is one of the fields. For example, a user might select โShawโsโ, and it means that the workflow would only have those rows that have [list] in the column that the users selected.
Okay, letโs do some troubleshooting. Does the following template produce a list of distinct (store #, store name) pairs?
<<Start: ORDERBY(FILTER("Previous Weeks Report",([_ROWNUMBER]=MAX(SELECT(Previous Weeks Report[_ROWNUMBER],AND(OR(([Company] = "Gonnella"),([Reference Company] = "Gonnella")),CONTAINS([_THISROW].[Store Lists To Include In Report],[List])))))),[Store #])>>
<<[Store #]>>: <<[Store Name]>>
<<END>>
no
only the Franklin Store
<<Start: Orderby(Select(Previous Weeks Report[Key],And(OR([Company]=โGonnella",[Reference Company]=โGonnellaโ),Contains([_ThisRow].[Store Lists To Include In Report],[List]))),[Today])>>
<<END<<
This does
Does each pair occur only once? If so, just use that instead.
Alright, now were making serious progress I used that instead, but I encountered a smaller error.
The Cedarville store visits go under cedarville, but since there are 2 rows with cedarville, it shows up twice. How to I make each store only show up once, even if there are 2 rows with the pair in the table?
To answer your question, pairs can occur multiple times.
I suspected as much. My original expression was an attempt to catch that. Thinkingโฆ
Try this:
<<Start: ORDERBY(FILTER("Previous Weeks Report",AND(([_ROWNUMBER]= MAX(SELECT(Previous Weeks Report[_ROWNUMBER],(LIST([Store #],[Store Name])= LIST([_THISROW-1].[Store #],[_THISROW-1].[Store Name]))))),OR([Company]=โGonnella",[Reference Company]=โGonnellaโ),CONTAINS([_THISROW].[Store Lists To Include In Report],[List]))),[Today])>>
That worked. THANKYOU SO MUCH!!!
Just did that. I formatted your formula to my formatting. Thanks a ton by the way!
User | Count |
---|---|
33 | |
30 | |
29 | |
20 | |
18 |