3. Create Template File

Create the template file with merge fields in Excel, Word, and PowerPoint files.

 

3.1.  Create Template File

Create the template files as follows.

 

Common

    Template file format

    • Create a template file in the same file format as the output file format, either Excel (.xlsx, .xlsm), Word (.docx, .docm), or PowerPoint (.pptx, pptm). For PDF output, create a Word (.docx) format.
    • Not all layouts and output patterns are supported. Complex layouts are not supported.
    • Template file size should not exceed 1 MB. If the file contains a large amount of text or images, even within 1 MB, the file will fail to output.
    • Not all features of Office files are available. Some features will be ignored.
    • Template files should be created in the Microsoft Office desktop version. Template files created by the online version or other applications are not supported.
    • Images such as company logos can be included in the template file. However, if large size images such as high-quality photos are included in the template file, it may take a long time to output the file, or an output error may occur. In the case of PDF output, the layout may be corrupted. Please confirm by actually outputting in a test environment.

     

    Merge Field

    • Enter the merge fields with the format {!Object Name.Field Name} in the template file.The format of the merge field is the same as the Salesforce classic email template.
    • The fields of the master object and related child objects can be used as the merge fields.
    • If the user outputting the file does not have reference permission to the merge field, the merge field will be output blank.
    • Formulas cannot be used such as {!If(xxxxx)} for the merge field.

    Example of merge fields

    Standard field of standard object: {!Opportunity.Name}

    Custom field of custom object: {!Bill__c.IssueDate__c}

     

    Reference Field

    • Reference fields can be output either Id or Name field. To output Name field, check the "Output Name Not Id For Reference Field" checkbox in the OFC_Template record.
    • Cross-Object Formulas (e.g. {!Opportunity.Account.BillingCity}) are available in ver 2.0 and later.

     

    Rich Text Area Field

    Rich text area field values are output with Html tagged values stored in Salesforce. The output cannot be formatted as it displays on the record screen. The values stored in Salesforce can be viewed in the Developer Console or Dataloader.

     

    Decimal point Considerations

    Some standard fields, such as Opportunity Amount and Quote Amount, may have decimal points stored in Salesforce even if the decimal point is not displayed on the record screen. For example, if Opportunity Quantity contains a decimal point, Amount is stored with a decimal point even though the data type of Amount is "Currency (16, 0)". The values stored in Salesforce should be checked in the Developer Console or Dataloader.

     

    For output options, refer to Output Options.

     

     

    Child Object

    • For the merge fields of the child object, set {!ChildObject Relationship Name.Filed Name} format in the template file. If the reference field is a custom field, append "__r" at the end of the child relationship name
      e.g., The reference field is a standard field: {!OpportunityLineItems.Product2}
      e.g., The reference field is a custom field: {!OpportunityCases__r.CaseNumber}
    • For the merge fields of the child object of the package object, set {!Namespace Prefix__Child Relationship Name__r.Field Name} format in the template file.
      g., If the package namespace prefix is "BillingMaster
      and the child relationship name is "BM_BillDetail_Bills,
      {!BillingMaster__BM_BillDetail_Bills__r.Name}
    • For the merge fields of the child object, enter only one line. The number of lines is output according to the number of child records.
    • To output sequential numbers of child records, enter {!number()}. Sequential numbers starting from 1 is output.

     

       *How to check the child relationship name

    1. Go to the Object Manager and check the list of fields of the corresponding child object.
    2. Click on the field name of the primary objects reference field in the child objects field list.
    3. The content displayed in the child relationship name in the fields setting information is the child relationship name. At this time, if the parent objects reference field is a custom field, the child relationship name will be the one with "__r" appended to the end of the displayed child relationship name. For package objects, prefix with namespace prefix + "__".

     

     

    PDF

    • To output page numbers to PDF, enter {!pagenumber()} in the header or footer of the Word file.
    • To output the total number of pages to PDF, enter {!pagecount()} in the header or footer of the Word file.
    • In the Word template file, in "Normal" style paragraphs, check off "Snap to grid when document grid is defined". If this is checked, the line spacing in the PDF file may not be set correctly.
    • To stripe a table of child objects, enter two child object rows. For example, if the first line has a blue background and the second line has a white background, odd-numbered lines are output with a blue background, and even-numbered lines with a white background. Alternatively, set the format to striped from the table design.
    • Only the following four PDF fonts can be used for PDF output. If other fonts are used in the Word template file, it will be output in the font set in the "PDF default font" in the OFC_Template record. When using double-byte characters such as Japanese, only Arial Unicode MS fonts can be output.

     

    Word Font PDF Font
    Arial Arial Unicode MS
    Microsoft Sans Serif
    Tahoma
    Helvetica
    Helvetica(sans-serif)
    Times New Roman
    Times
    Century
    Times(serif)
    Courier New
    Courier
    Monospace
    Courier(Monospace)

     

    PDF Limitations

    • Word field codes are not supported.
    • Blank spaces with tabs in Word are not supported.
    • Shapes and object output are not supported.
    • When outputting PDF, the ruled line of the table may be cut off from the bottom line of the previous page or the first line of the next page at page break.
    • Arial Unicode MS doesn’t support bold or italic font-weight.
    • External characters are not output.

     

       

      Sample

      Example of merge fields

      Primary Object

      {!Opportunity.Name} *Standard object.Standard field

      {!Bill__c.IssueDate__c} *Custom object.Custom field

       

      Child object

      {!OpportunityLineItems.Name} *Standard field.Standard field

      {!BillDetails__r.IssueDate__c} *Custom field.Custom field

       

      Excel Sample

       

      Word Sample

       

       

      3.2.  Reference

      When creating a template, also refer to the following articles.

      How to find the merge field and childrelation names

      Generate an Excel file in Salesforce using Office File Creator – (1) Create a template file

      Setting Formulas in Excel Template Files with Office File Creator

      Generate a Word document in Salesforce using Office File Creator

      Generate a PowerPoint file in Salesforce using Office File Creator

      Generate a PDF file in Salesforce using Office File Creator

       

       

      3.3.  Download Sample Template Files