🧺 Material Download Link

Ever wanted to allow students to download materials from status portals? Here's how you can generate download links. Typically, this process starts in an Application- or Person-scoped query.

Instructions

1. Start a subquery export that will become the download URL. If you want to offer links to multiple materials via liquid looping, start a Dictionary export (example screenshot below).
   
2. Join to Materials and filter down to just the material you want to make downloadable (or multiple materials if you're creating a dictionary).
   
3. Create a formula export as follows:

'/apply/download.pdf?part='
+ CONCAT('stream:', dbo.toGuidString([stream]))
+ '&h='
+ dbo.toGuidString(dbo.md5(convert(varbinary(max), concat('stream:', dbo.toGuidString([stream])) + dbo.salt())))

If you receive the error message Ambiguous column name 'stream'., you likely have multiple joins to Material. Only one join is necessary.

Examples

Single Material

Generating a link to a single material (Essay) - you should to add a sort if an applicant might have multiple essays on file:

Multiple Materials

Generating a dictionary export intended for Liquid looping as a bulleted list of download links:

Liquid to loop through this example and make bullet points:

<ul>
{% for m in multiple_materials %}
<li><a href="{{m.url}}" target="_blank">{{m.name}}</a></li>
{% endfor %}
</ul>

Enhanced Copy and Paste

Try copying and pasting these examples into your subquery. This is experimental; you must be on the Applications base.

You will have to change the Export type for both. You will have to separately paste the formula contents for Single Material.

Single Material

{"type":"subquery","parts":[{"uuid":"2f955639-cb25-4745-8beb-ec9d20672325","type":"export","parameters":[],"active":"1","name":"Dummy","source_type":"literal","query":"e4db1580-1669-4967-a631-6ac71287e97d","filterable":"0","sourceActive":"0","sourceExists":"0"},{"id":"9af1a4e9-3cc7-4e8d-897f-359411895ec5","uuid":"dc2db581-e7f8-4a80-b2da-c9e1de0f3239","type":"join","parameters":[],"name":"Materials","base2":"b17583c9-f431-49d8-845f-aa54152cf5ad","active":"1","source_type":"library","query":"e4db1580-1669-4967-a631-6ac71287e97d","filterable":"0","sourceActive":"0","sourceExists":"0"},{"id":"a8955d0e-718e-4100-a0a3-f243594ca2ef","uuid":"0ed866b1-1344-46f0-9ea1-beaa727019a7","type":"filter","parameters":[{"name":"","operator":"IN","operand":[{"value":"essay","name":"Essay"}]}],"name":"Key","parent":"dc2db581-e7f8-4a80-b2da-c9e1de0f3239","active":"1","source_type":"library","config":"<p><k>subquery_references</k><v><t>dc2db581-e7f8-4a80-b2da-c9e1de0f3239</t></v></p>","query":"e4db1580-1669-4967-a631-6ac71287e97d","filterable":"0","sourceActive":"0","sourceExists":"0"}],"base":"58db8520-0689-40b0-8cea-d509f4946dc6"}

Multiple Materials

{"type":"subquery","parts":[{"uuid":"c6987ca9-1163-4439-8e90-0161254262ad","type":"export","parameters":[],"active":"1","name":"url","source_type":"subquery","sql":"-4OSm3zwc9AsvIXmCbnsrDZZ1sSkfWNv708eOqxZ9FmWYpZoVAy5pEZHIIkqMFFNywxByy1OUh_9GrmxRRl2pfjk9d5ssSQvGx_wX9K1XHwduopnkg7tseAcHoqcI9WmOFmD8bp65PoYeyQ02DzliSKkLKBLrcge6UzfIuv5BL8cQEzBzy65XrviyPtw2PnF4rGl-rO6qa0Pm9ix7MrbILzzoo0T52o4vLtQ2QXk4s16i_Yjn_ktLIEZl7oVNcCp3Ujpl6rkUWcByDgMyJ0HJHiF8xcxooAe","config":"<p><k>subquery_output</k><v>formula</v></p><p><k>subquery_rank</k><v>1</v></p><p><k>subquery_formula</k><v>'/apply/download.pdf?part='\n+ CONCAT('stream:', dbo.toGuidString([stream]))\n+ '&amp;h='\n+ dbo.toGuidString(dbo.md5(convert(varbinary(max), concat('stream:', dbo.toGuidString([stream])) + dbo.salt())))</v></p><p><k>subquery_query</k><v><base>58db8520-0689-40b0-8cea-d509f4946dc6</base><s active=\"1\" name=\"Dummy\" uuid=\"df900a43-348a-44ce-9e4d-1b61c2d6109d\" source_type=\"literal\" query=\"e4db1580-1669-4967-a631-6ac71287e97d\" filterable=\"0\" sourceActive=\"0\" sourceExists=\"0\"><v>Dummy</v></s></v></p>","query":"e4db1580-1669-4967-a631-6ac71287e97d","filterable":"0","sourceActive":"0","sourceExists":"0"},{"id":"6e7cb9d6-e33e-4d54-bb00-367f92275903","uuid":"41bd9c1a-ce8c-4ca8-8c73-e300e177ac0d","type":"export","parameters":[],"active":"1","name":"name","parent":"968d61e6-35f5-4a95-9ea4-71b0697bfec8","source_type":"library","config":"<p><k>subquery_references</k><v><t>968d61e6-35f5-4a95-9ea4-71b0697bfec8</t></v></p>","query":"e4db1580-1669-4967-a631-6ac71287e97d","filterable":"0","sourceActive":"0","sourceExists":"0","format_type":"varchar"},{"id":"9af1a4e9-3cc7-4e8d-897f-359411895ec5","uuid":"dc2db581-e7f8-4a80-b2da-c9e1de0f3239","type":"join","parameters":[],"name":"Materials","base2":"b17583c9-f431-49d8-845f-aa54152cf5ad","active":"1","source_type":"library","query":"e4db1580-1669-4967-a631-6ac71287e97d","filterable":"0","sourceActive":"0","sourceExists":"0"},{"id":"d7baeaa6-9045-4c07-830b-18b8c4cc0b85","uuid":"968d61e6-35f5-4a95-9ea4-71b0697bfec8","type":"join","parameters":[],"name":"Lookup Material","parent":"dc2db581-e7f8-4a80-b2da-c9e1de0f3239","base2":"52f34332-e5ad-463d-a2d3-a60fd62c01d9","active":"1","source_type":"library","query":"e4db1580-1669-4967-a631-6ac71287e97d","filterable":"0","sourceActive":"0","sourceExists":"0","sourceJoin":"left outer join [lookup.material] lm__XID_ on (lm__XID_.[key] = m__JID_.[key])"},{"id":"f50bea68-a252-4c79-836b-ef4c9288903d","uuid":"9319a5d7-aded-449f-828a-a539ecf786ab","type":"filter","parameters":[{"name":"","operator":"=","operand":[{"value":"1","name":"Active"}]}],"name":"Active Status","parent":"968d61e6-35f5-4a95-9ea4-71b0697bfec8","active":"1","source_type":"library","config":"<p><k>subquery_references</k><v><t>968d61e6-35f5-4a95-9ea4-71b0697bfec8</t></v></p>","query":"e4db1580-1669-4967-a631-6ac71287e97d","filterable":"0","sourceActive":"0","sourceExists":"0"},{"id":"bb73a021-cf89-431a-8233-5adae66cff9f","uuid":"ce630759-f859-4c86-8c18-cf992c0388c7","type":"filter","parameters":[{"name":"Access","operator":"IN","operand":[{"value":"1","name":"Available for Student Upload"}]}],"name":"Access","parent":"968d61e6-35f5-4a95-9ea4-71b0697bfec8","active":"1","source_type":"library","config":"<p><k>subquery_references</k><v><t>968d61e6-35f5-4a95-9ea4-71b0697bfec8</t></v></p>","query":"e4db1580-1669-4967-a631-6ac71287e97d","filterable":"0","sourceActive":"0","sourceExists":"0"}],"base":"58db8520-0689-40b0-8cea-d509f4946dc6"}