I'm assuming the PO number is in data, and that POs and invoices are in different tables. If that is the case, you can do this to prevent the PO number from duplicating:
1) In the Manage Database dialog, click the PO number field and click "Options".
2) In the Auto-Enter tab, click "Calculated value".
3) In the calculation dialog, enter "". Click "OK".
4) Back in the Options dialog, uncheck the box that says, "Do not replace existing value of field (if any)".
What this will do is automatically enter a blank value in your PO field whenever you duplicate the Invoice record.
When I duplicated the layout it set up to pull the information from the “purchase orders” table. I have since tried creating an invoice table, with the same fields as the PO layout…….and set them up to be looked up from the PO information, but it is not working.
Let's back up. Did you duplicate a layout, or are you trying to duplicate a record? What tables are you trying to reflect data from?
I may have misread your question. If you duplicated a layout and you're trying to reflect data from a different table, then you need to change the underlying table occurrence the layout refers to. Go to the Layout Setup dialog and change the TO using the appropriate dropdown:
Is that more what you need?
Maybe I didn’t give a good enough description. I have several layouts created, and 3 of them are duplicates of the ‘PO’ layout. Simply because I want the info entered into one place to carry over to the other layouts. I have never created a database before, and am not sure if this was the best idea. Now that I have done that, when I duplicate an invoice due to partial billing it duplicates in all 4 layouts. Help if you can.
This is normal behavior. Any two layouts based on the same table occurrence will show the same data for the same record; this is as it's supposed to be. For example, if I have a layout for data entry and a layout for reports, I want the same data to be reflected on both layouts. I wouldn't want different data on my reports as what was showing on my data entry form, right?
So how exactly do you want it to work? What is your expectation?
What I’ve done is create the Purchase Order, Invoice, Packing slip and New contract notice based on the ‘purchase order’ layout. What I’d like to do is partially invoice against the PO. What is the easiest way to do this? I’ve attached my mess for your review and ideas.
Your data model is basically okay. But you need to change the Invoice layouts to be based on the Invoices table rather than on the PO table.
Layouts are tied to tables (technically, table occurrences). This is a concept in FileMaker known as context. Each layout points back to a table so that it reflects the data in that table. If you want to show an invoice, then you need to do what I said above: Set the layout up to show records from Invoices:
Once you do that, you'll need to change all the fields on that layout to be the fields in the Invoices table (because they'll all automatically change into the related fields in the PO table).
I'll give it a try and let you know. Thanks for all of your help!!!
Is there a way to copy the fields from the PO table into the invoice table? And, when I get all the same fields into the table, do I have to make them “look up” the info from the PO table? Sorry to have so many questions, but this is all new to me.
I'd have to ask the question first: What is it you're trying to accomplish by copying the fields (and, presumably, the data) from one table to another?
One of the basics of relational design is that you only store data in one place, unless you have a good reason to store it elsewhere. The reason for this is basic data integrity. As an example, let's say you have a purchase order (which you do), and that PO includes the address of the customer. What happens if you copy the address of the customer from the PO to the invoice - and the address changes? Do you change it in two places? What happens if you change it in only one place? Which one do you believe?
That may be a trivial example, but you can see the issue. When you start duplicating data around a database, you create either a data entry headache (because you have to update in multiple places) or a question mark as to what data is the correct data.
This is the reason why you only have fields for data in the table in which they belong - and you use the related fields on related layouts. For example, if I'm looking at an invoice, I wouldn't have the information about the PO in the Invoices table. Instead, I would put the fields from the PO table on the Invoice layout. I can do that easily enough through a relationship.
There is an exception: When I have time-sensitive data and I want to preserve a history. For example, say I have a unit price on an item. That unit price might change, but I don't want it to change on old invoices, because that would create an error on those past invoices. So, in a case like that, I would use a lookup to copy the unit price onto the invoice from the Products table (typically) so that, if the unit price for a product changes later, it will only affect invoices from that point forward.
So, what I would say is this: Only put the fields in each table that you really need in each table. Don't try to duplicate entire tables; if you do, it's an indicator that you probably have an issue in your data model.
Clear as mud?
Now, to answer your question directly: Yes, you can copy fields directly from one table to another (if you have FileMaker Advanced). Just select the fields you want, choose "Copy" (or Ctrl-C), switch tables, and choose "Paste" (or Ctrl-V).
Ok, let me explain a little more clearly. The PO and the invoices are using most of the same fields. As you can see, the layouts are almost identical. Each PO will result in multiple invoices, thus the desire to have all address and line item info carry over without having to re-enter. Ideally I’d like to be able to create a report that will total up the invoices billed against each PO, but I don’t want to get ahead of myself quite yet. Unfortunately I do not have FileMaker Advanced, so I will be manually entering each field into the invoice table to be used in my layout.
SSM Industries, Inc.
3401 Grand Ave.
Pittsburgh, PA 15225
412.777.5101 X 361
Let me ask a basic question:
Will the address or line item information on the PO change between the time the PO is issued and the time it's invoiced?
If it does, do you want to preserve the original invoice information in the database? (Presumably, the answer is "yes", but I want to make sure we're clear.)
Okay, so if the information will not change, then there is no need to duplicate it on the invoice. In fact, it's a bad idea.
1) On your Invoices layout, set it up to point to the Invoices table.
2) Instead of using the address and line item information from the Invoices table, use the fields from the PO table (here's an example for the Company_client field):
3) Where you need information that is unique to Invoices (and only in that case - such as Quantity Shipped), use the fields from the Invoices table.
4) Delete any fields from Invoices that are not unique to Invoices - fields that are duplicates of fields in PO.
Why am I telling you this? Because you should only keep data in one place in a database unless you have a compelling reason to duplicate it. In this case, all the information you need about the PO is kept on the PO record. Since it doesn't change when it's invoiced, there is no benefit in duplicating it. You can simply use the information from the PO on the invoice through the relationship you already have set up. In this way, you simply reference the information.
Hope this makes sense.