Discussions

Expand all | Collapse all

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

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

    Posted 01-16-2018 17:14
    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!


  • 2.  RE: How to extract month and year from date, then use them in a relationship

     
    Posted 01-16-2018 17:32
    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.


  • 3.  RE: How to extract month and year from date, then use them in a relationship

    Posted 01-16-2018 17:32
    How does your Months table look like ? Is it one record per day ? 


  • 4.  RE: How to extract month and year from date, then use them in a relationship

    Posted 01-16-2018 17:36
    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.


  • 5.  RE: How to extract month and year from date, then use them in a relationship

    Top
    Contributor
    Posted 01-16-2018 18:09
    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.


  • 6.  RE: How to extract month and year from date, then use them in a relationship

    Top
    Contributor
    Posted 01-16-2018 18:10


  • 7.  RE: How to extract month and year from date, then use them in a relationship

    Posted 01-16-2018 19:44
    Thank you so much! It works!