Invalid Relation between entities

(0) Share

Report

Posted on by D365FO user

465

I'm getting this error when exporting the composite entity: "Invalid relation between entities"

The relation between Entity2 and Entity1 is:
Entity1.Id = Entity2.Id

Entity1.JournalParmId= Entity2.JournalParmId

Entity1.LinkedJournalId = Entity2.JournalId

Entity1 EntityKey: JournalId

Entity2 EntityKey: JournalId

LinkedJouranId and LinkedJournalType are computed columns:

public static server str LinkedJournalId()
{

        return  strFmt('ISNULL((select top 1 JournalId from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.JournalParmId = %3 AND table1.JournalId != %4 AND table1.Id = %5 AND table1.JournalType != %6), %7)'
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Partition))
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, DataAreaId))
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalParmId))
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalId))
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Id))
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalType))  // this is new
            , SysComputedColumn::comparisonLiteral('')
             );
    
}


public static server str LinkedJournalType()
    {

        return  strFmt('select top 1 JournalType from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.JournalParmId = %3 AND table1.JournalId != %4 AND table1.RentId = %5 AND table1.JournalType != %6'
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Partition))
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, DataAreaId))
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalParmId))
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalId))
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Id))
            , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalType))
             );
}

*I've noticed that if JournalParmId in Entity 1 is not repeated then the export doesn't fail like example below

-- however, if more than JournalParmId in entity1, then it fails

I have the same question (0)

All responses (13)

Answers (0)

Martin Dráb 236,285 Most Valuable Professional on at

Like (0)

Report

RE: Invalid Relation between entities

The design of your entity is weird. Why exactly do you want to use a computed column as an entity key? You use some real fields to calculate the value - shouldn't they form the entity key?

What is the purose of such an entity?

A part of your relation is Entity1.JournalId = Entity2.LinkedJournalId, but your pictures shows that LinkedJournalId is in Entity1, not Entity2. Which information is correct?

It's strange that you claim JournalId to be unique, but then you add two more fields to the relation. A unique identification can't be made more unique. And if it makes a difference, it means that you entity key must be wrong.

Let's ignore code of LinkedJournalType() - it has nothing to do with the relation.

Was this reply helpful? Yes No
D365FO user 465 on at

Like (0)

Report
RE: Invalid Relation between entities

Hi Martin,

Sorry Entity1.LinkedJournalId = Entity2.JournalId

At first The purpose of this entity is that Table1 can have the following Records
Id JournalId JournalParmId JournalType 1 J1 1A 10 1 J2 2B 15 1 J3 2B 0 1 J4 4C 15 1 J5 4C 15 1 J6 4C 15

And my composite entity consists of table1 joined with table1 as well. Where Parent node show all Journals while child node
should display all records that have the same JournalParmId as the parent but it's JournalId is not equal to JournalId of the parent.

The relation between Table1Entity and LinkedTable1Entity is:

Table1Entity.Id = LinkedTable1Entity.Id

Table1Entity.JournalParmId= LinkedTable1Entity.JournalParmId

Table1Entity.LinkedJournalId = LinkedTable1Entity.JournalId -- i showed you by code how i get the value of LinkedJournalId

EntityKeys:

Table1Entity: JounralId

LinkedTable1: JournalId

Expected XML was this (I know that J3 is linked to J2 and not the opposite, because Table1Entity is inner joined with a table that contains J3 ant not J2)

- - J1 1 1A 10 - J3 1 2B 0 J2 15 J2 1 2B 15

The way i used to get LinkedJournalId and LinkedJournalType, is by specifying this in computed column: same parmId and same ID BUT journalId of child and parent is not equal

And everything was working fine for 3 years.

Now a new case came, where we could have journals that have the same JournalParmId but they are not actually linked (as they have the same journal type), so i don't want to show them in the linked node of the xml. Just the parent node.
Id JournalId JournalParmId JournalType 1 J1 1A 10 1 J2 2B 15 1 J3 2B 0 1 J4 4C 15 //new 1 J5 4C 15 //new 1 J6 4C 15 //new

So Expected XML now is like this:
- - J1 1 1A 10 - J3 1 2B 0 J2 15 J2 1 2B 15 - J4 1 4C 15 - J5 1 4C 15 - J6 1 4C 15

Now i changed the way to get LinkedJournalId and LinkedJournalType, by specifying this in computed column: same parmId and same ID BUT journalId of child and parent is not equal (same as before) AND I added that journalTypeId of child and parent is not equal

(as i showed in the code specified in the question)

and now i'm getting invalid relation

Was this reply helpful? Yes No
Martin Dráb 236,285 Most Valuable Professional on at

Like (0)

Report

RE: Invalid Relation between entities

What is the primary key of the table? Are there any other unique keys? Do I understand correctly that JournalId isn't unique?

Why do you have Id field in the relation if it's not mentioned in your requirements ("the same JournalParmId as the parent but different JournalId").

Was this reply helpful? Yes No
D365FO user 465 on at

Like (0)

Report

RE: Invalid Relation between entities

Hi Martin,

No JournalId alone is unique -- and the primary key of the table is JournalId

ID is there just to be more sure -- ID is like the order number

so you can say as well: inner node needs to display all records that have the same ID and same JournalParmId as the parent but it's JournalId is not equal to JournalId of the parent.

goshoom but after the new case, inner node needs to display all records that have the same ID and same JournalParmId as the parent but it's JournalId is not equal to JournalId of the parent and it's JournalType is not equal to JournalType of the parent

