3 Replies Latest reply on Jan 15, 2010 10:17 AM by philmodjunk

    Question about making portals with a query

    MDLInteractive

      Title

      Question about making portals with a query

      Post

      Hi Guys.

       

      So i'm new with Filemaker. I've followed a few video tutorials and am capable of making a decent layout with functionality. 

      Now im running into this problem and was hoping you can provide an solution ;)

       

      There is a customer, and he can have multiple orders.

      I have a table called orders. here are just the order name and price. Now every order has to have a status, with a status date. As it has to be possible to look in the history of an order (eg. status 3 on 1-12-09 and went to status 5 on 15-12-09) an simple field in the order table would not work. So i made another table, called status, and put an status ID, status label and status date in it as well a relationship orderIDfk linked to orderID in the order table, one to many)

       

      So now a order can have a status, and there should be a history of them. No problem.

       

      But now the catch:

      I need 4 portals beneith eachother:

      one portal showing the orders of customer 1 with status 4 as last made status

      one portal showing the orders of customer 1 with status 2 as last made status

      one portal showing the orders of customer 1 with status 8 as last made status  

      one portal showing the orders of customer 1 with status 1 as last made status

       

      But how can i make a portal show this information? I know the SQL query for it, but im totally stuck in filemaker. 

       

      So an customer can have multiple orders, and those orders have multiple statusses. But i want to know what order is in what status at the current moment.

       

      Is this possible (is hás to be) and how can i pull it off?

      Thanks in advance guys!

        • 1. Re: Question about making portals with a query
          philmodjunk
            

          Why multiple portals?

           

          You can set up your portal to show the entire log of status changes for the current order. You can specify a sort order for the portal so that the most recent status change is first in the list. You can specify a scroll bar for the portal if there might be more status changes logged than can be viewed at one time in your portal.

           

          Won't that do what you need?

          • 2. Re: Question about making portals with a query
            MDLInteractive
              

            Hi Phil,

            Thanks for helping. Your solution would work for a single order, unfortunatly i need to have an overview of multiple orders and their stausses. So Customer X has 4 orders, and i want to know wich of the 4 have a status of 10. Thats what i want to show in an portal.

            I hope that it is possible, as it will give alot more clarity to the application that i am building..

             

            Thanks again,

             

            Regards

             

            Mike

            • 3. Re: Question about making portals with a query
              philmodjunk
                

              You can set up relationships that use more than one pair of key fields. You can use this technique to set up one portal that "filters" for a specific status. You'd select a status from a drop down menu or a radio button list of status options and then only those would show in your portal. Or you could use the same basic trick to set up 4 different portals if you prefer.

               

              The relationships:

               

              Customer----<Orders---<Log  (one customer can have many orders, One order can have many log entries).

               

              Customer::CustomerID = Orders::CustomerID

              Orders::orderID = Log::orderIDfk

               

              Define a new field in Orders, (could be global), Status and make this relationship:

              Orders::OrderID = LogByStatus::orderIDfk AND

              Orders::Status = LogByStatus::Status

               

              A portal based on LogByStatus (has some source table as Log) will only show log entries that match Status. For a filtered portal, you'd just select different values in the Orders::Status field.

               

              For dedicated portals, you'd create one field in Orders for each status and link to separate table occurrences of Log.

               

              Many folks find the terms Table and Table Occurrence confusing. To learn more, click the following link:

              Table vs. Table Occurrence (Tutorial)