Relationships between modules

English Language - Forum
1

Hello

version: 8
database: MariaDB

We have created a relationships between 4 modules (documents, farmer, farmer_info, district) that represent tables in the database.

We need the relationships(foreign keys) between tables in the database to be created automatically after creating relationship between modules in SuiteCRM. This is not happening after creating relationship between modules in SuiteCRM.

Example: We have farmer field in contract module (as if farmer (fk) column in contract table). We’ve created the relationship between contract and farmer modules. When sending the test API request to the contract we see all the data and we get the result of related farmer data in this form:

“V8/module/company/contract/{id}/relationships/company_farmer_company_contract”
*‘company_farmer_company_contract’ - this represents the pivot table created by SuiteCRM in database.

The problems are:

  1. We have to create links per every contract each time (every row). But we want the SuiteCRM automatically create a ‘link’ between every related modules.
  2. When we want to create a connection in form of foreign keys in those tables like farmer.id is a FK in contract.farmer_id it appears that SuiteCRM creates tables (they are empty when created so we fill them with migrated data) where the ID columns, that are created automatically by SuiteCRM, are CHAR data type. That means we have to change them manually in database in every table and even after that we get errors.

Thanks in advance

2

What kind of relationships did you create in SuiteCRM? One-to-many, many-to-many, …?

There is also the possibility of using a simpler kind of relationship: a Relate field.

See this for more details if you really want to change the way SuiteCRM creates the relationships, removing the join tables and making direct Foreign keys:

3

There are 4 relationships in this case:

  • contract.district (one-to-many) dictrict.id
  • farmer_info.district (one-to-many) dictrict.id
  • farmer.personal_id (one-to-many) farmer_info.id
  • farmer.contract (one-to-many) contract.id
4

:point_up: you can’t change the ID datatype, it has to be char, but you can put any old IDs you already have in there, as long as they’re unique. At least, you could, until recently, and things may be different from the API: see here

Also, note that if you are just starting to do API work, you’re probably better off using the GraphQL API, not the API v8.

About SuiteCRM automatically creating the links to related tables - start by testing things from the UI, not from the API. From the UI, if the relationships are correctly created, I am sure SuiteCRM should be creating the links automatically. But I am not so sure, regarding the API: sometimes it is expected that you create the links yourself, to keep API operations atomic and giving more freedom for developers to put the data into the database in whatever form they prefer.

I hope this helps…

2 Likes