Need help with importing data
I created a Parts database. I have a main "Parts" table where the primary key is the field "Part_ID", but we go by the actual part number, which is a regular text field, "Part_Number".
I created another table called "Sub-Numbers", because one main part number can have many "sub numbers" (which act as interchanges for cross-reference).
I created a Parts Detail layout, which serves as the main layout for parts information. On this layout, I created a portal for the Sub-Numbers table, so when you look at one part record, the portal shows all/any sub numbers related to that one part number. I also set the portal to allow creation & deletion of Sub Numbers via this portal. And everything works great.
But ... I'd like to be able to import a file into the Sub-Numbers table that will upload the main part numbers and their relevant sub numbers. The format of the Excel sheet is:
Part_Number A Sub Number 1
Part_Number A Sub Number 2
Part_Number A Sub Number 3
Part_Number B Sub Number 1
Part_Number B Sub Number 2
Part_Number B Sub Number 3
Part_Number C Sub Number 1
So, after importing this data into the Sub-Numbers table, we should be able to see the Sub Numbers in the main Parts layout, in the portal.
The challenge I'm facing is that this Excel file has the value for the "Part_Number" field from the main Parts table, not the "Part_ID", which is the primary key and which makes the relationship between the main "Parts" table and the "Sub-Numbers" table.
In the "Sub-Numbers" table, I have 3 fields:
Sub_ID (primary key)
Part_ID (foreign key)
Sub_Number (Sub Number)
How can I import this "Master Sub" Excel file into the "Sub-Numbers" table using the "Part_Number" field (from the "Parts" table) as the determining or matching field? Right now, if I try to import the file, I'm not able to match the "Part_Number" with any field in the "Sub-Numbers" table ...