Is your table fixed (no option to add more columns)?

So based on your PP, you want to set the dates for the rest of the month, correct? In your screenshot they are all the same date but should really be a date of that month, right?

I think you can do a hidden field with your nested statement, then for the rest of them you can add days. 

Yes, the pay periods are two weeks so I've fixed the number of rows at 14 and then named each row with the corresponding day of the week. Yes, I would like each row to have the correct day of the month, not all the same date. I got as far as I could with a solution but I just don't know how to make the days increase.

How do I add days?

We have some staff who enter their schedule on the timesheet every day so that they can keep on top of their hours but some staff enter their entire timesheet at the end of the pay period so I can't do a function like "TODAY" or something like that.

Basically, I'm trying to turn the attached screenshot (done in excel) into a LF Form.

Background: I figured I would keep it easier for managers and finance to a simple form for most of our staff who have a regular schedule by adding the caveat question "Select the option that pertains to you" and adding field rules to show this table (as seen above) or to show a more complex table when they have overtime, banked time, etc. FYI - It gets complicated b/c staff can enter + and - time in the same cell (in the complex form) so I'm using a single line field for the rest of the items you don't actually see in this table but that are in the more complex table that shows when staff select "I'm logging more than just regular and flex time".

Hi,

Try using the code below. I used an external library called moment.js and it seems to work.

A few things to keep in mind.

1. I set the first column for holding the "Day" and the second column for holding the "Date".

2. The code runs whenever a change is made to the table. This is probably not the optimal approach but I set it this way because I don't have a complete understanding of how your Form is set up.

3. I am assuming that you only have one table. If there is more than one, you need to be more specific with the the selector: $('table.cf-table') since this selector applies to any Forms table. 

4. The format for my date column is set to MM/dd/yyyy. I'm not sure if this matters but just a FYI.

$(document).ready(function(){
  $.getScript('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.22.1/moment.js',function(){
    $('table.cf-table').on('change',function(){
    	var numrows = $('table.cf-table tr').length;
      	for(i=1; i<=numrows;i++){
        	var daycol = $('table.cf-table tr').eq(i).find('td input').eq(0);
        	var dt = $('table.cf-table tr').eq(i).find('td input').eq(1).val();
          	$(daycol).val(moment(dt).format('dddd'));
      	};
    });
  });
});

PS: I am interested to know how everyone would optimize the code so please feel free to comment if anyone has suggestions. 

I've never used external libraries or databases before. I assume the external library and/or database helps populate the correct day in the day column? Does the external library basically contain a list of dates and the corresponding day of the week? Could I use this to populate a range of pay periods so I don't need to manually update the form annually?

I added a column for "Day" (using "Day" as the column label, "Single Line" as the field type and "daycol" as the variable) rather than using a row label but I wasn't sure if I needed to add in a formula in order to get the day to show and if so what that would look like? Or does it need a CSS class? Other than labeling the variable the same as what appears in the JS code you suggested, there is no link (for me) to make a day of the week show up in that column (without an external library or DB). Can I get some hints on external libraries and databases so I can help this move forward?

Variable Definitions
Pay Period = 'PP'
Pay Period start date = 'PPstart'
Regular & Flex timesheet table columns:
Day = 'Timesheet_Regular.daycol'
Date = 'Timesheet_Regular.Date'
Complex timesheet table columns:
Day = 'Timesheet_Complex.daycol'
Date = 'Timesheet_Complex.Date'

Hi Emily,

The stored procedure (SP) would need to be configured in your database.   You would need to get with your DBA to set it up.  Stored procedures are common and would only take a minute to create.   Once it is setup, you would add (or refresh) your forms data source that referenced it so that it is available to your form in the Lookup Rules.

To use the SP to populate the table days and dates you need nothing more than 2 fields on your form (no classes or other calculations).   One of which you already have,  your pay period drop down.   The other field you would need is a numeric indicating how many days of data to return.   This could be a hidden field on your form that is set to 14 days or  if you are always returning 14 days,  that could be hard coded into the stored procedure and you would only need your pay period drop down.

If you configure the pay period drop down so that it has a value assigned, the pay period start date, that value can be sent to the SP as the starting date ....

Using a lookup rule:


The SP I posted is dependent on Microsoft SQL Server database since it references an internal table.  If you are using Oracle it will need a tweak.   The internal table is, master.dbo.spt_values, and all it does is return a list of numbers, 0,1,2,3 ....  used to increment the date.   No other tables required.

Usually more than one way to do things.  This may or may not be the route you take.   Happy to answer any questions you have if you try this method.

Andrew