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

    • Find from a List
      By surfninja13
      Hello All,
      This may have been addressed before and I am very new to scripting in filemaker.  Basically what I am trying to create is a script where I can find records from a carriage return separated list.  I have created a field and a button to execute the script.
      For Example: If I have 50 records for every US State, in the field I could put:
      California
      Nevada
      Oregon
      Thus populating just the 3 records.  
       
      Any help would be greatly appreciated.
       
    • Find record with multi AND criteria
      By RDWirr
      I am running FileMaker 14 on a Win 10 machine. I have a set of records that each have a field with a starting number and a field with an ending number. I have to perform a find that locates the record where the find criteria is greater than or equal to the starting number AND less than or equal to the ending number. I need to write a script that will just let the user enter the find criteria in a edit box and then go to the record. This will theoretically be only one record that would match from the dataset. Can anyone point me in the right direction with this?
      Thanks
      RDW
    • Relationship and Scripting - Assistance Request
      By Luke Cottingham
      Hiya,
      1st of all - I have personally refrained from posting for help on a forum as I wanted to try and read, learn and solve my problems using trial and error! I am learning the FMP platform from scratch and am currently in week 2 of it.
      My question relates to a file I have attached to this post - the products area.
      For each product I have a number of 'size options' within it and each with their own unique SKU (stock keeping unit). Each SKU needs its own barcode. My job now is to produce a method to easily assign an 'available' barcode to a specific SKU.
      Barcodes are purchased from GS1 and are globally unique. I have a table hosting a pool of barcodes that I have available to me. They can only be assigned to one SKU and once assigned, needs to be removed from the available selection of barcodes in this pool.
      Issues
      I wanted to set a placeholder text for 'EAN-13' field in the product layout (this is the barcode). This field is a button! When clicked it can perform a script to open a popover or window containing all barcodes that do not have a 'ProductOptionID' assigned to it.
      How do I structure this script? Click the EAN field button - this sets the 'ProductOptionID' variable globally (is this correct to do) - [$$productOptionID] The script then opens the object 'Barcodes'. This is a popover button with a portal inside filtering all barcodes from the pool that do NOT have a productOptionID value in the foreign key field The script must then pause whilst I manually select a barcode and set a $BarcodeID variable? Or should I script it to pick a barcode at random from the pool of available barcodes (i've already set a script to give available a figure of 1 and unavailable a figure of 0) Then the script must put the BarcodeID variable in the product option record Is the above the right way to go about it? I also need a way to un-assign a barcode from a product and therefore remove the value from the product option id field in the barcode pool I have one more query. I have a relationship between Product Option table and the Barcode Pool. This relationship is O2M the barcodeIDpk with a lookup for the actual EAN (barcode) value on the Product Option table. When I set a barcode ID in the Product Option table - shouldn't the ProductOptionIDfk field update in the Barcode pool with the ID of the product option that the barcode is assigned to??
      I am convinced I had it working earlier - but not anymore and cannot see to figure out why?
      See my relationship graph below and the solution attached.
      Thanks,
      Luke
      CRM Trial barcode.fmp12

    • Scripts drop down menu is greyed out
      By MutantST
      Hi All,
      Does anyone know why my scripts drop down menu might be greyed out? I am unable access these functions.
      Cheers, Simon
    • HELP creating homework planner app
      By Hamidfs
      Hi,
      Im in deep trouble
      I need help using scripts
      I am making a homework managing app for a project and i have created a few fields; date due, subject, task, and task type
      I need help on how to process this information and show computational thinking 
      I need help in creating POP UPS!!
      I am looking for something, when clicked on, pops up a screen showing the outstanding work when the user clicks on button 1 which has a date which has past . AND I'm also trying to make a button 2 which shows entries that are close to the next few days 
      please help as soon as possible and suggest any features i can add to the homework manager and how to write the script for the buttons