Discussions

 View Only
  • 1.  Counting Days

    Posted 10-21-2019 09:46
    I am having a difficult time in figuring out how to do this...
    Associates have their hotel length of stay recorded in two fields "Hotel Check-In Date" and "Hotel Check-Out Date"
    One of the functions for this is to determine the amount of per diem owed to an associate.

    The report that is currently being used is for the manager to select a date range for example start date is 10/21 and end date is 10/26. If an associate starts on the 21st or later... we are fine. However, this will not account for associates that started on 10/14 working thru 10/26.

    Somehow I need to build a report that will say "provide me a list of all associates that are working between dates 10/21 and 10/26 no matter whether started on 10/14 or even earlier.

    Any idea how to build this? It would be GREATLY appreciated!

    ------------------------------
    Hans Hamm
    ------------------------------


  • 2.  RE: Counting Days

    Posted 10-21-2019 10:51
    Edited by Evan Westbrook 10-21-2019 10:52
    Hans,

    If I understand correctly, you have a report that needs to display all associates staying at a hotel in a given date range. In your example, an associate with a start and end date of 10/14 and 10/26 should show up in a report for associates staying in hotels on the dates 10/21 through 10/26 even though 10/21 is not explicitly outlined in this associate's stay.

    I'm not sure if this can be done using the dynamic date range filter, as a formula field would need to reference a dynamic filter input. However, we may be able to think up something similar. Are there any business rules your user has that could be used to simplify the filter? Here are a couple of examples:

    Example 1
    Assumption:
    • End user just wants to see all associates in a hotel in the last 7 days
    Solution:
    • Create a checkbox-formula field that flags if the check out date is within the last seven days.
    • Set this formula field as the dynamic filter instead of a date range
    Example 2
    Assumption:
    • End user just wants to view dates based on some business time unit (i.e. week ending date, billing period, etc.)
    Solution:
    • Create a text-formula field that translates check in and check out date to the business time unit
    • Set this formula field as the dynamic filter instead of a date range

    Are there any ways you can think of to translate the business needs for the date range?

    ------------------------------
    Evan Westbrook
    PRIME Developer
    Harder Mechanical Contractors Inc.
    Portland OR
    ------------------------------



  • 3.  RE: Counting Days

    Posted 10-21-2019 13:16
    Evan,
    The 2nd example is closer to what is needed.
    If I was going to lay it out it would be something like this...

    Show me a count of nights for associates in hotel rooms from 10/21 thru 10/26
    Would give me 6 nights for both Joe and Sally
      14-Oct 15-Oct 16-Oct 17-Oct 18-Oct 19-Oct 20-Oct 21-Oct 22-Oct 23-Oct 24-Oct 25-Oct 26-Oct
    Joe X X X X X X X X X X X X X
    Sally               X X X X X X
    Show me a count of nights for associates in hotel rooms from 10/14 thru 10/26
    Would give me 13 nights for Joe 6 nights for Sally
      14-Oct 15-Oct 16-Oct 17-Oct 18-Oct 19-Oct 20-Oct 21-Oct 22-Oct 23-Oct 24-Oct 25-Oct 26-Oct
    Joe X X X X X X X X X X X X X
    Sally               X X X X X X


    ------------------------------
    Hans Hamm
    ------------------------------



  • 4.  RE: Counting Days

    Posted 10-21-2019 13:44
    Here is a thought.

    If this is a low usage situation by very few users, who need this filter. Then make a single record in a new table called Analysis Dates.  it will be Record ID# 1 and it will have two date fields for Analysis Start date and Analysis End Date.

    Make a relationship down to your detail records.  Now you can use these date fields to either filter records or to calculate the # of days within the hotel stay period which are within the Analysis Date range.

    If you like, the records can be displayed directly on the Analysis date range record as an embedded report.  But if not, I suggest that you display the Analysis Date range on the report itself as Group By headers or columns so the user knows what extra date filters are being used. 



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 5.  RE: Counting Days

    Posted 10-21-2019 13:50
    .. and if its multi user there is a solution for that too.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 6.  RE: Counting Days

    Posted 10-21-2019 15:21
    Hans,

    Mark has some good ideas below. I'm responding in a different branch of this thread just to keep our suggestions straight for you.

    To accomplish what is mentioned in Example 2, you could create a library table called something like "Weeks" (or whatever your consistent time unit is.) Here's how you might set it up:
    •  Fields: [Week Description], [Week Start Date], and [Week End Date].
    • Relate "Weeks" to "Hotel Stays" table one to many (one week can have many hotel stays.)
    • Set up an automation to automatically link a [Hotel Check-In Date]  [Hotel Check-Out Date] pair on "Hotel Stays" to the parent Weeks table. (See Brian's solution on response 3 for an example)
    • Set up report on "Hotel Stays" table to filter based on "Week" for the hotel stay instead of the start and end date.
      • i.e. "Show me the # of nights in Week Ending 10/20" instead of "Show me the # of nights between "10/14" and 10/20"
    If a truly dynamic feature is what you're after, ​​​Mark's solution would be good.

    ------------------------------
    Evan Westbrook
    PRIME Developer
    Harder Mechanical Contractors Inc.
    Portland OR
    ------------------------------