13 Replies Latest reply on Jan 15, 2010 12:55 PM by comment_1

    Invoice Dilemma

    BERGSTEN

      Title

      Invoice Dilemma

      Post

      So I need a little bit of hand holding for a little bit. I am familiar with database theory and I am trying to create a database for a rental company with FM7. My current problem is how to create an invoice where I can add line items from a generic inventory table I've created based on common rentals and common formatting proceedures we've been typing in by hand.

       

      I realize that the Item:Invoice is a m:m relationship and it probably needs a join table, but I am unfamiliar with how to create this join table in FM and how I would display the records in each other's table. I also need the information in the Invoice Item list to be directly editable since a lot of our invoices need tweaking to tailor specific needs to customers. For example, in my inventory list, a drum kit for rental will read specified drum sizes as X" instead of 12" for later editing once it is already in the invoice, so it cannot change the formatting in the inventory, just the invoice.

       

      I am wondering if there is a way to do this with a join table and portals? Or maybe a script? I'm open to any ideas that will allow editing in the invoice fields that will not affect the inventory records as well.

       

       

       

      Thank you! 

        • 1. Re: Invoice Dilemma
          comment_1
            

          BERGSTEN wrote:

          I realize that the Item:Invoice is a m:m relationship and it probably needs a join table


          Yes, it does - see if this demo can help:

          http://fmforums.com/forum/showpost.php?post/309136/

           

           


          BERGSTEN wrote:

          I also need the information in the Invoice Item list to be directly editable


          Use a lookup - same as the Price in the above demo.

           



          • 2. Re: Invoice Dilemma
            BERGSTEN
              

            The tutorial is great, but I still need to be able to edit the Invoice line items separately. In the tutorial, I can edit it only in the join table and it is then edited everywhere. I do not want this. I need it to stay the same in the LineItem Join Table and the Inventory List table, but be editable in the Invoice list after it is looked up.

             

            Thanks

            • 3. Re: Invoice Dilemma
              comment_1
                 That doesn't make sense: why does it need to stay the same in the line item? The line item belongs to the invoice - there is no other place that lists the invoice's items.
              • 4. Re: Invoice Dilemma
                BERGSTEN
                  

                Let me try and explain: When I edit the line item in an invoice view, it changes in the line item link table. Then when I add a new invoice and try to add the item number, it adds the changed line item to the new invoice. I need to have the line item stay the same but be editable in the Invoice view...

                 

                • 5. Re: Invoice Dilemma
                  comment_1
                     Have a look at that demo again: when you edit the price of an item for a specific invoice, it changes the LineItems table - but when you add the same product to a new invoice, you get the original price from the Products table.
                  • 6. Re: Invoice Dilemma
                    BERGSTEN
                       This doesn't work for me-- I edited the name of the Item in the Invoice, which edited it in the line_Items, which then edited it in the products table. (It won't let me edit the price right now because I'm on a PC, but I'll check on my mac when I get home...) But when I did this the other night, it edited the name in every table, and I assume this would also be true for the price..?
                    • 7. Re: Invoice Dilemma
                      comment_1
                        

                      BERGSTEN wrote:
                      it edited the name in every table, and I assume this would also be true for the price..?

                      No, because the price is looked up into the LineItems table. You need to do the same for every field you may want to modify in a specific invoice.

                      • 8. Re: Invoice Dilemma
                        BERGSTEN
                           I'm sorry to be a pest about this, but so what do I have to do to make both the price and the line item description editable in the invoice view only?
                        • 9. Re: Invoice Dilemma
                          comment_1
                             You need to have fields for them in the LineItems table, and those fields must lookup the data from the corresponding fields in the Products table.
                          • 10. Re: Invoice Dilemma
                            BERGSTEN
                              

                            So I tried to do what you described...

                             

                            When I edit the item's description in the invoice, it still changes the description in the products table.Furthermore, the description does not change in the linking table.

                             

                            I also can't add any more records to the portal I'm using from the invoice table view, I have to add an item and invoice ID in the link table before it will allow me to add anything else in the invoice table.

                             

                            • 11. Re: Invoice Dilemma
                              comment_1
                                

                              BERGSTEN wrote:

                              When I edit the item's description in the invoice, it still changes the description in the products table.Furthermore, the description does not change in the linking table.


                              Sounds like you are editing the Description field from the Products table. Double-click the field in Layout mode and select the Description field from the LineItems table.

                               

                               


                              BERGSTEN wrote:

                              I also can't add any more records to the portal I'm using from the invoice table view,


                              Portals are not functional in Table view (they require some room).

                               



                              • 12. Re: Invoice Dilemma
                                BERGSTEN
                                  
                                BERGSTEN wrote:

                                I also can't add any more records to the portal I'm using from the invoice table view,


                                Portals are not functional in Table view (they require some room).

                                 


                                I solved the Description issue (THANK YOU!! IT finally works =) But I'm not quite sure what you mean by the above. I think I meant to say "Form View" instead of table view. If I add new line items to the LineItems table with the product ID and Invoice ID, then I can add new ones to the portal in the Invoice form view, but otherwise I can't. If there isn't a way to fix that, is there a way I can add a button that would add a line item?

                                • 13. Re: Invoice Dilemma
                                  comment_1
                                    

                                  If you are adding line items in a portal (on a layout of Invoices), then you don't need to enter the InvoiceID - it is set automatically through the relationship.

                                   

                                   

                                  You CAN add line items through a script - roughly:

                                   

                                  Set Variable [ $invoiceID ; Invoices::InvoiceID ]

                                  Go to Layout [ LineItems ]

                                  New Record

                                  Set Field [ LineItems::InvoiceID ; $invoiceID ]

                                   

                                  However, this new line item does not yet have a product associated with it.