cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Find Cycle Based on Date

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?

 

 

24 REPLIES 24
Highlighted

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)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

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)

 

 

Highlighted

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

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

 

 

Highlighted

@TerryR 

What in your formula is a DatePicker control?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

DateValue2 comes from a datepicker.

Highlighted

@TerryR 

In that case, consider changing your formula to this:

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

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

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.

 

Highlighted

@TerryR 

What error or issue are you seeing?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

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

 

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,891)