How to download a text file from CRM

Rick Root

Hello there!

I was wondering if there's a relatively easy way to generate and download a simple text file from CRM? Basically, I've got a query that returns a JSON text field and I'd like to make it easy for certain users to download that text file.

Thanks!

Rick

John Parker

Hey Rick,

We've accomplished this in two different ways. The first and easiest way is if you have blackbaud's "auto file download" process (which I believe is not an out of the box feature), you can supply your query as an export to it and in the filename parameter you can literally add .txt to the end of the file name.

The second way is I found with a bit of digging the process that blackbaud uses to download as csv or xlsx can be used as a clr action on a data list for example. The script is here -

browser/htmlforms/platform/businessprocess/BusinessProcessExportAction.js

and there is actually a parameter you can set called ExportFormat. So if you have a datalist of queries/exports you want to export you can do something like this, just making sure your datalist pulls all the parameters as well.

Action -

<Action ID="00000000-0000-0000-0000-000000000000" Caption="Download as txt" ImageKey="RES:save">
    <ExecuteCLRAction xmlns="bb_appfx_commontypes">
        <ScriptIdentifier Url="browser/htmlforms/platform/businessprocess/BusinessProcessExportAction.js" ObjectName="BBUI.customactions.platform.BusinessProcessExportAction">
            <StaticParameters>
                <ParameterList>
                    <Param ID="TableKey">
                        <Value>Output</Value>
                    </Param>
                    <Param ID="RecordIDField">
                        <Value>BUSINESSPROCESSSTATUSID</Value>
                    </Param>
                    <Param ID="EnableActionFieldID">
                        <Value>ENABLEDOWNLOAD</Value>
                    </Param>
                    <Param ID="ParameterSetIDField">
                        <Value>PARAMETERSETIDFIELD</Value>
                    </Param>
                    <Param ID="BusinessProcessCatalogIDField">
                        <Value>BUSINESSPROCESSCATALOGID</Value>
                    </Param>
                    <Param ID="ProcessNameIdField">
                        <Value>NAME</Value>
                    </Param>
                    <Param ID="ExportFormat">
                        <Value>txt</Value>
                    </Param>
                </ParameterList>
            </StaticParameters>
        </ScriptIdentifier>
        <ActionContext>
            <SectionField>ID</SectionField>
        </ActionContext>
    </ExecuteCLRAction>
</Action>

Data list to get parameters -

select
    ep.ID
    ,ep.DESCRIPTION
    ,case 
        when ahq.ID is not null then 'Ad-hoc query'
        when sqi.ID is not null then 'Smart query instance'
        else 'Export definition' end as EXPORTTYPE
    ,case 
        when ahq.NAME is null then 
        case 
            when sqi.NAME is null then ir.NAME 
            else sqi.NAME end 
        else ahq.NAME end as QUERYSELECTION
    ,ed.NAME as EXPORTDEFINITION
    ,bps.STARTEDON as LASTRUNDATE
    ,cast(bps.ENDEDON - bps.STARTEDON as time) as LASTRUNDURATION
    ,case 
        when au.DISPLAYNAME = '' then au.USERNAME 
        else DISPLAYNAME end  as LASTRUNBY_USERNAME
    ,bps.NUMBERPROCESSED as LASTRUNRESULTCOUNT
    ,isnull(bps.STATUS, 'Not started') as LASTRUNSTATUS
    ,bpi.OWNERID as OWNERID
    ,coalesce(s.NAME, 'All sites') as SITES
    ,ep.SMARTQUERYINSTANCEID
    ,ep.ADHOCQUERYID

    -- Parameters needed for exporting
    ,bps.ID as BUSINESSPROCESSSTATUSID
    ,1 as ENABLEDOWNLOAD
    ,bpi.BUSINESSPROCESSPARAMETERSETID as PARAMETERSETIDFIELD
    ,ep.NAME
    ,bpi.BUSINESSPROCESSCATALOGID
from 
    EXPORTPROCESS ep
left join ADHOCQUERY ahq on ep.ADHOCQUERYID = ahq.ID
left join IDSETREGISTERADHOCQUERY ira on ep.ADHOCQUERYID = ira.ADHOCQUERYID
left join SMARTQUERYINSTANCE sqi on ep.SMARTQUERYINSTANCEID = sqi.ID
left join IDSETREGISTERSMARTQUERYINSTANCE irs on ep.SMARTQUERYINSTANCEID = irs.SMARTQUERYINSTANCEID
left join BUSINESSPROCESSINSTANCE bpi on ep.ID = bpi.BUSINESSPROCESSPARAMETERSETID
left join SITE s on s.ID = bpi.SITEID
left join EXPORTDEFINITION ed on ed.ID = ep.EXPORTDEFINITIONID
left join IDSETREGISTER ir on ir.ID = ep.SELECTIONID
left join IDSETREGISTERADHOCQUERY ira2 on ir.ID = ira2.IDSETREGISTERID
left join IDSETREGISTERSMARTQUERYINSTANCE irs2 on ir.ID = irs2.IDSETREGISTERID
-- Gets the most recent run
outer apply (
    select top 1
        ID
        ,STARTEDON
        ,STATUS
        ,STARTEDBYUSERID
        ,ENDEDON
        ,NUMBERPROCESSED
        ,BUSINESSPROCESSPARAMETERSETID
    from BUSINESSPROCESSSTATUS bps
    where bps.BUSINESSPROCESSPARAMETERSETID = bpi.BUSINESSPROCESSPARAMETERSETID
    order by STARTEDON desc
) as bps
left join APPUSER au
    on au.ID = bps.STARTEDBYUSERID
where 
    -- Use this to limit to the queries you want
    sqi.FOLDERID = ''
    or ahq.FOLDERID = ''