Discussions

Back to discussions
Expand all | Collapse all

Creating a list of numbers that are in between two numbers

  • 1.  Creating a list of numbers that are in between two numbers

    Posted 07-14-2020 00:01
    I have two fields that produce numbers [Start] and [Finish]

    If [S] = 2 and [F] = 10 I want a field that can list = 3 4 5 6 7 8 9

    ------------------------------
    Josh Hamilton
    ------------------------------


  • 2.  RE: Creating a list of numbers that are in between two numbers

    Posted 07-14-2020 07:45
    What is the highest possible and lowest possible values for S and F that the formula would need to handle?

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

    Original Message


  • 3.  RE: Creating a list of numbers that are in between two numbers

    Posted 07-14-2020 19:28
    lowest is 1 and highest is 48.

    What I am trying to do is create a time code system to stop double bookings,
    So I'll have a [Base Code] which states location and date (Venue-Court-Date) 
    A [Start Code] which is the starting time of the booking in which I have assigned values 1 through 48 to the possible time slots to create a booking (00:00 = 1, 00:30 = 2, 01:00 = 3 etc.)
    A [Finish Code] which behaves the same as the [Start Code] but lists the finish time
    So now I am trying to create a [Duration Code] which lists the time codes in between [SC] and [FC] and then either write a script or some other method to make sure that a new record cant have a [SC] that exists already as a [SC] or [DC] and cant have a [FC] that exists already as a [DC] or [FC]

    ------------------------------
    Josh Hamilton
    ------------------------------

    Original Message


  • 4.  RE: Creating a list of numbers that are in between two numbers

    Posted 07-14-2020 19:40
    Edited by Mark Shnier (YQC) 07-14-2020 20:34
    I think that this pattern works

    List(" ",
    If([S]< 1 and [F]>1, "1"),
    If([S]< 2 and [F]>2, "2"),
    If([S]< 3 and [F]>3, "3"),
    If([S]< 4 and [F]>4, "4"),
    If([S]< 5 and [F]>5, "5"),
    If([S]< 6 and [F]>6, "6"),
    If([S]< 7 and [F]>7, "7"),
    If([S]< 8 and [F]>8, "8"),
    If([S]< 9 and [F]>9, "9"),
    If([S]< 10 and [F]>10, "10"))

    .. and continue the pattern to 48

    Let me know how you make out in your double booking solution.  It's a generic problem which is difficult to solve so you may be onto to something here.

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

    Original Message


  • 5.  RE: Creating a list of numbers that are in between two numbers

    Posted 08-04-2020 03:36
    Hi Mark,

    Just wanted to update you on how I went with this.

    So instead of writing a script to handle the lookup I used an automation and relationship field lookups

    Here is a summary;
    Starting Fields:
    [Booking Code] = Venue ID:Court ID:Date
    [Start Code] = [Booking Code] & [Start Time ID] (00:00=1, 00:30=2, etc.)
    [Finish Code] = [Booking Code] & [Finish Time ID] (00:00=1, 00:30=2, etc.)
    [Duration Code] = [Booking Code] & [Duration Time ID] *each duration time id in the list has the booking code prefixing it ([Booking Code] [Duration Time Code 1] / [Booking Code] [Duration Time Code 2] / etc.)

    My automation then copies the codes and writes them into fields stored on the related court;
    [Starting Codes]
    [Duration Codes]
    [Finish Codes]

    These fields are also relationship lookup fields into from the Courts table into the Bookings table, so that once you select a venue and court in the booking record, the record can see all the existing booking codes made. 

    I then created 2 formula checkbox fields to check for Start Code conflicts and Finish Code conflicts;
    e.g. If(Contains([Court - Start Codes], [(Formula) Start Code]) or Contains([Court - Duration Codes], [(Formula) Start Code]), true, false); then just set up rules to stop the record from being saved if either checkbox is ticked.

    The automation also copies the list of codes in the lookup so it basically it ends up just adding the new booking codes to the list.

    ------------------------------
    Josh Hamilton
    ------------------------------

    Original Message