Auto populate dates

  • 1
  • 1
  • Question
  • Updated 11 months ago
  • Answered
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: 
  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. 
Photo of Karen Wondergem

Karen Wondergem

  • 150 Points 100 badge 2x thumb

Posted 11 months ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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.


 
Photo of Karen Wondergem

Karen Wondergem

  • 150 Points 100 badge 2x thumb
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. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
[start date] - weeks(2)

or also you can do

[start date] - days(14)
Photo of Karen Wondergem

Karen Wondergem

  • 150 Points 100 badge 2x thumb
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)  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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)
Photo of Karen Wondergem

Karen Wondergem

  • 150 Points 100 badge 2x thumb
Ah- sorry I wasn't clear I had to wipe out the existing formula and replace it. Sorry about that, now it works perfectly. :)