Skip to main content
Question

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

  • January 16, 2018
  • 0 replies
  • 560 views
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!
    This topic has been closed for replies.

    • SystemsBVI
    • Registered
    • January 16, 2018
    How does your Months table look like ? Is it one record per day ? 
    AviSikenporePro
    Forum|alt.badge.img+1
    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.
      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.
      Terms & ConditionsAccessibility statement