Two tables one copying data to the other

[fperezl]

Hi, lets see if someone can guide me. I would like to use two tables. The first table will be used for data entry and the second table will repeat the information of one field of the first table to make a calculation. I need a feature that if i add a record in table it automatically adds a record in table two.

how is this possible avoiding entering the info twice ?

thanks,

[Reed]

First, why does the calculation have to be in another table, and not just in the data entry table?

If you really want to automatically add a record in another table when you create (commit) a record in another table, you probably want to use a script triggering plugin.

Another option is if the two table are actually related, set up the relationship to allow the creation of related records, and then just set up lookups or auto-enter calculations to populate the other table. I'd have to know exactly what you're trying to do to explain better.

Dana

[fperezl]

Ok. I will try to explain.

I have around 4 tables. One is rates, the other is orders (for taking orders), invoicing and payroll. Orders, invoicing and payroll have one common field "order number'. These three tables are related, and all three tables have calculated fields. My intention is to instead of calling up the order number (by entering the info in each of the forms --> the order number ) in each of the separate tables to automatically generate the calculated fields, to actually have the order numbers automatically entered on invoicing and payroll.

One may think, why not include all the fields in one table. Answer, because i want to keep it separate.

any help is appreciated.

see ya friends..

[Reed]

So are all three of these related in a one-to-one manner? (One invoice for each order, and one payroll record)

If so, you could just place, for example, invoice fields on a layout tied an order record, and allow creation of related records. So when any data is entered into fields from invoices on that record, an invoice record is created, and it will have the same order_id as the order record. If the relationship was one-to-many, you could use a portal instead of plain fields, or use a scripted button to create the related records.

I've attached an example file that I just threw together... I used a field specifically for creating invoice records, and formatted it as a checkbox, but data entry into any invoice field on the order layout will result in the creation of a related invoice record. I didn't do anything for payroll records, but it would be the same...

You probably also want to add a line_items table, in case you ever want to sell more than one product on the same invoice. The line items could be added/viewed in a portal or with scripts. You could also add a customers table.

If you download Filemaker Business Tracker, you can see how FMI (Excelisys) designed a full application like this.

Dana

sample.fp7.zip