Jump to content

FM can't find specific record via ODBC


This topic is 1839 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I've got a strange case on my hands.  Here's some background:

I've got an ODBC connection to JD Edwards Enterprise 1 (Oracle DB).  I have a layout that shows fields from the RMA Line Items table.  There's a field at the top that lets you find by RMA number, then the matching records (found via self-join relationship) are displayed in a portal. 

This has been working well for years, and then one of our users stumbled across a record that couldn't be found by FileMaker, though it definitely exists.  The problem seems to be limited to one specific RMA number.  If you go into Find mode and enter the number, FileMaker says there are no matching records.  However, if I run an ExecuteSQL statement in the Data Viewer tool, that is able to find the records.  There are 179 line item records for the problem RMA number.

I'm pretty sure there's no limit on number of rows or anything like that.  I would have thought it to be some sort of record corruption in JDE that is preventing FileMaker from seeing the records, but that doesn't explain why I can see the data by doing an ExecuteSQL statement.  It must have something to do with the ODBC table occurrences, but I can't think of what that may be.  As I said, the problem seems to be limited to this one particular RMA number.

Any thoughts or ideas?  Thanks in advance!

Link to comment
Share on other sites

I can't see anything that looks obviously wrong or corrupt in the exported data.  Weird.

If I try to use the record scrubber to nav to the record, instead of doing a find, FileMaker will flash a "Records remaining to sort" dialog that never seems to get anywhere.  It just runs endlessly, and blinks on and off.  Does that behavior point to any particular problem or diagnosis?

Link to comment
Share on other sites

I don't think so, but I suppose that depends on what's considered an 'expensive' formula.

I've got two unstored calcs that use a custom function to convert a JDE (Julian) date to a regular Gregorian date, one simple Let calc that contains a couple If statements and Trims spaces from field values before returning a concatenation of two values, one Count function, one simple number / 1000 arithmetic function, and one simple concatenation calc.  There are no ExecuteSQL calcs or anything like that.

Link to comment
Share on other sites

I wonder if you can isolate what causes that 'records remaining to sort'.  Can you take a copy of that and start stripping out things until it does not happen anymore?

Any sorted relationships used in displaying related fields?  Any fields from more than one hop away?

Link to comment
Share on other sites

Well, I couldn't replicate the weird sorting behavior again.  I do have some data that is more than one hop away.  I have the records shown in the portal being sorted, but as a function of the portal, not the relationship.

I'm going to try creating specialized views containing only the fields I need, (if I can get proper permissons on the Oracle database) which should help cut down on the total amount of data.  Maybe that will help.  We'll see!

Thanks for the suggestions.  I'll try to remember to post back here if I reach a solution.

Link to comment
Share on other sites

This topic is 1839 days old. Please don't post here. Open a new topic instead.

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

  • Similar Content

    • By Buckie
      Tried adding an ODBC source using both MySQL 8.0.22 and MariaDB 3.1.10 drivers in Unicode mode and I can connect just fine, however it's impossible to add a table onto the relationships graph. It sees the table's name but when I try to add it, I get 
      This action cannot be performed because the required table is missing. error. It works fine with ANSI version of the MySQL driver, sans the ability to work with Unicode of course. I've tried multiple combinations, including making the database and the table strictly "latin", it still refuses to add the table. The test database itself is very simple, just a single table and a single field, no spaces and no unicode characters in names. Test/test/test, basically, tested with an empty FM database. The server is running MariaDB 10.4.16. Any pointers to solve that?
    • By 34South
      I previously used ODBC Manager (32 bit)  to great success importing data directly from Filemaker Server to JMP. I recently upgraded to Catalina (MacOS 10.15.5) and knew that one of the casualties would be this ODBC utility. I downloaded the 64 bit ODBC manager from Actual Technologies and successfully installed it but get the following message when trying to open an FM database from within the ODBC interface in JMP:
      dlopen(/Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so, 6): image not found
      I have navigated to Actual Technologies' web site believing I should download an ODBC driver but this comes at a hefty price tag, especially when converted to my local currency. Given the increasing costs of maintenance contracts and SSL certificates I had hoped to avoid further expenditure. Do I really need this and is there an alternative?
    • By andyCodling
      Apologies if I've put this in the wrong place.
      My Filemaker solution uses a FM database and an ODBC connection to a MySql database that is used to serve data to a website.
      I have complete read/write access to the data in the MySql database from inside FM and thus control of data published on the site.
      I would like the MySql database to be able to access tables in the FM database in the same manner.
      I have set up an FM ODBC connection to our FM server and that is visible in my local ODBC Manager, have tested the connection and that is good, but I can't see any way in the MySql apps I have installed on my system to create a table in the MySql database using live FM data, in the same way that I can make a table in FM using live data from the MySql database.
      Is it possible to do this?
      My main MySQL app is SequelPro.  And occasionally MySQL workbench and associated tools.
      Thank you.
      Andy Codling
    • By TimP
      I have been attempting to connect to a SQL database on my network using Monkeybread Software and each time that I attempt to do it I have encountered the same error, which I have attached here. I have set up an ODBC connection as well as made sure that I had the names correct, however I have gotten the same error message each time. Thank you for your help.

    • By jim.weeda
      For about 12 years, we've had a FileMaker database import external data from a .mdb  (Access) database that has data that is downloaded from some equipment.  The process has survived several computer upgrades (Windows XP and Windows 7) and FileMaker version upgrades (created in 7 and currently running in 16).  It broke when we moved into the Windows 10 environment.  The script is set up to retrieve the data from the .mdb file and it says it can't access the file.  So when I dive into the script and try to specify the ODBC connection it now asks for a username and password.  We've never had a user name or password tied to either the .mdb file, the ODBC connection, or the FileMaker file.  But no matter what we try, we can't get past this step.  Is this a bug?  What am I missing here?
      Thanks in advance.
  • Create New...

Important Information

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