1 Reply Latest reply on Aug 19, 2016 7:16 PM by philmodjunk

    Help on finding related records of only one value across a relationship

    tkessler45

      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":

       

           item::name problem::type

           ball           flat

           ball           cracked

       

      If I have another item "chair" with no problems, then the report looks like this:

       

           ball           flat

           ball           cracked

           chair

       

      Searching for "cracked" in the related field will show this as the output:

       

           ball           flat

           ball           cracked

       

      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").

        • 1. Re: Help on finding related records of only one value across a relationship
          philmodjunk

          You might set up a scripted find where users enter or (better) select the problem type in a global field and then your script can do your search on the item table and a portal filter can reference the global field to only show problems of the specified type. That will list all items with the specified problem. If you want to include all items, but only show a specific problem if it exists, filter the portal without performing any find.

           

          If you are only specifying one problem type at a time, use a one row filtered portal.

           

          Hmmm, come to think of it, you could also use a relationship with that global field as a second field that matches to problem type in the problems table and not have to use portal filter--which may result in a layout that updates faster.