3 Replies Latest reply on Nov 29, 2011 12:52 PM by philmodjunk

    Displaying a list of a certain status

    AJRobey

      Title

      Displaying a list of a certain status

      Post

      What tool would be best to use if I wanted to display data from my database in a list format on my front page.  For example, what would I use to create a little box that would show all of my orders that are in "DECLINED" status and their subsequent ID?  Such as the following:

       

      108483 DECLINED
      108488 DECLINED
      108499 DECLINED
      109999 DECLINED
      110987 DECLINED

        • 1. Re: Displaying a list of a certain status
          philmodjunk

          Use a portal with a filter expression that only permits related records with "declined" in the status field.

          Most often, the relationship for such a portal would be defined similar to this:

          LayoutTableOccurrence::anyField X PortalTableOccurrence::anyfield

          This matches any record in the layout's table to all records in the portal's table. The portal filter then omits all records except for those where the status field = "Declined".

          • 2. Re: Displaying a list of a certain status
            AJRobey

            These both use the same table though (TCM is the table name).

            So, TCM::First Name is a field.  TCM::CC Approval Status is the other field.  How do I do it if they are in the same table?  Can you give me an example using those two fields for CC Approval Status = DECLINED?

            • 3. Re: Displaying a list of a certain status
              philmodjunk

              You can relate a table to itself. This is called a "self join".

              Open manage | Database | relationships and click TCM to select it. Then click the duplicate button (two plus signs) to make a new table occurrence of the same data source table. You can double click this new occurrence box to open a dialog where you can change the name of the new table occurrence from TCM 2 to something more descriptive such as AllTCM or some such.

              You can then create your relationship by dragging from any field in TCM to any field in AllTCM. Doubleclick the relationship line to open a dialog box where you can change the default = operator to the cartesian join operatoer (X).

              Now you can add your portal to your layout and use:

              AllTCM::CC Approval Status = "Declined"

              as your portal filter expression.

              Note, since your layout is based on the TCM table you don't have to use a portal. I suggested that option because you wanted to "list it in a box".

              You can also set up this layout as a tableview or list view layout and just perform a find for all records where status is declined to list the exact same records. It just isn't a list you can display in a box like you specified.

              You may find this tutorial on table occurrences helpful in better understanding how they control the function of FileMaker database Tutorial: What are Table Occurrences?