Jump to content
Sign in to follow this  
Chris1961

Import .xls using variables in script

Recommended Posts

Chris1961    0

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
dansmith65    83

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
Chris1961    0

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  

  • Similar Content

    • By Keith McKay
      I have two tables, A & B, with similar fields.  I want to write a script that will search table B for records that match the fields that are entered in table A.  I am not sure how to do this.  Any suggestions?
    • By DreadDamsel
      Hi. I'm using FMPro 12 Advanced.
      Creating a database for our community centre and I need to add a membership number to contacts - but not all of them.
      I've created a separate table for Membership numbers (as not all contacts will have one, so serial numbers aren't appropriate).
      Each membership has a number of details fields.
      It's probably a REALLY logical and stupid problem I'm batting against - but I need to:
      Select a contact>go to their details>add a membership number (go to a floating window layout, enter the details to the serial-number-derived membership) and have the details including the number show up in a tab on the contact's details layout.
      I've created a relationship between my contacts file and my membership file via the Contact_ID>Contact_ID_fk and have played around with other relationships. 
      I've also created a simple script from a button on the Contacts membership tab to go to the Membership Layout window and create a new record. But it keeps creating a new record in the Contacts, not Membership - even though the relationship only has to create new records in the memberships side.
      I'm now running around in circles - please can anyone help me out here?????
    • By Julio
      Hello,
      I have a weird issue that just started happening when I use the 'Print to PDF' script step in a loop.
      I have a script that passes through and prints out a set of pages. When I use the script Debugger and when I reach the spot I am having issues with, I can see, during the preview, that it is in the correct record; showing the correct page, but once it is done and saved as a PDF, it is showing the first record that was browsed.
       
      I have attached a snapshot of the script I am using.
      If you notice, I have it go to the layout First, Performs a Find based on a Variable - It finds the proper records.
      Then it goes to the first record, (for this example, lets say record # 36)
      Starts a loop and saves the record being browsed to a PDF. Then it moves on to the next record in the found set.
      The Custom Dialog which SHOULD show the next records ID, does not. It still shows record #36.
       
      I have no clue as to why. Even though as I go through each script step and I can visually confirm it is moving to the proper record in the FileMaker client, it does NOT save the proper Record.
       
      If I switch the option on the 'Save Record as PDF' from Current Record, to records being browsed, it saves the proper records to PDFs, but it duplicates them 3 times and does not set the proper page numbers.
       
      Attached is also the end result
      The Observ ID: should be 36, 37, 38 but all three pages stay at 36 showing the exact same record.
       
      Thank you all for your help.
       
       
       
       
       
       
       




    • By dysong3
      I have a number of scripts that have worked perfectly for years in previous versions of FileMaker, however in FMP 15 they causse FM to crash.  They only way I have found to avoid the crash is to use the debugger and to use the "step into" button.  That of course is a very cumbersome workaround. You can see one of the culprits below.  Anyone have any ideas what's wrong here?
       
       

    • By sal88
      Hi all
      We have a web based tickets portal which creates new messages in our filemaker database - this is for customers' use. It does this using the PHP API (the website was developed by a third party). We would like the owners of tickets to be notified when the customer has added a message to one of the tickets that they are in charge of.
      Is there a way for filemaker/filemaker server to trigger a 'send email' script when a new record is created via the web portal? Or would it be simpler for this action to occur on the web side?
      Many thanks
×

Important Information

By using this site, you agree to our Terms of Use.