Jump to content

FM can't find specific record via ODBC


This topic is 2806 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 2806 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
×
×
  • Create New...

Important Information

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