First argument to Sequence error

[jerinke]

I am using the Sequence function in a formula to calculate elapsed time between dates, excluding weekends and holidays.  My data source is date columns in a SharePoint list.  When I play the app, it immediately displays an error, "The first argument to Sequence must be between 0 and 50,000."  When I complete the form and click Submit, the error returns.  The form works perfectly despite the error, and updates the SharePoint list correctly.

I also see the error when I open the app to edit.

The formula is in the default property of a text control in a form with data cards.

Here is the formula:

With(
{
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(DecisionDateValue1.SelectedDate - RequestDateValue2.SelectedDate + 1),
RequestDateValue2.SelectedDate + Value - 1
)
},
If(
And(
IsBlank(RequestDateValue2.SelectedDate),
IsBlank(DecisionDateValue1.SelectedDate)
),
// show nothing if any date pickers are blank
0,
// include only dates Monday to Friday
CountIf(
varDateRange,
And(
Weekday(Value) in [2, 3, 4, 5, 6],
Not(Value in 'Holidays Calendar'.HolidayDay)
)
)
)
)

DecisionDateValue1 and RequestDateValue2 are date pickers (date columns in SharePoint).

When I highlight Sequence(DecisionDateValue1.SelectedDate - RequestDateValue2.SelectedDate + 1) in the editor, I can see that the values are correct and that the value is always between 0 and 50,000.

Why am I getting this error and how can I get rid of it?

[iAm_ManCat]

A date object is actually a number, so what you got by subtracting them was a different single date, represented by a number.

What I'm guessing you are trying to do is X number of days + Y, so you should use the DateDiff() function to get the difference between them in days (X), then you can add Y to them:

With(
   {
      // generate a one-column table of all dates between start date & end date
      varDateRange: ForAll( 
         Sequence( 
            DateDiff( DecisionDateValue1.SelectedDate, 
                      RequestDateValue2.SelectedDate, 
                      Days
            ) + 1
         ),
         DateAdd(RequestDateValue2.SelectedDate, (Value - 1), Days)
      )
   },
   If(
      And(
         IsBlank(RequestDateValue2.SelectedDate),
         IsBlank(DecisionDateValue1.SelectedDate)
      ),
      // show nothing if any date pickers are blank
      0,
      // include only dates Monday to Friday
      CountIf(
         varDateRange,
         And(
            Weekday(Value) in [2, 3, 4, 5, 6],
            Not(Value in 'Holidays Calendar'.HolidayDay)
         )
      )
   )
)

Cheers,

Sancho

[jerinke]

Thanks for your response.  It gives the same date math results but also the same error, or warning.  I think it is some kind of warning because the app works fine either the original way or your way.

[iAm_ManCat · Accepted solution]

Yes you are correct, the date math has evidently been resolved since I last had this issue.

I recreated your whole setup except the holiday dates and I think the issue is just that you're subtracting the items the wrong way around and ending up with a negative number which a sequence can't use as its not between 0 and 50000 (same for my datediff recreation of your code).

You should have some logic to show an error if the decision date chosen is before the requested date:

If(
    //If dates would result in negative Number, show warning
    !IsBlank(RequestDateValue2.SelectedDate) && !IsBlank(DecisionDateValue1.SelectedDate) && (RequestDateValue2.SelectedDate > DecisionDateValue1.SelectedDate),
    "Please select a decision date after the request",
   //Otherwise continue
    With(
        {
            // generate a one-column table of all dates between start date & end date
            varDateRange: ForAll(
                Sequence(
                    DateDiff(
                        RequestDateValue2.SelectedDate,
                        DecisionDateValue1.SelectedDate,
                        Days
                    ) + 1
                ),
                DateAdd(
                    RequestDateValue2.SelectedDate,
                    (Value - 1),
                    Days
                )
            )
        },
        If(
            And(
                IsBlank(RequestDateValue2.SelectedDate),
                IsBlank(DecisionDateValue1.SelectedDate)
            ),
            // show nothing if any date pickers are blank
            0,
            // include only dates Monday to Friday
            CountIf(
                varDateRange,
                And(
                    Weekday(Value) in [2, 3, 4, 5, 6],
                    Not(Value in 'Holidays Calendar'.HolidayDay)
                )
            )
        )
    )
)

I've attached a POC app with the example but the code above should now work for you (assuming your label or input or whatever takes text, otherwise you'll need some other kind of warning for when the dates aren't in the right order

Cheers,

Sancho

[jerinke]

This worked nicely.  The most interesting thing is that I confirmed in the app editor that none of the three methods we tried produced a negative number in the Sequence function.  So although your final solution worked, I still don't fully understand why the others did not.  I documented each and captured screens for future reference (that's just how I am).  I'll be happy to post them if you are interested, but it is a moot point now.  Thanks a bunch for your help!

[zerocool55]

Hi Man Cat,

I'm hoping you can help me.

I'm trying to calculate business duration in days from two date picker (although these are MS team data pickers) both are part of an edit form. these pickers don't come with the property selected date but value and I can't use the code you suggested above. 

I'm using the below formula replacing the selected date with value but I have similar sequence error although the result is correct

Thank you, in advance, for your support.

With( {varDateRange: ForAll( Sequence(dte_EndDate.SelectedDate - dte_StartDate.SelectedDate + 1), dte_StartDate.SelectedDate + Value - 1 ) }, If( And( IsBlank(dte_StartDate.SelectedDate), IsBlank(dte_EndDate.SelectedDate) ),  CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] ) )

[rwittels]

Hi. Just curious, where are you getting "Value" from? e.g. What does Value in this line represent?

Weekday(Value) in [2, 3, 4, 5, 6],