Date Reference Proxy fields

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
What I am trying to achieve?

I have 2 tables.

Expense Table which has a field for Expense Date (of type Date)
We are managing our expenses by the fiscal calendar. I have a separate table (Fiscal date table) that maps the cal date to the fiscal month/quarter/year.

On the Expense table form, when I select the expense date I want to be able to see fiscal month/quarter associated with it.

I cannot directly use the calendar date(from the Fiscal date table) on the form because that shows up as proxy field and doesn't open up in a calendar format.

Any suggestions ??

Appreciate the help.  
Photo of Angel

Angel

  • 724 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
An option is to have the date formulaically connect to the fiscal year.  Then you can bring all the year/qtr/month, balance, etc. as lookup fields.

How is your 'Fiscal Year' table set up?  What is the Key Field (if not the Record ID#)?  What type of information do you have there?
Just trying to get some context so my next description makes more sense.
Photo of Angel

Angel

  • 724 Points 500 badge 2x thumb
How do i establish the date to formulaically connect to the fiscal table?

The fiscal Table has the calendar date, the fiscal month, fiscal quarter, fiscal year associated with that calendar date.

The Key Field is the Record ID itself.

Any leads anyone?
(Edited)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
You will want to set up a different field as the Key field.  Something like the Date, would be good, because you shouldn't have the same date more than once on the fiscal year table, correct?

Then when you make the relationship, it will use the key field (date) as the reference field.

You can then convert that reference field from a 'date' field to a 'formula-date' field.
In the formula, you will make it reference your [Date of Expense].

Then all of your expenses will automatically connect to the fiscal date.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I'm not sure how far into this you are, but often times its good to break those days, months, years, into separate tables.
 
So for 2017 you would have a years table with 1 record (2017), a months table with 12 records, and a days table with 365 records.

That way you can easily summarize and break things up a bit. (even down to weeks if need be)
Photo of Angel

Angel

  • 724 Points 500 badge 2x thumb
Thanks a lot for your help Matt. This is definitely helpful. I really didn't know we could use reference fields as formulas.