Question

Error Getting Date from collection

Forms Version 10

Updated January 10, 2023

asked on January 10, 2023

I am trying to get the date in current collection column.

=INDEX(Transportation_Expenses.DateTransportation, ROW())

should return the DateValue for the column. such as 44950

if I just use =Transportation_Expenses.DateTransportation I get all the DateValues seperated by a comma.

I use INDEX and ROW() to get information from other fields in the collection without issue, what am I doing wrong here?

0 0

Replies

replied on January 10, 2023

Where are you using INDEX to retrieve the date? Also, what exactly is the error you're seeing?

0 0

replied on January 10, 2023

The collection contains various fields related to travel. One of the option is Private Vehicle.

The government pays the traveler per mile such as 0.64 cents. The problem is that the amount changes from time to time. I have a hidden fields for the current rate, Future rate, and the effective date of the future rate. I need to determine which rate applies based on the travelers selected date in the collection. I know how to do this in Java script but I wanted to figure out how to use the built-in Laserfiche formulas.

The error is: "The field contains a calculation error" I figured out that I can't get the ROW() from outside the collection, it is always 0 which caused the error.

Getting the last row could be an issue as well since someone could go back and edit the date in a previous row. Is there a way to get the active row?

0 0

replied on January 10, 2023

No, there's not really an "active" row. The problem you're going to have here is that you're trying to reuse a formula field for an array of objects.

The formula outside of the collection requires a "static" reference to the table in order to properly determine the source value.

I think you need a way to define what counts as the "active" row because that could mean a lot of different things depending on the context.

"Active" could be the last row, the row with focus, the last one to change, etc., so you'll need a way to define that first.

0 0

replied on January 10, 2023

This field contains a calculation error.
References: Transportation Expenses Date

The collection contains:

Date picker (Date)

Numeric (Miles Traveled)

Outside the collections I have several hidden fields.

Active Mileage Rate

Current Mileage Rate

Next Mileage Rate

Next Rate Date

The calculation I am trying to perform in the Active Mileage Rate is a date comparison between Next Mileage rate and the Date selected by the traveler. If it's less the Active Date will contain the Current Mileage Rate and if >= it will contain the Next Mileage Rate.

Within in the collection in the Amount field, the calculation is (Active Mileage Rate * Miles Traveled)

After playing around with this I realized that I cannot use

=INDEX(Transportation_Expenses.DateTransportation,ROW())

outside of the collection because the ROW() always returns 0

I tried to to this in javascript but new rows added to the collection don't seem to have event handlers.

$('.TransportationDate').change(function(){

var SelectedDate = $(this).find('[id^=Field11]').val();
var NextRateDate = $('.NextRateChange input').val();
var CurrentMileageRate = $('.CurrentMileageRate input').val();
var NextMileageRate = $('.NextMileageRate input').val();
var ActiveMileageRate;

alert(SelectedDate + " " + NextRateDate + " " + CurrentMileageRate + " " + NextMileageRate);

if (SelectedDate >= NextRateDate) {
ActiveMileageRate = CurrentMileageRate;
   } else {
ActiveMileageRate = NextMileageRate;
   }


// $('.test2 input').val(ActiveMileageRate);

//});

});

This change event will only fire for rows that were already added, but not new rows added by the user.

0 0

You are not allowed to follow up in this post.

Question

Question

Error Getting Date from collection

Replies

Sign in to reply to this post.