Hello,
The Problem:
My team has a large spreadsheet used track the disposition of property assets, and has grown too large to use and maintain. A little background, when an asset is unusable, expired, lost, etc; it will get disposed via a form called Disposition Form (DF). That form might have more than one asset on it (DF Line Item). If the asset was purchased against a particular contract, the Disposition Form will be assigned a "Case" number and will go to a Contracting Officer for instructions on what to do with that asset. Additional data like Status, or Open/Close Dates are in the tracker, but for this illustration in not important.

Assuming the plan is building a solution with the following main tables (CASE, DF, DF Line Items, Programs), with these Relationships:
Case : : Case_id_pk = DF : : Case_id_fk
Case : : Program_id_fk = Program_id_pk
DF : : DF_id_pk = DF_LineItems : : DF_id_fk
You get the point. I imported the Excel sheet into a new solution and table is called "Legacy Normalized".

My question for the forum is: Is there a way to pull data from "Legacy Normalized" in a way where that data could be moved into its proper table?
So a Layout would show a Case Number and the DF Number that is attached to that Case and the DF's Line Items, should more than one exist.
Sometimes there are Cases with Multiple DF Numbers assigned to it.

Example:
 

PROGRAM	CASE	DF	DF LINEITEMS
American Air	AA1234	822516	1
American Air	AA1235	752289	5
American Air	AA1235	752290	2
American Air	AA1235	752291	1
American Air	AA1236	498652	1
American Air	AA1237	624893	2
Delta Air	DA2268	256121	1
Delta Air	DA2269	256123	13
Delta Air	DA2270	256126	9
United Air	UA3983	663478	2
United Air	UA3983	847577	1
United Air	UA3984	847578	1
United Air	UA3985	847579	2

I hope this example makes sense.
Basically, i want to split up that large table of data into its proper tables.
Any good suggestions is very much appreciated

Thanks

 

All you need to do is import the spreadsheet data 3 times, each time importing different columns into a different table. Using your example:

• Import the PROGRAM and CASE columns into the Case table;
• Import the CASE and DF columns into the DF table;
• Import the DF and DF LINEITEMS columns into the DF_LineItems table.

Before importing, set the validation of the PK field in both Case and DF tables to Unique, Validate always. This will "unflatten" the data by creating only a single record for each repeating value.
 

BTW, your terminology is off. What you have is a "flat" table. "Normalized" is what you want to end up with.

 

Thank you, that makes sense.
Sorry about the error in terminology, what i meant to say is it was cleaned up.
Imagine a large spreadsheet, and each row (record) was about 40 columns wide.
A "Case" might have one or more "DF's attached to it.
What we were doing was listing all the DF numbers in one cell and that was stupid.

So if a Case had DF 0001 attached to it, that was simple
But if a Case had DF 0001, DF0002, DF003 or more crammed in one cell under the column "DF's Attached", then it would be impossible to import into FM
Instead, I made sure each row under column "Case" matched a "DF Number"

CASE             DF Assigned
1234                0001 
1236                8745
1236                8271
1236                7625
1238                7263

I will try your method, thank you kindly