10 Replies Latest reply on Dec 4, 2013 7:06 AM by JamesHauschultz

    Anomaly?  Appleworks to Filemaker Pro

    JamesHauschultz

      Title

      Anomaly?  Appleworks to Filemaker Pro

      Post


           I export my database using ASCII.  I created new fields in Filemaker Pro using the same names as the source fields in Appleworks.  Two of the source fields do not show up from the original database.  The same two.  While the others show up, I've had to change the order of import to correctly match the fields.  That's not a big deal.  But the missing two fields show up in every record and I'll need the information to be included in the new database.  The database is over five years old and cutting and pasting those two field's enclosed information is a very big obstacle.  Why would fields not show up in the ascii file?  Thanks you.

        • 1. Re: Anomaly?  Appleworks to Filemaker Pro
          philmodjunk

               What kind of data is in these two fields? Text, numbers a date?

               And into what kind of field are you importing that data?

          • 2. Re: Anomaly?  Appleworks to Filemaker Pro
            JamesHauschultz

                 I'm sorry.  First of all, I have Filemaker Pro 11.0.v4.

                 My fields are Date, Invoice#, Job#, Client, Project, Services, Expenses, Invoice Total, Sales Tax, and Amount Due.

                 Date to Date

                 Invoice to Number

                 Job# to Number

                 Client P.O. to Number

                 Client to Text (Name and Address)

                 Project to Text (Description of Project)

                 Services to Text (The Services we rendered and money)

                 Expenses to Text (Expenses and money)

                 Invoice to Numbers (money)

                 Sales Tax to Numbers (money)

                 Amount Due to Numbers (money)

                  

                 Thank you, Jim

            • 3. Re: Anomaly?  Appleworks to Filemaker Pro
              philmodjunk

                   and which of those aren't importing?

                   And which file format are you using to import data?

                   If you open the ASCII file in a text editor, do you see the missing data there?

              • 4. Re: Anomaly?  Appleworks to Filemaker Pro
                JamesHauschultz

                Best questions! The two data fields not importing are "Cllient" and "Services" I tried both comma and tab options for text. Yes, in a text editor the information is there. Here's a copy of the first record from the text file. The order presented is incorrect in the text file, but it is there.

                Client: Eberle Productions 186 East Kirkham St. Louis, MO 63119 Did not import

                Date: 1/12/96

                Invoice#: 96-E100

                Job#: 96-2292

                Client P.O.: 2292

                Project: Southwestern Bell Telephone Yellow Pages Managers Meeting

                Expenses: No field information here

                Services: Script: “Turkey Trots” $600.00 Did Not Import

                Invoice total: $600.00

                Sales tax: No data to import

                Amount due: $600.00

                I'll upload two images...one is a screen shot of the text file including all the data in the record. I expanded the text file so that there are no margins. The second is the invoice as presented after the data has been entered.

                Thank you again for presenting a path for analysis.

                Jim

                • 5. Re: Anomaly?  Appleworks to Filemaker Pro
                  JamesHauschultz

                       It looks like the second did not upload.  I'll try again. This is a very old invoice and pertinent private business info has been excluded.  None of the info represents the current business.

                  • 6. Re: Anomaly?  Appleworks to Filemaker Pro
                    philmodjunk

                         The structure of your text does not look correct for importing from a tab delimited file nor comma separated values.

                         In a tab file, a tab character separates the data in each column and a return separates each record. From what I see here, you have some extra returns and possibly some tab characters that are keeping all the data from correctly importing.

                         I suggest that you try importing from the file using the "new table" option for the target table and then examine a table view of the imported data to see what needs to be done to get your data where it's needed. Please note that what you show is actually data that is usually imported into more than one table as an invoice often uses data from multiple tables.

                         The typical invoicing data model looks like this:

                         Customers----<Invoices----<InvoiceData>------ProductsServices

                         You'll find a working example of this set up in the invoices starter solutions that come with FileMaker 11, 12 and 13.

                    • 7. Re: Anomaly?  Appleworks to Filemaker Pro
                      JamesHauschultz

                           Attached are two more screen shots.  The first shows the 'Define Database Fields' reflecting the file I'm trying to import.  The dialogue box ends with "Description of Services" but the scroll bar reveals the rest and the window won't resize to show contents.

                           However, I created a new database and created fields that that have the same field name and field type.  Then I imported the text file.  Except for the two fields that did not import (TO: and Description of Services:) they all imported.  The Project Title was truncated, leaving off the words "Managers Meeting" which was on a separate line and probably entered after a keyboard return.

                           The second screen shot shows the 'source fields' to the 'target fields' dialogue box.  Note that "TO:' and "Description of Services" remain unimported.  The last two fields from the source fields (Client Job# and BB Invoice #) are not reflected in the source file and were never used for data collection.

                           So I am close.  Two fields remain imported although they appear in the source text file.  And one of them truncates.

                           I appreciate your patience and guidance.  I understand the correlations that must take place and have created fields in the new database to exactly reflect the field name and field type.  The content of those fields is not recognized yet.

                           Thanks,

                           Jim

                      • 8. Re: Anomaly?  Appleworks to Filemaker Pro
                        JamesHauschultz

                             And the second screen shot...

                        • 9. Re: Anomaly?  Appleworks to Filemaker Pro
                          philmodjunk

                               This is not an issue with the field types. It's an issue with the format of the file you are producing with the export from appleworks. The format produce will not import correctly due to the details I previously pointed out.

                               Have you tried what I suggested?

                               a) Select Import Records, but do not select an existing table for the import. Instead, select the New Table option from Target Drop down shown in your screen shot of the field mapping dialog.

                               b) after import, use Table View to view the new layout created for this new table so that you can not only examine the data in each field but look to see if some data was imported into a different record as the presence of a return character may be causing fileMaker to see that data as a separate record instead of data in the same field but on a new line.

                          • 10. Re: Anomaly?  Appleworks to Filemaker Pro
                            JamesHauschultz

                                 Dear Phil, or Mr. Modjunk,

                                 Please administer a cyber dopeslap.  When I clicked on the 'missing' field, the field opened and the data was there.  Apparently the first line of data was a return, thereby dropping the data down one line.  So it didn't 'show'.  It's all there.  Next step is to create field names that are the same as the source, and then make a layout similar to the original. 

                                 Thanks for your help.  Your instructions were exactly right, and in the process I learned a great deal about Filemaker Pro. If I can rate this, please let me know. 

                                 Jim