13 Replies Latest reply on Jan 31, 2014 9:52 AM by MichaelLawrence

    Auto-Fill Invoice with every Product

    MichaelLawrence

      Title

      Auto-Fill Invoice with every Product

      Post

           Hi guys. I am having trouble with a script I'm writing. Basically I want to add every current Product in my database to an Invoice. The user will at times need to start an invoice by running this script and adding all products to the invoice. I've been told the best way to do this is to write a script that pulls all products, then loops through them by adding each one. I've spent the last week working on it but I'm still not having any luck.

            

           Anyone that can help please let me know.

            

        • 1. Re: Auto-Fill Invoice with every Product
          philmodjunk

               And do you have the typical relationships for invoicing?

               Invoices--<LineItems>-----Products. (Lineitems is called "invoiceData" in the latest invoice starter solutions.)

          • 2. Re: Auto-Fill Invoice with every Product
            MichaelLawrence

                 Yes I'm using a variation of the Starter Solution Invoice and haven't changed any of the relationships. I have a script that I'm trying to work with but it isn't working all.

            • 3. Re: Auto-Fill Invoice with every Product
              philmodjunk

                   You'll need to substitute your file's names for those that I am using:

                   Define this new relationship to a new occurrence of Products:

                   Invoice----<Products|All

                   Invoice::anyfield  X Products|All::anyfield

                   Then use this script:

                   Set Variable [$ProdIDList ; value: List ( Products|All::__pkProductID ) ]
                   Set Variable [$InvoiceID ; value: Invoice::__pkInvoiceID ]
                   Freeze Window
                   Go to Layout ["InvoiceData" (InvoiceData ) ]     --> the latest two starter solutions call the "lineitems" table "InvoiceData".
                   Loop
                      Set Variable [$K ; value: $K + 1 ]
                      Exit Loop If [ $K > ValueCount ( $ProdIDList ) ]
                      New Record/Request
                      Set Field [InvoiceData::_fkInvoiceID ; $InvoiceID ]
                      Set Field [InvoiceData::_fkProductID ; GetValue ( $ProdIDList ; $K ) ]
                   End Loop
                   Go to Layout [original layout]

              • 4. Re: Auto-Fill Invoice with every Product
                MichaelLawrence

                     Thank you very much for responding so quickly Phil. I've tried the script so far and it's not working entirely. It basically is just adding one new product with the product Id of '1'. On the form I'm using the user normally selects the Item Name from a pop up menu. When it does insert the Product ID it doesn't automatically select or lookup the line item when the product id is inserted...

                     I created a new instance of the products table and called it 'Products ALL'. Then I created the relationship between those two with Invoice ID x Products ID.

                     Then I created the script as you indicated. One thing that I noticed is in the new starter solutions they don't have the Product ID field in the Invoice Data table. I created a new field called 'Product ID', and put the option as a Number, Indexed, Lookup. Hope that was correct. (I also added it to the form just so I can see which product id is being inserted for each LineItem).

                • 5. Re: Auto-Fill Invoice with every Product
                  philmodjunk

                       The first line of your script differs from mine. You need to use the List function to get a list of all Product ID's in one variable in order for this script to work.

                  • 6. Re: Auto-Fill Invoice with every Product
                    MichaelLawrence

                         Man. I'm sorry, you are absolutely right. I changed that line in the script, and now it pulls every single Product.  Thank you so much Phil.

                         The only thing I need to figure out now is how to make the 'Item' name pull up when the Product Id is filled into each line item.

                    • 7. Re: Auto-Fill Invoice with every Product
                      philmodjunk

                           If you have this relationship:

                           Invoice Data::product ID = Products::Product ID

                           Then you can add a field from Products directly to your portal row of your portal to InvoiceData so show that data from products. There are also auto-enter field options that can copy this data from Products to a field in Invoice Data as that is a useful way to look up the current price for each item.

                      • 8. Re: Auto-Fill Invoice with every Product
                        MichaelLawrence

                             Ah okay. Since the starter solution LineItem/Invoice Data table doesn't have the Product ID field, it uses "Invoice Data::product ID = Products::Product ID" relationship. I removed this relationship and replaced with 'Invoice Data::product ID = Products::Product ID' and that seems to do the trick.

                             I don't see anything wrong with removing this relationship do you?

                        • 9. Re: Auto-Fill Invoice with every Product
                          philmodjunk

                               It will likely break the starter solution. What I call LineItems, your starter solution calls invoice data. There are probably a number of auto-enter settings on fields in InvoiceData that now fail to work as they rely on that relationship to look up data from Products.

                          • 10. Re: Auto-Fill Invoice with every Product
                            MichaelLawrence

                                 Well that sounds unfortunate. I'm going to try it out now. What would be the best course of action in this situation then?

                                 I tried resetting the relationship back to Products::Item=Invoice Data::Item and then modifying the script by replacing Product Id with 'Item' and it seems to work. I'm going to spend tomorrow solely trying to break that script and anything else I can think of.

                                 Set Variable [$ProdIDList ; value: List ( Products|All::Item ) ]
                                 Set Variable [$InvoiceID ; value: Invoice::__pkInvoiceID ]
                                 Freeze Window
                                 Go to Layout ["InvoiceData" (InvoiceData ) ]     --> the latest two starter solutions call the "lineitems" table "InvoiceData".
                                 Loop
                                    Set Variable [$K ; value: $K + 1 ]
                                    Exit Loop If [ $K > ValueCount ( $ProdIDList ) ]
                                    New Record/Request
                                    Set Field [InvoiceData::_fkInvoiceID ; $InvoiceID ]
                                    Set Field [InvoiceData::Item ; GetValue ( $ProdIDList ; $K ) ]
                                 End Loop
                                 Go to Layout [original layout]

                            • 11. Re: Auto-Fill Invoice with every Product
                              philmodjunk

                                   What I am describing requires adding a new relationship between Invoices and Products that does not require changing any existing relaionships.

                                   Open Manage | Database | Relationships

                                   Select Products and click the duplicate button (two green plus signs).

                                   Rename the new Table occurrence to be Products|All or whatever name you prefer.

                                   Link it by any field in products to any field in Invoices, double click the relationship line and change the = operator to X.

                                   Now update your script's List Function calculation to return a list of ProductID's (or whatever this field is named in Products), for use in creating thise records in the Invoice Data table.

                              • 12. Re: Auto-Fill Invoice with every Product
                                MichaelLawrence

                                     Thanks for responding so quickly Phil. I'm sorry, I was responding late at night, and just copied your sample script with a few tweaks trying to illustrate my changes. I'm still having trouble getting the 'Invoice Data::Item' to refresh/lookup/insert whenever the product ID is pulled through.

                                     I have created the relationships you suggested and the new occurrence of Products as Products ALL. I used the script you provided using the Table/Field names in the appropriate areas. My screenshot of the script should be just as you intended.

                                     I only removed the default relationship of "Invoice Data::Item = Products::Item" because the Item name wasn't updating when the product Id was created. I added the relationship of 'Invoice Data::Product ID = Products::Product ID" like you suggested but unless I removed the default relationship it wouldn't work.

                                     That's the reason I tried the newer script with the Product::Item being the target of the List function and "Invoice Data::Item" SetField function.

                                • 13. Re: Auto-Fill Invoice with every Product
                                  MichaelLawrence

                                       I have switched all the basic default relationships back to normal and kept the new Product Table occurrence of course. I changed the script slightly and it seems to be working. I am going to continue my sample record creation and try to break it.