2

I have created three fields in a table: Country, State and City. I have given EDT as LogisticsAddressCountryRegionId for Country, LogisticsAddressStateId for State. The relations are MyTable.Country = LogisticsAddressCountry.CountryRegionId.

I am getting a best practise error as Only foreign key constraints are allowed on this table.

I tried few options like Setting New roles in relations. Doesnt work.

jub0bs
  • 60,866
  • 25
  • 183
  • 186
Raas
  • 261
  • 8
  • 25

2 Answers2

3

The error message is probably misleading. It will be linked to the extended data type with table relation.

Try following

1) Check if the EDT you are using is in the 2012 style or 2009 style (2009 has relations). If it is in the old style try to use new style datatype with table reference instead of relation.

2) Add relation to the table level.

3) "Table1 -> Field1,Field12

Index1 -> Field1 having AlternateKey set to "Yes".

Table1 -> properties->PrimaryIndex set to "Index1".

Table2 -> create a foreign key relation (Foreign key -> Primary key based)

then automatically a reation Table2.Table1 ==Table1.Field1 is created.

In this way you can easily create a relation on any field other than RecId. and also the BP error Only foreign key constraints are allowed on this table will remove."

http://dynamicsuser.net/forums/p/54753/288954.aspx

4) Try to export table into .XPO. Then change EnforceFKRelation property in file and import .XPO back to axapta.

https://erpcoder.wordpress.com/2014/08/04/get-rid-of-bp839-only-foreign-key-constraints-are-allowed-on-this-table/

5) You can find more about this error here http://microsoft-dynamics-ax-erp.blogspot.cz/2012/12/debug-bp-errors-in-dynamics-ax-2012.html

"For those of you who were wondering the reason for this BP error, it is because we should create a new Foreign key based relation instead of a normal relation.

Simply put, when you drag and drop the ItemID EDT on the table, it will ask for your confirmation to add the relation on the EDT.

Press Yes and a Foreign key based relation will be created. If you press No and want to create the relation manually, make sure you create a Foreign key based relation and not a normal relation.

Both the normal and foreign key relation looks the same and it is visually difficult to differnetiate between them. So, if you ever encounter the above mentioned BP and have a relation defined, delete and recreate a new foreign key relation."

boucekv
  • 1,220
  • 2
  • 20
  • 47
  • 1
    "Both the normal and foreign key relation looks the same and it is visually difficult to differnetiate between them." > I always wondered about this sentence, because I never found a way to differentiate between the two relations. But today I noticed that relation fields of a normal relation can be deleted (the context menu on right-click shows a "Delete" Option), while a foreign key relation does not allow deletes on relation fields, you can only delete the whole relation. – FH-Inway Mar 20 '17 at 09:53
1

You have to delete existing relation and add new relation to the same table. Then add relation fields and select New -> ForeignKey -> PrimaryKey based. AX will create all three fields.

Matej
  • 7,517
  • 2
  • 36
  • 45
  • I am not saving recid in these fields so i cannot try that option as that will create a field which will save recid. – Raas Mar 02 '15 at 17:41