Find limited to only 3 records

[plh212]

Is there something I'm missing in FM9 limiting the return of a Find to ONLY three records?

No matter what I'm searching I am only getting three records from a table (containing many more matches). This layout (used now in FM9 has been modified from a FM8.5) and I'm using portals to read a MySQL db.

[Raybaudi]

It's not clear to me if you want a limited FIND ( while in find mode ) or a limited result in a portal.

The second is as simple as make a portal of only 3 rows.

The first need a script like this:

Set Error Capture [ On ]

Enter Find Mode [ ]

Set Field [ yourTable::yourField ; "your search criteria" ]

Perform Find [ ]

...( here manage the eventual error ) ...

Go to Record [ No dialog ; 4 ]

Omit Multiple Records [ No dialog ; Get ( FoundCount ) - 3 ]

[LaRetta]

I'm using portals to read a MySQL db.

I do not believe that ESS data placed in the graph can be indexed by FileMaker. At least it can't be indexed enough to be the child side of a relationship nor used in value lists.

BTW, this is not called a find but rather filtering a relationship. A regular find works, right? It should although it usually is slower than an indexable find.

Edited October 30, 2008 by Guest
Added BTW

[plh212]

I am looking at an UNlimited search. Right now ANY search (Ctrl-F) in ANY fields return ONLY three records even if more exist.

[plh212]

BTW, this is not called a find but rather filtering a relationship. A regular find works, right?

A regular Find (Ctrl-F) returns ONLY three records, no matter how many records match the criteria. I mean the output is showing three records as long as there is at least 3 records that match of course (if less it is obviously showing less).

[comment]

I'd suggest you import the data into a local table and see how well the find works there.

[plh212]

I'd suggest you import the data into a local table and see how well the find works there.

The layout I am using has been working flawlessly in FM 8.5 - Including the Find. I recently updated it to FM 9 and instead of exporting data through scripts into various tables of my MySQL db I am now using portals to read and update data directly into MySQL.

[comment]

But you are saying it doesn't work now - so if you want to get to the bottom of this, you have some troubleshooting to do. The way I see it, it can be one of two things:

1. something's wrong with your ODBC connection; or

2. your expectations do not match the facts.

To eliminate one of these, you should import the data into a Filemaker table and change your references to point to it instead of to the ESS - so that you have EXACTLY the same situation with a local source.

BTW, you keep mentioning that you use portals, but you don't explain how.

[Vaughan]

There was a change of behavior introduced in FMP 9 that might be causing this. It's detailed in the FMP 9 Read Me file.

Interestingly, the change was made so that FMP's behaviour aligned more closely to that of standard SQL databases.

[plh212]

There was a change of behavior introduced in FMP 9 that might be causing this. It's detailed in the FMP 9 Read Me file.

I just finished reading this document. It's a rather short document (6 pages) and I could not find anything to this behavioral change you mentioned. Did I miss something or am I reading the wrong document?

[plh212]

To eliminate one of these, you should import the data into a Filemaker table and change your references to point to it instead of to the ESS - so that you have EXACTLY the same situation with a local source.

I had kept a Duplicate Layout but this one is pointing out to a db on my Desktop. Find returns ALL records containing the criteria searched.

[comment]

That's not the same thing as what I suggested.

[plh212]

That's not the same thing as what I suggested.

I am not sure I understand your point or what you are trying to have me achieved. For the past four years this layout (starting in FM7 and updated to FM8.5) has been working flawlessly (with this same exact db) in search no matter what the criteria is.

Since I updated one month ago to FM9 to take advantage of connecting directly with MySQL db and get instant updates I ran into all sort of problems. At this point I am not sure if I should not go back to FM8.5 and exports, the way I used to do, the data in .csv (using scripts since I have six tables to feed in MySQL db) and then importing these files into MySQL (I am using Navicat as front-end).

[David Jondreau]

But it's not the same layout. You've changed it to work with ESS. When you changed the TO the layout is based on did you change the TO source for the fields too?

[comment]

I am not sure I understand your point or what you are trying to have me achieved.

I am trying to move this forward. This will not happen unless you bring in some more information. It's a basic rule of troubleshooting: change only one thing at a time. You are comparing different layouts, different TO's and a different data source - all at the same time. How can we be sure that your layout is not corrupted, for example?

And you still haven't explained what role do portals play in all this.

[plh212]

To eliminate one of these, you should import the data into a Filemaker table

I just tried to import ALL records (1600 about) and built a Query to Import ALL records from ODBC into a new table. Guest what? ONLY 3 records were imported ! The first three. Why am I hitting a limit of 3 here as well?

When browsing I can view all my records one at a time though.

[plh212]

And you still haven't explained what role do portals play in all this

This layout is looking into SIX different MySQL tables. I have setup portals to read data from each table and display them at once in ONE FM layout.

[plh212]

...work with ESS. When you changed the TO the layout is...

I know what ESS is, but what is TO?

[comment]

A TO is short for Table Occurrence.

