Searching and Filtering Purchase Orders

Learn how to search, filter, and query purchase orders in NetSuite using SuiteQL.

Prerequisites

• NetSuite connector configured with suiteQL resource enabled
• Basic understanding of SQL queries

Basic Purchase Order Search

List All Purchase Orders

from abstra.connectors import run_connection_action

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT
                t.id,
                t.tranid,
                t.trandate,
                ts.name as status
            FROM
                transaction t
                LEFT JOIN TransactionStatus ts ON t.status = ts.id
            WHERE
                t.type = 'PurchOrd'
            ORDER BY
                t.trandate DESC
            FETCH FIRST 100 ROWS ONLY
        """
    }
)

for po in result['data']['items']:
    print(f"PO {po['tranid']}: {po['status']} - {po['trandate']}")

Output:

PO PO2024-001: Pending Approval - 15/01/2025
PO PO2024-002: Pending Approval - 16/01/2025
PO PO2024-003: Approved - 14/01/2025
...

Filtering by Status

Find Pending Approval Orders

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT
                t.id,
                t.tranid,
                t.trandate,
                ts.name as status,
                t.memo
            FROM
                transaction t
                LEFT JOIN TransactionStatus ts ON t.status = ts.id
            WHERE
                t.type = 'PurchOrd'
                AND ts.name = 'Pending Approval'
            ORDER BY
                t.trandate DESC
        """
    }
)

print(f"Found {len(result['data']['items'])} orders pending approval")

for po in result['data']['items']:
    print(f"  [{po['tranid']}] {po['memo'][:50]}...")

Find Orders by Multiple Statuses

statuses = ['Pending Approval', 'Pending Receipt', 'Partially Received']

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": f"""
            SELECT
                t.id,
                t.tranid,
                t.trandate,
                ts.name as status
            FROM
                transaction t
                LEFT JOIN TransactionStatus ts ON t.status = ts.id
            WHERE
                t.type = 'PurchOrd'
                AND ts.name IN ({', '.join(f"'{s}'" for s in statuses)})
            ORDER BY
                t.trandate DESC
        """
    }
)

Filtering by Date

Orders Created Today

from datetime import date

today = date.today().strftime('%d/%m/%Y')

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": f"""
            SELECT
                t.id,
                t.tranid,
                t.trandate,
                ts.name as status
            FROM
                transaction t
                LEFT JOIN TransactionStatus ts ON t.status = ts.id
            WHERE
                t.type = 'PurchOrd'
                AND t.trandate = '{today}'
        """
    }
)

print(f"Orders created today: {len(result['data']['items'])}")

Orders in Date Range

from datetime import date, timedelta

# Last 7 days
end_date = date.today()
start_date = end_date - timedelta(days=7)

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": f"""
            SELECT
                t.id,
                t.tranid,
                t.trandate,
                ts.name as status
            FROM
                transaction t
                LEFT JOIN TransactionStatus ts ON t.status = ts.id
            WHERE
                t.type = 'PurchOrd'
                AND TO_DATE(t.trandate, 'DD/MM/YYYY') >= TO_DATE('{start_date.strftime('%d/%m/%Y')}', 'DD/MM/YYYY')
                AND TO_DATE(t.trandate, 'DD/MM/YYYY') <= TO_DATE('{end_date.strftime('%d/%m/%Y')}', 'DD/MM/YYYY')
            ORDER BY
                t.trandate DESC
        """
    }
)

print(f"Orders in last 7 days: {len(result['data']['items'])}")

Filtering by Vendor

Orders for Specific Vendor

# First, find vendor ID
vendor_search = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, entityid, companyname
            FROM vendor
            WHERE companyname LIKE '%Dunder Mifflin%'
        """
    }
)

