Forum Discussion

HALEYHALEY's avatar
HALEYHALEY
Qrew Trainee
8 years ago

How to extract month and year from date, then use them in a relationship

Hello,

I have 3 tables set up: Months, Expenses, Incomes.

Two relationships: Months - Expenses and Months - Incomes (one month has many incomes and many expenses, sometimes several incomes/expenses on any given day).

Issue: when I enter a new expense, and the Date Created is automatically attached to the entry, the date is not used to automatically fill in the Month lookup field (and thus add the entry to the Months table). The Date Created and Month lookup field don't talk to each other. How can I make them communicate?

Thank you!
Ask about Quickbase functionality
  • AviSikenporePro's avatar
    AviSikenporePro
    Qrew Trainee
    8 years ago
    Create a new formula text field. Here is the code below 


    Case(Month(ToDate([Date Created])),
    1,"January",
    2,"February",
    3,"March",
    4,"April",
    5,"May",
    6,"June",
    7,"July",
    8,"August",
    9,"September",
    10,"October",
    11,"November",
    12,"December",
    "")

    This will create the month based on the date created. I think the issue you are having is that date created is a date/time field and not a date field. You need to do a typeconversion first using ToDate.
  • SystemsBVI's avatar
    SystemsBVI
    Qrew Assistant Captain
    8 years ago
    How does your Months table look like ? Is it one record per day ? 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    You may want to make a copy of your app before making those suggested changes.

    I suggest that you set up the Key field of your months table to be in the format YYYYMM, so for example a text field looking like

    201801
    201802
    201813


    You can use Excel to do an upload for say the next 10 years worth of months, so 120 months.
    etc 

    Then on the detail side of the relationship create a formula called [YYYYMM]

    It seems that you are linking the record based on the [Date Created].  I suggest having an override field so that you have the ability to force an entry into a different month, say for example, you get behind in the data entry near a month end.

    Then the formula for the YYYMM field on hew child side would be something like

    var date DateToUse =
    IF(not IsNull([Date Override]),[Date Override], ToDate([Date Created]);

    var text YYYY = ToText(Year($DateToUse));
    var text MM = Right("0" & ToText(Month($DateToUse),2);

    $YYYY & $MM

    That should make a YYYYMM field and then you can base your relationship to the months table based on that field.
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      8 years ago
      Don't use a text field... Just use the date field as the key field.  

      So on your Months table you will have a date field called [First Day of the Month].

      Then on your expenses and income tables you will connect the records via a formula reference field.  

      i.e your formula would be:
      FirstDayOfMonth([Date]).

      The reason you want to keep it a date and not a text field, is the reporting that you will eventually start building.  QB handles dates much better than text, especially if you want them in chronological order.

      Its always easier to pull things 'out' of a date field than it is out of a text field.