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 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?
    • By johnfranklin
      I have a solution that has been working well for several months and suddenly broke. This may have been perpetuated by the last round of windows security updates. I have a list of orders displayed in a portal. The source of the portal is SQL Native 11 ODBC data. Consisting of a header table and several related tables. Clicking on a portal row will create a new Filemaker record and populate it with the related ODBC data. This will work for several hours and then un expectantly hang. The find window  \ Find in progress \ Processing query window will appear. The copy cup will then appear. When this happens the database cannot be closed from Filemaker server. The ODBC data source is no longer accessible by other applications. The only way to break the connection is to restart the Filemaker Server.

      My configuration is as follows:

      SERVER

      Window Server 2016 Standard Edition

      Filemaker Server 16.03

      SQL Server native Client (64 bit)

      WORKSTATIONS

      Windows 10 Pro 64 bit

      FMP 16.03 user connections

    • By Rook183
      I have a solution running on a Filemaker 16 server. I have accessed the server via VPN on a remote Mac and now want to access the data via ODBC on that remote Mac.
      I have turned ON ODBC/JDBC for ALL USERS in the Filemaker file
      I have downloaded ODBC Manager – the free version
      But when I try access the Filemaker "System DSN" in the ODBC Manager, nothing appears. This is a mystery to me as this exact (and rather simple) setup works perfectly on my local network.
      I originally set up the local network some time ago, so:
      • Have I missed a step?
      • Does the ODBC not work across a VPN as opposed to local network where it works great?
      • Does the ODBC manager I am using not work on iOS Sierra?
      • Is there something else that I am missing?
       
      Notes: Filemaker 16
      Mac OS Sierra
      ODBC Manager: 1.0.12
×

Important Information

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