Working with the numbers
Unfortunately at this point in time our internet database work is
at a relatively primitive stage. We have a searchable ledger and
the searches can be fairly complex but all that we can return are the records
that match the search request. At this point we can't provide users
with a "report" based on a search that would do some of the statistical
work we would like to see happen and that we can produce with the same
database using the Filemaker database engine. We are working on this
issue and we will get it solved in time but at the present it is not possible
to have the search results provide any calculations or data manipulations.
It is possible to move a search's results into a spreadsheet like Excel, however, and have the computations done in that program. The problem is that it is a bit cumbersome and requires a fair amount of work with the search results. I would not recommend this approach with a search result that has much more than 50 records because it is necessary to edit every record in order to move the information into the spreadsheet. If you do decide you want more records however, the editing is till much faster than reentering all the data into a spreadsheet.
The difficulty lies in the way that a copy and paste returns information
from search results page. Start any copy, by the way, by selecting
the line that contains the field names as this gives you the essential
information to modify the text for work.
Step 1
On the search results page use your mouse to highlight the records that you want, starting at the first line that contains the field names . Use the browser functions to edit and copy the material.Step 2
Now paste that material into Microsoft Word. Word is currently the only word processor that I have had success with because of how it will convert text into a table. Once you have pasted you will have a result that looks like the sample below, with spaces indenting each succeeding field in the record.The key thing to understand is that you are supposed to have 9 (nine) fields and that the fields are separated by a line return or paragraph marker. What you need to do is to is a combination of erasing paragraph markers where they break up the information in a field, and adding paragraph markers to provide a "place holder" for fields that for one reason or another (and this happens with almost all of these records) had no data.
Date
Last Name
First
Company
Item
PerUnit
Number
Total
Page
01/23/1868
McPhail
Angus
tobacco
1.25
13
Step 3
Remove and add paragraph markers ("hard returns", which means that you press the Enter key).In the record above we can see: the date, a last name, a first name, the item, a final price, and the page number in the accounts receivable ledger. It needs paragraph markers added to take the place of: the company field, the PerUnit field and the Number field, like this:
01/23/1868
McPhail
Angus
tobacco
1.25
13
As you can see we now have 9 lines of "information". If one of the fields is broken by line returns as often happens with the company names and some items you need to erase that line return while adding the necessary ones. So this example needs the line return between "pair" and "socks" erased while adding the other required returns so that there are 9 lines. This:
01/23/1868
McPhail
Angus
pair
socks
1.25
2
2.5
13
needs to become this:
01/23/1868
McPhail
Angus
pair socks
1.25
2
2.5
13
Step 4: Create a Word table.
Once you have completed all of the editing you then highlight the results and from the Table menu click on "Convert text to table". This table, if you have placed all the line returns in the correct spot should now line everything up properly for you.Step 5: Copy the whole table and paste it into Excel
Once you paste the material into Excel you will then have a spreadsheet that will allow for the numerical or statistical manipulation that you might want to carry out. You may find that the "space characters" pushes the text material "out of sight" (you can't see the text in the cell but if you click on the cell you see it in the editing line pushed over by the spaces). You will probably want to edit out the extra spaces in Word rather than in Excel so that the fully edited text looks like this before it is converted into a Word table:01/23/1868
McPhail
Angus
drinks
0.25
4
1
13
Final Note
You may find that all you really need are the last 4 or 5 fields in order to create the charts or do the calculations you want to accomplish (this would give you the item description, the price per unit, the number of units purchased, the total price and the page number). The page number probably isn't important to you but it doesn't bother anything and would be easier to erase as a whole column in Excel rather than line by line in Word.In this case, then you simply delete all the information and fields
for each record except those that you want to use and make that into a
table and send the information on to Excel.
Apology
Once again we apologize for the messiness of this kind of operation but we can only accomplish so much within the time constraints of our project with Industry Canada. We will continue to work on the issue and hope to provide an online reporting function as well.
