8 Replies Latest reply on Dec 18, 2015 9:15 AM by jbroderick57

    Using a imported filename

    Stigge


      Hi

       

      Today am handling pre-invoicing in my company.

      To make a long story short i take loads of different excel cheets and merge them in to one. that one then calculates all the costs.

       

      My first step now is to import this big excel cheet in to Filemaker to be able to follow up on costs, calculate statistics etc. The next step is ofcouse to handle everything from filemaker, but thats far ahead in time.

       

      So my question that i seem not to be able to find an answer on, is if its possible to use the filename of a file that i imported.

      Our company is devided in to Teranga codes (like big departments) and also in the filename i have the monthly period. so instead of typing it in

      the teranga code and month manually i want it to be read from the file, since the file is always named the same way.

      ex: 2015- 07 - 5218 - Pre-Invoicing v2.xlsx

           Date: 2015-07

           Teranga: 5218

       

      Is that possible and do anyone know what command in my script to use?

       

      Thank you in advance..

       

      Stigge

        • 1. Re: Using a imported filename
          nicolai

          Hi Stigge,

           

          To get the file name, import your file into container field and use GetContainerAttribute(sourceField;"filename") on tha container field.

           

          There are a few ways to extract your data from the filename, providing it is consistent. My favourite comes from stackoverflow user michael.hor257 (sorry, Michael, don't know your tag here):

           

          for file name "2015- 07 - 5218 - Pre-Invoicing v2.xlsx"

           

          Let (

            [

                  myFileName = "015- 07 - 5218 - Pre-Invoicing v2.xlsx";

                  myList = Substitute ( myFileName ; "-" ; "¶" );

                  myDate = Trim(GetValue ( myList ; 1 ))  & Trim(GetValue ( myList ; 2 ))  ;

                  myTeranga = Trim(GetValue ( myList ; 3 ));

                  result = "Date: " & myDate & "¶Teranga: " & myTeranga

            ]

          ;

                  result

          )

          • 2. Re: Using a imported filename
            Stigge

            Hi Nicolai

             

            Thanks for the quick reply.

            Does this even work when the files are stored at different locations?

            And yes the files are consistant to the point that they are aways named that way. the text is ofcouse changed each month and i have 15 different files each month (different Terangas).

            I never used the container files that way before. :/ but i think am getting what you are saying. =D (my main language isnt English, its Swedish)

            But if i understand the way correct i need to load the file in to container field. as lets say step 1. and step 2 is then to start importing the file as before ?

            • 3. Re: Using a imported filename
              nicolai


              Stigge wrote: 

              my main language isnt English

               

              It's OKey. I am Russian, but we all speak FileMaker !

               

              Does this even work when the files are stored at different locations?

               

              The trick is to import the file into container filed temporarily, storing by reference. The container field could be global.

              I just tried it and it seems to ignore the location and gives you the correct file name. I did not try to insert the file from network share, but I do not see why it would not work.

              load the file in to container field. as lets say step 1. and step 2 is then to start importing the file as before ?

              That's correct:

               

              1     Import into container field as "File" and "store by reference" selected

              2     Use getValue(getAsText(YourContainerFieldName),2)  - gives you a file path which you can store in a variable

              3     Use previous formula to extract your data

              4     import the Excel file using the file path from the step 2

              5     optional - clear the container filed

               

              Nicolai

              • 4. Re: Using a imported filename
                Stigge

                Got it to work =) !!!

                 

                There was some problem to extract the text from the filename, the whole path was shown and the files are stored deep inside our network folders. but thats sorted. =)

                 

                Only thing now that i want to improve on it is that i dont want to choose the file 2 times..

                first to the container and second for the import.

                Is that a way around that ?

                • 5. Re: Using a imported filename
                  nicolai

                  Only thing now that i want to improve on it is that i dont want to choose the file 2 times..

                  first to the container and second for the import.

                  Is that a way around that ?

                   

                  Siggi, I had in mind a single script where you only import the file once. The first step gets the full path so you can script second import without user interaction.

                   

                  Have a look at a quick demo: drop import.xslx anywhere, open "file import.fm12" and click on "Import" button, select import.xslx file, you should see the file name on the screen and the new records are imported.

                   

                  NicolaI

                  • 6. Re: Using a imported filename
                    jbroderick57

                    I imagine the last step if you wanted the FileMaker file to be the same name and same contents as your spreadsheet would be to script the deletion of the container field and then save a copy as the $$FileName?

                     

                    How would you script the final save to rename the FileMaker File to Import.fmp, as in your sample file?

                    • 7. Re: Using a imported filename
                      nicolai

                      jbroderick57


                      I am really sorry, but I don't understand what you are asking. The files are a demo of the technique and not a solution.

                       

                      The import.xslx file does not have to be named like this, the whole point is to get any file name of the file imported. There is no need for FileMaker file to be saved and it could have any name.

                       

                      If you are talking about saving the Import Records "specify import order" mapping, you can temporarily add a sample file in the Specify Data Source after the $filepath variable,  map the import and save the order and remove the sample file from Specify Data.

                       

                      But if you really want to save the current FileMaker file with a different name you can use "Save a copy as" script step

                       

                      If I did not get it right, describe a bit more what are you trying to do.

                       

                      Nicolai

                      • 8. Re: Using a imported filename
                        jbroderick57

                        I think you answered my question.

                         

                        I got the impression that Stigge wanted the FileMaker File to have the same name as the spreadsheet file - maybe that wasn't the case.

                         

                        However, I do like the idea of having a "template" file, like your File Import.fmp that will bring in a spreadsheet, all the data, and then save a copy of itself as the spreadsheet file name. 

                         

                        Seems like this might be useful in a case where separate FM Files for number crunching a fixed set of data from a spreadsheet could be useful.  Thanks!