Searching Files in NetSuite File Cabinet

Learn how to search for files in NetSuite using SuiteQL queries.

Prerequisites

• NetSuite connector configured with suiteQL resource enabled
• Basic SQL/SuiteQL knowledge

Basic File Search

Search for files by name:

from abstra.connectors import run_connection_action

# Search for files containing "invoice"
result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, name, filesize, filetype, folder, createddate
            FROM File
            WHERE name LIKE '%invoice%'
            ORDER BY createddate DESC
            FETCH FIRST 10 ROWS ONLY
        """
    }
)

for file in result['data']['items']:
    print(f"📄 {file['name']} (ID: {file['id']}, Size: {file['filesize']} bytes)")

Search by Date

Files Created in Last 30 Days

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, name, filesize, createddate
            FROM File
            WHERE createddate >= BUILTIN.RELATIVE_RANGES('DAGO30', 'START')
            ORDER BY createddate DESC
        """
    }
)

Files Created in Specific Date Range

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, name, createddate
            FROM File
            WHERE createddate BETWEEN TO_DATE('2025-01-01', 'YYYY-MM-DD')
                                  AND TO_DATE('2025-12-31', 'YYYY-MM-DD')
            ORDER BY createddate DESC
        """
    }
)

Search by File Type

# Find all PDF files
result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, name, filesize
            FROM File
            WHERE filetype = '_PDF'
            FETCH FIRST 20 ROWS ONLY
        """
    }
)

# Find all Excel files
result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, name
            FROM File
            WHERE filetype IN ('_EXCEL', '_CSV')
        """
    }
)

Note: File types in SuiteQL have underscore prefix (e.g., _PDF, _EXCEL, _PLAINTEXT)

Search by Folder

# Files in SuiteFiles root folder (ID: -15)
result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, name, folder
            FROM File
            WHERE folder = -15
        """
    }
)

# Files in SuiteScripts folder (ID: -10)
result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, name, folder
            FROM File
            WHERE folder = -10
        """
    }
)

Search by File Size

# Files larger than 1MB (1048576 bytes)
result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, name, filesize
            FROM File
            WHERE filesize > 1048576
            ORDER BY filesize DESC
        """
    }
)

# Files smaller than 100KB
result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, name, filesize
            FROM File
            WHERE filesize < 102400
        """
    }
)

Complex Searches

Multiple Criteria

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, name, filesize, createddate
            FROM File
            WHERE name LIKE '%2025%'
              AND filetype = '_PDF'
              AND filesize < 5242880
              AND createddate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
            ORDER BY createddate DESC
            FETCH FIRST 50 ROWS ONLY
        """
    }
)

Count Files by Type

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT filetype, COUNT(*) as count, SUM(filesize) as total_size
            FROM File
            GROUP BY filetype
            ORDER BY count DESC
        """
    }
)

for row in result['data']['items']:
    file_type = row['filetype'].replace('_', '')
    total_mb = row['total_size'] / (1024 * 1024)
    print(f"{file_type}: {row['count']} files ({total_mb:.2f} MB)")

Pagination

Using FETCH FIRST and OFFSET

def search_files_paginated(page=1, page_size=20):
    offset = (page - 1) * page_size

    result = run_connection_action(
        "netsuite",
        "query",
        {
            "query": f"""
                SELECT id, name, createddate
                FROM File
                ORDER BY createddate DESC
                FETCH FIRST {page_size} ROWS ONLY
                OFFSET {offset}
            """
        }
    )

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

# Get first page
files_page1 = search_files_paginated(page=1)

# Get second page
files_page2 = search_files_paginated(page=2)

Practical Examples

Find Duplicate Files

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT name, COUNT(*) as count
            FROM File
            GROUP BY name
            HAVING COUNT(*) > 1
            ORDER BY count DESC
        """
    }
)

print("Duplicate files:")
for row in result['data']['items']:
    print(f"  {row['name']}: {row['count']} copies")

Find Old Files for Cleanup

result = run_connection_action(
    "netsuite",
    "query",
    {
        "query": """
            SELECT id, name, createddate, filesize
            FROM File
            WHERE createddate < TO_DATE('2024-01-01', 'YYYY-MM-DD')
              AND name LIKE '%temp%'
            ORDER BY createddate ASC
        """
    }
)

total_size = sum(f['filesize'] for f in result['data']['items'])
print(f"Found {len(result['data']['items'])} old files")
print(f"Total size: {total_size / (1024*1024):.2f} MB")

Search with User-Friendly Display

def search_files_display(search_term):
    result = run_connection_action(
        "netsuite",
        "query",
        {
            "query": f"""
                SELECT id, name, filesize, filetype, createddate
                FROM File
                WHERE name LIKE '%{search_term}%'
                ORDER BY createddate DESC
                FETCH FIRST 10 ROWS ONLY
            """
        }
    )

    print(f"\nSearch results for '{search_term}':")
    print("-" * 80)

    for file in result['data']['items']:
        size_kb = file['filesize'] / 1024
        file_type = file['filetype'].replace('_', '')
        created = file['createddate'][:10]  # Just the date part

        print(f"ID: {file['id']:<10} | {file['name']:<40} | {size_kb:>8.1f} KB | {file_type:<10} | {created}")

    print("-" * 80)
    print(f"Total: {len(result['data']['items'])} files\n")

# Usage
search_files_display("invoice")

SuiteQL Best Practices

Use FETCH FIRST instead of LIMIT

# ✅ Correct - NetSuite uses FETCH FIRST
"SELECT id FROM File FETCH FIRST 10 ROWS ONLY"

# ❌ Incorrect - LIMIT not supported
"SELECT id FROM File LIMIT 10"

File Type Prefixes

Remember the underscore prefix:

# ✅ Correct
"WHERE filetype = '_PDF'"

# ❌ Incorrect
"WHERE filetype = 'PDF'"

Date Functions

Use NetSuite's date functions:

# ✅ Correct
"WHERE createddate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')"

# ✅ Correct - relative dates
"WHERE createddate >= BUILTIN.RELATIVE_RANGES('DAGO30', 'START')"

Troubleshooting

"Unknown identifier" errors

Field names in SuiteQL may differ from UI labels. Common fields:

UI Name	SuiteQL Field
Name	name
File Size	filesize
File Type	filetype
Created Date	createddate
Modified Date	modified
Folder	folder

Query performance

For large result sets:

• Use FETCH FIRST to limit results
• Add specific WHERE clauses
• Use indexes (dates, IDs)

Empty results

Check your query syntax:

# Debug: Count total files
result = run_connection_action(
    "netsuite",
    "query",
    {"query": "SELECT COUNT(*) as total FROM File"}
)
print(f"Total files in cabinet: {result['data']['items'][0]['total']}")

Next Steps

• Upload files
• Retrieve and update file details

Resources

• NetSuite SuiteQL Documentation
• Abstra Connectors Reference