Hello, I have two tables for a time sheet ap...

[Joseph_Corbett]

Hello,

I have two tables for a time sheet app.

One table collects the time sheet information the other has each day of the year and associated

pay cycles.

In the time sheet table I am trying to capture each time sheet entry and would only like the “date” of the time sheet entry to come from a list of dates from the associated pay cycles?

I have created a date reference from the cycles table but I am confused on how to limit the date range selection to ONLY the list of dates in the pay cycle.

[Aleksi_Alkio]

Would you please elaborate with this… “limit the date range”, thanks.

[GreenFlux]

Ok, so you have a Cycles table, with a list of dates for each cycle. And you have a [Date] column in your Time Sheet table. And now you want the [Date] column to show a dropdown list of only the dates in a given pay cycle… Which cycle?

Should it be the current cycle only (no backdating option)? Or do you want to allow the user to select any pay cycle first, then display a date list for that cycle?

For the latter, you’ll need to add a [PayCylceNumber] column in both tables, so you have something to filter on. So for two-week pay periods, you’d have a dropdown column with 1-26, for example.

Then enter an expression in the SUGGESTED VALUES setting that yields the correct list of dates.

SELECT(Cycles[Date], [PayCycleNumber] = [_THISROW].]PayCycleNumber])

[Joseph_Corbett]

@GreenFlux

Mr. Petty, You are exactly right!

I see where you are going. What I am trying to do with the [Date] column is have the available chooses of dates.

I am looking for something along the lines of using TODAY() to look at the [Pay Cycle] from the Cycles table and only have the given list of [Date]s from the cycle table is validated options to choose from.

I am going to play with your thought.

Awesome idea!

[Joseph_Corbett]

@Joseph_Corbett

Mr. Petty, Yes I am looking to only allow the current pay cycle option only

[GreenFlux]

Please excuse formatting/typos- on mobile:

Yes, you’ll want to use the TODAY() function then. So you still need a [Pay Cycle] column in each table to compare, but the user won’t select the cycle.

You’ll have to calculate the Time Sheet[Pay Cycle] based on TODAY(), then apply that value, using the same method above (for dynamic pay cycle).

I think it would help if each row of the Cycles table had a CycleDateStart and CycleDateEnd column. Then you can search for rows of Cycle table where TODAY() is between those two values.

[Joseph_Corbett]

If I share this app with you would you mind giving me some sample expression(s) in the app to get me started?

[GreenFlux]

Sure, no problem. Please share it to: support@greenflux.us

[Joseph_Corbett]

@GreenFlux

Shared and thank you!

[GreenFlux]

Could you add a [Cycle] column to the Timesheet table please? I have access to the app definition, but not the Google Sheet.

Also, I would change [Time In] and [Time Out] to DateTime column types. Using only the time won’t work for shifts that run past midnight; the subtraction for the duration will be wrong.