The proper solution for this problem ("this problem" being a many-to-many relationship between Companies and Categories) is to use a join table to associate a company to a category - with each such association being a separate record in the join table. See an example here:
Once you have that, you can print your report using a list layout of the join table, sorted by category, with fields from Companies placed in the body part and fields from Categories placed in a sub-summary part.
1 of 1 people found this helpful
What you're dealing with here is called a many-to-many relationship. A given business can be associated with many categories; a given category can be associated with many businesses. Hence, many-to-many.
There are two ways of dealing with such a relationship in FileMaker:
1) A multi-key. (A what?) This would be a key field in the (probably, in this case) Business table where you would enter the key fields for each category in a return-delimited list, like this:
(Assuming you were using serial IDs, and 1, 4, and 5 corresponded to the three categories attached to this business.)
What FileMaker will do with this is, when you establish the relationship from the business key field to the category, it will see a relationship from categories 1, 4, and 5 to this business. You can then create a report from the Categories table and, when you show the related businesses from each category, they will show up.(Note: You'll probably need to do this using a calculation field; a portal would also work, but is difficult to pull off when printing.)
2) A join table. This is a table that sits in between the two tables and turns the many-to-many relationship into a pair of one-to-many relationships. Each record in the join table represents a combination of business and category. For example, if business 1, 4, and 5 are attached to category 1, and businesses 3, 5, and 6 are attached to category 2, you'd have these three join records:
Business ID Cat ID
In this way, you can create your report from the join table. It will show any related information you need about either the category or the business, and you've effectively duplicated the business and category records for display purposes.
The question comes down to advantages and disadvantages of each approach. Key field management is really the biggest issue, in my mind. With the multi-key, you add and remove items from the key fields to represent making and breaking relational joins. With the join table, you add and delete records from the join table.
Personally, I'm a bigger fan of the join table approach. I find it easier to grasp and more reliable. Trying to make sure you always have the right values in key fields in both tables I find difficult with the multi-key approach. I also find reporting to be easier and more natural with the join table. You can generally create your reports based on the join table, and things just work. With the multi-key, I find I end up having to create calculation fields to echo out what I need in the appropriate table - which is annoying. However, the join table does require adding another table to your data structure, and some people find it harder to understand.
1 of 1 people found this helpful
This is where a JOIN table is highly recommended.
call it business_categories
bus_cat_ID (auto-enter serial as every table should have one!)
Then you can PRINT from this join table (a layout). Since you can put the necessary related fields (from both business and categories) on the layout, you can have multiple businesses under multiple categories. It's what JOIN files are all about.
Move your categories to another table, use the drop-down list, if you will, but instead of setting a single field in the company, set a portal (with multiple rows). allow creation of the records to the join table and it will have all the records you need.
Yes, mochad, you'll need to devise a way to get existing "categories" and "companies" into the join file, but it's all scriptable and do-able. And works in FM versions, a ways back!
You have hit on a bit of a sore spot related to FileMaker...
If FileMaker Portals worked well when printing across page boundaries there would be ways to deal with your dilemma. Unfortunately Portals are virtually useless in a situation where they need to bridge page boundaries.
The real solution is for FileMaker to expand, the way they look at Context "at the Layout level". If FM added the ability to set layout parts based on a relationships (essentially making the Part like portal row)... It would open up a lot of doors to a more powerful FileMaker.
There are many other scenarios besides the one you presented where this would be valuable. For example account statements that need to show batches of accounts for transactions within a time range. There is no way (without creating new records) to display (from the transaction context) an account whom has a balance, Yet with whom also has no transactions in that time period. This is a fairly common financial situation.
I REALLY hate the idea of having to generate records to display a report or statement. In most cases one can "work around" the problem.
My 2 cents worth
You have hit on a bit of a sore spot related to FileMaker...
I don't think that's an accurate description. A join table is the proper way to resolve a many-to-many relationship in any relational database. If anything, Filemaker is more forgiving than other RDBMS applications by allowing a multi-key relationship to replace a join table in some (limited) situations.
I agree with you regarding the Join Table... And I also think Multi-key relationships are one the great features of FM.
But it still ONLY allows one relational context per layout... I would love to see the ability to have different relationships on different parts. Much like we can have different Portals from different relationships on one layout.
wsvp, can you clarify the "one relational context per layout"? Do you mean that a base table (table occurrence) is the context for the layout? In a way you do have different "relationships" on a sub-summary sorted by list layout. You could certainly have the categories in a sorted summary part and the companies in the body part (using the JOIN table, of course) for the report.
otherwise I'm not sure what you mean.
I guess what I am getting at is that you cannot mix records from more than one table on a single layout... A layout is based on the records from one table occurrence. I would like to be able to have the ability to have for example, the Body Part to be based on one table occurrence while a Title Header is based on another. This would give us the power and flexibility of the FM relational structure, combined with a better way to print it. Imagine the Body part as functioning like a bunch of portal rows. Only now you could sub-summarize those rows and print them across page boundaries.
I really feel this would be a more elegant way of dealing with many report issues. I guess this would be on my wish list for FM13.
Well, if you have a JOIN table and create a layout based on it, you can use all of the relationships to build your report. The Header has "one" relationship, the body another. you can put any field (related or global) on a layout. If you need " list " view, then think of body as the children and the other parts as parents, grandparents or whatever).
So, I guess you aren't understanding me or I'm not understanding you. There are way to do all sorts of things. Some are work-arounds and/or really cool tricks like Virtial Lists/Virtual Reports, Cross-tabs with calculations and/or filtered portals. And well, there's also the "create the report as HTML" and view in web viewer. OH, and export to another application (EXcel) via export or export with XML. Really! create a graphic for us illustrating what you want to do and I can bet there's someone who's done it in FileMaker....
I think this sort of drifts off a bit from the original post as the Join table is one way to deal with that issue. His post sort of struck a nerve for me as it could also be solved more elegantly "in my opinion" with this additional feature (one I have been hoping to see for close to 15 years).
The problem is not the Fields, its the Records. When you say the Header as the Parent and the Body as the Children, that is EXACTLY what I want... But, Unfortunately that is not how FileMaker works. You can put related fields or globals into any Part (ie, the Account in the header)... But it does not mean the Part is in that context. The layout is currently ONLY going to display "records" from one table occurrence (set in the Layout options). Thus only Parent records related to those child records can be displayed in the header. Globals don't solve the problem in this particular case. Let me see if I can explain this better...
Lets say we have an "Accounts" table and a "Transactions" table. Then we need a report/statement which needs to have all of the Accounts and Transactions together in a single Batch Style report/statement (totally in FileMaker). But, we only want transactions within a certain time frame (perhaps one month)... in addition we want the Statement/Report to include ALL of the Accounts with balances, even ones that don't have transactions in that date range.
Because the found set will have "no" Transactions in it "for that Account", the related account (header or sub-summary) will not be included in the report. Thus if they had a previous balance, do to "prior" transactions the Account would be missing because those Transactions are not in the Found Set. My current solution is to "Create" a Null transaction for each account which can have the date set to the correct value to be included (this establishes the relationship required.) I really don't like this approach, but I have not yet been able to come up with a better one.
Do to the high volume hundreds of thousands of Accounts and millions of transactions... (often thousands of Accounts and tens of thousands of transactions in a single report) it needs to be able to cross pages for each Account and page break at each account. Scripting them to run one account at a time is NOT an option, due to the output requirements (a single PDF file)... Append is way to slow for this volume, and Plugins are not an option either.
Keep in mind that I am trying to avoid creating any records or adding any more storage of any kind. I am aware that there are many work-arounds... I guess thats what I wish I could avoid. I don't believe the Join table will solve this as it would still have to be based off the Transaction table records.
Wow! I had no idea there would be so many people willing to help in just 30 minutes. THANK YOU ALL! I'm going to try to digest this all tomorrow and let you know how it goes.
we want the Statement/Report to include ALL of the Accounts with balances, even ones that don't have transactions in that date range.
Yes, that is indeed Filemaker's weak spot.
My current solution is to "Create" a Null transaction for each account which can have the date set to the correct value to be included (this establishes the relationship required.) I really don't like this approach, but I have not yet been able to come up with a better one.
I don't like this approach either - if only because it skews the transaction count. Not to mention that a transaction cannot have two dates - so that two users producing two different reports at the same time will collide.
I don't want to hijack this thread (even further... ) but surely there must be a better solution. Do you really need to list "thousands of Accounts and millions of transactions" in your report (how many pages does it come to)?
Both of the points you brought up are good concerns, but fortunately in this particular application only a single user can run the statements at a time and the count is not needed. In fact, the actual situation is even more convoluted than I described, but I wanted to keep the description simple.
The Statements typically have a few thousand accounts, and maybe 5,000 - 20,000 transactions. I never see the page count on my end, but I can imagine. Fortunately it is in the form of a PDF.
Couldn't you export the records and produce the PDF outside Filemaker?