11 Replies Latest reply on Aug 28, 2013 6:35 AM by philmodjunk

    Portal_relationship problem

    tays01s

      Title

      Portal_relationship problem

      Post

           To use generic terms, I have:

           - Order table related to

           - Product table. This table contains compositions of products.

           Both tables are represented in portals on a layout. I can select a particular order. There can be 1 or more products per order and other fields in the product portal permit me to specify what quantity of each product.

           Relationship: I've related __Product ID to Order::_ProductID (fk) because each product may be used in many different orders.

           Product portal: Because there are many products, I'd like to use "+"/ "-" buttons to add a row in which I can specify the next product and amount.

           A) the product pop-up is giving a not modifiable error + I think this scenario must need an intermediate table somewhere, but I've not figured out where/ how.

        • 1. Re: Portal_relationship problem
          philmodjunk

               Seems like you have two tables where you need three:

               Orders----<LineItems>-----Products

               Orders::__pkOrderID = LineItems::_fkOrderID
               Products::__pkProductID = LIneItems::_fkProductID

               And you'd use a portal to LineItems, not products for listing the produces for a given order.

               You can examine the Invoices starter solutions that come with FileMaker 11 and 12 to see working examples of this set of relationships though the table and field names will differ from what I have used here.

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

          • 2. Re: Portal_relationship problem
            tays01s

                 Thanks, I thought the problem sounded familiar.

                 On the Order layout I've got the portal for 'Line items' partially working: The pop-up value list for products functions and I can add/delete these items. However, there's a Product unit field I'd like shown in the portal. I've tried making the 'Line item: Unit' = Product::Unit, but the portal field remains emptyi.

            • 3. Re: Portal_relationship problem
              philmodjunk

                   If this is just to display data from that field in the product field, you can just add that field from Products to your portal row. If you need to copy that data into Line Items so that future changes to the field in Products do not change the value in existing line items (Pricing works this way), then a field in line items can be set to use either an auto-enter calculation or a looked up value setting to copy the value over from the corresponding field in products.

                   In neither case should you include this field as part of your relationship.

              • 4. Re: Portal_relationship problem
                tays01s

                     I've tried putting both fields into the portal:

                     - Product::Unit field and

                     - Line item:Unit where this is calculated as Product::Unit

                     without getting anything in the Unit field in the portal.

                     BTW relationships permit creation/deletion of records in Line items for both:

                     Order < Line items and Line items > Products.

                • 5. Re: Portal_relationship problem
                  philmodjunk

                       "Allow creation..." has nothing to do with this and I would not enable it for either table in the line items to products relationship.

                       Either your line item record correctly matches to a record in product or it does not. If it does not match to a record in product, any field from Product that you put in the line item portal row will be empty. If it matches, that field will show data from the related products record.

                  • 6. Re: Portal_relationship problem
                    tays01s

                         The problem was that my _ProductID (fk) wasn't numbering. I've put in a calculation of _ProductID=Product (ie. the one chosen from the Product value list). Seems to work. Is it best to keep fk's like _ProductID as 'prohibit modification during user entry'?

                    • 7. Re: Portal_relationship problem
                      philmodjunk

                           LineItems::ProductID should be the field that you format as the value list for selecting a product in your portal row and this should also be the match field that links to LineItems to Product.

                      • 8. Re: Portal_relationship problem
                        tays01s

                             Odd that my workaround actually worked; you're correct. Users need to check that Layout > Data > Data formatting > set to general, otherwise text won't display.

                        • 9. Re: Portal_relationship problem
                          philmodjunk
                               

                                    Users need to check that Layout > Data > Data formatting > set to general, otherwise text won't display.

                               That should not be the case. Is there any chance that you are trying to put text in a field of type number?

                          • 10. Re: Portal_relationship problem
                            tays01s

                                 So you mean I should have formatted my _ProductsID(fk) in the Listitems table as text to make it compatible with values from the 2nd field shown via the value list?

                            • 11. Re: Portal_relationship problem
                              philmodjunk

                                   No. I thought you were referring to the units field from products.

                                   the data format specified for the ID field--which should be of type number, has no effect on what data will appear in other fields on the layout as this does not change the value of the field.