Forum Discussion

PedroRocha's avatar
PedroRocha
Qrew Trainee
8 years ago

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

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! 
  • MichaelBarrow's avatar
    MichaelBarrow
    Qrew Cadet
    8 years ago
    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.
    • PedroRocha's avatar
      PedroRocha
      Qrew Trainee
      8 years ago
      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? 
    • MichaelBarrow's avatar
      MichaelBarrow
      Qrew Cadet
      8 years ago
      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.
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    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.
    • PedroRocha's avatar
      PedroRocha
      Qrew Trainee
      8 years ago
      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? 
    • MichaelBarrow's avatar
      MichaelBarrow
      Qrew Cadet
      8 years ago
      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.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      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.
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    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.