Disable "fill-in" in a choice field with this options set

Forms SharePoint 2019
1

Short question:
Is it possible to render a "choice-field" with the "fill-in" option as a regular choice field?

Long explanation:
My use case:
I'm in the process of migrating an existing MS Access database to SharePoint and converting the associated forms to Plumsail.

The database contains many lookup fields.

However, the author of the database did not work with ID as the primary key, that is to say: with the lookup fields, the value of the chosen option is stored in the field, and not the ID of the chosen option.

I could make the original database relational, but this is not only a lot of work, it also involves all kinds of queries and workflows.

So the idea now is to work for the form in Plumsail with:

  • a choice field with the option "Allow 'Fill-in' choices: Yes"
  • In SharePoint, the definition of this choice field contains only one option. A dummy value, e.g. "-"
  • when opening the Plumsail form, the values of the source list are retrieved using PnP and the options of the choice field are overridden using something like
fd.field(fieldName).widget.dataSource.data(myOptions);

That way I have simulated a "lookup" field in SharePoint, but where the value of the option is also written in the field instead of the ID.

So far, so good.

The only disadvantage of this way of working is that a user can also fill in other options themselves. After all, the definition of the choice field says "Allow 'Fill-in' choices: Yes".

But if I disable this option, I can't save a chosen option in the field (because the chosen option is not part of the SharePoint definition of the choice options, and you receive the error message "The text you entered isn't an item in the list.")

So ideally, my "SharePoint choice field with Allow 'Fill-in' choices: Yes" is rendered in Plumsail as a "choice field with Allow 'Fill-in' choices: No".

Is this possible?
Is it possible to prevent users from entering their own value in the choice field, even though the option "Allow 'Fill-in' choices" is enabled?

any help is appreciated,

kind regards,
bartplessers

2

Hi @bartplessers,

I think the easiest way to achieve this is to hide the SharePoint choice field and create a common Single choice field in its place for the user to fill in.
Once the SharePoint field is ready and configured, copy its options to the common field like so: Single Choice field — Plumsail SharePoint Forms Documentation

The Single choice field allows you to change its options with JavaScript even when its "Allow user value" property is false.

Make sure to copy the chosen value back into SharePoint field in fd.spBeforeSave(), and let me know if you have any issues implementing this approach.

3

Hi @Ilia,

thanx for you prompt response.

Indeed, I created already something like

  • SharePoint txt-field "fieldValue"
  • "dummy" PlumSail dropdown control "fieldName" (not bound to any SharePoint field)
  • some javascript logic that
    • populates the options in the dropdown
    • checks if the SharePoint field "fieldValue" has a value and sets the option of "fieldName" active
    • adds an action to the dropdown "fieldName" to populate "fieldValue" with the newly choosen option

So basically it's the same you suggested: using a unbound control to populate a SharePoint txt-field

For your reference, code here:

    async function changeDropDownField(fieldName, fieldValue, listName, columnName, columnNameExtra) {

        // get values for choice field
        let items = await pnp.sp.web.lists.getByTitle(listName).items.orderBy(columnName, true)();
        //console.log(items);

        // generic function to extract a column from a 2dim array
        function extractColumn(arr, column) {
            return arr.map(x => x[column])
        }

        // get the options of the dropdown field
        let myOptions = extractColumn(items, columnName);
        //console.log(myOptions);

        // set the options of the dropdown field
        fd.field(fieldName).widget.dataSource.data(myOptions);

        // find select value (stored in a corresponding text field) and set this option active  
        let index = myOptions.indexOf(fd.field(fieldValue).value);
        //console.log(index);
        fd.field(fieldName).widget.select(index);

        // add action to update the dropdown field
        function addAction(fieldName) {
            function myAction() {
                fd.field(fieldValue).value = fd.field(fieldName).value;
            }

            // Calling myAction value changes
            fd.field(fieldName).$on('change', myAction);

            // Calling myAction on form loading
            // myAction();

        }

        addAction(fieldName);

        // bug: je kan nog niet werken met lokale variabelen in de template
        // zie ook
        // https://community.plumsail.com/t/choice-field-how-to-use-local-variables-in-widgetoptions-template/14960
        myItems = items;
        myColumnName = columnName;
        myColumnNameExtra = columnNameExtra;

        // create template for custom rendering
        var template = '';
        //template += '#: console.log(data) # '
        //template += '#: console.log( items.find(item => item["DS_code"] == data)["DS_naam"] ) # '
        //template += '#: console.log(myGlobalVar) # '
        template += '<div style="white-space:nowrap;">';
        template += '<span style="'
        template += 'display: inline-block;'
        template += 'width:150px;'
        template += 'overflow: hidden;'
        template += 'white-space:nowrap;'
        template += 'text-overflow:ellipsis;'
        template += 'border-right: 1px solid silver;'
        template += '">'
        template += '#: data # '
        template += '</span> ';

        template += '<span style="'
        template += 'display: inline-block;'
        template += 'width:550px;'
        template += 'overflow: hidden;'
        template += 'white-space:nowrap;'
        template += 'text-overflow:ellipsis;'
        template += '">'
        template += '#: myItems.find(item => item[myColumnName] == data)[myColumnNameExtra] # '
        template += '</span> ';

        template += '</div>';

        fd.field(fieldName).widgetOptions = {
            template: template
        }
    }

PS.
Same problem here applies using local variables in templates, see also