Custom Tables with Where Attribute

Follow

This is an advanced feature that assumes some familiarity with SQL database queries and the Aeon database schema. The queries that are run against the database will be included in the weblog if debug logging is enabled, which can help troubleshoot problems that might arise when using this feature.

The "where" attribute has been added to most of the #TABLE tags available in the Aeon web pages. This feature allows sites to have more control over filtering the information that is displayed in tables. Using this attribute will always result in a result set that is a subset of what the #TABLE tag would otherwise generate (in other words, this feature will not allow patrons to retrieve records they otherwise would not with a given table, such as requests that they are not associated with or other users' information), but with tables such as "ViewAllRequests", you could essentially build your own custom tables. Alternatively, you could make a simple tweak such as excluding requests of a particular document type that is not relevant in the web pages.

Aeon Tables

The Aeon #TABLE names, the linked tables available to their "where" attributes, and examples.

#TABLE Names Available Tables
  • ViewAllRequests
  • ViewOutstandingRequests
  • ViewUserReviewRequests
  • ViewOrderEstimates
  • ViewOrderBilling
  • ViewRequestHistory
  •  ViewCheckedOutItems
  • ViewCancelledItems
  • ViewElectronicDelivery
  • ViewElectronicDeliveryUndelete
  • ViewActivityRequests
  • CreditCardPayment
  • ViewSearchResults
  • HistoryInformation
  • NotesInformation
  • TrackingInformation
  • ViewNotifications
  • ViewActivities
  • Transactions
  • Queues (linked by Transactions.TransactionStatus)
  • PhotoduplicationQueues (an alias for the Queues table linked by Transactions.PhotoduplicationStatus)
  • Accounts (linked by Transactions.TransactionNumber through the TransactionAccountLinks table)
  • Activities (linked by the Transactions.ActivityID)
  • BillingDetails (linked by Transactions.TransactionNumber)
  • BillingPayments (linked by Transactions.TransactionNumber)
  • Bundles (linked by Transactions.BundleID)
  • CustomFlags (linked by Transactions.TransactionNumber through the TransactionFlags table)
  • EmailCopies (linked by Transactions.TransactionNumber, only includes emails of type 'Transaction')
  • ExternalRequestsReceived (linked by Transactions.TransactionNumber)
  • Notes (linked by Transactions.TransactionNumber)
  • Tracking (linked by Transactions.TransactionNumber)
  • FileInformation (linked by Transactions.TransactionNumber through the TransactionAttachments table)
  • History (linked by Transactions.TransactionNumber)
  • Users (linked by Transactions.Username)
  • Researchers (an alias for the Users table linked by Transactions.ResearcherUsername)
  • BillingDetails
  • BillingDetails
  • BillingPayments
  • BillingPayments

Examples

  • Restrict a table to requests that belong to an exhibit activity:

    where="Activities.ActivityType = 'Exhibit'" 
  • Restrict a table to requests that have been invoiced and a note indicating that payment is overdue:

    where="BillingDetails.ID IS NOT NULL AND Notes.Note = 'Payment Due'" 
  • Restrict a table to requests whose researcher is a faculty member:

    where="Researchers.Status = 'Faculty'"
  • Restrict the billing details table to non-standard (not format, service level, or shipping option) charges:

    where="BillingDetails.BillingContext NOT IN ('Format', 'Service Level', 
    'Shipping Option')"
  • Restrict the billing payments table to credit card payments:

    where="BillingPayments.PaymentMethod='Credit Card'"
  • Exclude any Activities of type "Staff Training" from the Activities table

    <#TABLE name="ViewActivities" where="Activities.ActivityType <> 'Staff 
    Training'" headerText="Activities" noDataAction="ShowMessageRow" 
    noDataMessage="No Activities">
  • Show all outstanding photograph requests that are associated with an Activity

    <#TABLE name="ViewOutstandingRequests" where="Transactions.DocumentType 
    = 'Photograph' AND Transactions.ActivityID IS NOT NULL" headerText=
    "Outstanding Requests" noDataAction="ShowMessageRow" noDataMessage="No 
    Requests" sortable="true">

Errors

One important factor to keep in mind is that, internally, Aeon considers the values passed into its custom tags' attributes to be URL encoded, and a string that cannot be correctly decoded can cause an error resulting in the "Internal Error" page is shown. If you see the message "Invalid URL encoded character" in your weblog, this is the likely cause. For example, if you wanted to filter a requests table to only include those that had an ItemInfo1 field that contained the word 'special', you may be inclined to write:

where="Transactions.ItemInfo1 LIKE '%special%'"

This leads to the error mentioned above. After URL encoding the clause for this where attribute, we'd have:

where="Transactions.ItemInfo1%20LIKE%20%27%25special%25%27"

which gives the desired result. Although this is a bit unsightly, it's an unavoidable consequence of the library that Aeon uses internally to process the custom web tags. There are many simple, free URL encoders available on the web.

Questions?

If this article didn’t resolve your issue, please take a moment and answer a few questions to help improve our documentation:

Feedback