1 2 Previous Next 17 Replies Latest reply on Oct 4, 2012 3:00 PM by hobbiesdeveloper

    Table Relationship Problem

    hobbiesdeveloper

      Title

      Table Relationship Problem

      Post

           Hello,

           I encountered some problems with my table relationship not correcting showing all the necessary records. I currently have List( order_polineitem_VENDOR::CompanyName ) inside a Custom Dialog Box and it's showing only 3 out of the 4 Vendors with Company Names.

           I have included a picture of the Table Relationship for this problem. Everything is linked correctly but for some reason not all the PO Company names are showing up.

           It's currently showing this in the Customs Dialog message box.

           John, Inc (UT)
           John, Inc (FL)
           John, Inc (PA)

           It's getting this information from the Vendors associated with the polineitem which is associated with the order.

           It's supposed to show:

           John, Inc (UT)
           John, Inc (FL)
           John, Inc (PA)
           John, Inc (PA)

           I have 4 Purchase orders not 3, we added an extra John, Inc (PA) which it's not showing up in the list.
            

           I'm not sure why it's only showing the 3 PO's out of the 4 PO's.

           If there is anything else you need please let me know and I will be happy to post just to get this problem resolved.

            

           Thanks.

      ScreenHunter_14_Oct._03_12.58.jpg

        • 1. Re: Table Relationship Problem
          philmodjunk

               From what table occurrence context (from what layout?) are you performing this show custom dialog step?

               Your calculation: List( order_polineitem_VENDOR::CompanyName )

               refers to a table occurrence that is NOT one of the two in the relationship that you show the details of in your screen shot so I am not sure why the relationship between Order_LINEITEM and order_POLINITEM_DELAYCONTROL would be relevant to the issue at hand.

               PS. Did you try using the mouse to resize the custom dialog to make it taller?

          • 2. Re: Table Relationship Problem
            hobbiesdeveloper

                 It's coming from the Orders Layout.

                  

                 It's actually  List( order_VENDOR_DELAYCONTROL::CompanyName ) in the Custom Dialog box.

                 Not sure why using the order_VENDOR_DELAYCOUNTROL relationship show only 3 companies instead of the 4 that i have in the Purchase Order Layout. If i do List( order_PO_DELAYCONTROL::v_InvoiceNumber it shows all 4 of them.

                  

                 Hope that helps.

            • 3. Re: Table Relationship Problem
              hobbiesdeveloper

                   I just add this inside the custom dialog box

                    List( order_PO_DELAYCONTROL::_kf_VendorID ) & ¶ & ¶  &
                   List( order_VENDOR_DELAYCONTROL::__kp_VendorID )

                   For the first list it shows

                   VEN01
                   VEN05
                   VEN06
                   VEN06

                   but for the second list it shows

                   VEN01
                   VEN05
                   VEN06

                   it's missing VEN06 not sure why it skips that.

              • 4. Re: Table Relationship Problem
                philmodjunk

                     to repeat: "A layout to which table occurrence is current when the show custom dialog is performed?"

                     Did you try making the custom dialog larger? Perhaps all that is wrong is that your list is too long to fit inside the dialog given its current size.

                • 5. Re: Table Relationship Problem
                  hobbiesdeveloper

                       The Custom Dialog is setup on the Orders Layout.

                       It's using the Purchase order Layout and the Vendor Layout to get results.

                       What do you mean by when the table occurrence is current?

                  • 6. Re: Table Relationship Problem
                    philmodjunk

                         DId you try resizing the custom dialog?

                    • 7. Re: Table Relationship Problem
                      hobbiesdeveloper

                           Yes, i tried resizing. Here is a screenshot of it just in case.

                           Not sure why it only shows 3 of the Vendor ID's from the order_Vendor side but the PO Vendor ID shows all 4.

                      • 8. Re: Table Relationship Problem
                        philmodjunk

                             I am talking about using your mouse to make the custom dialog taller after it appears on your screen. (It's not shown in your last screen shot.) Did you try that?

                        • 9. Re: Table Relationship Problem
                          hobbiesdeveloper

                               Yes, i tried that. Here is a screenshot of what is showing.

                          • 10. Re: Table Relationship Problem
                            philmodjunk

                                 I wanted to rule out the simple stuff before digging deeper.

                                 Apologies in advance for asking this question but how do you know that this additional value should be listed in the custom dialog?

                                 Your second list function is: List( order_VENDOR_DELAYCONTROL::__kp_VendorID ) and it is evaluating from the context of a layout based on Order as I understand it.

                                 The relationships involved are:

                                 Order-----<Order_LINEITEM>------<Order_POLINEITEM_DELAYCONTROL>----order_PO_DELAYCONTROL>----order_VENDOR_DELAYCONTROL

                                 Do you have two records in the data source table for order_VENDOR_DELAYCONTROL that both have a __kp_VendorID of VEN06?

                                 The long chain of relationships involved means that you have to carefully examine both relationships and the matching records of each intervening table occurrence to determine why some values are returned by the list and not others. If you can possibly structure a relationship with fewer intervening occurrences, it will be much easier to figure this out.

                                  

                            • 11. Re: Table Relationship Problem
                              hobbiesdeveloper

                                   Not a problem. I only have 1 record with VEN06, which is called twice in the PO_DELAYCONTROL, but for some reason only one is shown in the list.

                                   On the dialog box it shows all 4 Vendors for 4 Different Records being used for on the Purchase Order Layout, 2 of the Records in the Purchase Order Layout being called to the VEN06, but when you do a list view it only shows 1 of the VEN06 from the VENDOR_DELAYCONTROL Relationship option.

                                   I looked through all the lines and wasn't able to find anything that can be wrong.

                              • 12. Re: Table Relationship Problem
                                philmodjunk

                                     If there is only one record in VENDOR_DELAYCONTROL where the value is VEN06, then your list function can only return that value one time. The fact that you have multiple related records that in turn link to the same record in VENDOR_DELAYCONTROL will not result in them being listed more than once.

                                • 13. Re: Table Relationship Problem
                                  hobbiesdeveloper

                                       Oops, my mistake. Looks like i was thinking that i would get the result twice.

                                       One more question, do you know if it's better to make the chain relationships shorter and just go to layout to the Purchase Order Layout and use the fields from there than having to make long chain relationships from one end to the other and having problems like these in the future?

                                       Will it run faster using shorter relationship chains?

                                        

                                       I appreciate all your help :)

                                  • 14. Re: Table Relationship Problem
                                    philmodjunk

                                         I really haven't tried to evaluate why you have this set of relationships, I've just limited to the "big picture".

                                         Generally speaking, the less "moving parts" to your solution, the better. The simpler your solution, the easier it is to modify and maintain and while I haven't made any tests on this, I would expect simpler relationship chains to evaluate more quickly.

                                         But the other side of the coin is that this assumes that it is possible to set up a simpler/shorter relationship chain that correctly references the data you need to reference. This is not always possible or may require complicating other parts of your solution in an effort to simplify this part.

                                         ExecuteSQL in FileMaker 12 is proving to be a real "game changer" in the way that it can enable a developer to "strip down" their relationship graph to just the "backbone" relationships--leaving out many previously necessary occurrences having replaced them with an ExecuteSQL query instead.

                                    1 2 Previous Next