🙋 Materials vs Documents

What's the difference between the [material] and [document] tables?

The [document] table supports the 'Configurable Joins - Document' query base, which in turn supports exporting documents to document imaging systems like Perceptive Content, OnBase, Softdocs, etc.

Document

This table contains entries for each item that you can export as a PDF/TIFF. Many, but not all, items in [document] are also present in [material].

Entries not present in Materials, such as decision letters, cached application PDF's, and references are excluded from the 'Configurable Joins - Document' query base via an inner join to [lookup.material] until you configure them as materials.

By default, Document queries include documents attached to 'deleted' applications. You can exclude these with a subquery filter like "EXISTS Document Primary Record GUID."

Material

Contains only records listed under the Materials section of the person/application records. Most [material] rows have a corresponding row in [document] with the same [id].

One exception is sys:reference_stream. Submitted references have one row in [document] with a key of sys:reference and a row in [material] with a key of sys:reference_stream. Both link to the same row in [stream].