13 Replies Latest reply on May 29, 2015 4:48 PM by burghfan

    Table Relationship Issue

    burghfan

      I have a solution to track two different types of orders; "Commission Orders" or "Distributor Orders"

       

      New "orders" can be placed by three different types of Clients; "Customers", "Contractors" and "Engineers"

       

      Commission Orders are tracked in the "Comm Tracked Orders" table and Distributor Orders are tracked in the "Distr Tracked Orders" table

       

      I can get all three Client tables (Customers, Contractors & Engineers) to relate to the "Comm Order Tracking" Table and to the "Distr Order Tracking Table".

       

      But only the "Customer" Client table will all me to create a relationship to the "Distr Order Tracking Table"

       

      New Orders (either Commission or Distributor) are enter via a Portal within the Client table. The "Comm Tracked Order" Portal" is fine in all three Client tables, the "Distr Tracked Orders" Portal functions the same only in the "Customer Table" but since I cannot get the the "Contractor and Engineer" to relate in the "Distr Tracked Orders" table the portal won't function.

       

      In the both the "Comm Tracked Orders" & "Distr Tracked Orders" Tables I have fields labeled; Customer ID, Contractor ID and Engineer ID

       

      Each Client table has an ID field (serial number) corresponding to that table i.e.; Customer ID in the Customer table, Contractor ID in the Contractor table and Engineer ID in the Engineer table.

       

      Any help would be appreciated thank you.

       

      See the attached screen shot

        • 1. Re: Table Relationship Issue
          erolst

          What you're trying to do would create a circular relationship, since there already are (albeit indirect) relationships from the other two TOs (“Contractors” and “Engineers”) to the “Distr Order Tracking Table” TO. A path between any two TOs must always be unambiguous!

           

          To solve your issue, you would need to create two new TOs of that table (each with a unique name) and add one of each to the “Contractors” and “Engineers” TO, respectively.

           

          But let me suggest a much easier structure …


          Since you only have two basic entities, you only need two tables: “Clients” and “Orders”. You can distinguish their respective sub-entities by type/category * (which you could store in a table to encode them as entities in their own right).

           

          So you'd have …

           

          • Clients: clientID_pk, clientType(ID)

          • Orders: orderID_pk, clientID_fk, { clientType(ID)_fk, } ordertype(ID)_fk.

           

          Now apply your business rules to determine which types of orders a user may create, depending on their type; use a script to create new Order records, rather than using an “Allow creation” relationship, so you can easily enforce these rules.

           

          * With your current setup, how would you handle the requirement to integrate new types of clients and/or orders?

          • 3. Re: Table Relationship Issue
            erolst

            You were saying ... ?

            • 4. Re: Table Relationship Issue
              burghfan

              Thank you for the response, I can work with either solution.

               

              I am curious as to why the TO of the "Comm Order Tracker allows all three "Clients" to be related in one TO while the TO of the "Distr Order Tracker" only allows the "Customers" table and not the "Contractor and "Engineer" tables.

               

              Whats the difference?

              • 5. Re: Table Relationship Issue
                erolst

                burghfan wrote:

                Whats the difference?

                I assume it is simply a matter of timing, not anything inherent in the tables/TOs: had you connected all three Client TOs to “Distr Tracker” first, then now it would be “Comm Tracker” that only allows one of the clients to be connected …

                 

                Anyway, it's a matter of circular relationships, or, more to the point, preventing them, to which end you need to add more TOs; but then …

                burghfan wrote:

                I can work with either solution.

                … a better structure is not simply a solution to your current issue, it will make building, maintaining and extending your database much easier down the road.

                • 6. Re: Table Relationship Issue
                  burghfan

                  Thank you. I'll start a rework with your 2nd suggestion while using the 1st as a way to keep the work flowing.

                   

                  I had set it up with the 3 types of clients; thinking that even though one type of "Client" may place the order the other 2 types of clients may be connected to the order.

                   

                  Example: Contractor: XYZ Construction provides the order, Engineer: Acme Engineers designed the project and Customer: Thrashed Wheat Brewery ends up with the product installed at their location

                  • 7. Re: Table Relationship Issue
                    erolst

                    Instead of indicating a specific Client's role by the foreign key field they're placed in (and thus necessitating three relationships to look up all Clients of an Order / all Orders of a Client), you could use a join table:

                     

                    Clients --< Participation (clientID_fk, participationType(ID)_fk, orderID_fk) >-- Orders

                     

                    where, just like with the Clients themselves, you indicate a Client's actual role within the Order by a participationType(ID) (which probably would be the same as the clientTypes(ID)s).

                     

                    This way you only need one relationship to see all Orders of a Client and vice versa; you could even allow a given Client/Contact to play multiple roles in a single Order, or multiple Clients playing the same role in one Order …

                    • 8. Re: Table Relationship Issue
                      burghfan

                      I created the new TO however when I select a button in the new order to go back to that related record i get the attached error

                      • 9. Re: Table Relationship Issue
                        burghfan

                        I see where that will need to be done, the way I have it setup keeps creating issues.

                         

                        Thanks for the responses.

                        • 10. Re: Table Relationship Issue
                          erolst

                          Is that a button that goes to a related record? If so, you need to add another script that targets to the new TO, or abstract your existing script (w/ script parameters, or identifying the underlying table and branching accordingly …)

                           

                          Cannot say more because I don't know your workflows, and how you designed your interface (and I need to peek at your screenshot to remind myself what is what in the structure); regardless, the upshot is that you must provide these (and other) functions in triplicate, due to your using multiple tables for the same entity …

                           

                          I would suggest that the next thing you do is to consolidate these tables; if done systematically, the entire process is (relatively) pain-free and can be achieved quite swiftly.


                          Should you need assistance, contact me via PM.

                          • 11. Re: Table Relationship Issue
                            burghfan

                            Hi I decided to take your advice regarding using a pop over button & portal versus a value list and a drop down menu.

                             

                            This is for a products list that will populate a field within an "prop item" portal for an estimates table.

                             

                            I have the pop over working great as far as opening the products that are related to a specific manufacturer.

                             

                            What I did was created the script below which I attached to the the field as a button. The selected field gets copied and pasted fine, but none of the other fields such product description or price get populated and the next time I select a product it just over writes the pre-existing paste.

                             

                            Any suggestions

                             

                             

                            Screen Shot 2015-05-29 at 5.23.49 PM.png

                            • 12. Re: Table Relationship Issue
                              erolst

                              burghfan wrote:

                              The selected field gets copied and pasted fine

                              Any suggestions

                              Sure:

                              1: Don't use Copy & Paste unless you absolutely have to.

                              2: Don't use Copy & Paste unless you absolutely have to.

                              3: Don't use Copy & Paste unless you absolutely have to.


                              n + 1: If you have several portals on a layout (or even if you don't), give each an object name.


                              Your script should rather look like


                              Set Variable [ $itemID ; Products by manufacturer::Item ID ]

                              Go to Object [ "propItemPortal" ]

                              Go to Portal Row [ last ]

                              Set Field [ PropItem::id_product ; $itemID ]

                              Go to Field [ PropItem::quantity ]

                               

                              burghfan wrote:

                              but none of the other fields such product description or price get populated

                              How could they? You're not setting them in the script …

                               

                              What you (probably) should have are auto-enter calcs in the line items table where this data is copied from the related Item/product record (which is related as soon as you enter that foreign key in your script).

                               

                              burghfan wrote:

                              the next time I select a product it just over writes the pre-existing paste.

                              This means that the last record is not what you think it is …

                               

                              Make sure your portal is based on a TO that has "Allow creation of related records…” checked in its relationship setup, and has a scrollbar … which means that you may have to create one TO/relationship for a display portal without Allow, and one for creation with Allow. This setting can neither be set per portal, nor programmatically.

                               

                              In light of this you would have:

                               

                              Set Variable [ $itemID ; Products by manufacturer::Item ID // selection portal ]

                              Go to Object [ "propItemPortal_create" // based on TO PropItem_create with "Allow" ]

                              Go to Portal Row [ last ]

                              Set Field [ PropItem_create::id_product ; $itemID ]

                              Set Variable [ $lineItemID ; PropItem_create::id // why every table should have an auto-enter serial ID! ]

                              Go to Object [ "propItemPortal_display" // based on TO PropItem without "Allow" ]

                              Go to Portal Row [ first ]

                              Loop

                                # find freshly created line item record

                                Exit Loop if [ propItemPortal_display::id = $lineItemID // this one! ]

                                Go to Portal Row [ next ]

                                # no exit after last because we know this record exists!

                              End Loop

                              Go to Field [ PropItem::quantity ]

                               

                              Let (

                                indispensable = "FileMaker Pro Advanced" ;

                                Case (

                                  PatternCount ( $myTools ; indispensable ) ;

                                  DoUse ( List ( "Script Debugger" ; "Data Viewer" ) ) ;

                                  Get ( indispensable )

                                )

                              )


                              PS: Wasn't your original question already answered, like, some time ago?

                              • 13. Re: Table Relationship Issue
                                burghfan

                                The example below worked , thank you very much

                                 

                                Set Variable [ $itemID ; Products by manufacturer::Item ID ]

                                Go to Object [ "propItemPortal" ]

                                Go to Portal Row [ last ]

                                Set Field [ PropItem::id_product ; $itemID ]

                                Go to Field [ PropItem::quantity ]