Data model calendar

[#1]

Hello,

I have 3 tables.

Defect: DetectedDate, ClosedDate

Request: RequestID, OpenDate, ClosedDate

TimeLog: RequestID, LogDate

I want to use a master calendar to persist date selections in my reports using Defect.DetectedDate, Request.ClosedDate, and TimeLog.LogDate.

The issue is that I also want Request.RequestID to join with TimeLog.RequestID.  How can I achieve both?

Thank you

[Gysbert_Wassenaar · Accepted solution]

You can't do both. I think you should create more calendars. See this discussion for more information. You could use a calendar data island if you want to select one date and find the matching records with that date in all your date fields. See this tutorial

edit: You can concatenate the fields for your key first and apply autonumber to that:

autonumber(ProjectName & DefectID) as MyKey

[#3]

Thank you, that's guiding me down the right path.

Any idea how to generate autonumbers?  My defect table has a compound key (ProjectName + DefectID = Primary Key).  I'd rather convert the two fields to an integer rather than concatenate the two fields.

Thank you