Need Help Creating Workflow Template

[Brady_Lovell]

I’ve got a gSheet that contains a database of orders. In this table I have the column [Status]. Whenever [Status] contains the value “AVAILABLE”, i want to bring all of the orders that are AVAILABLE and contain only the columns [Pickup], [Delivery], [Farm], [Customer], and [Rate] to the gDoc so that I can attach it to a Workflow.

Basically I’m wanting my gDoc template to contain a 5 column table, showing all AVAILABLE orders in my system.

This is what I have so far…

<<If: [Status] = “AVAILABLE”>>

Pickup: <<LIST([Pickup])>>

<>

[Phil · Accepted solution]

Try this:

<<Start: FILTER(Orders, [Status] = “AVAILABLE”)>>
Pickup: <<[Pickup]>>
Delivery: <<[Delivery]>>
Farm: <<[Farm]>>
Customer: <<[Customer]>>
Rate: <<[Rate]>>
<< End >>

[Brady_Lovell]

Thanks Phil, that did the trick!

[Phil]

Glad it worked for you.

[Brady_Lovell]

How can I sort the results to show the [Pickup] column in Ascending order?

<<Start: FILTER(Orders, AND([Status] = “Available”,[Pickup]>=TODAY(),[Pickup]<=TODAY()+2 ))>>

<<[Order #]>> <<[Pickup]>> <<[Delivery]>> <<[Appt]>> <<[Customer]>>

<< End >>

[Steve]

Wrap your FILTER() expression with ORDERBY():

ORDERBY(FILTER(...), [Pickup])

[Brady_Lovell]

I’ve almost got this thing put together, just struggling on the last step which is using SPLIT() to remove the first two digits “19” in front of the 19xxxx Ref number. For some reason I keep getting a comma as a result, and I can’t figure it out. Any ideas?

The Ref column is my Key so I used this Start Expression:

<<Start: ORDERBY(FILTER(Orders, AND([Status] = “Available”,[Pickup]>=TODAY(),[Pickup]<=TODAY()+2 )),[Pickup])>><<SPLIT([Order #],”19”)>>

[Phil]

If all of the Order# are the same length, you can use the Right function to get the rightmost N characters of the Order#.

If not, you can use the expression SUBSTITUTE(Order#, Left(Order#,2),"")

[Brady_Lovell]

The RIGHT() function did the trick, much appreciated.