1 2 Previous Next 18 Replies Latest reply on Jan 31, 2012 9:51 AM by Mike_Mitchell

    Help with record duplication

    jball

      I've created a purchase order layout, and then duplicated it to make my "invoice" layout so that the information is automatically in the invoice. However, now when I try to duplicate an invoice due to partial billings, the PO duplicates as well. Is there an easy fix to this????

        • 1. Re: Help with record duplication
          Mike_Mitchell

          Hello, jball.

           

          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.

           

          HTH

           

          Mike

          • 2. Re: Help with record duplication
            jball

            Mike,

             

            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.

            • 3. Re: Help with record duplication
              Mike_Mitchell

              jball -

               

              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:

               

              dialog.png

               

              Is that more what you need?

               

              Mike

              • 4. Re: Help with record duplication
                jball

                Mike,

                 

                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.

                • 5. Re: Help with record duplication
                  Mike_Mitchell

                  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?

                   

                  Mike

                  • 6. Re: Help with record duplication
                    jball

                    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.

                    • 7. Re: Help with record duplication
                      Mike_Mitchell

                      jball -

                       

                      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:

                       

                      dialog.png

                       

                      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).

                       

                      HTH

                       

                      Mike

                      • 8. Re: Help with record duplication
                        jball

                        I'll give it a try and let you know.  Thanks for all of your help!!!

                         

                        jan

                        • 9. Re: Help with record duplication
                          jball

                          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.

                          • 10. Re: Help with record duplication
                            Mike_Mitchell

                            jball -

                             

                            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).

                             

                            Mike

                            • 11. Re: Help with record duplication
                              jball

                              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.

                               

                               

                               

                               

                               

                              Thank You,

                               

                              Janice Ball

                               

                              SSM Industries, Inc.

                               

                              Power Division

                               

                              3401 Grand Ave.

                               

                              Pittsburgh, PA  15225

                               

                              412.777.5101 X 361

                              • 12. Re: Help with record duplication
                                Mike_Mitchell

                                Janice -

                                 

                                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?

                                 

                                Second question:

                                 

                                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.)

                                 

                                Mike

                                • 13. Re: Help with record duplication
                                  jball

                                  No, it will not change.  The only thing that will change is the quantity shipped on each record.  I want to be able to create multiple invoices for the same PO.

                                  • 14. Re: Help with record duplication
                                    Mike_Mitchell

                                    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.

                                     

                                    Do this:

                                     

                                    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):

                                     

                                    dialog.png

                                     

                                    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.

                                     

                                    Mike

                                    1 2 Previous Next