FAS Asset Accounting provides a very easy way to extract data
from a database and put it into a spreadsheet format for additional analysis and use in other
systems. This function is outside of reports, but can be used to create spreadsheets that report
on almost every function within FAS.
The Custom Export feature, which uses the Export Helper wizard, allows you to select exactly what fields
you wish to export, determine the order in which these fields are presented, and save the data in an
ASCII Comma Separated Value (CSV) file format. Finally, in addition to your export, you also create an
export map file that can be modified and used over again as often as you need.
To start the Custom Export, select File/Company Utilities/Export/Custom Export from the top menu bar. This
displays the Export Helper Introduction dialog box:
Then select the "Next" button to display the Select Assets dialog box.
From here, select the Company and the Group that you want to export. To get to the
desired Company, you first need to select the database in which it resides. Remember that the Group
determines what assets are to be exported (the group's group criteria), and the order in which they will
be exported (the group's sort criteria). These are established through Group Manager, before you
perform. Once these selections are made, push the "Next" button to display the Select
Map dialog box.
If you already have a saved Field Map, you can select it here by clicking
the "Use Saved Field Map" option button. Then browse to the field map file, which has an exp
extension. If you do not have a previously saved field map, click the "Create New Field Map" option
button, which is selected by default.
The lower half of the box asks you to select the book that you want to export. Please note that
with the Custom Export, you can export only one book at a time. If you need data from more than one
book, you will need to do multiple exports and then cut and paste the resultant spreadsheets in Excel
once you are finished. The "Include FAS Asset Inventory" check box allows you to select
FAS Asset Inventory fields for export. You will only use this if you use FAS Asset Inventory.
Once these selections are made, push the "Next" button to display the Field map dialog box.
This dialog box is very similar to the Import Map dialog box that is used in the
Import Helper. Under Available Fields, highlight the field you want to select, then push the "Add"
button. The field name disappears from the Available Fields list, and appears in the Export Field Map
list. Each field in the list is preceded by a letter, which represents the spreadsheet column that the
field data will be exported to.
Once a field is added to the Export Field Map list, you can change the order in which the data will be
exported by highlighting the field and pushing the buttons marked "Up" and "Down". These buttons are
available when you select the field you want to move. You can remove a selected field by highlighting
it in the Export Field Map List and pushing the "Remove" button. When you select any action that affects
the order of the data, the program automatically reorders the column assignments for you, so your
spreadsheet will have no blank columns.
If you do not select System Number as one of your fields, you will get this warning message:
It is not mandatory that you include System Numbers in your export. However, if you
are working with multiple exports, such as with multiple books, including this field is a good way to
verify that you have all the lines connected correctly in Excel.
Finally, note the two check boxes that are under the Available Fields list. Selecting the "Include
company data?" check box includes a header in the export file that names the company, book and group that
have been selected for this export. Selecting the "Include column header?" check box puts a header
line in your export file with the field names.
After you have selected all of the fields you want to export, and you have them in the desired order,
you can save the mapping by pushing the "Save Map" button. Once you push this button, you will need
to select a field map name, which will have an "exp" extension. This will be saved automatically in
the "Program Files\Best Software\FAS Asset Accounting" folder. It is important to save your
field map. If you are going to export multiple books you will need to export the same fields for
each book. If you have a saved field map, you can invoke it in the Select Map dialog box, and edit
it in the New Field Map dialog box. Once you have saved your field map, push the "Next" button to go
to the File Destination dialog box.
The File Destination dialog box is the last data that you need to enter into the Export Helper. First,
you need to provide a file name. Note that the default file name is FASExport, but you can change this. Also
note that the export will be in the CSV (Comma Separated Value) format. Second, you need to enter a
location for the file to be created. The illustration shows the program's default location. Once this
is entered, push the "Next" button to display the Summary dialog box.
This dialog box is merely a recapitulation of all previously entered data, and allows you to verify your
selections. If you are satisfied with your selections, push the "Export" button. If you need to make
changes to any of the previous screens, use the "Back" button to return to the dialog box you need to
correct.
Once you have completed the export, you will have a CSV file that can be opened and manipulated
in Excel. The first few lines will look something like this:
Note that the first line has the company name, the group and the book selected. The
second line has the field names as column headings. These two lines are specified by the checkboxes in
the Field Map Dialog box. The data appears in the subsequent lines, one line per asset.
This output file is in CSV format, and can readily be opened in Excel or any other spreadsheet
program. The format of the fields is identical to what Import Helper requires, so if you are looking
to import data, you may want to do some custom exports to get some "templates" to build your import
spreadsheet.
As you have seen, the Custom Export can handle only one book at a time. Suppose you want to export
data from more than one book, say Tax and Internal. This would require multiple exports. We would
suggest that you first export descriptive information, such as Company Asset Number, Location, GL
Account Numbers, etc. Then build an export map for your book data (Placed-in-Service date, Acquired
Value, Estimated Life, Depreciation Method, etc.) and then use that map to export your data book
by book. Once you have completed the exports, use Excel to merge these spreadsheets together, copying
and pasting from the exported spreadsheets into a master spreadsheet.
Export Helper is also included in FAS Gov Asset Accounting and FAS Nonprofit Asset Accounting. As you
can see, it is very easy to get data out of FAS and into other applications, once you understand how
the Custom Export feature works.
with
no comments
