Database population delema

[BikeBoy]

I am not entirely sure if this is the correct forum for my question, and i apologize if it isn't.

I need to populate new database with 4 fields:

1. Company (Company code is 0 through 3)

2. EmpID (4 digit id of employee)

3. Emp Type (12 different codes)

4. Salary grade (10 diff codes)

I already have Company and EmpID data in other databases, where EmpID is unique. In this new database EmpID will not be unique, since I need All POSSIBLE combinations of all 4 fields. In other words there will be:

4 (diff company)* 12 (diff emp type) * 10 (diff salary code) = 480 records for each employee.

How can I automate the population of such database? For few thousand employees the records can quickly reach millions.

[Pupiweb]

You might script it the whole thing but the real question is why do you want to do it ?

What's the final aim ?

[cjaeger]

What you are trying to create?

Does it meanthat every employee is employed in 3 companies, has 12 types and 10 different salaries simultaniously?

If not, you 'd better stay with a couple of related databases, where you can choose one out of those valuees in the fields.

But now on to the problem:

You just need 4 nested loops and 4 globals.

[Ugo DI LUCA]

Hi Bikeboy,

Agreed with Giuseppe and Christian that we'd need to know more about why you are doing this stuff...

As for the loop script, it clearly depends also on the above. You'd still needs relationships to populate each fields and lines of this new File. It's necessary also to see if your employee MAY have 12 types or MUST have 12 types...

Assuming this is likely to be a Project File structure in which each employee seems to have multiple "functions" (your type) within multiple projcts (The Company) and multiple rates (Isn't the rate somehow related to the functions ?), this could be set within a Line item with one record for each employee related to the Project_ID (Company_ID in this case). Even if each empoyee has 12 different functions ( ? ) within the same company, the same setting would be kept, may be using multiple Seljoins afterwards.

In this new database EmpID will not be unique

IMHO, each line would be unique, with some Foreign keys (Projetct and Employee_ID). These 2 keys should stay unique to be related to the Main files...

[BikeBoy]

OK, guys, Im coming clean. This is for a small human resource company, and they want to have BAR CODES for each new prospect employee. Moreover, they want to preprint a bunch of all possible bar codes, and have them ready when certain potential employee enters the picture. I know that sounds like branding catle, but thats how they want it, period. And they dont care about the wastage of labels, so fine.

Their goal is to quickly scan all potential positions for a prospect. I am by no means a fan of this scheme, but they are very adamant thats how they want it done, so OK.

EmpID is just a sequential number, so we can preprint into the future. The trick is that one employee can potentially have several employee types (one to many relationship), several different salary grades, and work for any of the 0-3 companies. Initially I wanted to have this in one database for the ease of exporting into Create-a-Label software, but Im beginning to this that multiple databases with One-to-Many relationships will be necessary. What do you suggest?

Thanks very much for your input

[cjaeger]

just use a nested loop

see attacchment

employee.fp5.zip

[BikeBoy]

cjaeger,

Thank you for doing this, I really appreciate it! 2 questions:

1. How do you specify validation and increment on "create until" field? I tried to see Options, but it's just a global number field.

2. How do you get values like AA, AB, AC to show on global fields in layout mode?

Thanks again for your help.

[cjaeger]

1 - FileMaker Fields retain most of their properties even if you change their type. So any validation method entered for a text field will still be there when the field is changed into a global. You can verify whichproperties are kept by changing the type back to original after having left "define fields" once.

2 - that's simply an option in the menu edit->view>sample data

[danjacoby]

There's no validation option on global fields.

Changing a field to a global field will cause a loss of data, since (for any user) there's only one value per global field across all records.

[BobWeaver]

Actually, if you have a field with validation on it, and then change it to global, the validation does indeed remain in effect even though you don't have access to the validation setup dialog afterwards (unless you temporarily change the field back to text or number).