Sign in to follow this  
Followers 0
Chris1961

Import .xls using variables in script

3 posts in this topic

Hi

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.

Query:

When I set the filepath manually within the script (ie [file:../../../../../Temp/BostonRD.xls]) I click OK, then select 'Sheet 1' of the x.ls 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.

Chris

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:


$file

file:../../../../../Temp/BostonRD.xls

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!

Chris

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

    • By Bailey Kessing
      I am running a server script periodically (every hour lets say) which does some database maintenance, uploads some files, etc. I do this on a number of Filemaker Servers (latest server version running on Windows machines) without problems…except for one server. On this server I use scriptmaster to up load some large files into containers as they are generated from scientific instruments. The problem is that the server runs seems to run out of RAM memory after a "few" days. I think this is the only thing that this server does differently than the other 4 servers and feel that this is the problem. My question is…is there a way to "flush" memory used by the plugin or by the FM server. Has anyone else experienced something similar or am I barking up the wrong tree? Thanks. 
    • By Oyseka
      Hi all, I know this should be easy but I am not getting it right.

      The mail gets sent but no attachment
    • By docasar
      Hello,
      It always go to "Default segment button bar", set on the button bar setting, when reenter to layout.  I need to go to same segment when user was working on before he left the layout.
      I know how to get back to last tab panel used, and I wonder if there would be similar for this.
      Thanks a lot!
      Luis.
    • By docasar
      Hello,
      I have a tab control panel with four tabs, and I would like to achieve that when I change tabs a script is triggered and would set the value of a global field called "zzz_g_collection_type" according to the tab name which is active.
      I need this field to dynamically filter the products that are shown on 1_row portals that I have put into each tab panel space.
      I have followed Elishree Dey tip on http://burnignorance.com/filemaker-pro-tips/trigger-a-script-when-user-changes-the-tabs-in-filemaker/ but I cant make it work, it only changes the value of "collection_type" field once and only to show that the first condition is not met.
      I have attached the picture of my script.
      My initial idea was to place on each tab space a picture-field of a product as a 1 row portal and then duplicate as much as products I have so when I click on the picture a script is triggered and the product is added to the invoice.
      Morever, I have set an Object name to each tab Control so it would work, and also the desired value for zzz_g_collection_type comes from another related table with only one record that I use as preferences, this is on Preferences Table I have three fields called "Product_Type_Short_#" and those match on Elishree Day script tutorial.
      Any ideas what I could be doing wrong?

    • By fm8443
      I have a Layout in Table Mode as (partially) shown in the attachment.
      (Note:  First row will have a Country Code in it - not shown in attachment)
      I want to "fill-down" the last country code in all rows (in the Country Field) UNTIL the country code changes.  Then I want to similarly do it for that one.  Until I reach the end of rows.
      My database will grow from 500 to 10,000 rows of text/number data.  I am uploading in batches from Excel.
      Is this possible?  I've tried various calculations and scripting without much success.  Thanks!
      I'm trying to get something like this:
      COUNTRY
      IT
      IT
      IT
      IT
      IT
      IT
      IT
      IT
      ES
      ES
      ES
      ES
      ES
      AU
      AU
      AU
      AU
      AU
      IN
      IN
      IN
      IN
      .......etc