Forum Discussion

JoshHamilton1's avatar
JoshHamilton1
Qrew Member
5 years ago

Creating a list of numbers that are in between two numbers

I have two fields that produce numbers [Start] and [Finish] If = 2 and = 10 I want a field that can list = 3 4 5 6 7 8 9 ------------------------------ Josh Hamilton ----------------------...
Formulas and functions
JoshHamilton1's avatar
JoshHamilton1
Qrew Member
5 years ago
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
------------------------------
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
5 years ago
I think that this pattern works

List(" ",
If(< 1 and >1, "1"),
If(< 2 and >2, "2"),
If(< 3 and >3, "3"),
If(< 4 and >4, "4"),
If(< 5 and >5, "5"),
If(< 6 and >6, "6"),
If(< 7 and >7, "7"),
If(< 8 and >8, "8"),
If(< 9 and >9, "9"),
If(< 10 and >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
------------------------------
  • JoshHamilton1's avatar
    JoshHamilton1
    Qrew Member
    5 years ago
    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
    ------------------------------