Cross-Referencing Primary Key from Date Calculation

[T. Azimuth Schwitters]

Hello, first time poster and FM newbie.

I'm creating a DB that tracks sales for a small retail sales team.

One table called "Quarter" contains a primary key called "__kp_Quarter" which is a unique identifier of a fiscal quarter. An example would be "2012 Q1". There are also two fields, startDate and endDate, which the user sets to the start and end dates of the quarter. This is entrusted to the user because lengths of fiscal quarters may vary.

In another table called "Sale," which tracks individual sales, there is a "Date" field. This is also edited by the user, because some sales are backdated. I'd like to have the field _kf_Quarter, where the content is auto-entered by detecting which quarter's date range contains the sale's date.

How is this possible?

Is there a better method to track sales by fiscal quarter?

[Lee Smith]

You should be able to delete your own post for up to forty eight hours if no one has posted a reply. you can also request a moderator to do this for this for you.

Keep in mind that this is a learning forum, and if you have solved this, you might post your solution for others.

Lee

[T. Azimuth Schwitters]

Nope, still looking for a solution. I can't seem to figure out a method that doesn't step through every record in the Quarter table to test the sale date against the quarter's date range.

[comment]

I'd like to have the field _kf_Quarter, where the content is auto-entered by detecting which quarter's date range contains the sale's date.

You could do this by a lookup, using a relationship based on:

Sales::Date = Quarter::StartDate

and setting the lookup to 'copy next lower value'.

Is there a better method to track sales by fiscal quarter?

Probably. Why is it that the quarter boundaries cannot be calculated? Are they set arbitrarily by a human?

[T. Azimuth Schwitters]

I may have found a solution.

Rather than get hung up on trying to display the current quarter in the Sales layout, I created a multi-criteria relation between the Sale and Quarter tables, in order to filter all sales >= the start date and <= the end date, then added a portal to the Quarter Summary layout.

To put it another way, I chose to look at the situation from a different angle: Not attaching a sale to a label, but creating a report from the sales' related table.