Alice - Thursday, November 12, 2015 11:13 AM:
How can I write the code for AML Search from the 4 search options if I want to use it to compare 2 date properties, instead of manually entering a criteria?
For example,
<due_date condition="lt">2015-11-12T00:00:00</due_date>
I would like to substitute 2015-11-12T00:00:00 with the property close_date.
tstickel - Friday, November 13, 2015 10:18 AM:
Alice
To compare the value of 2 properties you set the "where" attribute in the AML <Item> element instead of using a property element with a condition. For example, if your ItemType is MyItem then:
<Item type="MyItem" action="get" where="myitem.due_date < myitem.close_date"/>
Note that the "where" attribute contains a SQL comparison clause, so you need to preface the property name with the appropriate SQL table name. That is why the where clause contains myitem.due_date and myitem.close_date,
Alice - Friday, November 13, 2015 10:51 AM:
Sorry to be brutal but total rookie here... I have a feeling I am doing this completely wrong because in the AML search, it already defaults to start with <item type-"action item" ... so I don't think I have to enter that part again. This is throwing me a Bad xml: XML Parsing Error - can you advise what exactly I need to type into the blue box?
tstickel - Friday, November 13, 2015 2:49 PM:
Alice
Sorry, I did not realize that you were trying to use the AML Search from the Innovator Main Grid. I thought that you wanted to write an Innovator method to submit some AML. I don't know how to do what you are trying via the Main Grid AML Search. As you pointed out, it only gives you the opportunity to use property criteria that consists of constants, not other properties.
FYI, below is the AML item search that will do the correct search
<Item type="Action Item" action="get" where="action_item.jav_due_date < action_item.jav_clos_due_date"/>
There are 2 things to notice here:
1. When your ItemType name is multiple words separated by spaces, then the table name in SQL will have the words in lower case and separated by underscore. So that is why in the where clause you will preface the fields names with action_item.
2. In the where clause if you are doing a less than comparison, you can not use a less than symbol because that confuses the XML parser. So you have to use the escape sequence < instead. (Sorry, I should have shown that in my first response to your question). FYI, > is the escape sequence for the greater than symbol. The equal symbol does not need to be escaped.
Perhaps I could help more if you told me what you were going to do with the search results. We could have the results show up in a report instead of the search grid if that will meet your needs.
Alice - Friday, November 13, 2015 3:51 PM:
Thank you for your response it is very comprehensive even for a beginner like myself. As for your suggestion, generating a report would definitely be the next best thing. Basically I need to somehow show that on some action items, if the forecast completion date is not going to meet the due date then there needs to be some kind of an indicator, that's why I was exploring AML search because advance search didn't meet the needs.
If you can give pointers on how to generate a report to show that, that would be greatly appreciated!
PeterSchroer - Friday, November 13, 2015 4:01 PM:
You can also consider changing the color of the row or at least the Due Date cell background to RED when the forecasted date is too late. this is a fairly typical OnAfterGet Server method logic. Either compare the dates and highlight late Activities, or perhaps more sophisticated, have a Priority (1,2,3) assigned to Activities, and in the OnAfterGet, set the Priority=1 as an override whenever the forecast date is beyond the due date.
The Aras standard Project Management solution changes the colors of cell based on the comparison of Property Values, you find examples of how to do this by looking at the Methods associated with that solution.
Alice - Friday, November 13, 2015 5:25 PM:
That is an impressive feature I did not know about, and will keep in mind for future reference; however, I am worried that might open a can of worms and the Aras Main Grid will end up looking like a rainbow (project managers love to color code).
I happen to be trying to figure out how to build reports at the moment so any tips on how to configure that to compare the two properties would be amazing!
tstickel - Sunday, November 15, 2015 12:15 PM:
Alice;
There are 2 techniques for building reports in Innovator.
The most feature-rich way is to use the Innovator integration with SQL Server Reporting Services (SSRS). There are 3 documents on the Aras Downloads & Support Documentation web page that describe how to develop reports using SSRS. If you are already familiar with SSRS then I would recommend using it. You will likely need to become familiar with SSRS if you end up needing to define a lot of complex reports in Innovator. But, for a very simple report it might be easiest to start with the second report building technique.
The second technique for building reports is to use the Innovator XSLT Report Tool. There is a document on the Aras Downloads & Support Documentation web page that describes how to use the XSLT Report Tool. The Innovator Solutions database has a simple report already set up called "Approved Vendors Report", which would be a good model for a simple report. If you have any Vendors setup in your Innovator Solutions database (see Sourcing->Vendors), you can run this report to see what it looks like. The report definition for "Approved Vendors Report" is found under Administration->Reports. This is a good model for you because it is an Itemtype report that runs on the Server and has a "One Window" target, which is what you will need. The report definition includes a Report Query, which in your case would use the example query I provided in an earlier response (but you will have to add a "select" attribute to the query to specify which properties you want to appear on the report). You will probably find that the stylesheet definition for your report will be the most challenge, if you are not already familiar with XSLT. But the "Approved Vendors Report" stylesheet definition is a good model that you could use as an example for your report stylesheet.
I suggest that you give this a try and post more questions if you need specific help.
Alice - Monday, November 16, 2015 11:25 AM:
That's exactly what I am looking for - thank you for pointing me to the documentation location was having trouble finding it, will become very useful in exploring the XSLT Report Tool!
