Available in: Axsy Mobile for Salesforce, Axsy Field Service, Axsy Public Sector, Axsy Retail Execution

In addition to using the Axsy Smart Form Designer to create Smart Forms, it is also possible to programatically create Smart Forms using code. This article gives a description of how an Axsy Smart Form can be generated from a source Excel file using a SFDX script.

The methods described in this article are just one example of programatically creating a Smart Form. The concepts could be adapted to create Smart Forms using similar methodologies.

Axsy Smart Forms Data Model

The Axsy Smart Form Designer is under-pinned by several custom objects which are included as part of the Axsy Smart Forms Managed Package. As a Smart Form is created using the Designer, the underlying records for these Smart Form objects are created and modified accordingly.

The following table gives a non-exhaustive list of some of the relevant Smart Form objects:

As with most Salesforce objects, there are multiple methods for creating and editing records. As such, it is possible to create Smart Form records - such as for the objects in the table above - outside of the Smart Form Designer. Therefore, code can be written to generate entire Smart Forms by programatically generating its component Smart Form records. Furthermore, any generated Smart Form records would be picked up by the Smart Form Designer, allowing a programatically generated Smart Form to be accessible and further editable via the Designer.

Generating a Smart Form from an Excel File

One example scenario where it may be beneficial to create Smart Forms programatically - instead of manually with the Smart Form Designer - is when there is a need to convert existing checklists from one format, such as Excel files, into the Axsy Smart Form format. Rather than having to manually copy-paste the content from the Excel file directly into the Smart Form Designer, a script could instead be written to parse the contents of the Excel file and create the matching Smart Form records automatically. This one script could then be re-used across multiple Excel files of the same format to create multiple Smart Forms. 

Format of Source Excel File

As an example, see below a screenshot of an Excel file:

The format of the above example Excel file could be mapped to an Axsy Smart Form in the following manner:

Item	Excel Component	Corresponding Smart Form Component
	Work sheet tab	Each tab represents a Section of the Smart Form.
	"Section Title" column	In the Excel file, each row has a "Section Title". Rows with the same "Section Title" are grouped together as Sub-Sections within a Smart Form Section.
	Row	Each individual row represents a checklist item to be verified by the mobile user, i.e. each row represents a Smart Form Element.

To better illustrate the above mapping, here is a Smart Form running in the Axsy Mobile App that was generated based on the mapping in the above table:

Using an SFDX Script to Generate Smart Form Data

Using the above mapping as a guideline, a SFDX script could be written that takes as input an Excel file of that format and then generates the necessary Smart Form records. Please see the pseudo-code examples below which can be used as a starting point for such a script.

NOTE: It is assumed that the reader will be able to use the “sf” tool to create a skeleton SfdxCommand project using javascript/typescript. This framework provides primitives to create and update Salesforce objects on your target Salesforce Org.

NOTE: It may be easier to convert the XLS file into one or more CSV files and use those as input instead. Conversion from XLS to CSV format can be done within the Excel app itself.

Example pseudo-code to set up for Axsy Smart Form creation:

// 1: read the csv data

// csv lines will typically parse into an array of records like this:
type CsvLine = Record
type CsvFile = CsvLine[]
// the data for the entire spreadsheet will be a set of these:
type AllData = Record

// readAllData can, for example, read all the csv files in a given directory:
const data: AllData = readAllData();

// 2: transform the data into the "shape" of the Smart Form:
type Document = {
  name: string;
  sections: Section[];
};
type Section = {
  name: string;
  subsections: Subsection[];
};
type Subsection = {
  name: string;
  paragraphs: Paragraph[];
};
type Paragraph = {
  type: string;
  code: string;
  regulationText: string;
};
// note that we have only transformed the raw data here, we have not yet created an interactive form
const document: Document = transformData(data);

Example pseudo-code to create the Axsy Smart Form:

// createRecord will actually run the API call to create the record on the org
// formId will carry the actual Salesforce ID of the record to create junction 
// objects later
const formId = createRecord("axsy_forms__Form__c", {Name: document.name});

Example pseudo-code to create the Sections within the above Smart Form:

// note the description field here can be used to create a unique key for the record
// which might be useful for later updates (optional)
const sectionIds = document.sections.forEach(section => {
  createRecord("axsy_forms__Section__c", {
    Name: section.name,
    axsy_forms__Description__c: `${document.name}/${section.name}`
  });
});
// create a look-up mapping section names to sections SF ids:
const sectionIdsByName = // for example use lodash keyBy and mapValues

// we must now create the junction objects joining the sections to the form:
document.sections.forEach((section, i) => {
  const sectionId = sectionIdsByName[section.name];
  createRecord("axsy_forms__Form_Section_Association__c", {
    axsy_forms__Form__c: formId,
    axsy_forms__Form_Section__c: sectionId,
    axsy_forms__Sort_Order__c: i * 100,
    axsy_forms__Composite_Key__c: `${formId}-${sectionId}`
  });
});

Example pseudo-code to create a Sub-Section within the above Smart Form Section:

