You’re offline. This is a read only version of the page.
Hi
Is there a way to get the relationships between tables of a module in GP?
What if this module is installed from external resource?
Thx
*This post is locked for comments
That has been one of the most controversial topics that our colleagues and I have discussed thoroughly. The debate was raised due to a simple questions, Is Dynamics GP database relational or not ? Some people claimed that simply it is not since the theory of a relational database is not applied in Dynamics GP on the SQL server level, and therefore, tables are not linked through a primary-foreign key relationship, no database diagram can be printed illustrating the relations among tables. On the other hand, others have argues that the logic is there, and the relations are enforced by the application layers as it is stored within Dexterity.
Therefore, if you are looking for the ultimate value of how tables are linked, get the SDK as mentioned above, and check the rules and validations which are enforced by the application on the database tables.
Meanwhile, let us know if you have any further concerns,
Your feedback is highly appreciated,
Hi Hasan
Have you looked at the ER (Entity Relationship) diagrams provided in the SDK (Software Developer Kit)?
The SDK can be installed from the Tools folder of the DVD image.
David
Hi,
The only problem with this is that GP only has a couple of tables that have established foreign keys. The table relationships are handled by the dexterity programmer. There are several tools to aid you in your search, but there is not a data dictionary available that will tell you how everything is related. The SDK contains a model that will show you which tables are related, but it does not identify which column.
Kind regards,
Leslie
Hi
Good Day !!
Try this query ..
select t.name as TableWithForeignKey, fk.constraint_column_id as FK_PartNo , c.name as ForeignKeyColumn
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
--where fk.referenced_object_id = (select object_id from sys.tables where name = 'SY10600')
order by TableWithForeignKey, FK_PartNo
Regards,
Rinson KE
DBA
what do you mean by External Resource?
Inside GP SDK you can find table relationship detail per module.
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...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (