9 Replies Latest reply on May 22, 2011 5:01 AM by LaRetta_1

    Relationships help



      Relationships help


      I have two tables in my filemaker file business.fp7.  One table is a customer table with contact info etc..  The second table is an invoice table.  I created in both tables a field called id_company.  In the contact table this field automatically generates a unique serial number to the contact when a new record is created..  I have a button on my contact form that says create invoice.

      How do I click that button and have my invoice form open with the id_company field automatically populate the id_company field on my invoice form?

      I built a relationship between the two table by joining the id_company fields to each other. 

      I am not sure how to choose the right options in the invoice table for the id_company field since I want it to automatically transfer the id-company field info from the contact table.

      And would I use a script for my button that needs to tell the filed to automatically transfer the field info.  As of now my script for the button that says create invoice is as simple as goto layout (Invoice) and create new record. That works but the id_company field info, I should probably be saying data, is not being automatically inserted into my id_company field on the invoice form.

      I would much appreciate the help.


        • 1. Re: Relationships help


          I too have the same question.

          I am able to go the the Layout of another table/database, also able to create the new record, but the fields content i am not able to copy and paste on the related field. 

          Kindly also please let me know how can we solve the problem.



          • 2. Re: Relationships help

            "How do I click that button and have my invoice form open with the id_company field automatically populate the id_company field on my invoice form?"

            To both Kurt and Hemen,

            To the button on your Customer form, add this script:

            Freeze Window[]
            Set Variable [ $id_company ; Companies::id_company ]
            Go To Layout [ layout where you will begin entering your invoice information ; layout based upon Invoices table ]
            New Record/Request
            Set Field [ Invoices::id_company ; $id_company ]
            Go To Field [ whichever field you want to begin entering invoice data, such as PayMethod, ShipVia etc ]

            • 3. Re: Relationships help


              Thank you!  It works fine now. Wonder if you will know show me how to bring over the contact info like name, address, etc.. into that invoice.


              • 4. Re: Relationships help

                I would be happy to, Kurt.  The answer is ... you don't need to.  Since you now have a relationship from the Invoices::id_company to Companies::id_company, you have all the parent information you could want.  You can just place the company name directly on your invoice layout (either as regular field; turn off entry so Users won't change the actual company name in the other table) or place a merge field of:


                There is one historical issue and this is a business rule you will have to make.  Many times, we need to plant the company address right into an invoice in an actual field so that, in the future, if the company changes addresses, history for this invoice is preserved.  Usually businesses decide not to do this with company name but approx. 60% decide to do this with the address.

                If you decide to plant address into invoice, add onto your script right before you go to the field to begin data entry:

                Set Field [ Invoices::Address ; Companies::Address ]
                Set Field [ Invoices::City ; Companies::City ]

                ... etc

                You can also go to your address field in invoices and set it as a lookup or even use an auto-enter calculation to pull the information through.  But bottom line is that you don't need to bring that information over when you create the invoice originally; pull it through the relationship as needed (bringing only the ID with you on the trip).  This doesn't mean that you CAN'T bring the information; you could by filling additional variables but bringing only the ID is faster and cleaner.

                • 5. Re: Relationships help

                  BTW, I always put addresses as a separate table because it never fails that a company has a BillTo, ShipTo, Mailing and Physical address.  You can then present the addresses once the invoice has been created (create a pop-up of related addresses which concatenates address, city and state) for selection but inserts the AddressID into the ShipTo field.

                  That's kinda getting out there past your question but I'm confident that you can handle it and I believe you see why I was heading that direction.

                  • 6. Re: Relationships help


                    You have been a huge help. I did not go as far to put the addresses into it's own table. But I can see how that would be best. I have been trying all day to get my invoice table to relate to my expense table.  I don't know if it's possible. All I want to do is have a layout that shows my totals for (invoice)amount paid, payments due, and (expenses)total expenses. And then use a calculation to show the difference between the two.  I have an id_invoice and a id_purchase. I think I do with out this for now but I really would like to know how to do it:)  Thank you again for your help. You rock!


                    • 7. Re: Relationships help

                      There would be several tables involved here, Kurt, and I don't know what you have.  AR is separate from Expenses and I don't see how they WOULD relate until they come together much further up the financial chain.  Have you consulted with your Accountant on setting this up?  One of the biggest mistakes Developers make is not structuring according to proper accounting process.  Even with tax accounting and business management background, I wouldn't consider taking on anything more than AR and Inventory without extensive professional input; particularly not AP.

                      When you say expenses, maybe you mean something else?  Can you explain?  Invoices should have a LineItems table (which lists each product being sold with the ProductID).  Your Product table would have Retail Price and wholesale cost.

                      • 8. Re: Relationships help

                        I thanks for the help but i solved my problem by another way.

                        My question was to how to copy records to the another database on the click of an button.

                        Any ways i solved it Thanks.

                        • 9. Re: Relationships help

                          Why is Hemen Babla replying on kurtnivy's thread?  This keeps happening where the thread is started by one person and then a different person continues the conversation.  I feel like I'm in Twilight Zone.  Is this website messing up or do we have a person who has trouble keeping themselves straight? Surprised