Oqtane Tutorials
Oqtane Tutorials

Formulas Tutorials

Tutorial Home › Formulas
Get Started with Formulas
#2 Formulas for Pickers / DropDowns
Formulas affecting Field Settings
Requirements
Resources

Formulas for Pickers / DropDowns

These examples show how Formulas can modify the list of available options.

Important: Now using Formulas V2 🆕 in 2sxc 16

Formulas V2 has intellisense, stoppability, support for promises and more.

Important: The feature "Public Use of Edit Form" is disabled

If you want this demo to run for anonymous users you would need it. Register your site on patrons.2sxc.org to get access to the feature.

Try it:

    This formula changes the values in the second dropdown based on the country picked first. 


    Click on the (Σ) button above to see the edit-UI with the formula.

    Formulas of FormulasDropdown.Office

    Field.Settings.DropdownValues (Formula-Target: Field.Settings.DropdownValues)

    v2((data) => {
  // Start with the default list, as it could have changed
  const lines = data.default.match(/[^\r\n]+/g);
  
  // Filter and join again
  const keep = lines.filter(l => l.startsWith(data.Country));
  return keep.join('\n');
});

    Field.Settings.Disabled (Formula-Target: Field.Settings.Disabled)

    v2((data) => {
  return !data.Country;
});

    Try it:

      This formula changes the values in the second dropdown if the user wants more options. 


      Click on the (Σ) button above to see the edit-UI with the formula.

      Formulas of FormulasDropdownDirections.Options

      Field.Settings.DropdownValues (Formula-Target: Field.Settings.DropdownValues)

      v2((data, context) => {
  if (!data.FilterMainOptions) return data.default;
  const lines = data.default.split('\n');
  // only keep lines with 1 code like l,r,t etc.
  const onlyMain = lines.filter(v => v.indexOf(':') == 1);
  return onlyMain.join('\n');
});

      Try it:

        This is an advanced formula which will call a WebApi to get the possible values in a drop down. 


        Click on the (Σ) button above to see the edit-UI with the formula.

        Formulas of FormulasDropdownWebApi.Dropdown

        Field.Settings.DropdownValues (Formula-Target: Field.Settings.DropdownValues)

        v2((data, context) => {
  // Call the sxc web-api controller from other tutorials
  return context.sxc.webApi.fetchJson('app/auto/api/formulas/OptionsFromBackend')
    .then(data => {
      console.log('got data', data);// log for demo reasons
      // The result is a dictionary, so we convert to lines
      const lines = Object.keys(data)
        .map(k => k + ":" + data[k])
        .join('\n');

      return lines;
    });
});

        Source Code of FormulasController.cs

        // Add namespaces for security check in Oqtane & DNN despite differences in .net core/.net Framework
// If you only target one platform, you can remove the parts you don't need
#if NETCOREAPP
using Microsoft.AspNetCore.Authorization; // .net core [AllowAnonymous] & [Authorize]
using Microsoft.AspNetCore.Mvc;           // .net core [HttpGet] / [HttpPost] etc.
#else
using System.Web.Http;                    // .net 4.5 [AllowAnonymous] / [HttpGet]
using DotNetNuke.Web.Api;                 // [DnnModuleAuthorize] & [ValidateAntiForgeryToken]
#endif
using System.Linq;        // this enables .Select(x => ...)
using System.Collections.Generic;          // To use the Dictionary

[AllowAnonymous]                          // all commands can be accessed without a login
public class FormulasController : Custom.Hybrid.Api14 // see https://r.2sxc.org/CustomWebApi
{
  [HttpGet]                               // [HttpGet] says we're listening to GET requests
  public object OptionsFromBackend()
  {
    var results = new Dictionary<string, string>() {
      { "first", "First option from WebApi" },
      { "second", "Second option from WebApi"},
      { "third", "3rd option" }
    };
    return results;
  }

  // Note: Persons just copied from Books Controller to test functionality
  [HttpGet]                               // [HttpGet] says we're listening to GET requests
  public object Persons()
  {
    return AsList(App.Data["Persons"])
      .ToDictionary(x => x.EntityId.ToString(), p => p.FirstName + " " + p.LastName);
  }
}

// The next line is for 2sxc-internal quality checks, you can ignore this
// 2sxclint:disable:no-dnn-namespaces - 2sxclint:disable:no-web-namespace

        Try it:

          This is an advanced formula which will call a WebApi to get the possible values in a drop down - from Data in the App. 


          Click on the (Σ) button above to see the edit-UI with the formula.

          Formulas of FormulasDropDownWebApiData.Dropdown

          Setting DropdownValues (Formula-Target: Field.Settings.DropdownValues)

          v2((data, context) => {
  // Call the sxc web-api controller from other tutorials
  const promise = context.sxc.webApi.fetchJson('app/auto/api/formulas/Persons')
    .then(result => {
      console.log('got result', result);// log for demo reasons
      // The result is a dictionary, so we convert to lines
      return Object.keys(result)
        .map(k => k + ":" + result[k] + ' (' + k + ')')
        .join('\n');
    });

  // Return a loading value and a promise for later...
  return { value: data.value, promise };
});

          Source Code of FormulasController.cs

          // Add namespaces for security check in Oqtane & DNN despite differences in .net core/.net Framework
// If you only target one platform, you can remove the parts you don't need
#if NETCOREAPP
using Microsoft.AspNetCore.Authorization; // .net core [AllowAnonymous] & [Authorize]
using Microsoft.AspNetCore.Mvc;           // .net core [HttpGet] / [HttpPost] etc.
#else
using System.Web.Http;                    // .net 4.5 [AllowAnonymous] / [HttpGet]
using DotNetNuke.Web.Api;                 // [DnnModuleAuthorize] & [ValidateAntiForgeryToken]
#endif
using System.Linq;        // this enables .Select(x => ...)
using System.Collections.Generic;          // To use the Dictionary

[AllowAnonymous]                          // all commands can be accessed without a login
public class FormulasController : Custom.Hybrid.Api14 // see https://r.2sxc.org/CustomWebApi
{
  [HttpGet]                               // [HttpGet] says we're listening to GET requests
  public object OptionsFromBackend()
  {
    var results = new Dictionary<string, string>() {
      { "first", "First option from WebApi" },
      { "second", "Second option from WebApi"},
      { "third", "3rd option" }
    };
    return results;
  }

  // Note: Persons just copied from Books Controller to test functionality
  [HttpGet]                               // [HttpGet] says we're listening to GET requests
  public object Persons()
  {
    return AsList(App.Data["Persons"])
      .ToDictionary(x => x.EntityId.ToString(), p => p.FirstName + " " + p.LastName);
  }
}

// The next line is for 2sxc-internal quality checks, you can ignore this
// 2sxclint:disable:no-dnn-namespaces - 2sxclint:disable:no-web-namespace

           

          Get Started with Formulas
          #2 Formulas for Pickers / DropDowns
          Formulas affecting Field Settings