Discussions

Expand all | Collapse all

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

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

    Posted 06-14-2017 15:29
    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! 


  • 2.  RE: How do I create multiple child records by specifying a date range?

    Posted 06-15-2017 02:32
    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.


  • 3.  RE: How do I create multiple child records by specifying a date range?

    Posted 06-15-2017 16:31
    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? 


  • 4.  RE: How do I create multiple child records by specifying a date range?

    Posted 06-15-2017 16:48
    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">https://www.screencast.com/t/N4by4u8kAoL">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.


  • 5.  RE: How do I create multiple child records by specifying a date range?

    Posted 06-15-2017 05:48
    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.


  • 6.  RE: How do I create multiple child records by specifying a date range?

    Posted 06-15-2017 15:59
    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? 


  • 7.  RE: How do I create multiple child records by specifying a date range?

    Posted 06-15-2017 16:06
    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.


  • 8.  RE: How do I create multiple child records by specifying a date range?

    Posted 06-15-2017 17:38
    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.


  • 9.  RE: How do I create multiple child records by specifying a date range?

    Posted 06-15-2017 18:38
    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.


  • 10.  RE: How do I create multiple child records by specifying a date range?

    Posted 06-16-2017 05:09
    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.


  • 11.  RE: How do I create multiple child records by specifying a date range?

    Posted 06-16-2017 14:57
    Thank you Matthew!!!!