Creating a list of numbers that are in between two numbers

Question

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------------------------------

markshnier__you · Answer

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
------------------------------

joshhamilton1 · Answer

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)&nbsp;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 timeSo 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 · Answer

I think that this pattern worksList(" ",If(&lt; 1 and &gt;1, "1"),If(&lt; 2 and &gt;2, "2"),If(&lt; 3 and &gt;3, "3"),If(&lt; 4 and &gt;4, "4"),If(&lt; 5 and &gt;5, "5"),If(&lt; 6 and &gt;6, "6"),If(&lt; 7 and &gt;7, "7"),If(&lt; 8 and &gt;8, "8"),If(&lt; 9 and &gt;9, "9"),If(&lt; 10 and &gt;10, "10")).. and continue the pattern to 48Let me know how you make out in your double booking solution.&nbsp; It's a generic problem which is difficult to solve so you may be onto to something here.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

joshhamilton1 · Answer

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 lookupsHere is a summary;Starting Fields:[Booking Code] = Venue ID:Court ID:Date[Start Code] = [Booking Code] &amp; [Start Time ID] (00:00=1, 00:30=2, etc.)[Finish Code] = [Booking Code] &amp; [Finish Time ID] (00:00=1, 00:30=2, etc.)[Duration Code] = [Booking Code] &amp; [Duration Time ID] *each duration time id in the list has the booking code prefixing it ([Booking Code] [Duration Time Code 1] /&nbsp;[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.&nbsp;I then created 2 formula checkbox fields to check for Start Code conflicts and Finish Code conflicts;e.g.&nbsp;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------------------------------

Forum Discussion

Creating a list of numbers that are in between two numbers

4 Replies

Related Content

Create a formula duration over 5 years

Autogenerating Number

Invoice Number Formula

Bulk create child records from selection list

Converting Number to Text

Recent Discussions

Filter Monthly, Quarterly, Yearly from one Date field

Extrapolating nuanced data

Forms help

Share your feedback on the new Qrew Discussions

Generating QR Code with URL to record