EDI Exporter for InStar

EDI Exporter Help

EDI has added into our Enhancements a custom reporting window that will export data to a csv (Comma Separated Values) format allowing the user to open the information in Excel and Save As to an Excel format. This is to address two shortcomings we cannot remedy at this time within InStar. The Gross Profit Analysis is not correctly handling the dates and the Birthday List is just not returning the correct data from the drivers lists. Once the data is in Excel you may manipulate the data as needed.  Simply select the query from the drop down list, set your dates and click run.  You will be prompt to open the file or the folder.  Opening the file should open in Excel.  If not contact support and we can set the file type to open in Excel. support@ediconcepts.net.

InStar Birthday List
The birthday list query will secure a list of all birth dates from the personal driver records on file.  The system will also consider history records eliminating duplicates based upon the drivers license number.  Phone numbers are secured from the client record as well as the contacts on file.  The system will also export up to two e-mail addresses from the contacts.  since contacts are not directly tied to the drivers, the only thing on which the system can match is the first name of the driver to the first name of the contact for each customer number.  Matches will not be made if the first name of the contact differs from the first name on the driving record.

To produce a birth date list perform the following steps;

• Make sure the Personal Driver Birthdays query is selected.
• Set the beginning and end date to filter birth dates for consideration.  The beginning date must be the earliest date. (If there is a date already present, one must clear the field before typing a new date.  Dates need not be punctuated, for instance 8/30/1936 can be typed 08301936. Note the century must be used.)
• Now, simply click the Run button and wait while the program queries the InStar data to produce the birthday list requested.
• The system will prompt the user to open the folder or the file. Opening the file on most computers will be done in Excel. If your system does not open the file in Excel you need to associate the file type (CSV) with Excel or contact support. This is actually a comma delimited file.  If you wish to save in another format, Excel format for example, simply use the “Save As” option within Excel.
• Once you have your list in Excel, you may further manipulate the data to meet your needs.

Gross Profit Analysis

• Make sure the GrossProfitAnalysis.txt query is selected.
• Select a date range and the system will find the data and export to a csv file that will open in Excel and allow the user to further manipulate this information.
• Now, simply click the Run button and wait while the program lines the InStar data to produce the Gross Profit Analysis.
• The system will open the resulting list in an Excel spreadsheet.  This is actually a comma delimited file.  If you wish to save in another format, Excel for example, simply use the “Save As” option within Excel.
• Once you have your list in Excel, you may further manipulate the data to meet your needs.

Please note: Adjustments to accounting after the report has been done, may change the original report.  For instance, if the user voids an invoice after a report has been exported, this may change the report for the time period in which the invoice was voided.  For this reason, one may wish to export an updated report, after sufficient time has passed to account for any adjustments, for a true picture of the time frame in question.  Just food for thought.

Basic Information for Adding SQL Queries (This section is only to show what is possible and usually requires custom work.)

• Simple queries on one table are easiest to manage. 
• Wish to know what is in a table, Select * from f_Clients, Select * from fPolicies, etc.  See below for common tables.  If you want to know where to find data email support@ediconcepts.net  Screen shots are best to show us from where you wish to pull the data.
• If you need information from multiple tables you must use an equi-join (fPolicies.p_id = f_PADrivers.dv_id, join on the policy ID).  OmnisSQL does not have any transaction management so you must construct you joins with care.  General rule for efficiency is to keep the thing you have on the right side of the join.
• If you must use a left or right join that is *= or =*.  This is not very efficient in Omnis.
• You can write multiple queries in the EDI Exporter as long as you realize the first query will define the columns. Separate your queries with a semi-colon ;
• You can add remarks using /* this is a remark */
• If you need to use a date you must enter the dates at the top of the window and then use bind variables in the format @[iBegDate] and @[iEndDate]
• Case in queries is irrelevant.  I only type in mixed case as I find it easier to read.
• Very complex queries require custom coding.  Contact support@ediconcepts.net for more information.

Example of 2 queries that will produce a single set of data for both individual and group life policies. Note both use the same number and order for the columns so the data will not be mixed up.  This will produce a single spread sheet with the columns defined from the first query.

Select p_code,p_name, lh_PolicyNum, lh_ssNum, lh_TaxID
From fpolicies, f_Life
Where lh_id = p_id And p_effDate >= @[iBegDate] And p_ExDate <= @[iEndDate];

Select p_code, p_name, lhg_PolicyNum, lhg_ssNum, lhg_TaxID
From fpolicies, f_lhgrp
Where lhg_id = p_id And p_effDate >= @[iBegDate] And p_ExDate <= @[iEndDate];

Common Data Tables

• F_Setup (general agency information, close to 400 columns)
• F_Clients (General Client Information)
• Contacts (fcontact, fContactLink, fContactAddress, fContactPhone, fContactWeb, fContact.con_RecordID = fContactLink.Conl_ConId)
• fPolicies (Common policy information for any policy)
• F_PAdrivers (Personal Drivers)