Autogenerate based on two fields

[#1]

Hello

I want to autogenerate a link table where the link field is 'salesperson|mm-yyyy'

The reason is, I have loads of tables with completely different sales metrics, but they all have two common fields: salesperson and month.

So let's say I have 20 sales people (A,B,C,...) and 36 months. I want to generate a table with link field as follows:

Salesperson,Date,LinkField

--------------------------------

A , 01-2012 , A|01-2012

A , 02-2012 , A|02-2012

A , 03-2012 , A|03-2012

...

B , 01-2012 , B|01-2012

B , 02-2012 , B|02-2012

...

and so on. In this example, I'd end up with 36 * 20 = 720 records.

I'm thinking to use a combination of autogenerate (to generate the dates) and a for next loop to go through each salesperson record in turn.

Is this the best way, or is there a more straightforward way that I'm missing?

With thanks

James

[Oleg_Troyansky]

wouldn't it be cleaner to generate the data based on the existing combinations in your tables? This way, you will be sure that all the existing combinations are covered, and non of the non-existing combinations got generated for nothing:

LinkTable:

load distinct

   SalesPerson,

   Month,

   SalesPerson & '|' & Month as LinkKey

resident

     Tab1

;

load distinct

  SalesPerson,

  Month,

  SalesPerson & '|' & Month as LinkKey

resident

     Tab2

;

etc...

[#3]

Thank you for your reply. I see what you are saying and I will now have to think if that will work for me.

My concern is seeing salespeople disappear from certain reports if they don't have any records for that month in one of the tables. Isn't it better to show all salespeople in all months, but with null data against them instead of them just not being on the report?

I don't know what best practice is in situations like these.

Thanks again

James