Creating a Report with related tables (Solved)

[jackmatt2]

I am making a report to show products supplied by a supplier.

When I create the report, I select the different fileds from both the reports table and the suppliers table but when in browse mode, the fields of the relating table are never populated. It is as though the relationships are not working in reports but work elsewhere in portals etc.

what am I doing wrong?

See the attachement for relationships.

Edited August 24, 2010 by Guest

[bruceR]

"What am I doing wrong?"

That is impossible to tell at this point; we don't know what you're doing.

Please post a copy of your file. A clone, empty of data would be fine. Or a stripped down version or simplified example if you prefer.

[jackmatt2]

OK, I have attached the file so far.

Basically want I want to do is create a report that displays each supplier and their respective product names, including the first ProductResource (An image). So at least three tables will be included. I may also include the Category table also.

I will then use this when I go to the suppliers to buy items, I can just show them the report images as I buy them from markets and they don't have product ID's etc.

This will be printed off.

ebayInventory_Clone.zip

[bruceR]

Something like this.

ebayInventory_Clone.fp7.zip

[jackmatt2]

thanks

Well closer than I got, notice that the supplier name is still not showing. It has an orange circle next to it in the layout view, I think this means that it is not being resolved properly.

I also tried adding the category name but that had the same problem even though we have the foreign key relationships setup.

Edited August 23, 2010 by Guest

[jackmatt2]

Ok, I have figured out what is going wrong but I don't know how to fix it.

From the Products Table, all references to the product id are resolving properly (ProductResources and ProductItems). For the Suppliers and Categories it is not working because they have a foreign key relationship.

So the question is, how do you resolve these types of relationships in FileMaker.

For clarification, if you make a report using Products table.

Products::id = ProductResources::id (resolving properly in a report)

Products::category_id = CAtegory::id (not resolving in a report)

[comment]

You need to enter the category ID into Products::category_id - not the category name (check your value list).

[jackmatt2]

Ahhh yes, good point.

I thought that the text was an alias to the actual number because the field was set to Numeric only to find that this is not enforced.

I changed the list to store the ID and display the name. The problem is that when you select your supplier, the id shows, not the name of the supplier. Is it possible to fix this?

Edited August 23, 2010 by Guest

[comment]

You can use a pop-up menu instead of a drop-down list, and set the value list to show only the second field (this requires the second field to contain unique values only). Alternatively, place the related name field over the drop-down, and make it non-enterable.

[bruceR]

thanks

Well closer than I got, notice that the supplier name is still not showing. It has an orange circle next to it in the layout view, I think this means that it is not being resolved properly.

I also tried adding the category name but that had the same problem even though we have the foreign key relationships setup.

No, the supplier name very definitely IS showing. Run the script. Look at the details of the layout.

It sounds like you need to learn some very basic things about summary reports. In order for subsummary sort-by parts to show, you must sort by the specified field.

I suggest you complete the tutorial that comes in your FileMaker install folder.

[jackmatt2]

@command and @BruceR

Thanks, it turns out that @command was correct and I was using a text value instead of the id in the value for the field. All is fixed now, cheers, thanks for the help.