Automatically add date records

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
Looking for a way via a button and custom script that will allow someone to click the button which would add a predetermined batch of dates.

I have an appointments table that the team creates records for future dates 1 month in advance, so others can go in and book.

I would like to save them the time of creating these records by just having a button that preloads a month of appointments.
Photo of B

B

  • 20 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Photo of B

B

  • 20 Points
wow, thanks Dan.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
If you would like a native solution not involving code, post back here.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
If you would like a PureScript solution not involving native post back here.

Purescript
PureScript is a small strongly typed programming language that compiles to JavaScript.
http://www.purescript.org/
Photo of B

B

  • 20 Points
Okay, now I'm interested in seeing both.  Battle of Native vs Script, which is the best solution?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Sounds like we got an epic debate coming. I have to leave on a business trip in a few minutes so I am going to give Mark a few days head start.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Can you tell me more about what the set of records looks like.  For example are they creating a record per day or a record per week day or maybe 8 hourly slots per day for 30 days??
Photo of B

B

  • 20 Points
Daily appointments with an option for start time and duration.  Pretty basic.  There are other things like tagging a user for the appointment, but mostly, I just want to predetermine this based on who clicks the button.  Eg. If I click the button, daily appointment records are created for me for the next mon to friday.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
So how many records are being created, just 5 records?  And the date field gets populated with the date for the next Monday, Tuesday etc to Friday?
Photo of B

B

  • 20 Points
could be one week of appt, could be 1 month.  Better to say one month worth for this.

What they do is create "open" appts that other team members can go into and "book" them.  So this would allow them to make a month's worth of mon-fri "open" appts in advance and without having to create individual records or using grid-edit.

 Yes, basically creating a set of records that adds the date field automatically.

Let me know if that helps or if you need more info.

Thanks Mark!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
My suggestion is to write a formula URL button which will add a week of appointments at a time (so 5 records), and then land the user on a newly created record, so the user could just push a button and add then next week of records.

Do you know how to write a URL formula button?  The psuedo code would look like this as I'm stretched for free time right now and don't really have time to write out the full syntax.  You can contact me off line if you need the actual code written.

The idea would be to launch off an existing record to make a set of records for the next week

var date NextMonday = FirstDayOfWeek(AdjustWeek([date],1) + days(1);
var date NextTuesday = FirstDayOfWeek(AdjustWeek([date],1) + days(2);

etc to Friday

var text URLONE = urlroot() & "db/" & dbid() & "?act=API_AddRecord"
& "&_fid_(fid of date field here)= totext($NextMonday);

var text URLTWO = urlroot() & "db/" & dbid() & "?act=API_AddRecord"
& "&_fid_(fid of date field here)= totext($NextMonday);

etc to the Friday which is a bit different

var text URLTWO = urlroot() & "db/" & dbid() & "?act=API_AddRecord"
& "&_fid_(fid of date field here)= totext($NextFriday)
& "&disprec=1"; // this should land the user on the Friday record where they can push the button again.

// then we string them all together like this


$URLONE
& "&rdr=" & URLEncode($URLTWO)
& URLEncode("&rdr=" & URLEncode($URLTHREE))
& URLEncode(URLEncode("&rdr=" & URLEncode($URLFOUR)))
& URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFIVE))))


// so I guess I did, in fact, pretty much write out the syntax after all.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
That is an impressive number of rdr's you got there.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Surprisingly, I think I've actually done up to 12 redirects, well I guess maybe actually an initial call and then 11 more redirects for a nap I once wrote where I needed to create 12 months.

Quickbase really doesn't seem to mind the redirects at all. The only limitation I am aware of is that at some point the actual generated URL string gets to be extremely long and  some browsers may blow up. But aside from that they works surprisingly well and are stupid simple
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
I am in awe and almost choked on the smarties the Toronto speaker brought to the meetup I am in at the moment

I probably shouldn't tell you this but you can call API_ImportFromCSV from a formula URL to create multiple records in one go and thus avoid multiple API_AddRecord calls repeatedly linked together with rdr's.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Hmmmm, I will have to try that. The documentation on that API does not give an example of the formula URL format, so I never considered that could work.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
> The documentation on that API does not give ...

The documentation in general puts too much emphasis on forming XML requests and leaves out a lot of more productive techniques. The QuickBase API can actually accept parameters in a variety of other formats (GET query string, , POST query string encoded body, XML body, headers).

I hesitate to give you this information because instead of killing the use of rdr altogether (in favor of script) you are probably just going to make a cottage industry out of using API_ImportFromCSV in formula URLs and eventually tackle even more complicated tasks by adding a dozen rdr's on the new scheme.

The trick to using API_ImportFromCSV as a formula URL is to string out the record_csv parameter with a newline in between records of CSV data and to URLEncode the whole thing.

Avoid Excessive Redirects ~ Add Three Stooges
https://haversineconsulting.quickbase.com/db/bk7ne8qi3?a=q&qid=1

Formula for Button:

var Text csv = "Moe Howard,6/19/1897,125" & "\n" &
  "Curly Howard,10/22/1903,87" & "\n" &
  "Larry Fine,10/5/1902,115" & "\n"  &
  "Shemp Howard,3/11/1895,120";

var text URLONE = URLRoot() & "db/" & Dbid() & "?act=API_ImportFromCSV" &
"&apptoken=cqzpjxqd6kg4rxwewqg4dye6jf3" &
"&clist=6.7.8" &
"&records_csv=" & URLEncode($csv);

var text URLTWO = "https://haversineconsulting.quickbase.com/db/bk7ne8qi3?a=q&qid=1";

$URLONE & "&rdr=" & URLEncode($URLTWO)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Yup, that was a pretty useful tip for the next time I need to make a button to create a standard set of records.  Thx for the Syntax example.

As an aside, I have started to use Webhooks and find that they offer many solutions to QuickBase limitations.  I will post some examples.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Another rabbit hole to fall down. A QuickBase's Webhook can only react to a server-side detected (1) add, (2) edit or (3) delete event. Using JavaScript you can react to any client-side and there are a lot more client-side events than the three server-side events.

I am overstating my opinion of course and in fact I like Webhooks - but it is only a fraction of what is possible using script.