Jeroen Bosch - Wednesday, January 28, 2015 9:00 AM:
Hi all,
Here is my problem:
I have two item types IT1 and IT2 and a relationship R1 between them with an integer attribute AR1 in the R1 item type.
I would like to retrieve the IT2 items which are in relationship with a given IT1 item but in an order defined by AR1.
Here is my AML query:
<AML><Item type='IT1' action='get' where="IT1.id in (select related_id from R1 where R1.source_id = 'A5C0EE7EFAF24392A86CFE178341EC86' order by R1.AR1 asc)"></Item></AML>
I got the following error message:
"The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified."
Any idea how to fix my query?
Thanks
Jeroen
zahar - Wednesday, January 28, 2015 9:35 AM:
You change the AML to retrieve the relationship with T1 as related
<AML>
<Item type='R1' action="get" select="related_id(*)" orderBy="AR1" />
</AML>
And then in your code get from the Result only items with type IT1
Item onlyT1 = result.getItemsByXPath("//Item[@type='IT1']");
Jeroen Bosch - Thursday, January 29, 2015 5:11 AM:
Very clever workaround Zahar.
Thanks
Jeroen
Jeroen Bosch - Thursday, January 29, 2015 8:48 AM:
You change the AML to retrieve the relationship with T1 as related
<AML>
<Item type='R1' action="get" select="related_id(*)" orderBy="AR1" />
</AML>
And then in your code get from the Result only items with type IT1
Item onlyT1 = result.getItemsByXPath("//Item[@type='IT1']");
Hi Zahar,
I am struggling with one issue because the item list returned by getItemsByXPath contains not only the I1 items but also the R1 item as a parent item.
I have tested the xml generated after applying the AML along with the xpath "//Item[@type='IT1']" with an online xpath tester tool and everything is fine.
How can I retrieve only the I1 items with Innovator?
Thanks,
Jeroen
zahar - Thursday, January 29, 2015 9:07 AM:
Aras API is confusing at the beginning.
- The DOM object of the Item contains the full XML.
- The Item object itself can be a list of items or one item.
So what you can do is after:
Item onlyT1 = result.getItemsByXPath("//Item[@type='IT1']");
do the following:
if (onlyT1 != null && !onlyT1.isError() && onlyT1.getItemCount() > 0)
onlyT1 = onlyT1.getItemByIndex(0);
this will set onlyT1 with single Item and you will be able to access it's xml by:
onlyT1.node
Jeroen Bosch - Thursday, January 29, 2015 10:42 AM:
Zahrar,
once you get the node, how do you convert it to an item?
Thanks,
Jeroen
zahar - Thursday, January 29, 2015 11:13 AM:
I'm not sure why you need this?
I mean you already have an Item you don't need to export the xml node and import it again to other item. Just use the one you have.
Jeroen Bosch - Thursday, January 29, 2015 11:20 AM:
Zahrar,
once you get the node, how do you convert it to an item?
Thanks,
Jeroen
I have managed to solve my problem by retrieving the I1 item's id by accessing them iteratively and using getItemByIndex(j).getID().
Then, I retrieve the item using the item id via a standard AML query because I know the item type.
I am curious about other solutions and how to get the item from the node.
Thanks a lot
Jeroen
Jeroen Bosch - Thursday, January 29, 2015 11:22 AM:
I have tried to append the node to an item but I got an error about type mismatch, I believe.
Could you please show an example?
zahar - Thursday, January 29, 2015 11:56 AM:
You don't need second query. You already have the same data in getItemByIndex(0)
to convert AML to Item
Item newItem = this.getInnovator().applyAML("<AML><Item type='xxx' id='yyyy' /></AML>");
Jeroen Bosch - Thursday, January 29, 2015 12:17 PM:
You don't need second query. You already have the same data in getItemByIndex(0)
to convert AML to Item
Item newItem = this.getInnovator().applyAML("<AML><Item type='xxx' id='yyyy' /></AML>");
Actually, that exactly what I am doing but it is a second quay that you are making to the database.
I have already the item with all its data but "as a node". What is the syntax to "convert it" to an item?
Jeroen
zahar - Thursday, January 29, 2015 12:23 PM:
Sorry my mistake:
Item newItem = this.getInnovator().newItem("", "");
newItem.loadAML(<AML><Item type='xxx' id='yyyy' /></AML>)
But again you don't need to do that. Because you already has Item that point to IT item.
after you assign single item just use aras API to access the properties if IT item and you will see that you will get relevant properties and not parent or other items, althoug in DOM object you see full XML with all data
Jeroen Bosch - Thursday, January 29, 2015 12:35 PM:
Zahar,
you are helping a lot.
The forum is not the best place to communicate in a non ambiguous way.
When you say "after you assign single item just use aras API to access the properties if IT item and you will see that you will get relevant properties and not parent or other items, althoug in DOM object you see full XML with all data", I would be very happy to have a small example of code doing what you say.
Thanks
Jeroen
zahar - Tuesday, February 3, 2015 6:14 AM:
Sorry missed your last message.
The example is simple, i assume that you running this code from Aras internal server side Method and using C# :
Item itemToWorkWith = null;
string aml = "<Item type='RelationshipItemTypeName' action='get' select='related_id(*)' orderBy='order_by_att_name'></Item>";
Item qry = this.getInnovator().applyAML(aml);
if (qry != null && !qry.isError() && qry.getItemCount() > 0)
{
Item relatedItems = qry.getItemByXPath("//Item[@type='RelatedItemTypeName']");
if (relatedItems != null && !relatedItems.isError() && relatedItems.getItemCount() > 0)
{
Item itemToWorkWith = relatedItems.getItemByIndex(0);
}
}
if (itemToWorkWith != null && !itemToWorkWith.isError() && itemToWorkWith.getItemCount() > 0) {
string valueofsomeattr = itemToWorkWith.getProperty("some_att_name", "");
}
return this;
