Integrate external data into Great Plains fields

[Flynno]

I do not know if this is possible and any direction/help would be great.

I am doing and internship at a small company and I am trying to find a way to limit typographical errors that seem to be quite common and also reduce redundant data entry. As of now when a sales person lands an account tickets must be created in the service department to actually create the work ticket for the job.

To accomplish this now in the current system an excel file called "request for service" is generated from the sales department containing various information(sales person, customer, install location, billing address, a list of required parts with part numbers, and so on).

This excel file is given to the service department and then they are required to generate a work order/service ticket. However 80% of this work order is comprised of the same information already sitting in the external excel file "request for service." Therefore I feel the reentry of this data is redundant and a huge time sink creating instances and possibilities of errors in data entry.

I am trying to research methods to integrate this already existing information into the work ticket fields. However I am uncertain if there is a way to gather this data from the file with VBA and then fill various fields in the work ticket with it.

I was also looking into replacing the excel file altogether using some time of online/intranet form(maybe infopath) to link to the application but it seems quite difficult and involves applying the form data directly to the database which I would rather avoid doing to prevent having any incorrect data applied directly into the database. What I would rather is to have fields of the ticket auto populate using information from the excel file or infopath form allowing the service department to update any other required fields not in the "request for service" such as ticket number, order number(for the parts) etc. and continue to finish creating the ticket, printing and updating the database with the transaction as normal.

Is it possible to create a button or something using VBA or another method to grab this data and populate the fields. If not does anyone know where I can find out how to do this another way?

Thanks.

 

[Gammachaser]

Well, the first question is 'What application generates the Work Order / Service Ticket?'

 

[zelgar]

Consider using Mail Merge into a Word Document.

 

[Flynno]

It is Microsoft Great Plains (More Specifically it is Wennsoft addon for Microsoft Great Plains, it integrates seamlessly and functions from within Great Plains 2010. Sorry, forgot to mention that.

 

[zelgar]

So the procedures are currently:
1. Using Microsoft Great Plains 2010 an Excel File is created for a "request for service"
2. This "Request for Service" Excel file is sent to the service department who takes the data from the file and re-enters the data to generate a work order.

What are program is generating the final work ticket? If it is Word, you can easily make a form for Mail Merge that would take the information from the Excel and automatically put it in the Word Document.

Idealy, you should be able to go straight from your Great Plains software to printing your work orders. If it can generate Excel files, why can't you directly export the data to the work tickets?

 

[Flynno]

No the original "request for service is done in excel" then from that excel file (printed document) the service department has to sit and recopy all of the information in the accounting program(great plains/Wennsoft). This is the combo that creates the work ticket. The excel file is created in excel itself and is used by the sales people to just create a formatted document containing information required to make the service ticket (in GP/Wennsoft).

Microsoft Great Plains has its own built in VBA editor and macros/scripts can be created to control windows and data within it.

 

[Flynno]

The ticket must be created in Great Plains as we are a telecommunications company and when the fields in this program are filled and then processed our customer database/ transaction databases are updated, everything is done through great plains except for that original excel file created in the sales department.

It keeps records of all of our tickets, accounts, customers, bills, outstanding fees owed, ect.

 

[SkipVought]

How is the Excel workbook structured?

Is it network accessible to the people using GP?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Flynno]

No but it easily can be relocated to a network share that everyone can access. It is formatted with just a title and then a table containing everything except the inventory info. That is in another table below the first. However it still is set up with the standard Letter column - number rows (ie: G12, H9, etc).

I realize it needs to be network accessed but I am just wondering how to use a VBA button or something else to push the data in the cells into specific fields within the Service Ticket window in GP/Wennsoft.

 

[Flynno]

I feel that this should be possible, but I really don't know where to start. If using VBA is not the preferred method to do this integration is there an easier way maybe?

I have written basic scripts to fill fields with pre poulated text under the condition that the ticket is for a specific customer but never with grabbing external data. I know that each request for service would be a different customer and the file is already made and named so it is not practical to have the script changed for each new request for service.

 

[SkipVought]

The you ought to be able to query that workbook, using ActiveX Data Objects to grab the appropriate row of data, base on a criteria.

I've build stuff in Excel that goes out into a folder structure on the network and lists all files & path. From there you can parse the filename to get the related customer. Then its just a matter of using ActiveX Data Objects to query the appropriate workbook.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]