10 Replies Latest reply on Jan 7, 2014 11:25 AM by FileMakerNovice

    Auto Import a file by multiple users

    FileMakerNovice

      Title

      Auto Import a file by multiple users

      Post

           Hey all,

           I have an FM13 database that I import four xls documents daily.  Previously, I created a script that used the import step to import from my download folder.  I set it to import certain fields.

           I am now wanting to have other users on other computers import these files.  Here is one of my scripts:
           Set Variable [$drive; Value:Get(SystemDrive)]
           Set Variable [$user; Value:Get(UserName)]
           Import Records [No dialog; Source: "file:/$drive/Users/$User/Downloads/FileName.xls"; Worksheet: ""; Update Matching; Mac Roman]

           My problem is that it does not allow me to specify fields.  Each of the 4 reports has close to 20 columns, of which, I only import 3-10.  Is there something that I am not seeing?  Can this work?

        • 1. Re: Auto Import a file by multiple users
          philmodjunk

               Is it the same set of fields for each import or do you need to specify different fields each time that you import?

               Import records can preserve the field mapping so that the fields imported are the same each time that the script runs. The trick, when setting up the script to use a file reference that includes variables is to set up TWO file references. Keep the one that you have as line 1, but then use Add File to add a reference to a file for line two. You'll only use this reference while setting up the script step. When you run the script, the reference on line 1 will be used as long as it's a valid file reference. After you have the script set up and working correctly, remove or rename the file referenced in line 2 (the actual file, don't modify your script), to make sure that it can't be imported from by accident.

               For more on $Path variables, see this thread: Exploring the use of a $Path Variable in Scripts

          • 2. Re: Auto Import a file by multiple users
            FileMakerNovice

                 Ahhh!  Awesome.  Thanks.

                 So yes, the columns are the same each day with each import.  I just noticed that it is not really the fields on the left when doing the import field options but rather the column header from the Excel file.

                 Thanks again.

            • 3. Re: Auto Import a file by multiple users
              FileMakerNovice

                   Uh-oh.  Spoke to soon.  I'm going to download the file you linked but is there a short answer to how do I get the Username of the computer rather than the Filemaker username?  My Get(Username) gets the filemaker username.

              • 4. Re: Auto Import a file by multiple users
                philmodjunk

                     Not that I know of, But the Get ( DesktopPath )  and Get ( DocumentsPath ) functions will produce the complete paths to those folders.

                     The names that you need will be included in that text so you can parse out the portion that you need--including both account user name and system drive letter.

                • 5. Re: Auto Import a file by multiple users
                  FileMakerNovice

                       Ah.  Nice.  Good thinking.  I'll just take "desktop/" from Get(DesktopPath).

                  • 6. Re: Auto Import a file by multiple users
                    FileMakerNovice

                         Okay... ran into a problem with the script.  Please feel free to suggest a new subject/post.  

                         My XLS file has multiple tabs.  When I do what we discuss, it imports the first tab.  Any suggestions?

                    • 7. Re: Auto Import a file by multiple users
                      philmodjunk

                           And what result do you need for the import? To import from all tabs or just a different tab?

                           Each "tab" is a "worksheet" and you can specify the worksheet from which you want to import the data.

                           Also, if you can use Excel to create a "named range" to specify the data that you want to import, you can specify this as one of the options for importing from Excel.

                      • 8. Re: Auto Import a file by multiple users
                        FileMakerNovice

                             So...
                             Each report (XLS file) has 3-6 tabs.  Column headers are all the same but tab one is Fiscal Month, tab two Fiscal week, tab three is fiscal date.  There are some additional tabs in some reports but the one that I need is the third tab.  

                             It previously worked when I was importing from only my user folder.  At that time, it gave me an option on which tab/worksheet to use.  Now it does not choose the correct worksheet.  While I have filemac:$path/Downloads/filename.xls AND file:../Downloads/filename.xls, it allows to choose a worksheet.  When I only have filemac:$path/Downloads/filename.xls, it does not allow to add a worksheet.

                             EDIT:  If both files are present, the "update matching" does not appear to function properly.  End up with duplicates.

                        • 9. Re: Auto Import a file by multiple users
                          philmodjunk
                               

                                    While I have filemac:$path/Downloads/filename.xls AND file:../Downloads/filename.xls, it allows to choose a worksheet.  When I only have filemac:$path/Downloads/filename.xls, it does not allow to add a worksheet.

                               Please explain what you mean by this. Are you removing the second file reference from the script step or moving/renaming the file?

                               

                                    If both files are present, the "update matching" does not appear to function properly.  End up with duplicates.

                               Sorry, but that makes no sense. the script step will only open one of the two files. The presence of the second file won't affect how data is imported into the first file.

                               But "Update matching" has to be set up correctly and you need the right records in your target table's found set or you'll get duplicates. This usually means that your script needs to do a Show All Records before importing to make sure that your target table has all possible records in the found set so that the "Update matching records in FOUND SET" option can work correctly.

                          • 10. Re: Auto Import a file by multiple users
                            FileMakerNovice
                                 

                                      Please explain what you mean by this. Are you removing the second file reference from the script step or moving/renaming the file?

                                 Apologies.  I misread your first comment.  I modified the script.  Corrected now.

                                  

                                 
                                      

                                           If both files are present, the "update matching" does not appear to function properly.  End up with duplicates.

                                 
                                 I think you hit the nail on the head with the "Show All Records" script comment.  I will try this.