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

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

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

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (27,668)