// for each section, its contents, subsections and paragraphs will be 
// represented by a flat list of Element records
// so, assume we have a function like this to create an element:
function createElement(sectionId, type, name, sortOrder, data?) {
  const elementId = createRecord("axsy_forms__Form_Element__c", {
    axsy_forms__Section__c: sectionIdsByName[name],
    axsy_forms__Type__c: type,
    axsy_forms__Name__c: name,
    axsy_forms__Data__c: JSON.stringify(data),
  });
  // create the junction object to link the element to the section
  createRecord("axsy_forms__Form_Section_Element_Association__c", {
    axsy_forms__Composite_Key__c: `${sectionId}-${record.Id}`,
    axsy_forms__Form_Section__c: sectionId,
    axsy_forms__Form_Element__c: elementId,
    axsy_forms__Sort_Order__c: sortOrder
  });
}
// then, for each section
document.sections.forEach((section, i) => {
  const sectionId = sectionIdsByName[section.name];
  // assume a function nextSortOrder() 
  section.subsections.forEach((subsection) => {
    const subsectionId = createElement(sectionId, "Subsection", subsection.name, nextSortOrder());
    subsection.paragraphs.forEach((paragraph) => {
      // TODO create the elements for each paragraph, see below 
    });
  });
});

Example pseudo-code to create a Display Text Element with the checklist item text:

createElement(subsectionId, "Static Text", 
               `${paragraph.code} / ${paragraph.regulationText}`,    
               nextSortOrder());

Example pseudo-code to create for each checklist item a corresponding Checkbox/Toggle Element to mark compliance or non-compliance:

// creates a checkbox:
createElement(subsectionId, "Boolean", 
               `Non Compliant?`, nextSortOrder());
// creates a text field:
createElement(subsectionId, "Text", 
               `Reason for non-compliance`, nextSortOrder());

Smart Form Data Modifiers

As the SFDX script generates the exact Smart Form records that are used to power the Axsy Smart Form Designer, it is possible to further modify any programatically generated Smart Form with the Designer. For example, through the Designer, you could add rules around visibility or validation, or add Element modifiers such as whether a Text Element should be single-line vs. multi-line.

If the desired modifications are repeated often through the Smart Form, rather than add them through the Designer, they can instead be coded directly into the script which generates the Smart Form. For example, here are some fields on the Form Element object which are used for modifying its behaviour:

Making a Text Element Multi-line

Here is example pseudo-code for creating a Text Element and then adding a modifier via its "Data" field so that it is a multi-line text area instead of just a single-line text input:

// creates a text field:
createElement(subsectionId, "Text", 
               `Reason for non-compliance`, nextSortOrder(), {multiline:true});

Advanced Concepts and Examples

Upserting to Allow Changes

For each Salesforce record that is created, a good practice would be to give each a unique name or key so that it can be referenced in a later update. For example, the Name field of a Form Element can be used to hold a uniquely generated key (NOTE: this is not to be confused with the Salesforce ID). This key does not need to be human-readable as it is the Label field that is actually displayed to users.

To this point, in the examples above, a hash function was used to create a unique key for elements that combined the document, section and subsection names and, where appropriate, additionally the paragraph code and element type. Using a repeatable hash function allows for future updates to the Smart Form without having to recreate it from scratch. 

NOTE: One downside of using such a hash function is that, if you were to manually change a Section Name, for example, then re-running an update script would generate a brand new Section rather than updated the existing, re-named Section.

Updating Element Label Styles

Following on from the above, if there is a way to refer to elements through a constant key, we can re-parse the XLS or CSV file and run a blanket update on all elements in the Smart Form. For example, the pseudo-code below updates the text-styling of each element's label:

// reload the document
const doc: Document = loadDocFromCSVs()
// the following is a function of the document
// note that we do not need any of the actual salesforce IDs here
const allTitles = selectAllElements(doc).filter(isParagraphTitle);
// now we assume we have already created the form once on the org
allTitles.forEach(title => {
  // updateRecordName will perform the appropriate API call to update the record:
  updateRecordByName("axsy_forms__Form_Element__c",
  {
    Name__c: getElementName(title),
    Label__c: `<p><strong style="font-weight: bold; color: blue">${p.code}</strong>
      ${p.regulationText}</p>`
  });
})

Adding A Dynamic Visibility Rule

If the preference is to dynamically show the “Reason for non-compliance” field only if the mobile user selects the “Non Compliant?” checkbox, this is possible via the HideFormula field of a Form Element. In the below pseudo-code, each formula - one per text element - must refer to a previous Element in the Form, i.e. the checkbox. In this case, the formula must refer to the Salesforce ID of the checkbox element. As it is impossible to know the Salesforce ID when the Axsy Smart Form is first created, the addition of visibility rules need to happen as an update that runs after initial creation.

// the initial creation of the elements:
const elementsSFIdsByName = createElements(doc);
const allReasonTexts = selectAllElements(doc).filter(isReasonText);
allReasonTexts.forEach(reasonText => {
  // find the id of the appropriate checkbox element
  // (checkboxName is derivable from the context of the reasonText element)
  const checkboxId = elementsSFIdsByName[checkboxName];
  updateRecordByName("axsy_forms__Form_Element__c",
  {
    Name__c: getElementName(reasonText),
    HideFormula__c: `NOT(@${checkboxId})`
  });
})

Caching Results

Developing a Smart Form generation tool can be an iterative process. For example, you may end up running the form creation function many times as you develop the section creation function. As such, consider making a generic caching function which can wrap the creation functions such that subsequent calls will not actually attempt a call to your Salesforce Org.

const createCacheFn = (aFn) => {
  return (...args) => {
    // try load from file
    if(fileExists(aFn.name)) {
      return loadFromFile(aFn.name);
    }
    const result =  aFn(...args);
    saveToFileJson(aFn.name, result);
    return result;
  }
}