Dynamics Community Forum Thread Details

GL-Acount - Accscheduleline85 relationship

Hello,

I am trying to build a data model in Power BI using tables from Business Central, specifically:

G/L Account (table 15)
Acc. Schedule Line (table 85)

I want to relate them correctly in order to create financial reports based on the Acc. Schedules.

I have noticed that both tables have a field of the "Totaling" type, but it seems that this field cannot be used to relate them directly. I think a bridge table is needed to establish the relationship between these two tables.

My questions are:

Which bridge table should I use to relate G/L Account with AccScheduleLine (85)?
Which columns from each table are appropriate to create that relationship?
Is there any table in Business Central that already exposes the relationship between G/L accounts and Acc. Schedule lines?

Thank you very much.

Categories:

Dynamics 365 Business Central

Hi,

The table 85 - Acc. Schedule Line, is used to build Financial report in Business Central.

There is no direct link however following is the logic.

If in Acc. Schedule Line the field "Totaling Type" is set to value "Posting Accounts",

then you need to apply the filter on table "G/L Account" on the field "No." and the filter value would be the value of field "Totaling" from "Acc. Schedule Line" record.

And apply filter on "Account Type" field of "G/L Account" table as value "Posting". Then you total the value of Balance or Net change field as required in your report.

If "Totaling Type" is set to value "Total Accounts" then in the field "Account type" of "G/L Account" table you need to apply filter with value "Total" and "Begin-Total" and "End-Total".

However the value in field "Totaling" in "Acc. Schedule Line" will be in BC filter text format. It follows this guide to perform filters - https://learn.microsoft.com/en-us/dynamics365/business-central/ui-enter-criteria-filters#FilterCriteria

So I am not sure how you will use this information but this is the logic behind it.

Let me know if you need any other info or help.

✅ Tick the checkbox below to mark the answer as verified, if it helped resolve your question.

Regards,

Dhiren.

LinkedIn - https://www.linkedin.com/in/ca-dhiren-nagar-5079081b2/

Blog - https://dhirennagar.blogspot.com/

Quick Links

Leaderboard > Small and medium business | Business Central, NAV, RMS