Jump to content
Sign in to follow this  
slbr549

ODBC Excel Connection- read FM record based on Matching Cell

Recommended Posts

I am using Excel 365 on desktop and FileMaker Pro Advanced 15 with FileMaker Server. 

I am able to connect to FileMaker from Excel using ODBC to read all records in a table or matching it to a hard coded name. Sample:

Select work FROM "Student Time Import" WHERE badgename="Patrick Dollar"

What I'd like to do is add a where clause where it selects hours worked based on the date in the Excel spreadsheet. The cell's date is B12 and so on.  I know my syntax is wrong, but after several attempts, I haven't been able to solve it. Here is my syntax:

Select work FROM "Student Time Import" WHERE badgename="Patrick Dollar" and date = TimeSHeet(Select * FROM[SHRA Temp Timesheet.Range("B12).Value])

Thanks.

 

Share this post


Link to post
Share on other sites

This is more an excel question than a FM question so you may be better off asking this on an excel forum; every environment has its own specific way of constructing the SQL query.

One quick  note though: you have a field named "date".  That's a reserved SQL keyword and also the name for a datatype in most environments so try to avoid that name.  At the very least you will have to quote the name in your sql query.

  • Like 1

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 jchallenw
      Does anyone have experience and/or success using a 4D database as an external datasource through ODBC. I can add the system DSN and add it to FMP. Once I attempt to view the tables in the 4D database, I receive an error that the "ODBC connection is not compatible". Does anyone have a work around or suggestions? Thanks!
    • By Franziska B
      Dear 360 Works Team

      In our FileMaker Solution we want to create an Excel report including text, numbers (dates) and images via Scribe.

      The FileMaker export of text and numbers works fine. But we have a problem with the export of images in the xlsx.

      After the Creation of the Excel reports all text- and number elements are in the .xlsx but the image not.


       
      When we open the generated .xlsx, Excel shows the attached error massage:


      For the export of the pictures, we test following script commands:  
       
      ScribeDocValue[Name:“F5“;Value: table:: cell]
       
      ScribeDocWriteValue("Table1!F5", table::cell)
      SetVariable[$PicXYZ; Value: ScribeDocWriteValue("Table1!F5", table::cell)]
       
      Do we make a mistake, or could this be a known bug?
       
      Our Systemconfig:
      FileMaker Pro Advanced 16.0.4.403
      Excel 2010 and Office 365;          
      360 Works Scribe 3.08 and Scribe 3.09
      Server:
      FM Server 16.0.3.304
      Microsoft Windows Server 2008 R2 Standard
      Version 6.1.7601 Service Pack 1 Build 7601
      Could you help me please?
      Thanks


    • By Christopher Gauntt
      Hello all,
      I installed FileMaker Pro ODBC drivers, and was able to access the data with queries from Microsoft Excel.
      However, when I tried to access the same data with the same driver using Microsoft Visual Studio 2015, it could see the table occurrences, but it couldn't find any of the data columns.  It came back with the following error:
      " Error at Data Flow Task [ODBC Source [1]]: There was an error while trying to get schema information. Table has no columns."
      I got the same error, no matter which table occurrence I selected.
      Has anyone else run into this and solved it?
      I have been google searching without much luck.  Although I found one post of someone who claimed they were able to do it with Microsoft Visual Studio 2017, so maybe an upgrade is in order?
      Thanks in advance,
      Chris
       
       
       
    • By Christian Chojnacki
      Hello,
      we use your plugin Scribe for editing Excel files in our Filemaker solution.
      Since a few days we get an error message when calling the method "ScribeDocLoad" (see appendix) for newer Excel files.
      Older Excel files from the beginning of 2018 are not affected.
      So we wanted to ask if there might be a problem with the error in Scribe.
      We are currently using:
      Filemaker Pro Advance version 16.0.3.302
      Microsoft Excel 2016 Ver. 16.0.9029.2253 64-bit
      Scribe version 3.08
      In the appendix you will find an Excel file that throws an error message when opening with Scribe.
      With kind regards
      Christian Chojnacki
      Click to choose files
      Mappe1.xlsx

    • By JHickam
      In several tables of our solution there are records being created that have something like, "com.prosc.jdbc.JDBCUtils test d81e5f35-4f7c-4c53-92c1-bc32e3700094" in every field. Today I found 3 tables with collectively 150 records like this... this has been happening on an irregular basis since mirrorsync was implemented several months ago. Is this coincidence or something someone has seen before?
×

Important Information

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