I’m trying to extract information from the database using sql query. I want to calculate hours of labor in workorders which is completed but not closed.
This is what I’m starting with:
select ASERVICENUMBER, ASERVICERATEQUANTITY from dbo.AWORKORDER, dbo.AWORKORDERITEM, dbo.AWORKORDERITEMLABOR, dbo.ARATE, dbo.AWORKORDERSERVICE
where (dbo.AWORKORDERITEMLABOR.AWORKORDERITEMID=dbo.AWORKORDERITEM.AID
and dbo.AWORKORDERITEM.AWORKORDERID=dbo.AWORKORDER.AID)
and (ACLOSED = ‘0’ and ASERVICECOMPLETED = ‘1’)
I want to expand to select spesific type of orders, but first I have to be sure my starting point is correct. And my query is not correct… I’m not sure why. I really hope someone can give me a clue!
Ninni (3/4/2008)I’m trying to extract information from the database using sql query. I want to calculate hours of labor in workorders which is completed but not closed.
I want to expand to select spesific type of orders, but first I have to be sure my starting point is correct. And my query is not correct… I’m not sure why. I really hope someone can give me a clue!
Hi Ninni, we do not support or encourage people to work directly with the database under any circumstances.Over the years we have had too many incidents of people damaging their data and resulting in lost information and we get very nervous when we see people trying to work directly with the database in any manner.
Of course a simple read only query of information isn’t going to damage the data but it always seems to start with that then people feel it’s safe to do anything and they start modifying data andthings start to break in weird and unexpected ways that take a long time to fix if ever.
For that reason we’ve invested a lot of time and effort into making and providing at no cost a full developers API in addition to thebuilt in ability to makeall manner of custom reports within AyaNova itself so that end users have two powerful options that simplify all aspects of working with their AyaNova data in a safe, simple and consistent manner. For what you are trying to do the normal route is a custom report which can not only be printed but exported as data into other applications if necessary easily from within the report viewer built into AyaNova.
In terms of your query, what you are attempting to do goes right to the most complex heart ofthe work order schema structure. Just at a glance I can see you are mising several joins for the related tables that you are trying to get information fromand also a lot more items in your WHERE criteria to (for example) filter out quotes, Preventive maintenance etc.
It’s not impossible to build a query like that by any means but it is very complex and you need to know a quite significant amount about the internal structure of the work order and all the potential side effects of the wrong joins and criteria affecting the accuracy of the data you retrieve from it. As long as you are simply querying and not making any updates then it’s safe enough, but there are easier ways to accomplish what you want to do either via a custom report in AyaNova itself or an application that uses the developers API.
Perhaps if you can tell me the overall goal and what you are intending to accomplish I can better help you with an alternative method.
My goal is to retrieve data, not to modify. I want to sum up the amount of labour from different kind of work orders. Ex. all work orders which are completed but not closed. And then sort out work orders based on criterias like type of rate, category, user anddate.
From what I’ve seen of the custom reports, I can’t get all the information I need. I am however not familiar with the tool, there might be options there thatI’ve missed.
I will check out your developers API. It migth be valuable to us. We are also interested in exporting from Ayanova to another application, to simplify the prosedures with invoice.
Bianca (3/10/2008)My goal is to retrieve data, not to modify. I want to sum up the amount of labour from different kind of work orders. Ex. all work orders which are completed but not closed. And then sort out work orders based on criterias like type of rate, category, user anddate.
From what I’ve seen of the custom reports, I can’t get all the information I need. I am however not familiar with the tool, there might be options there thatI’ve missed.
I will check out your developers API. It migth be valuable to us. We are also interested in exporting from Ayanova to another application, to simplify the prosedures with invoice.
Hi Bianca (you changed your name? I looked at my last reply and thought “where did I get Ninni from?” then saw that was the original name. ),
You should check with regular support about the report you want because I’m fairly certain you can get that information as you want it, what you describe is fairly common although everyone has their own format they want to use, note that the reports also support full scripting in C# behind them making for some very complex reports if necessaryand the big advantage is that once a report is created how you like it in AyaNova then anyone can use it any time from within the program without having to know anything complex to do it.
Also you can very easily export from a report to (for example) Excel or Accessfor further analysis so as long as the information you need is on the report it can be easily exported out to another program without resorting to a complex query, just set up your conditions within the grid filters in AyaNova itself and print.
I don’t support reporting here in the developers API forum, but regular tech support will be happy to help you out with that, they’re the experts on that side of it. Drop them a line explaining what you want to do in detail or post it in the dedicated reporting forum here -> http://forum.ayanova.com/Forum100-1.aspx
If you are intending on exporting from AyaNova for invoicing the developers API is ideal, it’s what was used to build the QuickBooks and PeachTree (as well as AyaNova itself) add on programs which involve invoicing work orders, synching parts, customers, vendors etc so if you are integrating with another application you won’t find an easier way to do it.
For the benefit of other AyaNova users reviewing this post, another member of the company contacted support directly regarding the above but instead of via SQL query, how to get the data to a CSV file from a report, and I wanted to post here a link to the AyaNova Support Forum topic so that others can also refer to it as needed
),