Searching records on a MySQL db

[plh212]

Created a layout to display a complete record pulled from different MySQL tables with no problem only for one table. The relation is a one-to-many and no matter what I tried scripts, searches with AND in record searching I cannot pull the correct record.

Each of my products is associated with one, two, three or more categories. One of these categories is marked as "Main Category" and has a "Y" it the field "Main". I only one to display the main category in my layout (I am using it as a header to have together in the same category all the related products) for each of the record being displayed.

So we have:

Products Table with a unique Product_ID and Products_Cat with 3 fields: Product_ID, Category_ID, and finally Main with value of Y or N,. None of these fields being unique by itself, only the combination of the three is unique.

Example:

345609, 220, N

345609, 245, N

345609, 223, Y

120067, 220, N

120067, 245, Y

120067, 238, N

120067, 223, N

The pointer stays on the first record found in Products_Cat and does not display the correct category for the product. The table is indexed on Product_ID and Category_ID.

I tried having among other thing a match search with Product::Product_ID = Products_Cat::Category_ID AND Products_Cat::Main = "Y" but that does not do the trick.

Some help would really appreciated.

Filemaker Pro 11

MySQL db

[bcooney]

Hi. I would structure this a bit differently. You have Product->ProdCat<-Cat, right? I would store the "Main" CategoryID in the Product record, _kF_CatID_Main. Then, you can report from Product, and you do not need to worry about more than one join record marked as the "Main" category.

You can also create a portal on the Category form to show all the Products that have that CategoryID as their Main:

Category ->Product by CatID=_kF_CatID_Main

hth,

Barbara

[plh212]

Hello Barbara,

That's an excellent suggestion and I wished the cart software designer had thought of it, unfortunately I must deal with existing tables and fields already setup and can only access information with what we do have. Reconfiguring the tables would means re-configuring a lot in the program itself. Unfortunately not an option. Thank you for your help.

Philippe