🔍 Mastering the Uniface lookup Statement: A Developer's Guide to Database Record Checking

As a developer working with Uniface 10.4, understanding the lookup statement is crucial for efficient database operations. This powerful statement allows you to check if records exist without actually retrieving data - making it a performance-friendly option for validation scenarios. 🚀

Note: This article is based on the official Uniface Documentation 10.4, and I had assistance from AI in structuring this content.

📋 What Does lookup Do?

The lookup statement counts the number of occurrences that match the current profile in your database. It's like asking "How many records match this criteria?" without actually loading the data into memory.

🎯 Key Features

• No Data Transport - Only returns a count, saving bandwidth and memory
• No Read Trigger Activation - Bypasses trigger logic for pure checking
• Primary Key Matching - Uses primary key values when available
• Universal Compatibility - Works in all component types

📊 Understanding Return Values

The lookup statement returns its result in $status:

Value	Meaning
≥0	✅ Number of matching records found
-1	🔚 End of file encountered (no record found)
-2	📭 Table is empty
-3	⚠️ I/O error (hardware/software issue)
-4	❌ Table/file open failed
-15	🌐 Uniface network error
-16	🔌 Unknown network error

💡 Practical Example: Invoice Number Validation

Here's a real-world example showing how to prevent duplicate invoice numbers:

trigger loseFocus
; field : INVOICE_NUMBER
; Check if another record already has this number
; Prevent user from leaving field if duplicate exists

lookup
if ($status > 0)
    message "⚠️ No! This number has been used before!"
    return (-1)
else
    message "❌ I/O error %%$status"
endif
end; loseFocus

🏆 Best Practices & Pro Tips

🧹 Use clear for Total Counts

Want to count all occurrences in a table? Use clear before lookup:

clear
lookup
; $status now contains total number of records

⚡ Performance Consideration

While lookup is efficient, sometimes a retrieve/o operation might be faster, especially since it also checks data already loaded in the component.

🎯 Key Matching Logic

• If complete primary key is available → uses primary key values
• If primary key incomplete → uses all field values for matching

🚨 Error Handling

Always check $procerror for detailed error information:

• -2 through -12: Database I/O errors (UIOSERR_*)
• -16 through -30: Network I/O errors (UNETERR_*)

🎯 When to Use lookup

Perfect for:

• ✅ Validation routines
• ✅ Checking record existence before operations
• ✅ Counting records without loading data
• ✅ Performance-critical duplicate checks

Consider alternatives when:

• 🔄 You need the actual data anyway
• 🔄 Component already has relevant data loaded

🎉 Conclusion

The lookup statement is a powerful tool in the Uniface developer's toolkit. It provides an efficient way to check record existence without the overhead of data retrieval, making it perfect for validation scenarios and existence checks. Remember to always handle the various return codes properly to create robust applications! 💪

Have you used lookup in interesting ways in your Uniface applications? Share your experiences in the comments below! 👇