1 2 3 Previous Next 31 Replies Latest reply on Apr 3, 2017 10:51 AM by macfiles

    Complete noob working on a database

    macfiles

      Hi Everyone,

      I tried to look up what I need to do, but I'm unable to figure this out.

      Firstly, I'd like to point out that I know absolutely nothing about filemaker but I've been tasked with creating a functional database for our department. (BTW I'm a purchasing agent.. not IT)

       

      What I'm trying to do:

      I need help figuring out what can link

       

      Products > Workorders > Customers

       

      So that the Products are searchable in the Workorders and those products also show up in (possibly a portal?) the Customer's information. I want this section to show information from the work order in relation to the customer. It needs to have the product sold.

       

      I haven't adjusted this screen yet, because I don't know where to begin. I can't make a portal here unless the workorder screen showing the products somehow can link the products without a portal. I thought about using a drop down list, but there are too many products for that.

      Plus, each one needs to have a cost.

       

       

       

       

       

       

      Please! Any suggestions!

       

      Also, please dumb it down a bit for me. I've been watching all the videos and took the "training course" that didn't teach me anything at all.

      I basically don't know anything about this program but am doing my best!

       

       

       

       

      Thank you all!!!

        • 1. Re: Complete noob working on a database
          philmodjunk

          You need to figure this out on paper in a bit more detail before you start working with the software. Maybe you've done that, but if so, you haven't put it in your first post here and so we need to ask some questions.

           

          Customers---<Workorders    (---< means "one to many" )

           

          it would seem obvious that for any given customer, there can be many work orders and that for any given work order, there can only be one customer. But if this is not the case for your business, please let us know.

           

          Work Orders   ???   Products

           

          This is not as obvious. Will a work order only be linked to a single product? That seems the most likely case, but is it always the case for you business without even very unlikely exceptions?

           

          Assuming that this is so, you have what we call a "many to many" relationship between customers and products. Any given customer might be linked to more than one product and any given product might be linked to more than one customer. Again, this is just guessing on my part, might not be the actual case. Products might be specific to only one customer.

           

          If this is a "many to many", you can set up these relationships:

          Customers---<Workorders>----Products

          Customers::__pkCustomerID = WorkOrders::_fkCustomerID

          Products::__pkProductID = WorkORders::_fkProductID

           

          In manage database, you can define these tables and these fields, then switch to the relationships tab and drag from field to field (of those listed above after the::) to create relationships. Then double click the relationship lines and enable "allow creation of records via this relationship" for WorkOrders in the customers to WorkOrders relationship.

           

          (I'm using my preferred naming conventions for these fields. __pk identifies a primary key. The two underscores ensures that sorting the fields by name will list it first before all other fields. _fk identifies a foreign key. the single underscore ensures that it will sort to just below the primary key field.)

           

          This then makes it possible to put a portal to WorkOrders on your Customers layout. You can put _fkProductID in this portal row and set it up with a value list of Product ID's and Product names. This then allows you to create a new WorkOrder linked to the current customer by selecting a product from this value list.

           

          Since I'm working from a number of assumptions that could be wrong, I'll stop here. This is far from a complete solution. There are other options for creating and managing work orders, but this is a simple way to at least get started working with FileMaker.

          • 2. Re: Complete noob working on a database
            macfiles

            I do have this drawn out on paper. But it mostly just looks like the relationships table.

             

             

            "It would seem obvious that for any given customer, there can be many work orders and that for any given work order, there can only be one customer. But if this is not the case for your business, please let us know.

             

            Work Orders   ???   Products

             

            This is not as obvious. Will a work order only be linked to a single product? That seems the most likely case, but is it always the case for you business without even very unlikely exceptions?"

             

            Yes,

            It will be many to many with products to work orders. And Many to many with work orders to customers.

            • 3. Re: Complete noob working on a database
              BMyers

              FileMaker can be difficult to wrap your head around at first, especially if you haven't worked with databases before.

               

              As a former newbie myself, I'll deliver the bad news to you: it will take 30-50 hours to learn and get up to speed at a basic level.  If you have that time, you will build a great foundation that will help your company and make you a more valuable executive.  If you don't, that's fine too.  Don't punish yourself with trying to do a difficult project.  Hire a consultant.

               

              You report that you have watched "all the videos and took the 'training course' that didn't teach me anything at all."  Sounds like you need other videos or a different training course.  Believe me, you will drive yourself crazy asking questions on this forum without the necessary foundation to ask the right questions and understand the answers.  There is no shortcut on that 30-50 hours.  And that's to have a basic level of competency.  You'll spend the next 100 hours asking yourself simple questions and finding the answers take four or even eight hours to answer. 

               

              The Lynda.com courses are good. I particularly like the YouTube videos by Guy Stevens.  He has a whole program on products and orders.

              2 of 2 people found this helpful
              • 4. Re: Complete noob working on a database
                philmodjunk

                I'm not sure that you understood me. You say "yes", but then what you say after that contradicts what I posted.

                 

                What I sketched out is Many to man from Customer to products. From Customers to workOrders is one to many. From Products to workOrders is one to many. WorkOrders would be a Join table linking the the other two in a many to many relationship.

                 

                So let's look at it another way.

                 

                Will any single work order need to link to more than one product?

                Will any single work order need to link to more than one customer?

                 

                Will any given product ever link to more than one customer?

                • 5. Re: Complete noob working on a database
                  macfiles

                  Oh my gosh.. Thank you so much. My boss keeps saying

                  "well you don't need to learn scripts or anything complicated."

                  I feel like everything is complicated. He bought the FileMaker pro training course for start ups, but it only tells me WHAT I can do and not HOW to do it. Which I understand, but I need actual training.

                  I know absolutely ZERO about scripts or anything even closely related.

                   

                  How did you learn? I've been trying since at least November 2016. I 100% agree with you about becoming more valuable. Which is why I haven't given up yet.

                  • 6. Re: Complete noob working on a database
                    philmodjunk

                    "well you don't need to learn scripts or anything complicated."

                    Trying to create anything but an extremely basic solution without scripting is like trying to row a row boat with only one oar.

                    • 7. Re: Complete noob working on a database
                      macfiles

                      Will any single work order need to link to more than one product?

                      Yes, I would like to add 50 products on an order if needed.

                       

                      Will any single work order need to link to more than one customer?

                      No, only one customer per work order. But many work orders per customer.

                       

                      Will any given product ever link to more than one customer?

                      Yes, I want the products to be able to be used "universally" throughout all of my customers and work orders.

                       

                       

                      Thank you for your patience with me!

                      • 8. Re: Complete noob working on a database
                        macfiles

                        Great analogy. I'll use that the next time he tries to pull that.

                        • 9. Re: Complete noob working on a database
                          philmodjunk

                          Will any single work order need to link to more than one product?

                          Yes, I would like to add 50 products on an order if needed.

                          This detail significantly changes the basic data model.

                           

                          Customers----<WorkOrders-----<WOlineItems>------Products

                           

                          A portal to WOLineItems would typically be used to list multiple products on a single work order.

                           

                          This is essentially the same data model used in many invoicing and purchase order systems:

                           

                          Customers---<Invoices----<InvoiceDetails (or line items)>----Products

                           

                          Is the same structure, but with different names. I point this out, because any training materials that show you how to set up an invoicing system can be used to teach you how to set up your system.

                           

                          With this set up, you have multiple options for how to report information on products for a given customer.

                           

                          A portal to WorkOrders on the customers layout would list each Work Order once. Clicking a button in the portal row could open a popover that lists information about each product listed on that work order.

                           

                          A Portal to WOLineITems would list all the products for all the work orders for a given customer. IF a products appears on multiple work orders, it would be listed multiple times.

                           

                          A Portal to Products on the customers layout would list every product that is listed in a work order, but only once for each product.

                           

                          A summary report could be set up on a Layout based on WOLineItems that lists itemized info on WorkOrders, for a single customer or multiple customers. This report can be configured any number of ways depending on what you need.

                           

                          Cross tab reports that pull data from WOLineitems is also possible.

                           

                          And yes, you do need to invest more time in training. The forum is best suited for answering specific questions. You need training to provide a foundation of basic concepts on which to build on as you create a solution and ask those specific questions here in the forum.

                          1 of 1 people found this helpful
                          • 10. Re: Complete noob working on a database
                            macfiles

                            THANK YOU!

                            What do you suggest being the best way to learn this program?

                            • 11. Re: Complete noob working on a database
                              philmodjunk

                              I can't really give you recommendations. Every set of training materials makes basic assumptions about what prior knowledge the typical student starts with and on what teaching method will work for that student.

                               

                              You have had some good options suggested already.  You'll need to give different options a try and see what works best for you. Just be prepared to invest significant time in acquiring the needed skills.

                               

                              PS

                              I learned Filemaker by

                              a) graduating with a computer science degree

                              b) taking a class in how to use HyperCard

                              c) purchasing a Mac that had an educational bundle that included FileMaker Pro 2.5

                              d) reading FileMaker Help

                               

                              Back then, the books, videos, Blogs and Training eBooks did not exist.

                              • 12. Re: Complete noob working on a database
                                BMyers

                                philmodjunk: Trying to create anything but an extremely basic solution without scripting is like trying to row a row boat with only one oar.

                                 

                                 

                                I'd take that one step further.  Trying to create anything but an extremely basic solution without scripting is like trying to row a boat with no oars.

                                 

                                A person can't save a screen layout or a report without scripting, unless I'm missing something.  I see where a user can save a search, but it won't be sorted.  A script is necessary to save both a search and sort.  And a script is needed to attach a layout to a button.

                                 

                                It's true that these scripts are really basic and simple.  But to a Newbie who has not taken a CS course, I would imagine that looking at the script editor for the first time must be like looking at a Greek language textbook.  The user is floating with no means to go forward.  The user says "all I want to do is save my work."  FileMaker Corp. says "yeah, we know this is a 100% use case but we're not going to make it easy.  Go spend two hours learning the basics of scripting and then re-create your carefully-prepared report with scripts."

                                • 13. Re: Complete noob working on a database
                                  philmodjunk

                                  I really don't disagree, but:

                                   

                                  A person can't save a screen layout or a report without scripting,

                                   

                                  Did you forget "Save as PDF" as can be selected from the FileMenu?

                                   

                                  Most of the script steps except "branch and control" steps have corresponding menu options. Thus, as long as you keep it basic and simple, a fair amount can be done from menu selections. Much like zig-zagging across a pond in that single oar word picture...

                                  • 14. Re: Complete noob working on a database
                                    BMyers

                                    Phil, I did forget the Save as PDF function.  I guess that would work for some use cases.  I was thinking of the more common situation like when a user wants to see all the orders placed yesterday.  That could be a pdf but why create and store a pdf?  Or create and print a pdf.  The user just wants to see a quick summary of yesterday's activity sorted by customer.  I don't know how to do that and achieve MacFiles' boss's view that ""well you don't need to learn scripts or anything complicated."   As I see it, that requires a button, a special layout, a script, a date calculation, probably calculation fields for total dollar and total unit quantity.  Then inevitably someone at work will say: hey, it would be easy to do a report that would graph the amounts of each product sold last month, right? 

                                    1 2 3 Previous Next