Impex many to many relationship

[Former Member]

Hello all , I am trying to implement a many-to-many relation ship as i have a table "plants" and a table "showrooms" has m:n relation , i started with making item types for each one

   **<itemtype generate="true" code="Showrooms" autocreate="true">
                        <deployment table="Showrooms" typecode="30275" />           
     <attributes>
         <attribute qualifier="showRoomCode" type="java.lang.String">
             <description>Show room code or sales office</description>
             <persistence type="property" />
         </attribute>
      </attributes>
  </itemtype>**
  
  **<itemtype generate="true" code="Plants" autocreate="true">
                        <deployment table="Plants" typecode="30276" />
                
     <attributes>
         <attribute qualifier="plantCode" type="java.lang.String">
             <description>this is the plant</description>
             <persistence type="property" />
         </attribute>
       
      </attributes>
  </itemtype>**
  

and i implemented a many-many relation

         <deployment table="Showrooms2Plants" typecode="30277"/>
         <sourceElement qualifier="showroom" type="Showrooms" cardinality="many" ordered="false">
             <description>Showrooms</description>
             <modifiers read="true" write="true" search="true" optional="true"/>
         </sourceElement>
         <targetElement qualifier="plant" type="Plants" cardinality="many" collectiontype="list" ordered="true">
             <description>this is the plants</description>
             <modifiers read="true" write="true" search="true" optional="true"/>
         </targetElement>       
     </relation>

my aim is to query in the data given a showroom returns a list of plants ,, but when i insert data into Plant and Showroom tables it doesn't insert any data in the link item table (source and target) ,, Please help what iam missing here.

[arvind-kumar_avinash]

- unfortunately, the answer provided by Bijesh is wrong but you have already accepted it. This will confuse someone who is looking for the correct answer. Please accept the answer only when you have tried the provided solution and you have found it to be working. Probably, you can still unaccept it and this will motivate Bijesh to correct his answer and moreover, by finding its status as unanswered, other experts will also try to look into your problem.

[Former Member]

well said, done i have made the necessary changes.

[arvind-kumar_avinash · Accepted solution]

Unfortunately, the answer provided by Bijesh is wrong.

You can do the following which I have already tested to be working.

Your items.xml:

 <relations>
     <relation code="Showrooms2Plants" autocreate="true" generate="true" localized="false">
         <deployment table="Showrooms2Plants" typecode="30277"/>
          <sourceElement qualifier="showrooms" type="Showroom" cardinality="many" ordered="false">
              <modifiers read="true" write="true" search="true" optional="true"/>
          </sourceElement>
          <targetElement qualifier="plants" type="Plant" cardinality="many" collectiontype="list" ordered="true">
              <modifiers read="true" write="true" search="true" optional="true"/>
          </targetElement>       
      </relation>
 </relations>
 
 <itemtypes>
     <itemtype generate="true" code="Showroom" autocreate="true">
         <deployment table="Showrooms" typecode="30275" />           
         <attributes>
             <attribute qualifier="showroomCode" type="java.lang.String">
                 <persistence type="property" />
             </attribute>
         </attributes>
     </itemtype>
       
     <itemtype generate="true" code="Plant" autocreate="true">
         <deployment table="Plants" typecode="30276" />                        
         <attributes>
             <attribute qualifier="plantCode" type="java.lang.String">
                 <persistence type="property" />
             </attribute>               
         </attributes>
     </itemtype>
 </itemtypes>

The ImpEx:

 INSERT_UPDATE Plant;plantCode[unique=true]
 ;p1
 ;p2
 
 INSERT_UPDATE Showroom;showroomCode[unique=true]
 ;s1
 ;s2
 
 UPDATE Showroom;showroomCode[unique=true];plants(plantCode)
 ;s1;p1,p2

After executing the above ImpEx, you can check the following flexible search query:

 SELECT * FROM {Showrooms2Plants}

[Former Member · Accepted solution]

Thanks Arvind , i tried this and it worked.

[arvind-kumar_avinash · Accepted solution]

You are most welcome.

[Former Member]

Can you paste your impex here by which you are trying to insert data ? Generally the impex should look like below : Insert_Update Showroom, showRoomCode(code)[unique=true];plant(code)(&componentRef); ;show1;plant1; Insert_Update Plants;plantCode(code)[unique=true];showrooms(code)(&componentRef); ;plant1;show1;

[Former Member]

My impex is INSERT_UPDATE showrooms; showRoomCode[unique=true];plant(&componentRef); ;showroom1;plant1 INSERT_UPDATE plants;plantCode[unique=true];showroom(&componentRef); ;plant1;showroom1;

but i have problems after the insertion

INSERT_UPDATE showrooms;showRoomCode[unique=true];plant(&componentRef) ,8796256990787,,,column 2: cannot resolve value 'plant1' for attribute 'plant';showroom1;plant1 INSERT_UPDATE plants;plantCode[unique=true];showroom(&componentRef) ,8796158654020,,,column 2: cannot resolve value '1220' for attribute 'showroom';plant1;showroom1;

[Former Member]

The issue here is that you need to define &componentRef in your impex header if you want to reference it later. Please consult the "Document ID" section of the documentation.

In your case you should have something like below:

 INSERT_UPDATE showrooms;showRoomCode[unique=true];plant(&plantRef);&showroomRef
 ;showroom1;p1;s1
 
 INSERT_UPDATE plants;plantCode[unique=true];showroom(&showroomRef);&plantRef
 ;plant1;s1;p1;