I have a table of "items" with columns for id and name, and this is related by id to another table for "problems", which references the id of a given item, and then includes problem types. I have a data collection layout for the items that allows you to create an item, and then use a portal to enter problems for that item, which populates the "problems" table. Sometimes an item will have a single problem, or it may have multiple problems, or it may have no problems. This works great for data collection, and works as expected; however, when I do reporting I get stuck with a problem.
I would like to report this data in a WebDirect interface that shows all items and problems. Since I have this relationship established, I can create a list layout (primarily so i can create summary headers) for the "items" table, and then include related fields from the "problems" table. However, if I do this, then if I search for a specific problem, on the related record, I sometimes get duplicate "item" entries in the results. This happens because an entry that has that particular problem type may also have another problem associated with it, and since I'm searching with respect to "items," the results will be of that item, and then all combinations of it and related records.
If I choose the related "problems" record to use as the table for the layout, and then include a field for the item itself (ie, its name), I can then search for only a specific problem type and get all items that had that problems, but this approach will only show problem records, skips all items that do not have any problems.
Overall, if I have an item "ball" that has two problems "flat" and "cracked" I will get the following in the report if I search for "ball":
If I have another item "chair" with no problems, then the report looks like this:
Searching for "cracked" in the related field will show this as the output:
This makes some sense given that I'm showing found "items" and both "ball" item entries share the same id (used for the relationship); however, its not what I want.
How can I maintain the relationship between these two tables, but then be able to report in a way that allows me to be able to show all item/problem combinations by default, but then search for a specific problem and have the results only show me the items and related entries that share that specific problem? Using the "items" table for the layout seems closest, if I can just find a way to eliminate "item" record entries in the resulting list view that did not contain the search term (ie, remove the "ball" entry that has "flat" as the problem when I've searched for "cracked").