FM can't find specific record via ODBC

[eljefejb]

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!

[Wim Decorte]

Export the data to excel or CSV and inspect it there to see if anything is weird about it.

[eljefejb]

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?

[Wim Decorte]

any unstored calculations in that table that have 'expensive' formulas?

[eljefejb]

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.

[Wim Decorte]

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?

[eljefejb]

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.