10 Replies Latest reply on Mar 9, 2011 8:26 AM by DrewMontague

    Help with database relationships

    DrewMontague

      Title

      Help with database relationships

      Post

      Hi, I'm trying to customise my filemaker pro so I can see line items (fields called rotation number, product description and amount) from a sales order database in the contacts order history in the contacts database. I seem to be doing something wrong - any help would be gratefully received.

      Thank youSmile

      filemaker-screenshot.jpg

        • 1. Re: Help with database relationships
          philmodjunk

          A little hard to help you from what little you describe in that post.

          What have you done so far to try to set this up? How does it fail?

          Presumably this is the contacts starter solution?

          What version of FileMaker?

          On what layout are you trying to set this up?

          • 2. Re: Help with database relationships
            DrewMontague

            Oops! Working with Filemaker Pro 11 version 11.03  I am using the BPS add on, so the relationship is between the contacts db and the sales order db. Contacts has a tab marked sales orders and within it I am showing the fields ::amount  ::rotation number ::wine  ::case size - these are the fields in the sales order invoice items

            Hope this makes it clearer. Really appreciate any help.

            Thank you

            • 3. Re: Help with database relationships
              philmodjunk

              Well, I don't have the Business Productivty Pack (I think that's what you mean by BPS), so I still don't have enough context to know how to help you here...

              • 4. Re: Help with database relationships
                DrewMontague

                I do mean the Business Productivity Pack but I have also tried to achieve the same result using the regular Filemaker Pro 11 starter solutions with no success.

                • 5. Re: Help with database relationships
                  philmodjunk

                  Yes, but the context from which you are trying to set this up can tell me why it isn't working for you. The layout on which you try to set this up and the relationships either already defined in the template file or added by you can make a huge difference here in what results, if any you will receive.

                  If you can attempt setting this up in a starter solution file and then describe fully what you have set up and how it is failing, I can probably provide some assistance to you.

                  • 6. Re: Help with database relationships
                    DrewMontague

                    Really appreciate you trying to help me with this problem.

                    I created line item fields in the Sales Order db that I want to be able to view  in the Contacts db. The fields are:

                    Amount, Case Size, Wine Description and Rotation Number.

                    Amount is a calculation field (indexed, =Price*Quantity)

                    Case Size is a text field (indexed)

                    Wine Description is a text field (indexed)

                    Rotation Number is a number field (indexed)

                    Having trouble uploading screen shots to give you a better idea so I will have to do it by text instead.

                    If you go into the manage db under contacts the only table is contacts, however in the relationship section there is a relationship between contacts and line items.

                    in the line items box - amount, case size, rotation number and wine description are all in the top section of the box and have 'crows feet' linking them to the contacts box. at the contacts box is a flat line, not crows feet. in the top of the box they all appear to link through to one thing, a field i called 'amount contact'

                    if i click on the edit relationship box between the connections i can see amount contact in contacts table  = amount in line items table

                    I hope you can understand what I'm trying to convey.

                    • 7. Re: Help with database relationships
                      philmodjunk

                      I just looked over the Starter Solution list and there is no "sales order" starter solution. There is a contact manager, but this does not appear to match what you describe here.

                      You can upload screen shots by clicking the edit link on the very first post of this thread or you can upload screen shots to a file sharing site and then post the download link here in any post to this thread.

                      I can tell you that this set up is incorrect:

                      "in the line items box - amount, case size, rotation number and wine description are all in the top section of the box and have 'crows feet' linking them to the contacts box."

                      Your relationship should not link by the actual data entered. You normally use just one pair of linked fields for most relationships. It should link a field with a value that is common to all records on the many side of the relationships. You may have a contact ID field in the Contacts table that auto-enters a serial number. You could link this field to a contacts ID field in the second table to get a list of all records in the second table that have that contact ID and thus are linked to that specific customer.

                      Here's a simplified invoicing demo file created by a person named Comment and uploaded to a different FileMaker forum you may want to look at to get a better understanding on how this might be done: 

                      http://fmforums.com/forum/showpost.php?post/309136/

                      • 8. Re: Help with database relationships
                        DrewMontague

                        Ive added some screen shots in the hope it better explains the problem.

                        • 9. Re: Help with database relationships
                          philmodjunk

                          I had to set my browser to maximum magnification and then decipher very blurry text, but I managed to read the screen shots.

                          Question: When you open Manage | database | relationships and double click one of the Contacts tables (bill to or ship to), What data source do you see? Does it refer to the contacts table in the contact manager file?

                          I'm guessing that it does.

                          If so, you could add a Table Occurrence of Sales Order to your Contacts file. Link SalesOrder and LineItems like this:

                          Contacts::contactID = Sales Order::bill To Contact ID
                          Sales Order::invoiceID = LineItems::InvoiceID

                          Now you've replicated the relationships in Sales Order in your Contacts file.

                          This may be the long way around to do this. It may be easier to set up the layouts you want in the Sales Order file instead of contacts, assuming that they share the same contacts table like I think.

                          • 10. Re: Help with database relationships
                            DrewMontague

                            Thanks, I will try this now I'm back in the office. I really appreciate your help.