Date Reference Proxy fields

Question

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. &nbsp;

mcfneil · Answer

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.

angelangel · Answer

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?

mcfneil · Answer

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.

mcfneil · Answer

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)

angelangel · Answer

Thanks a lot for your help Matt. This is definitely helpful. I really didn't know we could use reference fields as formulas.&nbsp;

Forum Discussion

Date Reference Proxy fields

Related Content

Proxy fields

Multiple reference proxy fields help

Reference tables quandary

Tracking References and Definitions

Same reference field, different selections within the form