Typical scenario folks will have several InnerSubQueries for each TranGLC Type. I think there are a few Epicor Demo BAQs we can find.
You picked the most difficult task, because doing it right determines the speed. If you are Multi-Book or Multi-COA you will have duplicates. If you do Backflush you will have too much TranGLC Data… You will have to make a Sub-Select to find your MainBook for example etc… If you want the PartClass GL Account you have to go to EntityGLC etc… It all depends how much you want the BAQ to do.
You should be fine if its just a few GL Accounts, here are some notes on joins etc, may be relevant to you.
TranGLC Matrix
TranGLC Key Map to Related Tables.xlsx (15.0 KB)
Some Notes
These are some TranGLC to PartTran notes, but some still apply to you, especiall the conversion from integer to nvarchar stuff.
Optimizing Joins between TranGLC and PartTran in Epicor
When joining the TranGLC and PartTran tables in Epicor (especially in BAQs), performance can vary dramatically depending on:
- The direction of the join
- Which table is used as the driving table
- Data volume in each table for your specific query
- Proper handling of data type conversions (since TranGLC keys are
NVARCHAR(50))
- Filtering on
RelatedToFile = 'PartTran'
TranGLC acts as a bridge table linking General Ledger details to source transactions like inventory movements in PartTran.
Primary Indexes
| Table |
Primary Index Fields |
| TranGLC |
Company, RelatedToFile, Key1, Key2, Key3, Key4, Key5, Key6 |
| PartTran |
Company, SysDate, SysTime, TranNum |
Join Options
There are two main strategies — choose based on which table has fewer qualifying rows after your filters (e.g. date range, TranType, etc.).
Option 1: Start from PartTran (usually better when filtering on dates / fewer PartTran records)
Use when you apply cut-off dates or other selective filters on PartTran (typical for inventory / PartTran reconciliation reports).
| TranGLC Field |
PartTran Field |
Join Condition (in BAQ expression) |
| Company |
Company |
TranGLC.Company = PartTran.Company |
| RelatedToFile |
— |
TranGLC.RelatedToFile = ‘PartTran’ |
| Key1 |
SysDate |
TranGLC.Key1 = CONVERT(NVARCHAR(50), PartTran.SysDate, 1) |
| Key2 |
SysTime |
TranGLC.Key2 = CONVERT(NVARCHAR(50), PartTran.SysTime) |
| Key3 |
TranNum |
TranGLC.Key3 = CONVERT(NVARCHAR(50), PartTran.TranNum) |
- Why this often performs better: PartTran usually has date-based filters → smaller result set early → TranGLC is looked up efficiently.
Option 2: Start from TranGLC (better when filtering on GL periods / fewer TranGLC records)
Use when your main filter is on fiscal year/period, BookID, JournalCode, etc. (typical for GL reconciliation reports).
| TranGLC Field |
PartTran Field |
Join Condition (in BAQ expression) |
| Company |
Company |
TranGLC.Company = PartTran.Company |
| RelatedToFile |
— |
TranGLC.RelatedToFile = ‘PartTran’ |
| Key1 |
SysDate |
CONVERT(DATE, TranGLC.Key1, 1) = PartTran.SysDate |
| Key2 |
SysTime |
TRY_CONVERT(INT, TranGLC.Key2) = PartTran.SysTime |
| Key3 |
TranNum |
TRY_CONVERT(INT, TranGLC.Key3) = PartTran.TranNum |
- Note:
TRY_CONVERT is safer in newer versions (avoids errors on bad data); older versions may need plain CONVERT.
LEFT JOIN Considerations (from TranGLC to PartTran)
When you want all TranGLC records (even without matching PartTran), use a LEFT OUTER JOIN and safer conversions:
TranGLC.Company = PartTran.Company
AND TranGLC.RelatedToFile = 'PartTran'
AND TRY_CONVERT(DATE, TranGLC.Key1, 1) = PartTran.SysDate
AND TRY_CONVERT(INT, TranGLC.Key2) = PartTran.SysTime
AND TRY_CONVERT(INT, TranGLC.Key3) = PartTran.TranNum
BAQ Designer Tips
- Never use the dropdown for non-Company fields — type the full expression manually (e.g.
CONVERT(NVARCHAR(50), PartTran.SysDate, 1)).
- Include
TranGLC.RelatedToFile = 'PartTran' as a criteria or join condition.
- Test both join directions with your real filters and compare execution plans / run times.
- Add indexes or statistics if possible (DBA task).
- Use SubQueries if the BAQ becomes complex — isolate the TranGLC ↔ PartTran part first.
Choosing the Correct Option
- PartTran reconciliation report with date cutoffs → Option 1 (PartTran as starting point) usually faster.
- GL Reconciliation by Fiscal Period / Year / Journal → Option 2 (TranGLC as starting point) usually better.
Wrong direction can cause 10×–100× slower performance due to table scans on large tables.
Frequently Asked Questions
Q1: Which table links GLJrnDtl to transaction tables (APInvHed, InvcDtl, PartTran, etc.)?
A: TranGLC is the central linking table.
Q2: How to link TranGLC to GLJrnDtl?
| TranGLC Field |
GLJrnDtl Field |
Join Condition |
| Company |
Company |
= |
| BookID |
BookID |
= |
| FiscalYear |
FiscalYear |
= |
| FiscalYearSuffix |
FiscalYearSuffix |
= |
| JournalCode |
JournalCode |
= |
| JournalNum |
JournalNum |
= |
| JournalLine |
JournalLine |
= |
Q3: General pattern to link TranGLC to any transaction table?
Filter TranGLC.RelatedToFile = 'TableName' and match Key1–Key6 to the table’s primary key fields (excluding Company).
If >6 key fields → Key1 often = SysRowID (e.g. TaxDtl).
Q4: BAQ errors linking non-character fields to TranGLC Keys?
Convert transaction fields → NVARCHAR(50):
| Data Type |
Conversion Formula |
| INTEGER |
CONVERT(NVARCHAR(50), Table.Field) |
| DECIMAL |
CONVERT(NVARCHAR(50), Table.Field) |
| DATE |
CONVERT(NVARCHAR(50), Table.DateField, 1) |
| BIT/BOOLEAN |
CASE WHEN Field = 0 THEN 'False' ELSE 'True' END |
Q5: BAQ still slow even with correct joins?
- Verify join direction matches data volume.
- Convert TranGLC Keys → transaction types instead (sometimes faster).
- Use subqueries.
- Reduce returned columns.
- Apply tight filters early.
Q6: How to find a table’s Primary Key?
→ Epicor Data Dictionary Viewer → select table → look for index starting with PK_ (top of list).
Q7: How to find GL account for unposted transactions (e.g. AP Invoice expense line defaults)?
Join as usual + TranGLC.RecordType = 'A' (‘A’ = Account Data Only = unposted).
Record Types in TranGLC
- A — Unposted (pre-posting engine)
- R — Posted / Reference Data (after posting)
Q8: Multiple TranGLC rows per PartTran record?
- Normal (one-to-many) — posting engine can create multiple GL accounts per transaction.
- Filter
RecordType to isolate posted/unposted.
- Aggregate or use DISTINCT / grouping if needed.
Q9: Multiple TranGLC rows per GLJrnDtl record?
- Normal when summarization is enabled — multiple transactions can summarize into one journal entry.
- Filter
RecordType and be aware of summarization settings in GL Transaction Types.
By selecting the appropriate join direction, handling conversions correctly, and filtering strategically, you can achieve significantly better BAQ performance when working with TranGLC and PartTran. Always test both options with your actual data and filters!
GL Journal Entry Missing on TranGLC Table & Related Questions
Q10: Why can’t I find a General Journal (Journal Code = GJ) in my BAQ when querying the TranGLC table? Does this mean the journal didn’t post?
Answer:
This behavior is working as designed.
- General Journals (GJ) do not create records in the TranGLC table.
- The TranGLC table exists specifically to link subledger transactions (AR, AP, AM, CM, PartTran, etc.) to the General Ledger.
- Examples: Cash Receipts (from AR), AP Invoices, Inventory Adjustments, etc. appear in TranGLC.
- Pure General Journal Entries (created directly in the GL module) are not considered subledger transactions, so they do not generate TranGLC records.
Where to find General Journals:
- Query the GLJrnDtl table (General Ledger Journal Detail).
- If the journal appears in GLJrnDtl, it has posted successfully and correctly affected the GL balances.
Additional table to check:
- GLJrnDtlMnl (Manual General Ledger Journal Detail)
- Contains records created via:
- “Transfer from other modules” process
- Direct G/L Journal Entry program
- Before posting, these have a related GLJrnHed record.
- After posting, they appear in GLJrnDtl.
Special case – Deferred Expenses:
Only General Journals that include Deferred Expense logic may create TranGLC records.
In that case, filter on:
TranGLC.RelatedToFile = 'GLJrnDtlMnl'
Q11: How does the “Summarize GL Account” setting work?
- The system does summarize accounts into GLJrnDtl (the posted journal detail table) when summarization is enabled.
- However, it does NOT summarize records in the TranGLC table.
Reason:
The Chart Tracker (and similar drill-down tools) relies on TranGLC to show every individual source transaction detail.
Summarizing TranGLC would break the ability to trace back to the original subledger documents (e.g., which specific AP invoice or PartTran created the GL entry).
Q12: When is the GLPeriodBal table populated?
GLPeriodBal is populated/updated whenever any posting process completes successfully.
It summarizes net movements for each:
- Company
- COA Code
- Book ID
- Fiscal Year
- Fiscal Year Suffix
- Fiscal Period
- Account
Additional ways it gets updated:
- Running “Verify Balances” → “Transfer Opening Balances to New Year” process
- Fiscal Period 0 typically stores the beginning/opening balance for the year.
Q13: What are common signs of corrupt or problematic GLJrnDtl records?
Look for these issues:
-
Balance Flags missing/inconsistent
The following flags should all be 1 (true) for the record to appear correctly in the Trial Balance report:
PerBalFlagTBFlagDailyBalFlag
-
Missing GL Accounts in balance fields
BalanceAcct and TrialAcct must contain valid GL account numbers.- If either is blank (
''), the record will be excluded from Trial Balance.
Quick query to find problem records:
SELECT *
FROM Erp.GLJrnDtl
WHERE PerBalFlag = 0
OR TBFlag = 0
OR DailyBalFlag = 0
OR BalanceAcct = ''
OR TrialAcct = ''
TranGLC Duplicate Records – Understanding RecordType & UserCanModify
You may see multiple rows in TranGLC for what appears to be the same transaction. Common causes:
Example query to inspect duplicates:
SELECT RecordType, UserCanModify, *
FROM Erp.TranGLC
WHERE Company = 'YourCompany'
AND Key2 = '1250505' -- example key (adjust as needed)
AND UserCanModify = 1
GL Control Types – Quick Reference
| Type |
Purpose |
Visible/Modifiable? |
Populates TranGLC? |
Typical Use Case |
| Reusable |
Flexible, user-modifiable GL controls (e.g., Customer, Part defaults) |
Yes |
Yes (via business entities) |
AR/AP/Inventory defaults |
| Instance |
Specific account for one item/transaction |
Sometimes |
Yes (pre-posting) |
PORel, specific rules |
| Reference |
System-generated link between source transaction and GL |
No (hidden) |
Yes |
PartTran → GL, APInvDtl → GL, etc. |
Note on APInvExp (AP Invoice Expense Lines):
Duplicates with different RecordTypes can be hard-coded behavior in some versions for expense distribution.
Another common cause of duplicates:
- Multiple COAs (Chart of Accounts) or multiple Books active in the system.
- Transactions may post to each book, creating separate TranGLC rows.
MainBook – How It’s Calculated & Used
GLBook.MainBook is a flag (usually 1 = main/primary book, 0 = secondary/alternate book).
Typical usage in BAQs (example join):
LEFT OUTER JOIN Erp.GLBook GLBook
ON TranGLC.Company = GLBook.Company
AND TranGLC.BookID = GLBook.BookID
AND TranGLC.COACode = GLBook.COACode
AND GLBook.MainBook = '1' -- only main book
Common filter to include only main book transactions:
WHERE (TranGLC.BookID IS NULL OR GLBook.MainBook = '1')
This ensures the query focuses on the primary ledger and avoids inflating results with secondary books (if your company uses multi-book accounting).
By understanding these behaviors, you can build more accurate BAQs and avoid chasing “missing” or “duplicate” records that are actually functioning as designed in Epicor’s GL architecture.
I want to see it beat me and make me feel like my job is on the line. If you want the answer DM.