2 Replies Latest reply on Jun 16, 2013 5:12 PM by MichaelHolden

    Having trouble with relationship linking



      Having trouble with relationship linking


           Hi All,

           I'm new to this, so apoligies if this is a dumb question. [I've also had look through forum, but couldn't find a satisfactory answer].

           I've created a very basic 3 table FileMaker Advanced database. These databases are:


           Customers [contains the fields: Account Code, Name, Address, Phone and Fax]

           Products [contains the fields: Product Code, Name, Cost price, Sell Price 1, Sell Price 2]

           Sales Orders [contains the fields: Customer Account Code, Order Date, Order Number, Product Code, Order Qty, Unit Cost]


           The Sales Order Table doesn't have either the Customer Name or Product Name.

           The purpose of this database will be to have an iPadon the road, for delivering goods. The user will access the Sales Order information, get a signature and print/email to client. So that the Sales Order displays the Customer Address [and Name] and also the Product Name, I have had to setup some relationships.

           I have set the relationships as follows:

           SalesOrder.ProductCode = Products.ProductCode

           SalesOrder.CustomerCode = Customers.CustomerCode


           All off the 4 linked fields above are stored as Text fields in FMP. When creating a layout which shows the Customer Name and Product Name, these fields display blank.

           I have been able to get around the Product Name not displaying on the layout by changing the field type for both SalesOrder.ProductCode and  Products.ProductCode to Number. Preferably I'd like to link these as Text Fields as they could end up being Alpha/Numeric Codes.

           The Customer Codes are actually text, so the workaround listed above for Products will not work for Customers. 

           Can anyone advise, shed some light on why these links work if these fields are Numeric and wont when Text?

           I have looked at the tutorial example and that works; setting up my database the exaxt same way hasn't proven to be successful.

           Please note, all 3 tables are being linked through an Excel Recurring import. Again, I have reviewed the original XLS files, checked the field types in FMP and all should work okay.


           Thanks in advance.





        • 1. Re: Having trouble with relationship linking


               A possible reason...

               If your relationships are   Customer---< Sales Order >--- Product

               A layout based on Product will not display a customer unless there is a sales order...there would be no record in the join table to create the link.

               In this 3 table relationship, the vast majority of your operations layouts should be based on the sales order table.  Your reporting would be based on Customer or Product for the most part.

          • 2. Re: Having trouble with relationship linking

                 Hi Ninja

                 Thanks for the response. I was able to work it out. The issue did stem from the way I was bringing the data in from Excel. The Excel file actually consisted of some VLOOKUP data. Long story short; the Prodocut Codes and Customer Code numbers needed to be TRIMMED in Excel - they had extra spaces after each code. 

                 For example, code 'ABC' was coming across from XLS as 'ABC     '. Therefore FMP couldn't make a match.

                 All fixed now though.