12 Replies Latest reply on Aug 11, 2014 3:22 PM by philmodjunk

    Relational issue

    DavidDicken

      Title

      Relational issue

      Post

           I'm having some trouble figuring out why the address fields don't populate after I create the relation

           What I did was create a TO for the ShipTo , BillTo and SoldTo  sections of my sales order each getting a Primary Key UUID then created a foreign Key as a number in my SalesOrder TO for each of those. Then drew the connecting line creating a one to many for the Table Occurrences allowing records to be created and deleted

           I have tried to use a calculation:

           salesorder_CUSTOMERS_SoldTo::sold to City

           I have tried to use a Look up to no avail 

           I Think it might be the relation itself, but I'm not sure.

           It appears I can only send 1 screen shot per post. I have more should you desire. 

            I hope you can help.

           Thanks in advance.

           David

      FMP_Relations_Graph.png

        • 1. Re: Relational issue
          DavidDicken

               Sorry I sent you the Customer layout when in fact I was refering to my SalesOrder layout. Anyways here is a screenshot of the  sales order layout also

          • 2. Re: Relational issue
            philmodjunk

                 You can always make additional posts to a thread in order to upload more images.

                 The most immediate problem that I see is that the layout shown is based on Customers while your table occurrences are not linked to Customers in your relationships.

                 Is the layout shown, the one where you are trying to show the Ship To, Bill to, etc information?

                 I would think that you would be using a layout based on SalesOrder for that.

                 The SalesOrder table could, look up or auto-enter (both copy the data) from these tables via your relationships. And on a layout based on SalesOrder, you can simply add fields from each of the green table occurrences directly to the layout and they will show data from the related table.

                 If you are trying any of those options from the context of SalesOrder and it's not working, that suggests that your SalesOrder record and customer records do not have values in their match fields that correctly match records--a data entry problem rather than a relationship problem.

            • 3. Re: Relational issue
              DavidDicken

                   Dear Phil, 

                   Thank you for all you do.

                   So I thought I would want the info from the customer layout to populate the fields on my Sales Order layout.  You know like one customer can have many Sales Orders. One to many. The TO's are duplicate customer TO's renamed.

                   So what you said about the data entry problem , could you please explain what you mean by match field.  I think the match fields are the _kp and the _kf. What data that I'm entering is mis-matched and what does that mean. It does seem to be the way you explained because I'm actually getting nothing from the auto enter or look ups. I put the fields from the green TO's onto the Sales Order layout with no results just blank fields after selecting a customer.

                    

              • 4. Re: Relational issue
                philmodjunk

                     Your relationships look correct, but there are several possible reasons your data is not showing up on the SalesOrder layout:

                     1) I can see that fields such as "::Sold To Address" are from a related table occurrence, as indicated by the leading ::, but I can't tell WHICH table occurrence was selected when adding that field to your layout. This should be a field from the SalesOrder_CUSTOMER_Soldto table occurrence and thus you should see SalesOrder_CUSTOMER_Soldto::Sold To Address if you click this field while in layout mode and check to see what is shown in "display data from" in the Inspector's Data tab. If you see Customers::, then this is the wrong table occurrence.

                     2) I see Bill to and Sold to field names. With the relationships that you are specifying with TO's for BIll To and Sold to, you no longer need two sets of addresses, make sure that the fields that you are entering data into on your customer layout corresponds to the data you want to see in these fields.

                     3) the match fields are indeed the _kf and __pk fields defined in your relationships. They should be the same data type either both number or both text. YOu have four _kf fields in SalesOrder Each matches to a specific _kp field in Customers or Order description. In order for data to show on your layout via the SalesOrder to SalesOrder_CUSTOMER_Soldto relationship, the value in _kf_customer_soldto must exactly match the value of _kp_customers_ID. And you will want to check these values using fields formatted as edit boxes or drop down lists so that the field formatting doesn't hide the true value of the fields.

                • 5. Re: Relational issue
                  DavidDicken

                       I hope I'm  not frustrating you.

                       okay so all the foreign keys and the primary keys,(Match Fields), are the same:  They all are number.

                       The fields that I'm referring in this instance are for the "Sold To" on the sales order layout and they all are coming from the TO  salesorder_CUSTOMERS_SoldTo.  I double checked them. (address, city , state , zip, country, phone and email)

                        The Customer Field on the Sales Order layout is local with a drop down list of customers from customers layout and that is how I select a customer for the Sales Order Layout. I hope that is correct.

                       I checked the __kp_CustomersID field in customers it reflects a number in this case 37 not sure how to verify how that number matches the number from the drop down list in the customer field on the Sales Order though. 

                       In the manage database under the table Sale Order I have this Specify Calculation  for Sold to address

                       Evaluate this calculation from the context of : Sales order

                       with this calculation

                       Sold to Address =

                       salesorder_CUSTOMERS_SoldTo::sold to Address

                       and the rest of the referred fields follow suit.

                        

                        

                  • 6. Re: Relational issue
                    philmodjunk

                         Are you aware that the calculation field that you've just shown me is not a field that is present on the SalesOrder layout which you uploaded earlier?

                         There's a field named "sold to address", but the :: at the start of the field name shows that this does not come from the SalesOrder table occurrence.

                         

                              I checked the __kp_CustomersID field in customers it reflects a number in this case 37 not sure how to verify how that number matches the number from the drop down list in the customer field on the Sales Order though.

                         In order for a salesorder layout to show "Sold to" data from the Customers record where __kp_CustomersID = 37, Put a copy of _kf_Customer_SoldTo on your layout formatted as an edit box. That field must show the value 37 or fields from the "sold to" table occurrence will not show data from the Customer record whose ID is 37.

                    • 7. Re: Relational issue
                      DavidDicken

                           Good morning ,

                           I hope you had a good weekend.

                           I have made some changes and removed all the records from both the Sales Order and Customer Layouts and reset the auto serial #'s to one. Recreated a customer record so I have data (Davids Test Customer) Recreated a Sales Order. I did this to keep everything clean.

                           I put the __kp_Customers_ID field on the sales order layout and nothing is shown in the field in browse mode after selecting from the drop down field a list of customers using the _kf_CustomerID_soldTo field. I was expecting the number 1 to be shown here as that is what is on the customer layout.

                           You mentioned that I should  "Put a copy of _kf_Customer_SoldTo" on my layout formatted as an edit box. I did that and it showed the customer name instead of a number.

                      I seem to be quite lost. Do I want to have the fields from the Customers layout on my Sales Order Layout? i.e. :: sold to address from the salesorder_CUSTOMERS_SoldTo Table or should it be a field that is local to the Sales order Layout? Seems I have tried both ways an still get no results. the following are the recent screen shots

                      • 8. Re: Relational issue
                        DavidDicken
                        /files/f3bd678420/Sales_order_layout_mode_2.png 3600x1080
                        • 9. Re: Relational issue
                          DavidDicken
                          /files/24942d3469/Edit_Relationship_Sales0rder_CUSTOMER_SoldTo.png 3600x1080
                          • 10. Re: Relational issue
                            philmodjunk

                                 The problem here is that your value list is incorrectly set up to enter a name into the field instead of a number.

                                 Open your "use values from a field" value list in Manage | Value lists and change the first field setting to show data from __pkCustomerID. Select "also display values from" and then select the Name field for Field 2. This value list will display names but enter ID numbers.

                                 Usually, such a value list has the "show only values from second field" option specified.

                                 Note that this is a "beginner level" value list set up. While easy to set up, it has several potential issues as you put your database into regular use:

                                 1) As your list of customers becomes large, it becomes cumbersome to scroll through that very long list of customer names to find the correct customer. Since the value list enters an ID number, you can't auto-complete on this value list to quickly trim it down to size.

                                 2) The list only allows you to have one customer with any given name. If you get a second customer named "John Smith", you won't be able to select the second customer from your list as the value list will omit the duplicate name.

                                 Other, more sophisticated methods for selecting a customer are possible that use scripting and/or additional relationships to make selecting a customer a more user friendly fashion. I'd get this simple version working first, then, if interested, we can explore more sophisticated options.

                            • 11. Re: Relational issue
                              DavidDicken

                                   YES! It Works. Thanks to your patience.

                                   The Sold to fills in info . So how do I get that to work with the bill to and ship to fields? 

                                   You raise good points regarding being to large or same name.

                                   I'm very  interested in the more sophisticated options. 

                              I'm ready to give it a go if you are.

                              I'm grateful for all your help.

                                    

                                    

                              • 12. Re: Relational issue
                                philmodjunk

                                     A typical way to handle this is to not have "bill to" and "ship to" fields but to have "bill to" and "ship to" records. If you look at the data being recorded, the same type of data is being recorded in the bill to and ship to fields--your basic street name, city, state and zip fields. So relational database developers often use an "addresses" table and link one to a customer or contacts record as the billing address record and link another record in the same table as the shipping address record.

                                     So you might have:

                                     ADDRESSES|BillTo-----<Customer>------ADDRESSES|ShipTo

                                     Customer::_fkShiptToID = Addresses|ShipTo::__pkAddressID
                                     Customer::_fkBIllToID = Addresses|BillTo::__pkAddressID

                                     So the addresses table would have these fields:

                                     __pkAddressID
                                     Street
                                     City
                                     State
                                     Zip

                                     And you'd add the BIllTo::Street field to your layout to show the street name and number of the billing address, but add ShipTo::Street to your layout to show the same infor for the shipping address. Fro what I see, you are actually pretty close to this set up already.

                                     Note that this also makes it possible for multiple customers to have the same billing or shipping address--such as setting up a customer record for two different departments of a large company such that they have the same billing address, but possibly different shipping addresses.

                                     There are numerous ways to make a drop down list more user friendly. One method is to use a conditional value list where selecting a "categrory" in field 1 limits the list of customers that appears in field 2 to just the customers that are a member of that category.

                                     And you can set up "search portals" where entering text into a search field filters down the list of matching records listed in a portal to fewer and fewer records with each keystroke. Clicking a listed record in the portal then selects it.

                                     But one of my favorites is a name based look up system where you get back your auto-complete enabled drop down list of names, but the records are linked by ID numbers with a script that can detect both new customers and customers with identical names and handle both situations:

                                     FileMaker 12 or newer users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                                     Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7