More detailed example of pre-populating fields from external source

Forms Public Forms
1

Dear Plumsail,

I have read with interest your articles about pre-populating form data:-

But there isn’t enough material here for me to figure out how to get myself started. I can’t seem to find what I’m looking for in the community forum, and I think an expanded example would make a useful addition to your website!

For my use case, I wish to:

  1. Add a URL parameter called UserID (which I could happily do myself) and for this to populate a hidden field called UserID in the public form.
  2. Use the value of the UserID to lookup that person’s name, email and age in the following table, in order to pre-populate those form fields (though maybe they can modify their values in case their info has changed).
  3. I could host this table as an excel file on OneDrive for example (I don't wish to incorporate all the data within the URL itself as it will be too long, and I do not wish to populate the fields from their browser storage, as they might use multiple computers.)
  4. There will then be additional fields that they will fill in before submitting their form response. (For example their job title.)

I would be enormously grateful if you could give me the javascript code that I can paste into the JS editor in my plumsail form to get it working (after I have customized it with the correct url and field names), as well as giving step-by-step instructions (e.g. do the looked-up fields need to be dropdown type? Do they need to be disabled?) – While the use case above is a toy example, I can generalize it to my requirements.

UserID,Name,Email,Age
6576edgjhgc4r87654dt,JoeBloggs,joe.bloggs@emailtheplanet.com,Child
08u7udcwo3uz93bcigh,JohnSmithjohn.smith@supersupermail.com,Adult

Thank you!

2

Hey @UNECE,

This is the code I used to make a simplified version of your form:

fd.rendered(async function() {
    let userid = getUrlQery();
    await fd.field('DropDown1').$on('ready', () => {
        getTableData();
        populateFields(userid);
    });
});

function getTableData() {
    let dropdown = fd.field('DropDown1');
    let table = fd.control('DataTable');
    table.value = [];
    
    for (i = 1; i < 1000; i++) { // Here "i < 1000" is needed so that an eternal cycle doesn't occur; adjust the value if there are more than 1000 rows in your table
        dropdown.value = i.toString();
        if (dropdown.selectedItem == null) return;
        
        var record = {UserId: dropdown.selectedItem.B, Name: dropdown.selectedItem.C, Surname: dropdown.selectedItem.D};
        table.value.push(record);
    }
}

function getUrlQery() {
    var queryString = window.location.search;
    var urlParams = new URLSearchParams(queryString);
    return urlParams.get('userid');
}

function populateFields(userid) {
    let table = fd.control('DataTable');
    fd.field('Name').value = "";
    fd.field('Surname').value = "";

    for (let i = 0; i < table.value.length; i++) {
        if (userid == table.value[i].UserId) {
            fd.field('Name').value = table.value[i].Name;
            fd.field('Surname').value = table.value[i].Surname;
        }
    }
}

I used a dropdown field to get data from an excel table hosted in my OneDrive, like this: Drop Down field — Public web forms

Here's how the table itself looks (I need the Number column to iterate through all rows, it should have numbers counting up from one; the "value column" property of the dropdown is set to A):
Screenshot (134)

I then stored the data in a DataTable control, like this: Data Table — Public web forms

Let me know if you have any questions.

3

Here's the exported form so you can take a look at it as a whole:
Table pre-populating.json (4.4 KB)