Item file w/ multiple keys???

[VA12]

I've got a clean solution for producing product lists, catalogs, etc. It grew out of a variety of lists that were produced in Excel and managed manually. In the lists and in the current solution, there is one item number for each product, with additional fields to define whether it is stocked in one or several of our different warehouses, and a few other variables that come into play.

 

The solution is sprouting legs, and the next step is to make a live connection to our big enterprise system so that the data that lives on that side can stay over there and not have to be imported or managed in the FMP solution. This seemed straightforward enough until I got my hands on a list of tables & fields available in the enterprise system and stumbled across the way items are defined over there. Here's the summary.

 

Items are managed in two tables. Master table & Location table. The part that's stumping me is that each table has multiple key fields, and it is the combination of those key fields that is unique, not the value of each field in each record.

 

Master table contains, along with a lot of other fields:

 

kp Item Number (six digit number)

kp Company (one digit number)

 

Location table contains

 

kf Item Number (from Master table)

kf Company (Ditto)

kp Division (text 3 digits)

kp Department (text 3 digits)

kp Warehouse (text 3 digits)

 

Pricing & other variable information is stored in Location table. Universal info in Master table.

 

So, for example, item 643164 can exist in all the following combinations.

 

Master:

643164 C1

643164 C2

etc.

 

Location

643164 C1 DV1 DP1 WH1

643164 C1 DV1 DP1 WH2

643164 C1 DV1 DP1 WH3

643164 C1 DV1 DP1 WH4

643164 C2 DV1 DP1 WH1

643164 C2 DV1 DP1 WH2

643164 C2 DV1 DP1 WH3

643164 C2 DV1 DP1 WH4

etc., etc., etc.

 

My problem is that I'm trying to find a temporary way of duplicating the enterprise system tables in FMP, do data dumps out of that system and import into what are basically duplicated tables in FMP, in anticipation of connecting to the enterprise system. The connection is a few months away.

 

In writing all this out it's become clear to me that I'm nuts for trying to implement a complicated temporary solution, and I should just leave well enough alone until it's time to connect, but I'm still curious as to how it would be done if I wanted to? The use of a totally different ID field generated in serial seems indicated, but how to keep the combinations unique? Is that just some sort of validation calculation?