1 2 Previous Next 18 Replies Latest reply on Jun 18, 2010 9:37 AM by philmodjunk

    SOme help on thinking...

    LuisN

      Title

      SOme help on thinking...

      Post

      Hey Guys, i need some help here.. i am not sure how to set this up

       

      Ok so i have Invoice DB, Sales Order DB, and Inventory DB.  heres what i need to do.

       

      someone orders something thats in inventory, fine. add it to the line item move on. BUT client wants to order something "Custom". So we skip the insert of the "Item / Part # " thats from inventory and we just type into the description , Wheel 20" etc as example.

       

      Now this "Special order" is from Company A ok.

       

      Lets say that same client wants to order another item, Custom as well.

      Wheel 22" from Company A again.

      1 more time the client wants a custom item

      we enter it in with price and all BUT this is from Company B!

       

      ok now, in order for ME as the dealer order these custom parts i want to create a "Line item" for each item for Company A or Company B to then PDF and fax to the company A and B. i want to be able to when pressing this button it will send the 2 first line items with the correct info to the Purchase Order DB for Company A and then the 3rd item to Purchase order DB for company B.

       

      Did i explain it correctly?

        • 1. Re: SOme help on thinking...
          philmodjunk

          Some questions: How do you designate "company A" and "company B" in your order. Do you have a field in line items where you specify this?

           

          "...send the 2 first line items with the correct info to the Purchase Order DB for Company A and then the 3rd item to Purchase order DB for company B"

          Does this mean you have created separate tables for each such supply company? (Not what I'd recommend here.)

           

          One thing that can be scripted is a process that:

          1) switches to a layout based on line items

          2) performs a find for all the special order line items

          3) sorts the records by supply company so that all items to be supplied by the same company are grouped together.

           

          That's useful for report purposes, but I wouldn't "send" records to another table such as a Purchase Order table. Instead, I'd create Purchase order records that link to the appropriate line item records. How I'd set that up depends on what I need for this.

           

          Do you send out one such purchase order per customer order (assuming it has a special order item), per supplier company?

           

          or do you send one purchase order for all items special ordered from that company for the same day's invoices (or even a larger span of time)?

           

          • 2. Re: SOme help on thinking...
            LuisN

            Ok So i think i got you and i almost did it. i did this

             

            i made another DB called Line Items. So if the "Invoice" has custom items, you have to click on a button that will script and check if either Product # is filled in or if the Reference which is the # to the line item if its already filled out in the Line Items DB then it will NOT run the script. if it does then it will go to the DB line Items and you manually enter it in , all the info. it creates a Ref. ID #.

             

            then when creating a "purchase order" you have go into the Purchase Order DB. BUT what i want is, in the Portal, i want to have a + sign which then does this. so i need some help.

             

            First, checks the Field "Company" so lets say ABC. if the company ABC then check all OPEN line items that have that status. (if the PO is already faxed or printed the line item should be changed to Complete) then it will open a window which shows you all the open Line items. you then select the line item ref # and it automatically adds it to the PO portal , Ref #, Description, Price, quantity etc.

             

            how would i do that?? =(.

            thanks in advance for the help.

            • 3. Re: SOme help on thinking...
              philmodjunk

              First, is this what you now have in your relationships in Manage | Database | Relationships?

               

              Invoice----<LineItems>-----Inventory

               

              Do you have a portal to LineItems on your inventory layout so that one Invoice can list multiple purchased items?

              If so, then you should have these match fields linking Invoice to LineItems: (You may have different field names)

               

              Invoice::InvoiceID = LineItems::InvoiceID

               

              Where Invoice::InvoiceID is an auto-entered serial number.

               

              I would then add two fields to LineItems if they aren't already so defined:

              PO_ID and SupplierID

               

              I'd link my Purchase Order table to LineItems like this:

              Purchase Order:: PO_ID = LineItems:: PO_ID

               

              Purchase Order:: PO_ID should also be an auto-entered serial number.

               

              Then, I'd create a new Purchase Order record, select the Supplier ID for this record and run the following script:

               

              Set Variable [$SupplierID ; Value: Purchase Order::Supplier ID]

              Set Variable [$PO_ID ; Value: Purchase Order:: PO_ID

              Go To Layout [ LineItems ]

              Enter Find Mode []

              Set field [LineItems:: PO_ID ; "=" ] // Finds records where PO_ID is empty

              Set Field [LineItems::SupplierID ; $SupplierID ]

              Set Error Capture [on]

              Perform Find []

              IF [ Get ( FoundCount ) > 0 /* Custom order records for this supplier were found */]

                  Replace Field Contents [ no dialog; LineItems:: PO_ID ; $PO_ID] //assigns all unassigned custom order items for this supplier to new PO record

              Else

                 Show Custom dialog ["No unassigned custom order items for this supplier were found..."]

              End IF

              Go To Layout [Original Layout]

              • 4. Re: SOme help on thinking...
                LuisN

                Wow, lol. :)

                 

                ok so will that script when pressing the + button go and select all the OPEN Line Items from the specific Vendor?

                • 5. Re: SOme help on thinking...
                  LuisN

                  Also, i have the PO_ID as the RecordID is this ok?

                   

                  Also i am not involving Inventory in any of this. I have the following DB's

                   

                  Invoice DB which is where the employee will pick the Product ID. and it will auto fill in the rest of the fields.

                   

                  Then theres Line_Items DB where this is just a "GHost" or a dummy DB to dump the Special Orders

                   

                  Last is the Purchase_Order DB

                   

                  If i click inside the Invoice DB add line item,

                  it grabs the Description, Price, and dumps it into a new record in the Line_Items DB which then has a Ref # which ='s the RecordID

                   

                  Once the Employee does this, they should go back to the Invoice DB and in the Ref # field of that item they have to choose the new Ref # from the Drop down menu.

                   

                  The part im stomped on is in the Purchase_Order DB

                  i need to click create PO and it goes and brings up that window that allows you to select the "Open" line items that for the specific Company chosen.

                   

                  did i explain it a bit better?

                  • 6. Re: SOme help on thinking...
                    philmodjunk

                    We have some problems here: 

                     

                    Also, i have the PO_ID as the RecordID is this ok? 

                    Not a good idea. See this recent thread for why: http://forum-en.filemaker.com/t5/Using-FileMaker-Pro/Why-Record-ID-s-should-not-be-used-as-Primary-Keys-in-Filemaker/td-p/69610

                    An auto-entered serial number will avoid trouble in the future.

                     

                    Invoice DB which is where the employee will pick the Product ID. and it will auto fill in the rest of the fields.

                    Then theres Line_Items DB where this is just a "GHost" or a dummy DB to dump the Special Orders

                     

                    I'm trying to steer you away from that approach. In a typical Invoice, the customer may need to order more than one item. This is usually accomplished by using a portal to a LineItems table on the Invoice layout so you can fill in information for multiple items in one transaction with the customer.

                     

                    It's generally a bad idea to store the same information in multiple tables. What I am suggesting is that you enter the information once and then simply create PO records that link to groups of line item records based on the Supplier selected for that PO.

                    • 7. Re: SOme help on thinking...
                      LuisN

                      Hey Phill, sent you a PM.

                      • 8. Re: SOme help on thinking...
                        LuisN

                        Hey i might be able to make this easier.

                         

                        Ok, IF i have a drop down field. that lets me choose the "Reference / Line Item ID" IF i choose the correct Line Item ID, it should auto fill all the field, description, price etc into the each field. How would i do that?

                        • 9. Re: SOme help on thinking...
                          LuisN

                          Yes this will actually work really well. i choose from the drop down meny the Line Item ID and i need to make a script that will

                           

                          Get the fields needed from that Record and fill them out into the corresponding Fields. this will make it much easier for me.

                          • 10. Re: SOme help on thinking...
                            philmodjunk

                            I hesitate to say this before looking at your files that you emailed to me, but you shouldn't need a script, you can set up looked up value options on each field to copy the data over once you've selected a value in the key field.

                            • 11. Re: SOme help on thinking...
                              LuisN

                               :)

                               

                              how do i do that. lol

                              • 12. Re: SOme help on thinking...
                                philmodjunk

                                Hold that question, you've got some basic issues that need to be resolved first.

                                 

                                Ok, I've glanced at your files and I see some basic problems. For starters, You have three tables named LineItems, one in Sales_Orders, one in Purchase_Orders and a third file you've created and linked into the other two. What I've suggested is that you have exactly one LineItems table that is linked to both Sales and Purchase Order tables.

                                 

                                Having looked at the file, I'm not completly clear on whether that will work for you. Please answer the following questions:

                                 

                                Do you need to put items on a purchase order that aren't listed on a Sales_Order?

                                If no, then do you need to list items on a purchase order that aren't custom orders?

                                • 13. Re: SOme help on thinking...
                                  LuisN
                                  Well no for now. When creating a purchase order from custom orders from the invoice, I only need the special order. Maybe if we run out of inventory, yes ill need to then create a PO . Does that sound ok?
                                  • 14. Re: SOme help on thinking...
                                    philmodjunk

                                    It's just a matter of understanding your business model.

                                     

                                    In a sense, you need to generate a purchase order for more product whenever inventory levels reach their "re-order" point--which could be zero or you could set up your system to order sooner to avoid being out of inventory because the re-ordered items haven't arrived yet. Since special order items, by definition have an inventory level of zero, they automatically trigger the need for a purchase order.

                                     

                                    Make sense?

                                     

                                    Managing your supply chain so that you aren't out of stock on any critical items but don't have all your capital tied up in unsold inventory is one of those balancing acts that can make or break a company's profitability.

                                     

                                    If I've analyzed things correctly, you will need either two line item tables or a single table with the records identified as either purchase order or sales order line items.

                                    In either case, data from the special order line item records on the sales order form will need to be copied to a new Line Item record. What's intriguing here is selecting the optimum approach for handling special order items.

                                     

                                    Can you send me a copy of your Inventory file? I'm curious to see which method is used to compute inventory levels in this file and that may help with the final analysis here.

                                    1 2 Previous Next