Struggling with Parent/Child Start: Functions in Workflow

[retailpartnerco]

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.

[retailpartnerco]

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!

[Aleksi]

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 >>

[retailpartnerco]

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!

[Steve]

Both Store Name and Store # can be blank?

[retailpartnerco]

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

[Steve]

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>>

[retailpartnerco]

“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”><>”,
Thats what happened when I plugged it into the doc.

[Steve]

Try removing the extra line breaks and spaces I use to format my expressions. I think templates are kinda sensitive to that.

[retailpartnerco]

I got the same error.

[retailpartnerco]

Im going to make sure that I followed your formula first though. Maybe that is my error

[retailpartnerco]

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] : .”,

[retailpartnerco]

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

[Steve]

Is the Store # column of the Ref type? Does the table it references have a column named Visit Details?

[retailpartnerco]

docs.google.com

Daily Report

Daily Report (PST) Company,Reference Company,Key,List,List Only,Store #,Store Name,Today,Visitor,Visit Details,Needs Follow Up?,Image 1,Image 2,Image 3 Gonnella,Baker's Group,1,Acme East (Gonnella),Acme East,871,Ogletown,2/26/2019,Marty...

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

[Steve]

Try changing this in the template:

[Store #].[Visit Details]

to just this:

[Visit Details]

[retailpartnerco]

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.

[Steve]

Hmmm… What does this do, from your original template?

CONTAINS(
  [_THISROW].[Store Lists To Include In Report],
  [List]
)

[retailpartnerco]

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.

[Steve]

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>>

[retailpartnerco]

no

[retailpartnerco]

only the Franklin Store

[retailpartnerco]

<<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

[Steve]

Does each pair occur only once? If so, just use that instead.

[retailpartnerco]

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.

[Steve]

I suspected as much. My original expression was an attempt to catch that. Thinking…

[Steve · Accepted 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])>>

[retailpartnerco]

That worked. THANKYOU SO MUCH!!!

[retailpartnerco]

Just did that. I formatted your formula to my formatting. Thanks a ton by the way!