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
what do you mean by External Resource?
Inside GP SDK you can find table relationship detail per module.
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
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 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
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,
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
