5 Replies Latest reply on Apr 19, 2010 2:27 PM by selmorzine

    Creating invoicing and PO functions

    ChrisHeier

      Title

      Creating invoicing and PO functions

      Post

      Hi, I'm very green with Filemaker, so bear with me a bit on this.  I'm trying to put together a layout in a purchasing database which will involve purchase orders.  I've tried playing with the PO layout included with filemaker, but I keep on making it break when I try and make it do what I want.

       

      Basically, the plan is to have a number of databases:

       

      Hardware

      Supplier

      Contact

       

      I've figured out how to set up the supplier and contact information so that I can select a supplier on a drop down list and I'm good for address and the like, same with contacts (albeit, slightly different).  My problem comes into play with the portal list.

       

      What I'm trying to do is tie the portals listing capabilities into the hardware database, so that when I type, paste or scan in a SKU, the manufacturer, part name, cost and weight all pop into the list.  I was successful doing this with the layout, but only for 1 entry.  I don't really understand how this portal thing works for entering data, so really, what I need to try and do at this point is to figure out how to make this list work.

       

      I want to just simply create a list of product simply by inserting the SKU, then do all of the post calculations on it thereafter like the PO template does.  Can I get some guidance on this?  I'm having troubles trying to find anything in the documentation that can help me out on this. 

        • 1. Re: Creating invoicing and PO functions
          TKnTexas
            

          In answer to the scanning of SKUs:  you need to have your set up include an ENTER key following each scan.  It will then advance for you. 

           

          From a scanning perspective, I would not scan through to a portal.  I would scan to the database directly.  Maybe others have a better answer than this.  

           

           

          • 2. Re: Creating invoicing and PO functions
            ChrisHeier
              

            I use scanning as just an example.  I know how to create forms where I simply reference to an item by SKU, and all of the other fields are filled out, by creating a direct relationship with the SKU line on that form to the SKU line in the hardware database, then just referencing related fields back to the hardware database.

             

            It is still similar functionality I want, only as a line item for quotes, invoices and POs.

             

            I'll generally have the fields; SKU, Manufacturer, Part Name, Cost, Weight; for doing POs, with SKU and quantity being the only user modifiable field on the line item.

             

            Here is an example. The supplier field is directly linked to my suppliers database, and automatically fills in the address info. 

             

            The problem is in the portal I broke.  I was able to insert a SKU, and the rest of the info fills in, but it ends up appearing twice, and I can't add more.

             

             I blame it on my lack of understanding as to how this all works, but I know Filemaker can do what I want it to do.  Like I said, I'm fairly green at databases in general, but at least the nice thing with Filemaker is that I've been able to pick up as much as I have within the first few days of my trial period.

            • 3. Re: Creating invoicing and PO functions
              TSGal

              Chris Heier:

               

              Thank you for your post.

               

              It is difficult to determine what is the cause without seeing the database, but there is a possibility that your portal is not broken.  Although I see two entries for that SKU, the right side displays two different entries.  That is, one entry has an Amount, and the other one doesn't, so maybe two records were entered into the portal.

               

              Find the key field that links the two tables together and copy the key for that record.  Go into the second table and search for that value of the key field, and if there are two records entered, remove one.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Creating invoicing and PO functions
                ChrisHeier
                  

                Thanks.  I should mark this solved actually.  I figured out what I had ended up doing wrong.  I went back and made a new database using the Purchase Order template again.  This time, I finally tried playing around with the line items relationships.

                 

                1) created a relationship between Line Items::ID_Product and Hardware::p_sku

                2) In the purchase orders table, I now point the Manufacturer, part and cost to the appropriate fields in the hardware database

                3) Did the same as in step 2 within the Line items layout.  Not sure if this mattered or not, but I did it anyways just in case.

                4) Updated the Product_ExtendedAmount calculation to reference the appropriate change to the Hardware::p_cost 

                 

                It seems to work thus far, and I think I may be able to go even further and put together barcodes in a physical product book that I can just scan into filemaker for all sorts of things. 

                • 5. Re: Creating invoicing and PO functions
                  selmorzine

                  Hi,

                   

                  I am new to FM and have found your question on this forum.

                  I am building a database to cater for customers, bookings i.e. booking ski lessons and then invoicing but I have got stuck on the booking bit.

                   

                  I have created a layout similar to the one on your question and I need to find out how to set up the portal properly.

                  Basically I need the portal to do sometime similar to yours, i.e. let me choose a product and then this will fill in some of the fields and I will fill in the rest, then let me add as many products as wanted.

                  I can't seem to get it to go past the first line, so it won't let me add more than one product and also it will not bring in the rest of the data even though it has been related, I need to do it manually. I have set up the data that I want in another layout and then set the relationship to go from the product name to the product name.

                   

                  Would you be able to help me, as I am running out of options and don't know it well enough to sort it out yet!

                  Many thanks,