Sage 100 ProvideX ODBC
Developer Reference

$conn = New-Object System.Data.Odbc.OdbcConnection(
    "DSN=SOTAMAS90;UID=YourUser;PWD=YourPassword;COMPANY=COMPANY_FILE;"
)
$conn.Open()

$cmd         = $conn.CreateCommand()
$cmd.CommandText = $query
$adapter     = New-Object System.Data.Odbc.OdbcDataAdapter($cmd)
$ds          = New-Object System.Data.DataSet
$adapter.Fill($ds) | Out-Null

$conn.Close()

# Results are in $ds.Tables[0].Rows
foreach ($row in $ds.Tables[0].Rows) {
    Write-Host $row.CustomerNo $row.CustomerName
}

import pyodbc

conn = pyodbc.connect(
    "DSN=SOTAMAS90;UID=YourUser;PWD=YourPassword;COMPANY=COMPANY_FILE;"
)
cursor = conn.cursor()
cursor.execute("SELECT CustomerNo, CustomerName FROM AR_Customer LIMIT 10")

for row in cursor.fetchall():
    print(row.CustomerNo, row.CustomerName)

conn.close()

-- Exact date
WHERE InvoiceDate = {d'2025-01-15'}

-- Range
WHERE InvoiceDate BETWEEN {d'2025-01-01'} AND {d'2025-01-31'}

-- Greater than
WHERE InvoiceDate > {d'2025-02-12'}

-- PowerShell variable interpolation
$sql = "SELECT * FROM AR_InvoiceHistoryHeader WHERE InvoiceDate = {d'$dateStr'}"

SELECT * FROM AR_Customer LIMIT 100
SELECT * FROM AR_Customer LIMIT 100 OFFSET 100   -- rows 101-200
SELECT * FROM AR_Customer LIMIT 100 OFFSET 200   -- rows 201-300

-- LIKE (% wildcard works)
WHERE CustomerName LIKE 'Acme%'
WHERE CustomerName NOT LIKE '%test%'

-- IN instead of OR
WHERE CustomerNo IN ('0000001', '0000002', '0000003')

-- Numeric comparison
WHERE CustomerNo < '0000254'

-- Null checks
WHERE EmailAddress IS NOT NULL
WHERE InactiveReasonCode IS NULL

SELECT A.Field1, B.Field2
FROM
    { IJ TableA A
      INNER JOIN TableB B
      ON A.KeyField = B.KeyField }

SELECT
    H.InvoiceDate, H.InvoiceNo, H.CustomerNo,
    D.ItemCode, D.QuantityShipped, D.UnitPrice,
    (D.QuantityShipped * D.UnitPrice) AS ExtAmt
FROM
    { IJ AR_InvoiceHistoryHeader H
      INNER JOIN AR_InvoiceHistoryDetail D
      ON H.HeaderSeqNo = D.HeaderSeqNo AND H.InvoiceNo = D.InvoiceNo }
WHERE
    H.InvoiceDate BETWEEN {d'2025-01-01'} AND {d'2025-01-31'}

# Query 1: the join you can do in SQL
$queryMain   = "SELECT D.VendorNo, D.ItemCode, H.CustomerNo, H.OrderDate
                FROM { IJ AR_InvoiceHistoryDetail D
                       INNER JOIN AR_InvoiceHistoryHeader H
                       ON D.InvoiceNo = H.InvoiceNo }
                WHERE H.OrderDate BETWEEN {d'$start'} AND {d'$end'}"

# Query 2: a separate table you can't join in SQL
$queryVendor = "SELECT VendorNo, VendorName FROM AP_Vendor"

# Execute both...

# Build a hashtable for O(1) lookup
$vendors = @{}
foreach ($v in $vendorTable.Rows) {
    $vendors[$v.VendorNo] = $v.VendorName
}

# Join in PowerShell
$results = foreach ($row in $mainTable.Rows) {
    [PSCustomObject]@{
        ItemCode   = $row.ItemCode
        CustomerNo = $row.CustomerNo
        VendorName = $vendors[$row.VendorNo]   # joined here
        OrderDate  = $row.OrderDate
    }
}

# Sort after retrieval — ORDER BY doesn't work in the driver
$results | Sort-Object InvoiceDate -Descending
$results | Sort-Object CustomerName, InvoiceDate

# Inline math works in SQL, but ROUND() does not
# Option 1: let the spreadsheet round (usually fine)
# Option 2: round in PowerShell
$marginPct = [Math]::Round($row.MarginPercent, 2)

# Divide-by-zero guard (epsilon trick — works in SQL too)
# Add 1e-9 to denominator — rounds to 0 in any spreadsheet
# (D.QuantityShipped * D.UnitPrice + 1e-9)

# UCASE / LCASE don't exist in the driver — use PowerShell
$row.CustomerName.ToUpper()
$row.CustomerName.Trim()
$row.ItemCode.Substring(0, 3)

# CONCAT — use PowerShell string interpolation
"$($row.FirstName) $($row.LastName)"

