5 Replies Latest reply on Mar 22, 2011 12:00 PM by philmodjunk

    Newbie question. Invoices template.



      Newbie question. Invoices template.


      Hi all!

      Taking as example the Invoices template included in FM 11 Pro, I wanted to add items to the Invoices layout portal using the Product Name field instead using the drop-down list that takes values from the Product ID value list.

      I enabled the checkboxes browse mode and find mode for the edit box to allow me write in, and also the auto-complete checkbox using existing values.

      That was an epic fail...

      I know the portal is taking data from the Line Items::Product Name fields. Table which is linked to the Related Products::Product ID, so only shows the product name if a product in other row has been added before. In that case, shows the product name while I'm writing, but not fills the part number, price, extended price fields, just only the name.

      The question is, how can I do it?

      I've been trying a lot of combinations with relationships but nothing worked, and I don't know too much about scripting coding to do it by myself.

      Please, could anyone help me to do this? I want to write only the product name, and when pressing tab, return or enter, automatically fill the Product ID, Price, etc. Taking the existing values from the Products or related table instead selecting it from the drop-down list.

      Thanks in advance for your patience and excuse my poor English.


        • 1. Re: Newbie question. Invoices template.

          To do this, you'd need a relationship that links the Product Name field in Line Items to the Product Name (or description, you'd have to check the table for the precise name) field in Products.

          Companies do change the names or descriptions of their products and you don't want such a change to adversely affect the function of your database. That's why the starter solution uses the Product ID code with a two column value list that also lists the product names in the first place.

          There are search tools you can devise that allow you to enter part of a name or description and then you can select from the list of matching products to add it to the invoice. A script enters the Product ID number into the next row of your portal when you select the product by clicking it or a button next to it.

          • 2. Re: Newbie question. Invoices template.

            Hi PhilModJunk!

            That was the first thing I did when tried to modify the starter solution. I linked the Products::Product Name field to the Line Items::Product Name but nothing is shown on the Product Name edit box in the invoices portal when I write in.

            I assume I'll need to add a script to find the Product ID of the Product Name on the portal edit box triggered by the event OnObjectExit, but I don't have idea about how that script should be.

            Please, could you take a look to the Invoices starter solution and tell me what should do I do? I know I'm asking a lot, but I have no idea and I'm totally lost.

            Anyway, thank you very much.


            • 3. Re: Newbie question. Invoices template.

              Setting up the relationship, remember that I said this was not a good idea, is just the first step. To get the drop down of product names requires that you create a value list of the names and format the field to use it.

              Here's one scripted approach that you can try to adapt to this purpose: Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

              You might also download the Known Bugs List file (click the blue download button) and look at the portal in the window that pops up when you click New or Edit on the first layout. There's a portal field with a drop down, auto-complete enabled list used to look up bug reports by name, but then the triggered script enters the BugID number to keep the relationship. The issue with this is that the names used may change in the future. This is an issue I judged minor for bug reports, but could create more serious issues with an invoicing system.

              With these scripted methods, you should not change the relationship as it remains based on the ID number.

              • 4. Re: Newbie question. Invoices template.

                I already know your autocomplete drop down tutorial, before asking I did a search. I was trying to implement with my database but I wasn't able to do it work.

                Anyway I think your known Bugs List database may help me. I don't understand everything but does all I want to do. Let's see if I can implement into the starter solution.

                Thanks again.

                Best regards,

                • 5. Re: Newbie question. Invoices template.

                  The bug list file and the tutorial implement much the same approach here. Feel free to focus in on specific areas where it didn't work for you.