cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TerryR
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

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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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)

 

 

@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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

 

 

@TerryR 

What in your formula is a DatePicker control?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

DateValue2 comes from a datepicker.

@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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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.

 

@TerryR 

What error or issue are you seeing?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

"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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,769)