if vendor_search['data']['items']:
    vendor_id = vendor_search['data']['items'][0]['id']

    # Find all POs for this vendor
    result = run_connection_action(
        "netsuite",
        "query",
        {
            "query": f"""
                SELECT
                    t.id,
                    t.tranid,
                    t.trandate,
                    ts.name as status
                FROM
                    transaction t
                    LEFT JOIN TransactionStatus ts ON t.status = ts.id
                WHERE
                    t.type = 'PurchOrd'
                    AND t.entity = '{vendor_id}'
                ORDER BY
                    t.trandate DESC
            """
        }
    )

    print(f"Found {len(result['data']['items'])} orders for vendor")

Grouping and Aggregating

Count Orders by Status

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT
                ts.name as status,
                COUNT(*) as count
            FROM
                transaction t
                LEFT JOIN TransactionStatus ts ON t.status = ts.id
            WHERE
                t.type = 'PurchOrd'
            GROUP BY
                ts.name
            ORDER BY
                count DESC
        """
    }
)

print("Purchase Orders by Status:\n")
for item in result['data']['items']:
    print(f"  {item['status']:30} {item['count']:>5} orders")

Output:

Purchase Orders by Status:

  Pending Approval               90 orders
  Fully Billed                   267 orders
  Pending Receipt                45 orders
  Approved                       120 orders

Sum Order Values by Status

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT
                ts.name as status,
                COUNT(*) as order_count,
                COUNT(DISTINCT t.entity) as vendor_count
            FROM
                transaction t
                LEFT JOIN TransactionStatus ts ON t.status = ts.id
            WHERE
                t.type = 'PurchOrd'
            GROUP BY
                ts.name
            ORDER BY
                order_count DESC
        """
    }
)

for item in result['data']['items']:
    print(f"{item['status']}: {item['order_count']} orders from {item['vendor_count']} vendors")

Pagination

Process Large Result Sets

def fetch_all_purchase_orders(batch_size=100):
    """
    Fetch all purchase orders in batches using pagination
    """
    offset = 0
    all_orders = []

    while True:
        result = run_connection_action(
            "netsuite",
            "query",
            {
                "query": f"""
                    SELECT
                        t.id,
                        t.tranid,
                        t.trandate,
                        ts.name as status
                    FROM
                        transaction t
                        LEFT JOIN TransactionStatus ts ON t.status = ts.id
                    WHERE
                        t.type = 'PurchOrd'
                    ORDER BY
                        t.trandate DESC
                    OFFSET {offset} ROWS
                    FETCH NEXT {batch_size} ROWS ONLY
                """
            }
        )

        items = result['data']['items']
        if not items:
            break

        all_orders.extend(items)
        print(f"Fetched {len(items)} orders (total: {len(all_orders)})")

        offset += batch_size

        # Stop if we got fewer items than requested (last page)
        if len(items) < batch_size:
            break

    return all_orders

# Usage
all_pos = fetch_all_purchase_orders(batch_size=100)
print(f"\nTotal orders fetched: {len(all_pos)}")

Advanced Filters

Complex Multi-Criteria Search

from datetime import date, timedelta

# Find orders that are:
# - Pending approval
# - Created in last 30 days
# - From specific subsidiary
# - Value over 10,000

