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

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

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

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

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

"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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (77,697)