How do I create multiple child records by specifying a date range?

  • 1
  • 1
  • Question
  • Updated 1 year ago
  • Answered
So I have two tables. One for Employees and the other for Vacation Time. How do I create multiple entries on the Vacation Time table at the same time, by selecting a start date and an end date? 

The goal is to have a calendar report showing the name of the employee on each day of the calendar where the date range applies. I don't want to enter the record day by day, one by one. 
Any help would be greatly appreciated! 
Photo of Pedro Rocha

Pedro Rocha

  • 218 Points 100 badge 2x thumb

Posted 1 year ago

  • 1
  • 1
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
I had this exact same need and had to hire a consultant to create a script. It is 200 lines of code. It works great, but at a high cost relative to a non-code solution. I would love a non-code solution. I don't know how it can be done, though. I would be interested in possible suggestions too. One thing that greatly facilitates my script is a Calendar table that has one record for every business date. The script can pull a subset of those records between the start and end dates to drive the looping.
Photo of Pedro Rocha

Pedro Rocha

  • 218 Points 100 badge 2x thumb
Michael thank you for replying. I thought this would be a very common issue among Quick Base users but you are the only person I found with this same need. At this point I'm considering every option. 
Did this Calendar table have all business dates for the whole year? 
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
Yes, I find that stuff like this is a pretty common need for us in a number of areas. Here's a screen shot of some of the fields I have in my Calendar table. The Date field is the primary key so that I can easily relate it to any other table that has a date field.
https://www.screencast.com/t/N4by4u8kAoL

I also use a system variables type of table that I call Parameters that has one record with Record ID# = 1 that  I relate to all other tables through a  numeric formula field = 1, and that has some important date-related and month-related fields for where we are in our monthly and yearly business cycle.

These two tables make everything date-related a whole lot easier.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
How many days is the average date range?

What if you clicked a button x number of times?  So if you had 5 days request, then you'd just click 5 times...

If that sounds like a cheap, but doable solution let me know and I can describe it more.
Photo of Pedro Rocha

Pedro Rocha

  • 218 Points 100 badge 2x thumb
This sounds a lot more doable. We can have up to two weeks of vacation. I'd much rather click on a button 14 times then enter 14 records one by one and fill out all the required fields. Thank you very much for the response. How would I make this work? 
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
The use-case for my script approach is for pre-populating attendance records with an empty/unknown status, and I run it for 30 days out to handle people who are going on vacation for up to 2 weeks so they can get their anticipated daily attendance updated before they go.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,464 Points 50k badge 2x thumb
Just to chime in here.

I had done an app which does not use script, but which can generate X number of records.  The technique involves using a table preloaded with 500 records just numbered from Record ID 1 to 500.  Then using a parameter type record to save parameters it uses API_RunImport to import qualifying records from that table to the to the target table to create up to 500 records.

So its still requires a "consultant" to set this up, but there is no script required.
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
Very cool! I'm certainly not disparaging consultants or code at all. I've been helped greatly over the years by Josh Shortlidge at Data Collaborative, and every dollar has been well-spent. I just want to keep things as simple as possible. Mark, you are one of the best at that. Thanks for all of your support on this forum.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Pedro, 

You will want to use the API_AddRecord function in a formula URL to get the new records added / click.


https://<em>target_domain</em>/db/<em>target_dbid</em>?a=API_AddRecord&apptoken=<em>app_token<br></em>&_fid_6="&URLEncode([Record ID#) &"
&_fid_8="&URLEncode([Next Day Off])

For simplicity sake, I'm going to assume you just have 2 tables. 
Parent Table: Employee Requests
Child Table: Days Off

You make a request with that has a [Start Date], and an [End Date].

On the [_Days_Off] table you will have a [Date] field and also relate it to the [_Employee_Requests]

In the relationship make a summary field that summarizes the maximum [Date] of the Day Off.

You will then need to use that [Max Day Off Date] and add one day to it.

Make a formula date field called [Next Day Off], and the equation would be.

[Max Day Off Date]+Days(1)

But if [Max Day Off Date] is blank (i.e. its your first record to be made) you need to account for that.

If(IsNull([Max Day Off Date]), [Start Date], [Max Day Off Date]+Days(1))

Now that you have the set up together, you can work on the formula-url.
Just mapping the Record ID# to the [Related Request] and the Next Day Off to the [Date] field.

You will need a refresh between each click, as to get the most updated max date.  So it might look something like this.

var text URL= https://<em>target_domain</em>/db/<em>target_dbid</em>?a=API_AddRecord&amp;apptoken=<em>app_token</em><br>&_fid_6="&URLEncode([Record ID#)<br>&_fid_6="&URLEncode([Record ID#) &"
&_fid_8="&URLEncode([Next Day Off])<br> ;
"javascript:" &
"$.get('" & 
$URL & 
"',function(){" &
"location.reload(true);" &
"});" 
& "void(0);"

I hope that helps.  Its not my favorite, but if you really can't use script, and need a shortcut to creating multiple records.  This will do it with minimal set up time.
Photo of Pedro Rocha

Pedro Rocha

  • 218 Points 100 badge 2x thumb
Thank you Matthew!!!!