1 2 Previous Next 20 Replies Latest reply on Dec 14, 2012 11:47 AM by philmodjunk

    Creating new orders

    EP

      Title

      Creating new orders

      Post

           Can someone shed some light on recommending a way to add new orders for a client?  Lets say I have a database of clients;  I want to add new orders to their record (when the place a new order) which appear in a portal (Orders Pending) on a layout with 3 portals: 1-Orders Pending 2-Orders Shipped 3-Orders Completed).  I know how to set up the portals and relationships. I also know how to use portal filtering so when I click a drop down list in portal 1 and select "Order Shipped" the clients order/info will jump into portal 2.  

           My question is this: How do I create this in the Clients Record so that I can add orders to their record? Should I use a portal? In other words, I click on Johnny Smith's record which contains all of his contact information.  What fields/portals/etc do I need to add to this layout so I can add orders to his record?  Thanks

        • 1. Re: Creating new orders
          philmodjunk

               Do you have this relationship?

               Clients-----<Orders

               Clients::__pkClientID = Orders::_fkClientID

               But perhaps with different names? (see Common Forum Relationship and Field Notations Explained if my notation is not familiar to you.)

               If so, you do not need to add any fields to create a new related record in Orders for the current client record:

               Freeze Window
               Set Variable [$ClientID ; Clients::__pkClientID]
               Go To Layout ["Orders" (Orders)] ---> any layout based on orders will work
               New Record/Request
               Set Field [Orders::_fkClientID ; $ClientID]
               Go to Layout [original layout]

               Note: this script can trip certain script triggers each time it changes layouts.

          • 2. Re: Creating new orders
            EP

                 Makes perfect sense thanks.

                 Do you have any tips on preveting data entry people from creating duplicate clients? For example, Johnny Smith calls in an order and a new employee takes the order and enters Johnny Smith as a new record, instead of realizing he already exists?

            • 3. Re: Creating new orders
              philmodjunk

                   I once dealt with that with a database that managed Volunteers. Since there were multiple ways a volunteer's contact info could be added to the DB, there was a very high likelyhood of the same volunteer being registered twice.

                   It's a very "messy" issue as there is no single piece of info that can identify a duplicate with 100% accuracy. People do have the same name and sometimes they even live in at the same address (John Smith Jr. might live with his father John Smith Sr.). And names have idiosyncratic spelling so that one employee might enter "Jon Smith" while another enters "John Smith"...

                   What I came up with was a "name search" portal that the user would use to select an employee. The portal displayed name, address and contact info, but a portal did a "contains this text" filter on the names in the portal. The user could then click a portal row to select a contact or start a new contact record if it looked like none of the names matched.

                   Here's a demo file that contains that search portal along with several other methods of finding records by name: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7 If you are using FileMaker 12, open it from the File menu to get a copy converted to .fmp12 format that you can open and inspect.

              • 4. Re: Creating new orders
                EP

                      

                     Do you have this relationship?

                     Clients-----<Orders

                Clients::__pkClientID = Orders::_fkClientID

                But perhaps with different names? (see Common Forum Relationship and Field Notations Explained if my notation is not familiar to you.)

                If so, you do not need to add any fields to create a new related record in Orders for the current client record:

                Freeze Window
                     Set Variable [$ClientID ; Clients::__pkClientID]
                     Go To Layout ["Orders" (Orders)] ---> any layout based on orders will work
                     New Record/Request
                     Set Field [Orders::_fkClientID ; $ClientID]
                     Go to Layout [original layout]

                Note: this script can trip certain script triggers each time it changes layouts.

                     ------------------------------------------------

                     Maybe I need a break, but I've become confused in the actual execution of this.  I have a layout with client records, let's make believe just 3 fields: 1-Name 2-Address 3-Phone.  Now I have a layout with Order information and the following fields: 1-Product 2-Price 3-Order status.  How do I actually link the order to the Client?  

                • 5. Re: Creating new orders
                  philmodjunk

                       Go to Manage | Database | fields

                       Select the Client table.

                       enter a field name such as __pkClientID. Make it a number field and either double click it or click Options to open the Field Options dialog box. Select the auto-enter tab and select the serial number option.

                       Now select the oders table.

                       Add a new field, _fkClientID and define it as a number field. Do not select any auto-enter field options for this field.

                       Click the relationships tab.

                       Drag from __pkClientID to _fkClientID to link the two in a relationship.

                       But since you have three portals to Orders, you should already have this relationship in your database for use with one or all of those portals...

                       The script then links the order to the client by copying the value of __pkClientID into the _fkClientID field of the newly created order record.

                       Please note that with the correct relationship options, you can create a new linked orders record by simply entering data into the bottom blank "add" row of a portal to orders.

                  • 6. Re: Creating new orders
                    EP

                         Been giving this some thought.  An idea I came up with:

                         I want to put a button (add new order) above the orders portal.  When clicked, a popup window will display for the user to input the order into the appropriate fields;  there will be a field with a value list referencing the Clients names and addresses.  The user can begin typing and if there are no matches, the user will be prompted: "Would you like to add a new client?" And can enter the new clients info.   Is there any way I can script this?

                    • 7. Re: Creating new orders
                      EP

                           In trying to execute my above idea, the part I am stuck on is: I can't seem to have the _fkClientID and _pkClientID link up correctly since I am doing this backwards (Many-to-One relationship?) .  Do I just need to change my relationship around?

                      • 8. Re: Creating new orders
                        philmodjunk

                             I thought that the whole idea was to create a new order for the current client record?

                             Many to One isn't really a problem. Using a drop down list to select a parent record for a new child record (such as selecting a client for a new order record), is a very common way to work with related records in FileMaker.

                             The simple to set up solution (Not necessarily the best solution):

                             Define a value list of ClientID's and Client Names from the client table. You can specify that the value list, use values from two fields, make the ID field the first field and a full name or even a full name/address field as the second field. (You may need to define a field with an auto-enter calcualtion that combines data from several fields for this second field.)

                             Now you have a value list to use where you can select a client by name, but the value list enters the ID number when you make a selection from the value list. A button next to this drop down can be used to start a new client record if the client cannot be found in the drop down.

                             Where this gets cumbersome is the amount of scrolling through names that you may have to do if you have a client list of any appreciable size.

                             If you are up to adding an additional relationship and some scripting, you can, instead, set up a text field for the client's name and a value list of names with auto-complete enabled, where a script manages some of the details. The result is a value list that links the records by ID yet handles cases where there are duplicate names or there is no matching name--triggering the dialog offering to start a new client record.

                             See this demo file:

                             FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                             Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                              

                        • 9. Re: Creating new orders
                          EP

                          I thought that the whole idea was to create a new order for the current client record?

                          It *was* until I tested my solution and found a better way for what I am trying to achieve (Your post pretty much states exactly what I want to do).  I am going to look at the file you attached and try the scripting, as I think this is a "cleaner" way to go.

                                

                          Thanks again.

                          • 10. Re: Creating new orders
                            philmodjunk

                                 You may also want to consider one of the "search" portals shown in the following demo file:

                                 This gives you a "contains this text" kind of search with the results listed in a portal where the user can decide whether or not the data listed in a portal row is the client that they are looking for. If it is, they can click the portal row to perform a script to enter the ID number of that record where needed in your database. If the search text comes up dry, they click a button to start a new client record. This option may better handle the often unusual ways that people spell their names.

                            https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

                            • 11. Re: Creating new orders
                              EP

                                   Do I need to change my relationship from     _pkClientID--<_fkClientID       to       _pkClientID>--_fkClientID for this to work?

                              • 12. Re: Creating new orders
                                philmodjunk

                                     No. (and if it did make sense to do that, your fields would be incorrectly named--causing possibly major confusion down the line...)

                                • 13. Re: Creating new orders
                                  EP

                                       In the last file you linked me to.  The search portal called "Pattern Matching Search"- How did you enter the fields into the portal, when I click them it looks like they are "locked" or uneditable. Thanks

                                  • 14. Re: Creating new orders
                                    philmodjunk

                                         That is by design. The layout is not designed to permit editing data inside those search portal records.

                                         The fields are from a related table of Products. I've used Button Setup to turn the fields in the portal row into a button so that clicking them performs a script to select that record to enter the product ID of that portal row into a new record in the lineItems table.

                                    1 2 Previous Next