# CASE doesn't work in the driver — use a ternary or switch
$channel = switch -Regex ($row.WebOrderNo) {
    '^(1|[gG])'  { 'Web Channel 1' }
    '^(2|[sS])'  { 'Web Channel 2' }
    default       { 'Offline' }
}

# IFNULL — use null coalescing
$name = if ($row.VendorName) { $row.VendorName } else { 'Unknown' }

# Install-Module ImportExcel  (no Excel required)
$results | Sort-Object InvoiceDate -Descending |
    Export-Excel -Path "C:\Reports\Sales_$(Get-Date -f yyyyMMdd).xlsx" `
                 -AutoSize -BoldTopRow -FreezeTopRow

SELECT
    H.InvoiceDate, H.InvoiceNo, H.CustomerNo, H.BillToName,
    D.ItemCode, D.ItemCodeDesc, D.ProductLine,
    D.QuantityShipped AS Qty,
    D.UnitPrice,
    (D.QuantityShipped * D.UnitPrice)              AS ExtAmt,
    D.UnitCost,
    (D.QuantityShipped * D.UnitCost)               AS ExtCost,
    (
        (D.QuantityShipped * (D.UnitPrice - D.UnitCost))
        / (D.QuantityShipped * D.UnitPrice + 1e-9)
        * 100
    ) AS MarginPct
FROM
    { IJ AR_InvoiceHistoryHeader H
      INNER JOIN AR_InvoiceHistoryDetail D
      ON H.HeaderSeqNo = D.HeaderSeqNo AND H.InvoiceNo = D.InvoiceNo }
WHERE
    H.InvoiceDate BETWEEN {d'$startDate'} AND {d'$endDate'}

SELECT
    H.SalesOrderNo, H.OrderDate, H.CustomerNo, H.BillToName,
    D.ItemCode, D.ItemCodeDesc,
    D.QuantityOrdered,
    D.QuantityShipped,
    (D.QuantityOrdered - D.QuantityShipped) AS QtyBackordered,
    D.UnitPrice
FROM
    { IJ SO_SalesOrderHeader H
      INNER JOIN SO_SalesOrderDetail D
      ON H.SalesOrderNo = D.SalesOrderNo }
WHERE
    D.QuantityOrdered > D.QuantityShipped

SELECT
    SalespersonNo,
    SUM(CommissionAmt)  AS TotalCommission,
    SUM(SalesAmt)       AS TotalSales,
    COUNT(InvoiceNo)    AS InvoiceCount
FROM AR_SalespersonCommission
WHERE PeriodEndDate BETWEEN {d'2025-01-01'} AND {d'2025-12-31'}
GROUP BY SalespersonNo

SELECT
    VendorNo, VendorName, InvoiceNo,
    InvoiceDate, DueDate, InvoiceAmt, Balance
FROM AP_OpenInvoice
WHERE Balance > 0

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI\MAS 90 4.0 ODBC Driver]
"Driver"="PVXODBC.DLL"
"Setup"="PVXODBC.DLL"
"SQLLevel"="0"
"DriverODBCVer"="03.00"
"APILevel"="1"
"ConnectFunctions"="YYN"
"FileUsage"="1"
"UsageCount"="dword:0000000"

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Sage Software\ProvideX ODBC Driver]
"Serial"="500001-1-5DE5C882FC4DBEB6"
"Name"="Registered MAS 90 & MAS 200 user"
"Company"="Sage Software, Inc."

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI\ODBC Drivers]
"MAS 90 4.0 ODBC Driver"="Installed"

$action = New-ScheduledTaskAction `
    -Execute "powershell.exe" `
    -Argument "-ExecutionPolicy Bypass -NonInteractive -File C:\Scripts\DailyReport.ps1"

$trigger   = New-ScheduledTaskTrigger -Daily -At "06:00AM"

$settings  = New-ScheduledTaskSettingsSet `
    -RunOnlyIfNetworkAvailable `
    -StartWhenAvailable `
    -ExecutionTimeLimit (New-TimeSpan -Minutes 30)

$principal = New-ScheduledTaskPrincipal `
    -UserId "DOMAIN\YourUser" `
    -LogonType Password `
    -RunLevel Highest

Register-ScheduledTask `
    -TaskName "DailyInvoiceSummary" `
    -Action $action -Trigger $trigger `
    -Settings $settings -Principal $principal

$logRow = $null

try {
    # ... your script work ...
    $logRow = [PSCustomObject]@{
        Date   = Get-Date -f "yyyy-MM-dd"
        Result = "OK"
        Error  = ""
    }
}
catch {
    $logRow = [PSCustomObject]@{
        Date   = Get-Date -f "yyyy-MM-dd"
        Result = "FAIL"
        Error  = $_.Exception.Message
    }
}
finally {
    # Prepend so newest row is always at top
    if (Test-Path $logPath) {
        @($logRow) + (Import-Csv $logPath) | Export-Csv $logPath -NoTypeInformation
    } else {
        $logRow | Export-Csv $logPath -NoTypeInformation
    }
}