145
Greetings,
I have experience working with GP backend and zero knowledge of SL. Could someone please let me know what tables to use/how to join them to get financial data from SL databases to Excel. Much appreciated.
-polaro
You're right Rick, I don't know why he isn't using some of the other tools in SL to create these Financial statements, but, then I've seen in the past where mgmt fixates on one style and can't seem to let it goes. As for tables and fields, if you go into the SL Help menu and Help Topics - scroll to the bottom of the list and you'll find a couple of Schema docs. The first one should give you a list of all the Tables, and then all fields in that table, for the entire database (less and third party additions.)
I don't know how to thank you. You've saved me many hours of browsing the web. The reason I want to get data from the backend is that my client wants record-by-record info to do audit I guess. Again, appreciate your help.
Best wishes
I have been a Solomon (Dynamics SL) ISV for over 20 years and have never found a published data dictionary. There used to be (pre-Microsoft days) a manual that shows the various data tables related to the various input screens but that is not exactly what you would need. the good news is that the original Solomon folks did a very reasonable job of naming data tables and field within those tables such that it is relatively easy to figure things out. But, as you implied, knowing the relationship of the various data tables is never that obvious.
Having said this, in your case, everything you need to push data into Excel to produce a balance sheet or an income statement is contained within that accthist table in the application database. The trick is to make sure you are pulling the correct fields since there are many with similar names. For example, there are a set of fields for the month-to-date net postings, another set for the month-to-date consolidation amounts and a third set for the month-to-date allocation amounts. You will want to use the pdtbalnn fields for a financial type statement as well as the corresponding ytdbalnn set. So, for example, if you want period 4's net postings, use ptdbal03 (remember they count from 0) and ytdbal03 for the YTD balance through period 4. If you want to pull the GL account name as well as the GL account number, join in the account table on the acct field.
I am a bit curious as to why you are not using FRx or Management Reporter to create the financial statements instead of pushing data out to Excel to make those statements. Perhaps it is because you are not familiar with those report writers but they are specifically designed for that purpose and are already mapped into the appropriate tables and fields. You do not need to answer this, I was just sort of wondering.
Thanks! Is there any documentation with data directory for SL db? Do I need to join tables or all P&L data is contained in one? I'd assume it has the keys for accounts, groups, etc but what about key labels? Appreciate your help
SL holds the information in a table named accthist. Accthist has a record for every natural account / sub-account combination for each fiscal year. This table also holds budget information and statistical information so you will likely want to filter on a particular ledger id. There are many fields in this table but you will be most interested in the month-to-date fields and the year-to-date fields. The month-to-date fields contain the net posted amount for that month, not the ytd balance through that month. the ytd field contains the net posted amount through the current fiscal period. Please note that the mtd fields count from 0 instead of 1 so the first fiscal month field ends with 00 and not 01.
Hope this helps. Let me know if you need more information.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,558
Super User 2024 Season 2
#2
Martin Dráb
228,647
Most Valuable Professional
#3
nmaenpaa
101,148
Share
Report
All responses (
Answers (