You are viewing limited content. For full access, please sign in.

Question

Question

Forms Custom Date Field Restrictions & Autofill a Dropdown Field Based on Date

asked two days ago

Hi Everyone,

 

The goal I'm going for is to set the minimum date of a date field as the current date (under the advanced tab, I can only set the minimum as a static date). Once that date is selected, a dropdown field will autofill with the corresponding pay period that date falls under. Any ideas on how to go about this? I've played around with JavaScript, but have been unsuccessful so far.

 

Pay Periods:

 

Pay period dropdown field choices:

0 0

Answer

SELECTED ANSWER
replied 7 hours ago

Here is what I got to work in case this is helpful: 

 

LFForm.onFieldChange(function(event) {
    console.log("Field change event triggered");

    // Get the date value from field 2
    var dateValues = LFForm.getFieldValues({fieldId: 2});
    
    // Check if dateValues is not empty
    if (dateValues.length === 0) {
        console.log("No date value found in field 2");
        return;
    }
    
    var dateValue = dateValues.dateStr; // Use the dateStr property
    
    // Log the date value to the console
    console.log("Date value from field 2:", dateValue);
    
    // Define the pay periods
    var payPeriods = [
        { period: "P1", begins: "12/22/2024", ends: "1/4/2025" },
        { period: "P2", begins: "1/5/2025", ends: "1/18/2025" },
        { period: "P3", begins: "1/19/2025", ends: "2/1/2025" },
        { period: "P4", begins: "2/2/2025", ends: "2/15/2025" },
        { period: "P5", begins: "2/16/2025", ends: "3/1/2025" },
        { period: "P6", begins: "3/2/2025", ends: "3/15/2025" },
        { period: "P7", begins: "3/16/2025", ends: "3/29/2025" },
        { period: "P8", begins: "3/30/2025", ends: "4/12/2025" },
        { period: "P9", begins: "4/13/2025", ends: "4/26/2025" },
        { period: "P10", begins: "4/27/2025", ends: "5/10/2025" },
        { period: "P11", begins: "5/11/2025", ends: "5/24/2025" },
        { period: "P12", begins: "5/25/2025", ends: "6/7/2025" },
        { period: "P13", begins: "6/8/2025", ends: "6/21/2025" },
        { period: "P14", begins: "6/22/2025", ends: "7/5/2025" },
        { period: "P15", begins: "7/6/2025", ends: "7/19/2025" },
        { period: "P16", begins: "7/20/2025", ends: "8/2/2025" },
        { period: "P17", begins: "8/3/2025", ends: "8/16/2025" },
        { period: "P18", begins: "8/17/2025", ends: "8/30/2025" },
        { period: "P19", begins: "8/31/2025", ends: "9/13/2025" },
        { period: "P20", begins: "9/14/2025", ends: "9/27/2025" },
        { period: "P21", begins: "9/28/2025", ends: "10/11/2025" },
        { period: "P22", begins: "10/12/2025", ends: "10/25/2025" },
        { period: "P23", begins: "10/26/2025", ends: "11/8/2025" },
        { period: "P24", begins: "11/9/2025", ends: "11/22/2025" },
        { period: "P25", begins: "11/23/2025", ends: "12/6/2025" },
        { period: "P26", begins: "12/7/2025", ends: "12/20/2025" }
    ];
    
    // Find the matching pay period
    var matchingPeriod = payPeriods.find(function(period) {
        var begins = new Date(period.begins);
        var ends = new Date(period.ends);
        var date = new Date(dateValue);
        return date >= begins && date <= ends;
    });
    
    // Set the value of field 1 based on the matching pay period
    if (matchingPeriod) {
        LFForm.setFieldValues({fieldId: 1}, [matchingPeriod.period]);
        console.log("Field 1 set to " + matchingPeriod.period);
    } else {
        console.log("No matching pay period found");
    }
}, {fieldId: 2});

 

1 0

Replies

replied two days ago

You can set the minimum date to the current date using Field Rules: Minimum Date set to today's date/current date? - Laserfiche Answers

Then you could use a lookup rule to populate the pay period field.  

1 0
replied two days ago

Thank you!!

 

Lookups are only for external data sources (SQL), correct? Is that our only option?

0 0
replied two days ago

If you are on cloud you can load a table and use the look up from there otherwise, yes you would connect to an external DB.  I could probably make this into a JS, but it will be pretty long and obviously will need to up updated each year with new dates.  Do you not have the ability to use an external DB?

