🙋 Materials vs Documents

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

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

The Configurable Joins - Materials query base is for querying information about materials. It's often used with checklist rules, but it's not useful for exporting PDF materials.

Document

The view 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."

Try this template query: 62c824ca-83b4-4d3c-ad2d-92b25c8847fd:rwf. It contains properly-constructed joins to Person, Application, and Material and some example exports and filters to help you get started.

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].

Some older material keys are obsolete. For example, the following keys will not generate a PDF:
sys:application_proof_references
sys:application_proof_schools