I still don't understand the portals' role in this. You say your layout has portals looking into ESS data - but what is the layout's base table? Any find you perform will look for records in the layout's table only.

[Vaughan]

I just finished reading this document. It's a rather short document (6 pages) and I could not find anything to this behavioral change you mentioned. Did I miss something or am I reading the wrong document?

From the FMP9 Read Me document:

11.12. In the previous versions of FileMaker, some predicates in a relationship would be ignored if they had an empty value. This could result in finding records which don’t match the relationship criteria, even finding all records in the related table. Predicates based on empty values will no longer match to any other records. Any databases which depended on the old behavior will need to be modified to use a different relational design. This change was made to match standard relational behavior in SQL databases.

[plh212]

I still don't understand the portals' role in this.

If I understood correctly and please correct if I am wrong. After setting up ESS one can set setup a layout with "Show Records From: .

But I realized setting up a layout this way one can only look at one table at a time.

If on the other hand I set up each field in the Field/Control Setup with "Display as: Edit Box" and "Display Data From: I can then have one layout showing data from my all six MySQL tables in one layout. Of course each table has a relationship built with "ProductID" in order to show the correct data.

[plh212]

11.12. In the previous versions of FileMaker, some predicates in a relationship would be ignored if they had an empty value. This could result in finding records which don’t match the relationship criteria, even finding all records in the related table. Predicates based on empty values will no longer match to any other records. Any databases which depended on the old behavior will need to be modified to use a different relational design. This change was made to match standard relational behavior in SQL databases.

Yes I read it. That was the very last paragraph of the document I agree that this is, as you mentioned, a change of behavior but I failed to see how it relates to the limitation in showing 3 and only THREE records with ANY search criteria and now even when IMPORTING as stated in a previous post -- My import earlier today was limited to 3 records with a Query for ALL records to be imported.

[comment]

If on the other hand I set up each field in the Field/Control Setup with "Display as: Edit Box" and "Display Data From: I can then have one layout showing data from my all six MySQL tables in one layout.

Yes, but then performing a find in this layout becomes rather meaningless. I am coming to suspect that your layout is based on a table that has three records, and that you keep finding those three records over an over again.

If you want to find records in your SQL source, you must do this in a layout that's defined to show records from that source. Finding on another layout, using related fields, will look for LOCAL records that have related records that meet the search criteria.

[plh212]

Yes, but then performing a find in this layout becomes rather meaningless. I am coming to suspect that your layout is based on a table that has three records, and that you keep finding those three records over an over again.

Ok. Excellent point !

But allow me to disagree:

A - My db contains 1579 records all of them products from about 30 different suppliers. When searching with a supplier's name as a criteria I am only getting 3 and ONLY 3 records!

B - It also does not explain why when I am importing ALL records (that should be 1579 records total) from the MySQL products table into a new FM table I am only getting 3 records in the new table!

C - I just created a new layout using "New Layout/Report" --> showing records from MySQL_productstable using Standard form layout --> Specify fields --> Move All --> Finish. ALL records from the products table are showing. Browsing: no problem showing ALL records. FIND supplier=mysupplier (which has 31 products in this db)... finding... 3 records !!

Anyone has any idea what's wrong with this scenario?

[comment]

This is good progress. Now I would suggest you try this:

1. Go to your new layout (showing records from the MySQL_productstable) and do Show All Records.

2. Import records from MySQL_productstable into a NEW table.

3. Repeat the find in the new table.

[plh212]

This is good progress. Now I would suggest you try this:

Ok. Done.

Result:

Total Records Added/Updated: 3

Total Records Skipped Due to Errors: 0

Total Fields Skipped Due to Errors: 0

Table Created: TestLP_DB3

3 (Three) is the Magic Number! :

[plh212]

OK. I Found the culprit.

The ODBC driver (Actual Technologies) installed over the Summer by one of our intern was actually a Demo version !!

When you buy a license of an ODBC driver, the 3 row limit per query limitation will be removed. You will also have access to free minor upgrades. We will send your license key(s) to you at the e-mail address you supply below.

Simple. I am surprised no one thought about it or mentioned it. But hopefully this will serve a number of other folks facing the same situation.

[plh212]

Comment: It does look like we were posting at the same time but my post got online 8 seconds before yours and you did not see it. In any event I can confirm that EVERYTHING is working flawlessly and that I can now see and access more than 3 (three) records.

Thank you ALL for your time and help. Cheers.

[bcooney]

wow, what a great thread on troubleshooting. Brings back memories of fighting with an email plugin, only to realize after days that I was in demo mode and it timed out after 30 mins. It wasn't the mail server, the host, the firewall, the authentication settings, the password...lol.

The more you know, the harder troubleshooting is!

[comment]

The more you know, the harder troubleshooting is!

Then I guess I don't know much…

The way I see it, it can be one of two things:

1. something's wrong with your ODBC connection; or

2. your expectations do not match the facts.

[bcooney]

Ah, yes, Michael. You get to take the hamster home this weekend. :)