5 Replies Latest reply on Jul 12, 2013 11:03 AM by Vicky

    How can two portals work together

    Vicky

      Title

      How can two portals work together

      Post

           Hi,

           I am new to Filemaker and the concepts of portals and table occurrences confuse me.  My database knowledge is really limited.

           On the order layout, I have created one portal(Portal A) based on the model table with two fields: model and quantity. Portal A works with a value list for factory id and factory name so that when a factory is selected,  Portal A will display all the different models from the selected factory and users can type in values for quantity. Each factory is supposed to be able to get multiple orders and to be able to have different quantities for the same models.

           Portal A displays models and quantity when a factory is selected, but each time I create a new order and use the same factory, the quantity remains the same and when changed, all the quantities for the model on the other orders change too. I do not know how to make it so that each time a new order is created, the quantity will be blank and will not affect previous orders.

           Portal A is supposed to work with Portal B so that  Portal B can act like a receipt where it displays the model and quantity selected in Portal A along with other information such as price and which factory the models are from.  Looking around on forums, I think I need to create a TO for Model, but I do not know how to connect it to the other tables. I have tried connecting it to the Order table using OrderID and creating a portal based on the new TO for Model, but nothing shows up in the portal.

           I have uploaded a screenshot of the order layout with the portals and the relationships between the tables without the new Model TO.

           I apologize for the long post. Any help will be appreciated,

           Thank you!

            

      Screen_Shot_2013-07-11_at_12.24.17_PM.png

        • 1. Re: How can two portals work together
          philmodjunk

               I had to think back. I've helped one other person with the same exact question:

               Your Tables and relationships might be set up like this:

          Model-----<OrderLineItems|ByOrder>------Order------<Model|ByFactory----OrderLineItems|byFactoryOrder

               Model::__pkModelID = OrderLineItems|ByOrder::_fkModelID
               Order::__pkOrderID = OrderLineItems|ByOrder::_fkOrderID
               Order::_fkFactoryID = Model|ByFactory::_fkFactoryID

               Model|ByFactory::gOrderID = OrderLineItems|ByFactoryOrder::_fkOrderID AND
               Model|ByFactory::__pkModelID = OrderLineItems|ByFactoryOrder::_fkModelID

               Enable "Allow creation of records via this relationship for OrderLineItems|byFactoryOrder in the above relationship.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               There are several tricky details to set up:

               gOrderID should be a field with global storage specified in field options. You'll need to set up the ONRecordLoad script trigger to automatically update it with the value of Order::__pkOrderID. You set up Portal A as a portal to Model|ByFactory with a Quantity field included from OrderLineItems|byFactoryOrder. When you specify a quantity in this field, a record in OrderLIneItems will automatically be created and will appear in Portal B--a portal to OrderLineItems|ByOrder.

               The blue Tutorial: What are Table Occurrences? are occurrences of your OrderLineItems (OrderJoinFactory) table. The green are occurrences of Model.

          • 2. Re: How can two portals work together
            Vicky

                 Thank you Phil!

                 I tried your suggestions, however I am still having trouble with making the portals work together, because I do not think I am following your instructions properly.  

                 For connecting Model|ByFactory and OrderLineItems|byFactoryOrder, I am unsure about how to create the one to one relationship. I just made the foreign keys unique in validation tab.  Does that create problems? Was I suppose to create serial numbers for the foreign keys as well or was I suppose to do something else?

                 For the layout, I found the OnRecordLoad script trigger, but I am not sure how to make it automatically update gOrderID. I created a new script and called it OnRecordLoad and specified it in the script trigger.  The script has

                 Go to Field [Model|ByFactory::gOrderID]

                 Set Field [ Order::_pkOrderID]

                 The value list to choose the factory does not seem to be working anymore, but I cannot tell because Portal A is not working.

                 Any suggestions on how to fix the problems?
                  
                 I will attach a picture of the updated database.
                  
                 I really appreciate your help.
            • 3. Re: How can two portals work together
              philmodjunk

                   Foreign key fields should never be constrained as unique nor should they be auto-entered serial numbers. They get their values from the primary key of a matching record in a related table. OrderID in OrderJoinFactory cannot be constrained to unique values as you can have many records in OrderJoinFactory that must link to the same record in Order.

                   Your script should read:

                   Set Field [ Model::g_OrderID ; Order::_pkOrderID]

                   When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

              • 4. Re: How can two portals work together
                Vicky

                     Most of the portal work now. Thank you so much!

                     I also changed the field for Factory Code (drop down box) from Factory:: _pkFactoryID to Order:: _fkFactoryID, because it stopped working with the ID from FactoryTable.  I don't what happened, but I can select most of the factories just fine, but I am unable to select certain factories. For example:

                     If factory1 is currently in factory code, when I try to select factory 3, it still stays at factory1 but when I try to select factory11, I can select it with no problem and everything works fine.

                • 5. Re: How can two portals work together
                  Vicky

                       I fixed the factory code problem. :) Thank you very much Phil! You are awesome!