end_date = date.today()
start_date = end_date - timedelta(days=30)

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": f"""
            SELECT
                t.id,
                t.tranid,
                t.trandate,
                ts.name as status,
                t.memo
            FROM
                transaction t
                LEFT JOIN TransactionStatus ts ON t.status = ts.id
            WHERE
                t.type = 'PurchOrd'
                AND ts.name = 'Pending Approval'
                AND TO_DATE(t.trandate, 'DD/MM/YYYY') >= TO_DATE('{start_date.strftime('%d/%m/%Y')}', 'DD/MM/YYYY')
            ORDER BY
                t.trandate DESC
        """
    }
)

print(f"Found {len(result['data']['items'])} matching orders:")
for po in result['data']['items']:
    print(f"  [{po['tranid']}] {po['status']} - {po['trandate']}")

Search by Text in Memo

search_term = "Dunder Mifflin"

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": f"""
            SELECT
                t.id,
                t.tranid,
                t.trandate,
                t.memo,
                ts.name as status
            FROM
                transaction t
                LEFT JOIN TransactionStatus ts ON t.status = ts.id
            WHERE
                t.type = 'PurchOrd'
                AND t.memo LIKE '%{search_term}%'
            ORDER BY
                t.trandate DESC
        """
    }
)

print(f"Orders mentioning '{search_term}': {len(result['data']['items'])}")

Helper Functions

Reusable Search Function

def search_purchase_orders(
    status=None,
    vendor_id=None,
    date_from=None,
    date_to=None,
    search_text=None,
    limit=100
):
    """
    Generic purchase order search with multiple filters
    """
    conditions = ["t.type = 'PurchOrd'"]

    if status:
        conditions.append(f"ts.name = '{status}'")

    if vendor_id:
        conditions.append(f"t.entity = '{vendor_id}'")

    if date_from:
        conditions.append(f"TO_DATE(t.trandate, 'DD/MM/YYYY') >= TO_DATE('{date_from}', 'DD/MM/YYYY')")

    if date_to:
        conditions.append(f"TO_DATE(t.trandate, 'DD/MM/YYYY') <= TO_DATE('{date_to}', 'DD/MM/YYYY')")

    if search_text:
        conditions.append(f"t.memo LIKE '%{search_text}%'")

    where_clause = " AND ".join(conditions)

    result = run_connection_action(
        "netsuite",
        "query",
        {
            "query": f"""
                SELECT
                    t.id,
                    t.tranid,
                    t.trandate,
                    ts.name as status,
                    t.memo
                FROM
                    transaction t
                    LEFT JOIN TransactionStatus ts ON t.status = ts.id
                WHERE
                    {where_clause}
                ORDER BY
                    t.trandate DESC
                FETCH FIRST {limit} ROWS ONLY
            """
        }
    )

    return result['data']['items']

# Usage examples
pending_orders = search_purchase_orders(status="Pending Approval")
recent_orders = search_purchase_orders(date_from="01/10/2025")
vendor_orders = search_purchase_orders(vendor_id="98765", limit=50)

Best Practices

Use Indexes for Performance

# ✅ Good: Filter on indexed fields (id, tranid, trandate, type)
"""
SELECT * FROM transaction
WHERE type = 'PurchOrd' AND tranid = 'PO2024-001'
"""

# ❌ Slow: Filter on non-indexed fields like memo
"""
SELECT * FROM transaction
WHERE memo LIKE '%expensive search%'
"""

Limit Result Sets

# ✅ Always use FETCH FIRST for large queries
"""
SELECT * FROM transaction
WHERE type = 'PurchOrd'
FETCH FIRST 1000 ROWS ONLY
"""

Use Appropriate Date Formats

# ✅ NetSuite date format is DD/MM/YYYY
date_str = date.today().strftime('%d/%m/%Y')

# ✅ Use TO_DATE for date comparisons
"""
WHERE TO_DATE(t.trandate, 'DD/MM/YYYY') >= TO_DATE('01/10/2025', 'DD/MM/YYYY')
"""

Common Issues

"Unknown identifier" Error

Problem: Query references non-existent field

Solution: Use only standard transaction table fields:

• id, tranid, trandate, type, status, entity, memo, createddate

Empty Results

Problem: No orders match criteria

Solution:

1. Remove filters one by one to identify issue
2. Check date format (DD/MM/YYYY)
3. Verify status name matches exactly (case-sensitive)

Slow Queries

Problem: Query takes too long

Solution:

1. Add FETCH FIRST N ROWS ONLY limit
2. Filter on indexed fields first (type, trandate)
3. Avoid LIKE searches on large text fields

Next Steps

• Reading Purchase Order Details - Get complete PO information
• Filtering by Custom Fields - Use custom fields in searches
• Creating Purchase Orders - Create new POs