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.
"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:
Set Variable [ $id_company ; Companies::id_company ]
Go To Layout [ layout where you will begin entering your invoice information ; layout based upon Invoices table ]
Set Field [ Invoices::id_company ; $id_company ]
Go To Field [ whichever field you want to begin entering invoice data, such as PayMethod, ShipVia etc ]
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.
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 ]
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.
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.
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!
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.
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.