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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By Scott Pon
      Using MS Access 2010. Filemaker 13 Pro, Filemaker 13 Server.
      I have a MS Access database that that I programmed to do a lot of calculations and creates data. Now I want that data saved in a Filemaker database.  I was able to create ODBC connection to the Filemaker database.  I am able to view the Filemaker tables/data in MS Access.  
      At 8AM daily I want the Access database to do the calculations, and then insert the data to Filemaker . However every time I try this, the Filemaker Login Screen comes up.  And I would have to manually enter the username and password.
      Is there any way to bypass the log on screen?
      Thanks in advance.
    • By trevix
      On windows 7 (VMware on OSX), using FMP17 and Microsoft Access 2016
      Trying to do a ODBC import and selecting the datasource, I get asked for Username and password, even if the Access DB is not password protected.
      The data sources are correct (tested from Livecode) and should be working without user name and password.
      On the properties of the ODBC pane setup the path seems correct (not pointing to 32): %windir%\SysWOW64\odbcad32.exe
      What am I missing?
    • By brownt
      I have a friend for whom I've done a bit of consulting helping him build some project tracking tools in FileMaker.  Helped him through the basic stuff around relating tables together, doing a bit of scripting, creating some basic reports based on data he's entered... Well, now he's hooked!  And would like to do everything for his business in FileMaker.  His current partner does everything in Excel, and has over the years developed a project management spreadsheet he uses to track project funds, activities and generate a financial summary for their clients...  Well, now I've been asked to see if it can be redeveloped in FileMaker, because my friend doesn't want multitudes of Excel sheets for every project, that he has to paw through when he wants to pull data for his other tracking tools that are in FileMaker.   He'd like to be keeping all the data in one place.
      His partner's Excel sheet basically tracks the date of an activity, a note about the activity (what business or consultant or company performed the activity), what kind of activity it is (each spreadsheet for each project may have anywhere from 8 to 16 different kinds of activity) and how much money was associated with that activity (credit or expense).  Tracking that is not a tough deal in FileMaker, that piece I've already sketched out.  Reporting on it is where I'm having problems, because he'd like FileMaker to produce a report that looks as close to what the Excel sheet looks like, including columns for each kind of activity they're tracking, with activity totals, balances, budget remaining, etc...  Where I'm really really running aground is the fact that there may be any number of different kinds of activity going from project to project.  Some may have 7 different kinds of activity, some may have 15 or 20. 
      I've included a sanitized, example version of one of his Excel files.  I can't wrap my head around how best to do up a report that doesn't have a set number of fields (what would correspond to the columns in his Excel).  I could do it as a great big flat file database and put in fields for every possible kind of project activity, I guess, but.... argh!  
      I would welcome any thoughts on how a report like the one I've included as an Excel file here could be created in FileMaker...  Or if the combined expertise here figures this is something that is best left to Excel (I must admit, my thoughts have drifted that direction as I wrestle with this).
       
      Thanks in advance!
      report.xlsx
    • 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


  • Who Viewed the Topic

    1 member has viewed this topic:
    Terrible Toll 
×
×
  • Create New...

Important Information

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