Sign in to follow this  
Followers 0

Import .xls using variables in script

3 posts in this topic


Sorry, new here so please forgive me if I am in the wrong area!

I have a file which relies totally on externally provided data. I cannot link directly to the data and have to download the individual .xls files manually from the internet and I store them in a child-folder called "Lincs" in the My Documents folder using a direct path name.

Problem is, this will be used by other people in other countries and the route to their My Documents folder is differnet to mine.

To get round this problem I used the following in my script:

Set Error Capture [ On ]

Set Variable [ $file; Value:"filewin:" & Get ( DocumentsPath ) & "namefolder" & settings::globaltextnamefilewithextension ]

Import Records [ Source:“$file”; Method:Update existing; Character Set:“Windows ANSI”] [ No dialog ]I

f [ Get ( LastError ) = 100 ]

Show Custom Dialog [ Title: "Error"; Message: "File Does Not Exist"; Buttons: “OK” ]

End If

This seems to work OK.


When I set the filepath manually within the script (ie [file:../../../../../Temp/BostonRD.xls]) I click OK, then select 'Sheet 1' of the file, and then select 'data' as the 'First Row Option'. Then, when I click on 'Specify Import Order', the source fields are displayed which I can match up. I can also select 'Don't Import First Record (contains field names)", and then click OK again. Then click on 'Import Options' and select 'Import'. All works great.

However, when I use a variable to import the file, I am not given the 'Sheet No' option and when I try to specify the import order no source options are displayed. Also I cannot choose to omit the first record containing the field names.

It works in principle, but imports the 'title fields' or a blank record from the .xls file. Also, how does it know which fields to match up?

As I say, it works in principle but I don't understand why and how can I stop it importing the 'title fields' from the .xls file?

Any help would be greatly appreciated.



Share this post

Link to post
Share on other sites

Using a variable for a path to import records can be a little tricky since FileMaker cant find the file via your variable while you are editing your script. So, while editing the script, set the filepath manually so you can specify the import options, then you can change the path back to the variable path, and it should work.

You can also just specify multiple paths, and leave it like that. To do this, enter this text in the "Specify File" window:




Share this post

Link to post
Share on other sites

Hi Dan

Thanks for that. Yes, makes sense when you think about it. As I said, it seemed to work OK, I just couldn't see why!



Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • Script Running Way Too Long
      By Lawrencex
      Hello FM Pros
      Would appreciate some tips for the following script problem:
       I have a situation here where my scripts in FM 13 Pro Adv takes more than 5 hrs to complete a calculation.

      OS: This is Win 7 Pro. with 16 GB RAM (i5 Processor.)

      I have Field "A" which is a 7 digit code numbers FIELD, which most often are repeating numbers, from 200 different types.
      (And I have upto 80 million records, in this field.) Code examples: 1234567, 2345678, 5678012,...

      Fields "B" to "Y" (are global Fields) provides a reference number for each type of the 7 digit codes from Field "A". For
      example the code 1234567, as above, the reference is, eg. HH/2015/Jul/132, Code 2345678 the reference is, HH/2015/Sep/314,...etc

      Field "C" list the reference numbers from Field "B" in the order of the records from Field "A"

      I use the following scripts in Field "C"

      If [Field A="1234567"]
      Perform Script [Replace Field Contents Field C;Field B]
      Else If [Field A="2345678"]
      Perform Script [Replace Field Contents Field C:Field B]

      Else If (...until 200 lines of the references from Field)

      End If

      The script runs well. However, it takes more than 5 hrs to complete the entire 80 million records.)

      Would appreciate your suggestion to speed run the scripts.

      Many thanks in advance.

    • One-time use button
      By FrancoFranco
      Hi all,
      Apologies if this in the wrong place, but it seems like the best fit. Just wondering if anyone can help me out with a script I'm struggling with.
      The background: I have a button I'm using to launch a new window with another layout, then create a new, related record. This action will only happen if the user has completed a specific field in the original layout (__pkCollectionRef), step one of the script.
      With the new window open, the user will then make a selection from a checkbox set and close the window, which will put their selections into a field in the original layout.
      This part's all fine. What I want  then, however, is for the button to be made inactive once they've completed their selection, so the script won't run if they press it a second time (this is to prevent the creation of redundant records - there is another button that will allow them to edit their previous selections if required).
      I've attached two screen grabs of the script steps. The first is the script I require help with, the second is the script run if the necessary conditions of the first are met (i.e the creation of a related record in a new window and layout).
      The _fkPickList field in the original layout will only be populated upon the creation of the new related record, so I had thought that adding the isValid calculation as a means of halting the script would do the trick, however what's happening is that it's preventing the script from running in the first place.
      As a side note, I've also added conditional formatting to the button so that when the _fkPickList field isValid the text is greyed out. This one discourages pressing the button a second time, however, not prevent it from working.
      Thanks in advance for the help. All comments appreciated.

    • Inserting hyperlinks into mail merge scripts
      By wdedalus
      I've been doing merges for years using the "Merge Mail" script.  It is a handy tool.
      To date, when I wanted to include a url, I just had the url be part of the e-mail. Currently, I'm trying to send a note with a *lot* of links, and want to hyperlink the actual text.  But I can't figure out how to do it!  Searching the web has proven fruitless.  Any FM gurus out there who can shed some light on this?
    • IF Script skipping steps
      By eddiedredge
      Hi All, 
      I'm struggling with a script and count see what I'm doing wrong.
      The frustration is its a simple script. For some reason the script will not action the second option. If red it will run red script if blue it will run blue script, but if green it moves down to the blue if and runs the blue script. I'm so confused.  The script is just a trigger to run another script all three of which are the same just with different layouts. Any advice appreciated. 

    • Creating records with a unique alphabetical field value, but constrained within a previously assigned numeric field
      By Tony Morosco
      Sorry if my title is vague, I am not sure exactly how to describe this problem.
      I have taken a look in the Forums, and haven't found this question before. (If I did, please point me there.)  And of course I've looked through the documentation in FM extensively.
      I am a botanical garden curator, and this database focuses on two tables: accessions of plants (groups of plants received at one time, with multiple individuals), and on locations of plants (individual plants of one accession, but planted in different areas).  For each individual Location (of a single Accession) I would like to assign it a unique qualifier for that accession.  These qualifiers are based on letters of the alphabet, so-as not to confuse them with the numerical accession numbers.  
      For instance, for accession 2002-0101:
      For the first Location record, I would like the qualifier to be "A" 
      For the second Location record, I'd like for it to be "B"
      For the third, I'd like it to be "C"
      and after the last Z value, I'd like for it to assign them AA, AB, AC, etc.  I don't imagine there will be any values higher than AZ.
      So I am looking for a way to come up with a script and/or assigned calculation for qualifier to do this when creating a new location record in the locations table.  
      I've already assigned these letters to existing records in our database with a script.  Roughly, the existing script runs by:
      Sorting the Locations table, If the accession number is a new, the qualifier is set to "A",   If the accession number already exists, then based on the previous records existing qualifier value, assign the next qualifier value for the record.  Capture the accession number and qualifier in a variable, for use in the next record Move on to the next record. I know that the plant collections registrar can come up with the qualifier in their head (de-novo), but frankly they don't need that headache searching through the records and coming up with a new qualifier to create a new location. 
      I know this is rather complicated, but let me know if I should come up with two tables to let you know what is going on.  I am sure it is something simple that I am overlooking.
      And please let me know if I am being confusing, or have not laid out the problem in a logical way.