Retrieving Orders with Specific Customized Product

Overview

You may need to find orders that include a specific option of a customizable product (e.g., branded) to know how many items were sold. 

Solution

Submit a request to Symphony Support, providing the name of the product option and the required date range to pull the orders.

<supportagent>

Gendurance may submit requests to retrieve orders with customizable kits (i.e. GX kits branded with team logos). All customization options for GX kits are listed in the background.json file in Symphony GitHub. To be added to the team, refer to the Symphony Access Guide.

Other customers do not have customizable products currently. If they will add such products, customization data for ordered items can be found in the extendedData field in the VictoryLineItem table.

1. Connect to the database.
2. Run the required query:
   • To retrieve all order IDs:
     

     SELECT * FROM SPOrder
     WHERE victory_id IN (SELECT victory_id FROM VictoryLineItem 
     WHERE created > '2020-07-21 00:00:00' 
     and extendedData LIKE ('%<name of the product option>%'));

   • To retrieve the number of orders containing the required customized product:

     SELECT count(id) FROM SPOrder 
     WHERE victory_id IN (SELECT victory_id FROM VictoryLineItem 
     WHERE created > '2020-07-21 00:00:00' 
     and extendedData LIKE ('%<name of the product option>%'));

     • Note: If you cannot find orders for the required option, you can increase the date range or search for all sold items containing the name of the option. For example:

       SELECT * FROM VictoryLineItem where extendedData like ('%panthers%')

</supportagent>