MI Schools Import a Trial Balance

You may need to prepare the Excel file for importing. Create a new tab named, RawData, to leave source data intact. Copy the source data into RawData. Insert 8 columns named: ImpSfdMunID, ImpSfdSadID. ImpSfdFundCode, ImpSfdFunctionCode, ImpSfddObjectCode, ImpSfdStateCode, ImpSfdGrantCode, ImpLocationCode.

Include: ImpSfdAccountString, DescriptionSource, ImpSection, ImpSfdAmount

ImpSection should be labeled: Revenues, Expense & SOP

Populate ImpSfdMunID with MunID

Populate ImpSfdSadID with the SadID

Populate ImpSfdFundCode with a formula to calculate the Fund Code

Truncate table “aa_mi_se_import_tb”.

Import trial balance into “aa_mi_se_import_tb”

Updated the Fund Code ID: UPDATE aa_mi_se_import_tb JOIN c_fundtypestatecodes ON FtsStateNumber = ImpSfdFundCode SET ImpSfdFtsID = FtsID WHERE FtsState = 'MI' AND FtsModID = 2;

Search for missing Fund Code ID’s.

SELECT * FROM aa_mi_se_import_tb WHERE ImpSfdFtsID IS NULL;

Updated the SscID’s: UPDATE aa_mi_se_import_tb JOIN m_finsubcategories ON SscLineNumber = ImpSfdFunctionCode JOIN m_fincategories ON SucID = SscSucID SET ImpSfdSscID = SscID WHERE SscState = 'MI' AND SucSection = 'Expense' AND SucModID = 2;

Search for missing SscID’s: SELECT * FROM aa_mi_se_import_tb WHERE ImpSfdSscID IS NULL;

Update Object Codes from the Account String: UPDATE aa_mi_se_import_tb SET ImpSfddObjectCode = RIGHT(LEFT(ImpSfdAccountString,12),4);

Delete any existing records in the Annual Data: DELETE FROM s_financialdatadetail WHERE SfdSadID = 60026;

INSERT INTO s_financialdatadetail (SfdMunID, SfdSadID, SfdFtsID, SfdSscID, SfdFocID, SfdFundCode, SfdFunctionCode, SfddObjectCode, SfdAccountString, SfdAmount, SfdDateAdded, SfdDateModified) SELECT ImpSfdMunID, ImpSfdSadID, ImpSfdFtsID, ImpSfdSscID, ImpSfdFocID, ImpSfdFundCode, ImpSfdFunctionCode, ImpSfddObjectCode, ImpSfdAccountString, ImpSfdAmount, NOW(), NOW() FROM aa_mi_se_import_tb WHERE ImpSfdAmount <> 0 AND ImpSfdAmount IS NOT NULL