Question

Forms - Auto-fill a field based on a Date Field

Forms

Updated August 17, 2022

asked on August 17, 2022

I have a mileage reimbursement form that I'm trying to do automatic calculations on. The problem is that I have 2 different reimbursement rates depending on whether the travel occurred up to 6/30/22, or on or after 7/1/22. The values go into a table that lists a date for each travel instance and the miles. My question is, does anyone know how I might auto-fill a field in the row with one of the two rates based on the date that is entered in that row? That would allow each row to calculate the monetary reimbursement with the correct rate, which could then be totaled in a field outside the table.
I'm assuming this would require using Javascript. If that's the case, could you please give me some sample code of how to accomplish it?

Thanks in advance!

0 0

Answer

SELECTED ANSWER

replied on August 17, 2022 • Show version history

Is this meant to be a read only value (meaning the user can't change it) or just a default?

If it is read only, you could do it with calculations you just need to use the right Date function for the comparison: DATE(year, month, day)

Something like this to calculate the right rate

=IF(INDEX(Table.Date,ROW())<DATE(2022, 7, 1),0.55,0.65)

Or like this to calculate everything together

=MULT(INDEX(Table.Miles,ROW()),IF(INDEX(Table.Date,ROW())<DATE(2022, 7, 1),0.55,0.65))

1 0

replied on August 17, 2022

That's perfect! It's a read-only value, so those options are exactly what I need. Thanks Jason!

0 0

Replies

replied on August 17, 2022

Here's the code I've cobbled together, though I know it's not correct. Forgive me, my Javascript ability is far below what it should be.

    $("#q2").on("change", function(){
     var d2 = new Date("#q2").val();
     var d1 = new Date("07/01/2022");
      if (d2 < d1) {
    
    $("#q8 input").val("0.55");
    }
    else {      
      $("#q8 input").val("0.65");
    }
  });

Here's my table:

0 0

You are not allowed to follow up in this post.

Question

Question

Forms - Auto-fill a field based on a Date Field

Answer

Replies

Sign in to reply to this post.