1 2 Previous Next 20 Replies Latest reply on Jun 26, 2010 6:48 PM by LuisN

    Ok Still Can not figure it out..

    LuisN

      Title

      Ok Still Can not figure it out..

      Post

      I am in Invoice view. I created a proposal which is a different Form View from Invoice. So its all in the same DB. i have the relationships right etc.

       

      I have a field, that is a drop down.. called Proposal ID. i want to be in Invoice view, "Creating a new invoice" and choose the Proposal. This will copy all Line Items from Proposal xyz into my new invoice. whats a good way to get this done?

        • 1. Re: Ok Still Can not figure it out..
          LuisN

          Any takers?

          • 2. Re: Ok Still Can not figure it out..
            LaRetta_1

            You are simply not providing enough information.  As in your previous post, I requested your file or (at minimum) a copy of your script and I received neither.  We do this for a living but that does not mean that we can mind-read. :smileyhappy:

             

            UPDATE:  It would also help if you drop the word DB.  There are tables and there are files.  And the layout doesn't mean anything - what matters is the table occurrence name that a layout is based upon. 

            • 3. Re: Ok Still Can not figure it out..
              LuisN

              lol so thats what i need help on. what you need from me to get help :)

               

               

               

               theres really no script that i have for the setup i wrote in this thread.

              I have Tables in 1 file.

              i have a table for Proposal

              I have a Table for Proposal Line Items

               

              Invoice Table

              Invoice Line Items

               

              I want to take all the line items in the Proposal #34 and place them into Invoice # 12.

               

              what would work ?

               

              • 4. Re: Ok Still Can not figure it out..
                Steve Wright

                Take a look through this thread which describes how to duplicate a parent record / including child (related) records and change the relevant ID's

                 

                http://fm.lithium.com/t5/Using-FileMaker-Pro/Copy-Records-with-Portals/m-p/49049#M38360

                 

                When writing the script, it helps to visualise what you would do, if you where doing it yourself manually .

                 

                • 5. Re: Ok Still Can not figure it out..
                  LuisN

                  I went over a few things, but what i dont get is.. will this work even if the Portal is a different table from the portal im copying from? and also the table of which the records lays in?

                  • 6. Re: Ok Still Can not figure it out..
                    Steve Wright

                    Im sure I mentioned before that it sounds like you dont need to have 4 tables.

                    Basically, from what I can gather

                     

                    Tables for : Proposal and Invoice contain almost the same information, adding a 'type' field would let you work with it differently

                     

                    i.e

                    type = "proposal"  |  Proposal ID = "100" | Invoice No = ""  |  Name = "Mr Smith"

                    type = "invoice"  |  Proposal ID = "" | Invoice No = "50"  |  Name = "Mr Smith"

                     

                    You could then display all records where type="proposal" in a list view etc, or vice versa.

                     

                    Then the  proposal line items / invoice line items could also be merged to one table based on the same principle..

                    especially if there are a lot of common fields across both of these

                     

                    With that kind of setup, all you would need to do is duplicate (as per the linked post) changing the type and the Proposal ID / Invoice No

                    • 7. Re: Ok Still Can not figure it out..
                      LuisN

                      Hmm.. ok well the thing is, i dont have a field that says Type. So there might be the problem. All i have is Invoice ID or Proposal ID. So, if the script goes to check something it has to first check for Type = Invoice , then look for ID that is being asked for.

                       

                      the Form views are very different in Propo and Inv. so if i Duplicate, it will be very different. thats why im just having trouble understanding =( sorry for my ignorance. trying to learn .

                      • 8. Re: Ok Still Can not figure it out..
                        Steve Wright

                        Sorry, what I missed from part of my post is that "I think" you should only have tables for 

                         

                          Document

                          Line Items

                         

                        Instead of :

                         

                          Proposals

                          Invoices

                          Proposal Line Items

                          Invoice Line Items

                         

                        Instead of using 4 tables, use two and add a type (or any name you like) field to separate the records, so for instance

                        The Document Table would contain both proposals and invoices, however each record would have a corresponding type specified.

                         

                        The layout can look as different as it needs to be

                         

                        If there are only a few fields different between each of the tables, but the majority are the same, then this makes sense

                         

                         

                         

                        • 9. Re: Ok Still Can not figure it out..
                          LuisN

                          heres the file sample i created with the help from sws

                           

                          Sample

                           

                          Please keep in mind i want to just copy the line items from proposal into the invoice, AND i will edit the invoice which i do not want editing of the proposal if i change naything in invoice view.

                          • 10. Re: Ok Still Can not figure it out..
                            LaRetta_1

                            In your prior thread, you wanted to go to Products if you had a ProductID or go to References if you had a Reference ID.  I asked for screen shot which you gave me.  You said simply that my script didn't work.  No detail, nothing I could use.  I asked for copy of your script.  How do you know it didn't work if you didn't even create a script as I had directed you do?

                             

                            If we spend time assisting you, the least you can do is provide us with the information necessary when we ask for it.  As it is, all of the time I spent in your prior post just went out the window and you STILL have no idea how to use Go To Related Record[].

                            • 11. Re: Ok Still Can not figure it out..
                              LuisN

                              I am not sure if you noticed, i am not talking about the same script. that i put on the back burner. THIS thread has to do with something different. when i am done with this one i will go back to the code that you have provided and insert this into the same file i attached. thanks

                              • 12. Re: Ok Still Can not figure it out..
                                LaRetta_1

                                Well, I've done it using same Invoices/LineItems and indicated whether quote and then if accepted, turn it into invoice.  And I've also designed where quotes are separate table.  This is particularly important if the items being quoted might be purchased (invoiced) at different times (so you would want to select only specific quote lines to invoice) and/or if you need to keep a record of your quotes (separate from your invoices) so whether or not you should maintain a separate quotes table depends entirely upon each business case.

                                 

                                QuoteToInvoice was a file I prepared awhile back which shows maintaining two tables as you have structured yours, Luis.  And it shows how you can select separate items to invoice.  And it stops you from invoicing same items more than once as well as tracking (from invoice perspective) back to the quote the items came from.

                                 

                                From the quotes table, add a few lines from the quote (decide to invoice a few of them).  After you select a few, they will appear in the yellow multi-line field then click button to invoice them.  Next, go to the Invoice layout.  You will see that it created an invoice based upon that CustomerID and also maintained a link to the original quote (and even the specific line of the quote), based upon the prices discussed and quoted in your quotes table.

                                 

                                Added ... I did of course skip Customer table and Products table.  The purpose of this demo was to show quote-to-invoice activity and I wanted to keep the demo focusing only upon that functionality.

                                 

                                • 13. Re: Ok Still Can not figure it out..
                                  LuisN

                                  Thats PERFECT! i read what you said and i am going ot download the file now to inspect it. One question, can the person Modify the Invoice? This is important as the "Quote / Proposal" will always stay the same, example during invoicing they only can lol "afford" to purchase x y and NOT z. so the proposal stays with xyz. Later during the month they say, hey remember that proposal, can you invoice me for z. so i guess what i can then say is,

                                   

                                  go to Quote/proposal. click again create invoice from Quote/Proposal and it copies all the records to a new invoice and i guess you just delete the x and the y.

                                   

                                  There CAN be a , lets say pop up window which SHOWs you all the Lineitems in the Quote before making the invoice and allows you to select the Line Items you want to copy over. that WOULD be nice, but more advanced. i will keep things simple for now as i learn fmp more and more.

                                  • 14. Re: Ok Still Can not figure it out..
                                    LuisN

                                    And of course, i didnt open the file till now and I LOVE what you showed me :) let me disect it and learn what you did.

                                    1 2 Previous Next