7 Replies Latest reply on Feb 8, 2009 9:14 PM by jd2775

    shortcuts from Excel to Filemaker?

    jd2775

      Title

      shortcuts from Excel to Filemaker?

      Post

      Hi, 

       

      At work we use PDA's with Pocket Excel to take data.  I am wondering if there is a way to create shortcuts in Filemaker as to save time using the PDA.. so for example...if I enter "1" into Excel that would translate to "House" when it gets imported into Filemaker.  Basically I am trying to save time at the PDA.  There is a set number of "full name" options, maybe 12 or so...so I am hoping there is a way I can just enter the numbers 1-12 on the PDA and have that show up as the full name when imported into Filemaker.  I hope that makes sense

       

      thanks 

        • 1. Re: shortcuts from Excel to Filemaker?
          TSGal

          jd2775:

           

          Thank you for your post.

           

          When importing data from another file, FileMaker can translate information for you.  You will need to set up your field(s) with the translation values.

           

          Here is a simple case.

           

          Create a database with one table and one text field entitled "TEXT".  While still defining the field, click on "Options..."   Under the "Auto-Enter" tab, put a check mark in the option "Calculated value".  In the new dialog box, enter:

           

          Case (TEXT = "1"; "House";

             TEXT = "2"; "Apartment";

             "Other" )

           

          That is, if the value is "1", then display "House".  If the value is "2", display "Apartment".  Otherwise, display "Other".

           

          Click "OK", and then uncheck the option just below "Do not replace existing value of field (if any)"

           

          Click OK a couple of times and enter 1 into TEXT.  When you tab out or click outside the field, you will see "House" appear.  Create a new record and enter 2 into TEXT.  "Apartment" will appear when you exit the field.

           

          Blank out the entries in both records.

           

          Create a text file, two lines, with the following information:

           

          2

          3

           

          Save it as "Import.txt".

           

          Return to FileMaker Pro, pull down the File menu and select "Import Records -> File"

           

          Select "Import.txt", and map the data to the field TEXT.  Most likely, this is already done.  In the bottom left, the default is to Add new records.  However, let's use the second option, "Update existing records in found set".  (You can definitely use the Add new records option, but since we blanked out two records, and we have two records in the import.txt file, I just wanted to show you this option, too.)  Click the "Import" button in the lower right corner, and an important dialog box now appears.  Check the option "Perform auto-enter options while importing".  If you don't check this option, then the raw data will be imported and no translation will occur.  Once checked, click "Import".  You will see that your records now have the values "Apartment" and "Other".

           

          If you need clarification for any of the above steps, please let me know.

           

          TSGal

          FileMaker, Inc. 

           

           

          • 2. Re: shortcuts from Excel to Filemaker?
            jd2775
               Thank you TS Gal, I will give this a shot!  Seems pretty straight forward
            • 3. Re: shortcuts from Excel to Filemaker?
              jd2775
                

              That worked perfectly, thank you TS Gal.

               

              One question though, there will be 6-7 other fields that will be imported at the same time from this Excel document, is it ok to save it as .txt even with all the other fields present?  None of the other fields have any calculations or anything, they are straight text or numbers

               

              Thanks again 

              • 4. Re: shortcuts from Excel to Filemaker?
                TSGal

                jd2775:

                 

                Glad you got it to work.

                 

                Yes, if you have other fields, they will import just fine.  I was using a simplistic example for one field.  You could have several fields set up this way so when you import, the translation is done.  If you don't have any "options" on those other fields, they will just come in as text, number, date, etc.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: shortcuts from Excel to Filemaker?
                  jd2775
                    

                  Ok, I have another question about this but on a much bigger level.  It has become apparant that this particular application we use on the PDA's at work won't accept text in the barcode, only integers.  So I am going to need to convert about 100-150 designated numbers to text.  I have a Excel file made up with Column A containing numbers 100-150 and Column B containing the matching text conversions.  Is there a way to import this file one time, so in the future whenever the number barcode is read, the text equivalent shows up in Filemaker? Or do I have to enter all this information *gasps* manually in the calculated values box?

                   

                  Thanks 

                  • 6. Re: shortcuts from Excel to Filemaker?
                    TSGal

                    jd2775:

                     

                    When I first set this up, I had a feeling you were going to add additional codes.  :-)

                     

                    Seriously, if you are going to have 100-150 designated numbers to text (and probably expanding), then it would be better to put those numbers and text values in another table and link the files together.  Keep your existing table.

                     

                    Pull down the File menu and select "Manage -> Database..."

                     

                    Click on the Tables tab, and create a new table entitled LOOKUP.

                     

                    Click on the Fields tab, and the upper left pull-down should now say LOOKUP with no fields listed below.  Create two new fields:

                     

                    CODE (Text)

                    TRANSLATION (Text)

                     

                    Click on the Relationships tab, and you will see a graphical representation of the tables in your database file.  I don't know how many tables you have created, but at a minimum, you will have your original table and LOOKUP table.  In The LOOKUP table, click down (and hold down) on the CODE field, and drag it over to the TEXT field in your original table.  Let go, and you will see a line connecting the two tables together with an icon halfway in-between.  At this point, click OK and return to Browse Mode.

                     

                    Switch to the LOOKUP layout, pull down the Records menu and start adding records.  That is, enter the CODE and accompanying TRANSLATION.  For example, in my previous post, I might enter:

                     

                    CODE - TRANSLATION

                    1 - House

                    2 - Apartment

                     

                    Do this for a few records so we populate the table.  Be sure to add one record that isn't in the original Case calculation!  I'll explain later...

                     

                    In essence, we are putting all the codes and translations into a separate table, and then using this to lookup and retrieve the translation.

                     

                    So how do we do that....

                     

                    Let's return to "Manage -> Database..." and click on the Fields tab.  In the top left, select the original table.  Double-click on the TEXT field, and the Options for TEXT will appear.  We are going to change the "Calculated value" so click on "Specify...".

                     

                    In the top left, it shows the fields for the current table.  Change the pop-up to "LOOKUP", and you should now see "CODE" and "TRANSLATION".  Remove the old calculation (Case....), and double-click on "TRANSLATION".  The calculation should now say, "LOOKUP::TRANSLATION".  Click OK a couple of times and go to Browse Mode.

                     

                    Be sure to select the original layout.

                     

                    Earlier, I said to enter a new Code and Translation.  Now, in the TEXT field, enter that Code, and the Translation should appear when you either tab or click outside the field.

                     

                    That's it.

                     

                    I know I skimmed through this quickly, so if you have any questions or need clarification, let me know.

                     

                    TSGal

                    FileMaker, Inc. 

                    • 7. Re: shortcuts from Excel to Filemaker?
                      jd2775
                        

                      Thank you again TSGal, it seems to have worked again.  I will just import all my records from Excel into the LOOKUP table, and then I will be ready to roll

                       

                      Thank you!