Linking to time frames across tables

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Hi, I've been battling with the below for a couple of days and feel there is probably a relatively simple solution. 

I want to compare the below metrics, which have their own tables, against monthly targets.

  • Sales (i.e. when the sale is made)
  • Revenue recognized (i.e. revenue associated with % of the project complete)
  • Invoices (Cash flow, i.e. when invoices are received) 

I have one table called Forecast which has the sales and revenue forecast per month. Each of the above is in a one to many forecast with Forecast (e.g. Forecast --< Invoices). In the Forecast table the Period is expressed as YYYY-MM.

The issue is, I still need to capture actual dates in the tables above (at least Sales and Invoices) so selecting the Forecast Period is an annoying extra step.

Is there a way to auto populate the Reference field so it is the same as the date of the record in the child table (converted into the same format)?

Thanks in advance

Photo of Matt

Matt

  • 62 Points

Posted 3 years ago

  • 0
  • 1
No problem.

The reference field can be a formula

The formula would be

List("-", ToText(Year([Invoice date])),right("0",totext(month(invoice date])),2))
Photo of Matt

Matt

  • 62 Points
Thanks for the quick response Mark. I realize reading your response I had already done this but not realized I'd come across the answer.
So you are smarter than you thought you were.  Perfect.