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

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • In Progress
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!
Photo of HALEY

HALEY

  • 180 Points 100 badge 2x thumb

Posted 10 months ago

  • 0
  • 1
Photo of Systems BVI

Systems BVI

  • 530 Points 500 badge 2x thumb
How does your Months table look like ? Is it one record per day ? 
Photo of Avinash Sikenpore

Avinash Sikenpore

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

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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.
(Edited)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of HALEY

HALEY

  • 180 Points 100 badge 2x thumb
Thank you so much! It works!