How to avoid a circular Reference when using multiple date fields and a central master calendar..
I've seen a few posts on this subject, but nothing that matches directly...
I have a master calendar that I am using with the main field I'm linking to being 'Order Date'. By linking this through the calendar, I have all my months, quarters, fiscal periods etc.. and therefore can view all sales across orders by date element selected.
However I also need to compare my sales versus my quota's, by sales person. The quota information resides in another table and has a quota date attached which runs across a quarterly period. The sales person is tied into the invoice info via customer id and territory. The quota and sales person are linked together, but I'm having problems with the dates as if I link these, then I end up with a circular reference. I don't really see how I can concatenate this to the orders table as I still need to split across the quarters and anyway it is only the date that links the two of those, never mind the circular references, I feel that I have been going round and round in circles myself with this!
1. Either make sure that the Quota date does not have the same filed name as the Sales date,
2. or you can concatenate the Quotas with the Sales into a common Transaction table, setting TransactionType to "Quotas" resp. " "Sales" (then dropping Quota and Sales tables)
