Expand all | Collapse all

Auto populate dates

  • 1.  Auto populate dates

    Posted 12-20-2017 20:03
    I am a complete newbie so this may be an easy question for some. I am using QB to manage courses at a school. I have a project sheet with the following fields: ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1684244/RackMultipart20171220-33758-1yswpg1-Screen_Shot_2017-12-20_at_11.52.59_AM_inline.png?1513800021">
    1. Launch term (Dropdown - text multiple choice)
    2. Launch date (Date)
    3. Copy date (Date)
    4. End Date (Date)

    I want to auto populate the three date fields based on the choice in the dropdown. For example, If the user chooses Spring 2 2018, the term start auto populates with the date for Spring 2, the course copy auto populates 2 weeks before the date for spring 2, and the end date auto populates for one month before the course copy. 

    Right now they are not connected to anything. Is this possible? Please see image for a visual. We have multiple terms per year, with multiple dates, across multiple schools, so each term is unique as is their respective dates. 

  • 2.  RE: Auto populate dates

    Posted 12-20-2017 20:13
    No problem,

    I suggest creating a table of School Terms.  That table will hold your drop down choices for the School Terms.  If this is a new app with no data yet, then I suggest leaving the Key field of that table being the Record ID. If you have a lot of data, too much to Grid edit then post back and we can deal with that.

    The fields on the table will be
    Term Name (text field)
    Start date (date field)
    and the rest of the fields will be formula date fields.

    The formula to adjust a date to a different month is

    AdjustMonth([Start Date], -1)

    for example that would make a date calculate to 1 month before the start date.

    Then you will make a relationship where 1 School Term has many Courses, and then lookup all those data fields down into your Courses record.

    Post back if you have any questions.


  • 3.  RE: Auto populate dates

    Posted 12-21-2017 17:03
    Thank you! How would I adjust the date by two weeks? The copy date field should be -2 weeks from the start date. I tried .5 but that didn't seem to work. 

  • 4.  RE: Auto populate dates

    Posted 12-21-2017 17:05
    [start date] - weeks(2)

    or also you can do

    [start date] - days(14)

  • 5.  RE: Auto populate dates

    Posted 12-21-2017 17:14
    Thank you- I am gettting a syntax error for both..? This is my formula based on the field of Term Start Date.
    AdjustMonth([Term Start Date],- weeks(2)  

  • 6.  RE: Auto populate dates

    Posted 12-21-2017 17:18
    The AdjustMonth function is only used to adjust dates in whole integer month increments.  There is a specials function for that as well as another one AdjustYear, as not all months and years are the same length.

    But to adjust by a certain number of days, you do not use the Adjust Month function at all, so just use what I suggested above

    [start date] - weeks(2)

  • 7.  RE: Auto populate dates

    Posted 12-21-2017 17:25
    Ah- sorry I wasn't clear I had to wipe out the existing formula and replace it. Sorry about that, now it works perfectly. :)