Find Cycle Based on Date

[TerryR]

I want to use powerapps to identify the cycle in which a product was produced.  I have a separate table set up like this...

Cycle      CycleStartDate

162         2019-05-29

161         2019-04-17

160         2019-02-27

159         2018-12-11

For example, if the product was produced on 3/16/2019, the app needs to show that the product was produced in cycle number 160.  Any ideas on how to do that?

[RandyHayes]

You're going to need to convert the data in your textbox to a date value.
Change the DateValue2 references in your formula to DateValue(DateValue2.Text)

[TerryR]

I've been thinking that too.  Tried several ways of trying to correct that possibility, with no luck.

Doesn't work - Date Value has invalid arguments

LookUp('[dbo].[CycleDates]',
DateValue(DateValue2.text)<CycleEndDate
&&
DateValue(DateValue2.text)>=CycleStartDate).Cycle_Number

Doesn't work - not a valid connector

LookUp('[dbo].[CycleDates]',
DateValue(Text(DateValue2))<CycleEndDate
&&
DateValue(Text(DateValue2))>=CycleStartDate).Cycle_Number

Doesn't work - not a valid connector

LookUp('[dbo].[CycleDates]',
DateTimeValue(Text(DateValue2))<CycleEndDate
&&
DateTimeValue(Text(DateValue2))>=CycleStartDate,
Cycle_Number)

[RandyHayes]

@TerryR 

The only formula you have that is valid is this one:

LookUp('[dbo].[CycleDates]',
       DateValue(DateValue2.Text)<CycleEndDate &&
       DateValue(DateValue2.Text)>=CycleStartDate
      ).Cycle_Number

If CycleEndDate and CycleStartDate are date/time fields, then this should be doing what you want.  Make sure that you have a valid date in your DateValue2 control.  

Also, in red above, your original formula you copied here had lower case "text", that is not a valid property - it is "Text".

See if that helps you.

[TerryR]

Could it be that my problem is that I'm using datepicker as my source for the date I'm looking up?

[RandyHayes]

@TerryR 

What in your formula is a DatePicker control?

[TerryR]

DateValue2 comes from a datepicker.

[RandyHayes]

@TerryR 

In that case, consider changing your formula to this:

LookUp('[dbo].[CycleDates]',
       DateValue2.SelectedDate < CycleEndDate &&
       DateValue2.SelectedDate >= CycleStartDate
      ).Cycle_Number

[TerryR]

I've tried that before and retried now, but no success.

I changed the SQL lookup table start and end dates to "Date" fields instead of date/time.  That doesn't seem to have helped either.

[RandyHayes]

@TerryR 

What error or issue are you seeing?

[TerryR]

"Not a valid connector error response.  An error occurred on the server."  

The other lookup functions on the same screen are working though.