Transferring and matching records in a 1:M relationship from two Excel files into one table in FMP Ver. 11 file
I want to create a new FMP database file called Chart of Accounts (i.e., COA) by merging and matching selected data from two Excel files (called Accounts and Cost Centres).
That is, I want to transfer 4335 Cost Centre codes (i.e., records) in a Cost Centres Excel file and 2656 General Ledger (GL) Account codes (i.e., records) from the Accounts Excel file, so that in the new COA FMP file each GL account code (i.e., record) will have 4335 different Cost Centre codes (i.e., records) .
The relationship Accounts: Cost Centres is One: Many.
The number of records in the COA FMP file will result in 11,513,376 records (2656 GL Account codes x 4335 Cost Centre codes) — i.e. each GL Account code record has 4335 Cost Centre Codes records.
The GL Accounts and Cost Centre Codes are each 6 numbers, 000000 to 999999.
Will you please advise how I can do this?