0 0
replied two days ago

Yes, we're self hosted and our organization doesn't have a dedicated DBA (I'm working on it!). Thus, our IT is hesitant to play around with it so requests take a very long time. But, if this is the best option, I'll have a conversation with them. What would the javascript look like?

0 0
replied one day ago

Let me test one and see what I can get for you.

0 0
replied 16 hours ago

thank you!!

0 0
replied 9 hours ago

It's trickier doing this with code instead of lookups, but it should still be doable.

 

Here's a start...

 

This Javascript code is for the Layout Designer (not Classic Designer) and was tested on version 11.0.2311.50556.

Add 9 date fields to your form and 1 dropdown field to your form (you can leave the values on the dropdown blank, they'll be populated via the Javascript).
These fields can't be read-only or the Javascript won't be able to populate them (but you should still be able to disable them via Field Rules if you don't want the user to be able to edit them).
The date format of the date fields should be YYYY-MM-DD so that is matches what the script will be populating.
Mine looks like this:

 

You'll need the ID numbers for these 10 fields as those will be populated in lines 2-11 of the Javascript below.

 

The only other part of the Javascript you should need to edit is lines 14-18 - these five fields configure the pay period values for the year - in theory, this is the only part you will need to edit each year.

When the form is loaded, the Javascript creates an array of the dates automatically, and populates the fields with them.  The first 6 fields on the form are jsut informational fields that display the dates for the first and last pay period.  The dropdown field populates the selectors for the pay periods that were generated by the script.  And the last 3 date fields will populate based on what was selected from the drop-down.

//Variables for Fields
var firstBegin = 2;
var lastBegin = 5;
var firstEnd = 3;
var lastEnd = 6;
var firstPay = 1;
var lastPay = 4;
var periodDropDown = 7;
var selectedBegin = 10;
var selectedEnd = 11;
var selectedPay = 9;

//Variables for Pay Periods
var firstBeginDate = '2024-12-22T00:00:00';
var firstEndDate = '2025-01-04T00:00:00';
var firstPayDate = '2025-01-10T00:00:00';
var periodInDays = 14;
var periodCount = 26;

//Simple function to add n days to a date.
function addDays(date, days) {
  const newDate = new Date(date); // Create a new Date object to avoid mutating the original
  newDate.setDate(newDate.getDate() + days);
  return newDate;
}

//Create arrays for Dates
var beginDateArray = [];
var endDateArray = [];
var payDateArray = [];
for (let i = 0; i < periodCount; i++) {
  beginDateArray.push(addDays(Date.parse(firstBeginDate), (i * periodInDays)));
  endDateArray.push(addDays(Date.parse(firstEndDate), (i * periodInDays)));
  payDateArray.push(addDays(Date.parse(firstPayDate), (i * periodInDays)));
}

//Populate the First and Last Begin Date Fields
var dateString = {dateStr: beginDateArray[0].toISOString().slice(0, 10)};
LFForm.setFieldValues({fieldId: firstBegin}, dateString);
dateString = {dateStr: beginDateArray[(periodCount - 1)].toISOString().slice(0, 10)};
LFForm.setFieldValues({fieldId: lastBegin}, dateString);

//Populate the First and Last End Date Fields
dateString = {dateStr: endDateArray[0].toISOString().slice(0, 10)};
LFForm.setFieldValues({fieldId: firstEnd}, dateString);
dateString = {dateStr: endDateArray[(periodCount - 1)].toISOString().slice(0, 10)};
LFForm.setFieldValues({fieldId: lastEnd}, dateString);

//Populate the First and Last Pay Date Fields
dateString = {dateStr: payDateArray[0].toISOString().slice(0, 10)};
LFForm.setFieldValues({fieldId: firstPay}, dateString);
dateString = {dateStr: payDateArray[(periodCount - 1)].toISOString().slice(0, 10)};
LFForm.setFieldValues({fieldId: lastPay}, dateString);

//Populate the Options in the Select Pay Period Dropdown Field
var arrayForSelect = [];
for (let i = 0; i < periodCount; i++) {
  var element = {label: "P" + (i + 1), value: (i + 1)};
  arrayForSelect.push(element);
}
LFForm.changeFieldOptions( { fieldId: periodDropDown }, arrayForSelect, "replace" );

//When the selected pay period changes,
//update the three selected date fields.
LFForm.onFieldChange(function() {
  var selectedPeriod = LFForm.getFieldValues({fieldId: periodDropDown});
  var selectedBeginDate = {dateStr: beginDateArray[(selectedPeriod - 1)].toISOString().slice(0, 10)};
  LFForm.setFieldValues({fieldId: selectedBegin}, selectedBeginDate);
  var selectedEndDate = {dateStr: endDateArray[(selectedPeriod - 1)].toISOString().slice(0, 10)};
  LFForm.setFieldValues({fieldId: selectedEnd}, selectedEndDate);
  var selectedPayDate = {dateStr: payDateArray[(selectedPeriod - 1)].toISOString().slice(0, 10)};
  LFForm.setFieldValues({fieldId: selectedPay}, selectedPayDate);
}, {fieldId: periodDropDown});

 

1 0
replied 8 hours ago

I thought what was needed was the drop-down to populate with the value based on a date selected.  Whichever bracket the date field fell in is the pay period to populate in the drop down or have I been trying to write this incorrectly?

1 0
SELECTED ANSWER
replied 7 hours ago

Here is what I got to work in case this is helpful: 

 

LFForm.onFieldChange(function(event) {
    console.log("Field change event triggered");

    // Get the date value from field 2
    var dateValues = LFForm.getFieldValues({fieldId: 2});
    
    // Check if dateValues is not empty
    if (dateValues.length === 0) {
        console.log("No date value found in field 2");
        return;
    }
    
    var dateValue = dateValues.dateStr; // Use the dateStr property
    
    // Log the date value to the console
    console.log("Date value from field 2:", dateValue);
    
    // Define the pay periods
    var payPeriods = [
        { period: "P1", begins: "12/22/2024", ends: "1/4/2025" },
        { period: "P2", begins: "1/5/2025", ends: "1/18/2025" },
        { period: "P3", begins: "1/19/2025", ends: "2/1/2025" },
        { period: "P4", begins: "2/2/2025", ends: "2/15/2025" },
        { period: "P5", begins: "2/16/2025", ends: "3/1/2025" },
        { period: "P6", begins: "3/2/2025", ends: "3/15/2025" },
        { period: "P7", begins: "3/16/2025", ends: "3/29/2025" },
        { period: "P8", begins: "3/30/2025", ends: "4/12/2025" },
        { period: "P9", begins: "4/13/2025", ends: "4/26/2025" },
        { period: "P10", begins: "4/27/2025", ends: "5/10/2025" },
        { period: "P11", begins: "5/11/2025", ends: "5/24/2025" },
        { period: "P12", begins: "5/25/2025", ends: "6/7/2025" },
        { period: "P13", begins: "6/8/2025", ends: "6/21/2025" },
        { period: "P14", begins: "6/22/2025", ends: "7/5/2025" },
        { period: "P15", begins: "7/6/2025", ends: "7/19/2025" },
        { period: "P16", begins: "7/20/2025", ends: "8/2/2025" },
        { period: "P17", begins: "8/3/2025", ends: "8/16/2025" },
        { period: "P18", begins: "8/17/2025", ends: "8/30/2025" },
        { period: "P19", begins: "8/31/2025", ends: "9/13/2025" },
        { period: "P20", begins: "9/14/2025", ends: "9/27/2025" },
        { period: "P21", begins: "9/28/2025", ends: "10/11/2025" },
        { period: "P22", begins: "10/12/2025", ends: "10/25/2025" },
        { period: "P23", begins: "10/26/2025", ends: "11/8/2025" },
        { period: "P24", begins: "11/9/2025", ends: "11/22/2025" },
        { period: "P25", begins: "11/23/2025", ends: "12/6/2025" },
        { period: "P26", begins: "12/7/2025", ends: "12/20/2025" }
    ];
    
    // Find the matching pay period
    var matchingPeriod = payPeriods.find(function(period) {
        var begins = new Date(period.begins);
        var ends = new Date(period.ends);
        var date = new Date(dateValue);
        return date >= begins && date <= ends;
    });
    
    // Set the value of field 1 based on the matching pay period
    if (matchingPeriod) {
        LFForm.setFieldValues({fieldId: 1}, [matchingPeriod.period]);
        console.log("Field 1 set to " + matchingPeriod.period);
    } else {
        console.log("No matching pay period found");
    }
}, {fieldId: 2});

 

1 0
replied 7 hours ago

I definitely could have misunderstood.  I'm not sure.

0 0
replied 7 hours ago

Yes!! Thank you all!! This is perfect until we can get SQL figured out at our organization. Appreciate you all <3

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.