Was this reply helpful? Yes No
Martin Dráb 236,285 Most Valuable Professional on at

Like (0)

Report

RE: Invalid Relation between entities

If Journal ID is unique, involving the other fields is useless, because they can't make it any more unique.

Also, you don't need to do anything to make sure that "its JournalId is not equal to JournalId of the parent". If Journal ID is unique, a record can't have the Journal ID same as any other journal, including the parent.

Was this reply helpful? Yes No
D365FO user 465 on at

Like (0)

Report
RE: Invalid Relation between entities

Hi Martin,

Yes i need to check if JournalId is not equal to the parent. because Table1Entity and LinkedTable1Entity have the same table, so the inner node related to LinkedTable1Entity, should not show the same JournalId in the parent. And in the new case it should not show the same JournalId in the parent AND it should not show same JournalType as the parent

So currently here's the case that is breaking the entity as I mentioned before

Table1Entity // note that here there is no J2, because always when 2 records are actually linked, only 1 appears that i detect by joining table1 with another table
Id JournalId JournalParmId JournalType LinkedJournalId LinkedJournalType 1 J1 1A 10 NULL 1 J3 2B 0 J2 15 1 J4 4C 15 NULL 1 J5 4C 15 NULL 1 J6 4C 15 NULL

LinkedTable1Entity // contains table1 not joined with anything so J2 appears here, but because it's not in Parent entity I make sure it will only appear in inner node and not parent node.
Id JournalId JournalParmId JournalType 1 J1 1A 10 1 J2 2B 15 1 J3 2B 0 1 J4 4C 15 1 J5 4C 15 1 J6 4C 15

now when joining these two entities -- relation based on ID, JournalParmId and LinkedTable1Entity.JournalId = Table1Entity.LinkedJournalId

When I'm standing at J1, Linked node should show nothing

When I'm standing at J3, Linked node should show J2

When I'm standing at J4, Linked node should show nothing

When I'm standing at J5, Linked node should show nothing

When I'm standing at J6, Linked node should show nothing

** my previous comment that contains the xml shows all the details as well

goshoom when I had J1,J2 and J3 in table1, the entity didn't break, because when J2 and J3 where linked, EntityTable1 didn't contain both J2 and J3, just J3.

But now since there is J4.J5,J6 and they both appear in both entities by sharing the same journalParmId, but they are not actually linked,it fails.
So somehow it's failing when the JournalParmId is repeated in Parent Entity. but why??

Was this reply helpful? Yes No
Martin Dráb 236,285 Most Valuable Professional on at

Like (0)

Report

RE: Invalid Relation between entities

If Journal ID in the table is unique, two record (a parent and a child) clearly can't have the same Journal ID, because that wouldn't be unique.

Was this reply helpful? Yes No
D365FO user 465 on at

Like (0)

Report

RE: Invalid Relation between entities

Hey Martin,

But Parent and Child are exactly the same table which is table1

So table1 in general has J1,J2,J3,J4,J5,J6

-- J2 and J3 are linked and share the same JournalParmId

-- J4,J5,J6 are not linked from a business point of view but they still share the same journalParmId

So Parent Entity (which is table1 joined with another table) has J1,J3,J4,J5,J6 -- no J2

And I designed child entity in a way to show linked Journals from table1

so that the xml shows

J1 in Parent linked with nothing from child

J3 in Parent linked with j2 from child

J4 in Parent linked with nothing from child

J5 in Parent linked with nothing from child

J6 in Parent linked with nothing from child

So when i say child entity shouldn't show journals from ParentEntity.. is because child node should only show J2 in this case

that's why i made this link childEntity.JournalId = ParentEntity.LinkedJournalId

and they way i detected linkedJournalId in ParentEntity, is by looking at a journal who has the same JournalParmId but different JournalId -- So again J2 and J3 has same JournalParmId but because Parent has J2 i only want to get J3

Was this reply helpful? Yes No
Martin Dráb 236,285 Most Valuable Professional on at

Like (0)

Report

RE: Invalid Relation between entities

Yes, I know it's the table table - I'm talking about records in the table. When you say that JournalId in Table1 is unique and you have two records - a parent and a child - they must have two different Journal ID. If they had the same, JournalId wouldn't be unique.

Was this reply helpful? Yes No
D365FO user 465 on at

Like (0)

Report
RE: Invalid Relation between entities

but those are different entities that are joined together in a composite entity. So if Entity1 (parent) which consists of table1 had J1, then Entity2 which consists of table1 as well can have J1 as well, as the entity key is JournalId for each one.

So table1 can have J1 and J1 can't be repeated true. But Entity1(Parent) can have J1 and Entity2(Child) can have J1

But anyways what I'm trying to do is Make Entity1 contain Journals that shouldn't appear in Entity2. That's why i made the link between them based on Entity1.LinkedJournalId = Entity2.JournalId

So i'm assuming if I join the two entities here based on ID JounralParmId and LinkedJournalId

The result should be like this -- when joining the two entities based on the relation i specified -- but i'm not sure why the entity is failing

Id JournalId JournalParmId JournalType LinkedJournalId LinkedJournalType Id JournalId JournalParmId JournalType 1 J1 1A 10 NULL NULL NULL NULL NULL 1 J3 2B 0 J2 15 1 J2 2B 15 1 J4 4C 15 NULL NULL NULL NULL NULL 1 J5 4C 15 NULL NULL NULL NULL NULL 1 J6 4C 15 NULL NULL NULL NULL NULL

Was this reply helpful? Yes No