Solved

Filter Orders Value Greater Than 0 In Flow

Koennn
Excursionist
22 0 29

Hello!

I am trying to get a weekly sales report that shows the total orders with a value greater than 0.

 

I am able to get a count of all orders, but I can't figure out how to filter out orders with a value > 0.

 

The Flow looks like this

Screenshot 2023-02-18 at 15.53.26.pngThe Get Order Data query looks this

created_at:<='{{ scheduledAt }}' AND created_at:>'{{ scheduledAt | date_minus: "1 week" }} AND total_price:>0

The last part of the code does not seem to have any effect. I'm curious what's wrong with my query.

 

Koen

Accepted Solution (1)

paul_n
Shopify Staff
1185 144 278

This is an accepted solution.

The queryable fields are listed here: https://shopify.dev/docs/api/admin-graphql/2023-01/objects/QueryRoot#connection-queryroot-orders

 

There does not appear to be a way to query by the order amount. As a workaround for that, you could do something like:
1. Order Created

2. if order total > 100

3. Add "Over 100" tag

 

Note: This only affects orders going forward (but you could run it manually on past orders). 

 

Then in your workflow, add this to the query:

tag:"Over 100"

 

If you need to total only orders over $100, I think the best way in a workflow to do that would be to handle it in the email body by looping over the orders and counting in liquid. Something like this code (not tested but close):

{% assign mysum = 0 %}
{% for order in getOrderData %}
   {% if order.currentTotalPriceSet.shopMoney.amount > 100 %}
         {% assign mysum = mysum | add: order.currentTotalPriceSet.shopMoney.amount %}
    {%endif %}
{% endfor %}
{{ mysum }}

 

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.

View solution in original post

Replies 3 (3)

paul_n
Shopify Staff
1185 144 278

This is an accepted solution.

The queryable fields are listed here: https://shopify.dev/docs/api/admin-graphql/2023-01/objects/QueryRoot#connection-queryroot-orders

 

There does not appear to be a way to query by the order amount. As a workaround for that, you could do something like:
1. Order Created

2. if order total > 100

3. Add "Over 100" tag

 

Note: This only affects orders going forward (but you could run it manually on past orders). 

 

Then in your workflow, add this to the query:

tag:"Over 100"

 

If you need to total only orders over $100, I think the best way in a workflow to do that would be to handle it in the email body by looping over the orders and counting in liquid. Something like this code (not tested but close):

{% assign mysum = 0 %}
{% for order in getOrderData %}
   {% if order.currentTotalPriceSet.shopMoney.amount > 100 %}
         {% assign mysum = mysum | add: order.currentTotalPriceSet.shopMoney.amount %}
    {%endif %}
{% endfor %}
{{ mysum }}

 

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.
Koennn
Excursionist
22 0 29

Hello Paul, thanks for this workaround! It works for now, hopefully in the future the query option will be added.

BetterReports-K
Shopify Partner
177 6 16

Hi @Koennn,

 

Keanna here from the Better Reports support team. I would also recommend our app Better Reports for this! 

 

We can create a custom weekly sales report filtered for orders with a total price > 0.  We can show individual order details or aggregate sales metrics for the week. This can be scheduled to be sent to you automatically at a set cadence (ex. Mondays at 8 am).

 

In addition to custom reporting capabilities, Better Reports offers more than 80 built-in reports that cover your most common reporting needs including sales, current inventory, refunds, payouts, and more. Our reports can even be scheduled to send at a set frequency to your email or Google Drive!

 

I encourage you to install Better Reports and start your free 14-day trial and I'll be happy to set this up for you.