8 Replies Latest reply on Jun 9, 2012 9:02 AM by karendweaver

    Need some advice on just how to set this up correctly.....


      I am trying to build an application (Filemaker Pro 11) and can't quite get my head around the best way to set up my tables, etc. Basically the larger part of the database is a no big deal Contact Management style database. However, a small subset of "contacts" will become "clients". Once a contact becomes a client, I need to create a set of about 300 records, keyed to the new client, each with from two to maybe five or six or more fields. These records will contain the new client's "selections". For example: Client is John, his selection for "Roofing" is "Concrete tile", "Style 112", "Color green" = a record with four fields keyed to client John.


      My initial thought was to have a table that had a Master Set of all of the possible records and fields needed and to then some how copy these records to a related child file to the Contract Management table keyed to the new client. Further my thought was that once the contact became a client, there could be a button that would start a script that would copy the records into the related child table. I have a suspicion that there is probably a better way to implement this but I don't really have a clue


      Also, there seems to be many threads on how to copy records from one table to another using loops, but a lot of people seem to have trouble with this. Being new to Filmmaker Pro, I am a little wary of trying to implement a copy from one table to another especially if there is a better way to set up my application.


      So is this the way to go?





        • 1. Re: Need some advice on just how to set this up correctly.....

          Something's amiss in your described data model: If there is a chance that, once a contact is a client, they will order recurringly, you need to set up an Orders table that is tied to client. When a contact becomes a client, create a new record in Orders and relate the ordered items (copied/imported from the MasterList) to that record, not to the Client record. This would be no different from an Invoicing system, where many customers can have many orders/invoices, each of which consists of one or more line items, which in turn are based on a Products table, and where you never connect a product directly to a customer. (As far as I can, unless you work for free, your system will be an Invoicing solution, with Invoices tied to Orders; but that's beside the point here.)


          In fact, you should set up an Order table even if you are sure there would be no recurring orders, because you need to store the order details somewhere, and if you'd do that in the Contacts table, then, among Other Bad Things™, the database scheme would be cluttered with fields which are not needed for a regular contact.


          You should also definitely keep a MasterList, because this is in effect your Products table from the example above, and you need it to keep track of prices, inventory levels, re-orders etc.


          As for the creation of child records / line items: If you really need to create a lot of them, it will work faster to isolate those entries in the MasterList which are selected for a given order and then import them (that is, their IDs and other order-related data) into the child/join table. Then do a Replace Field Contents to set the foreign key for the OrderID. (This is easy because after an import, the imported records constitute the Found Set.) Replace Field Contents is problematic in a multi-user environment due to record locking issues, but should be OK for newly created records which no one else can access at that moment, or for a single-user solution.


          Importing is definitely an option to keep in mind, and it might be easier to grasp at first then list processing / looping. (Which at some point you can't avoid, so you might as well get over with it )

          1 of 1 people found this helpful
          • 2. Re: Need some advice on just how to set this up correctly.....

            There is no chance that the master list will be "reordered". In fact, I was thinking that I need some little code to turn off the button once the list had been copied over to the new table. I might have explained this better. We're a home builder. Once a contact becomes a client, we sit with them and they make "selections".  The selections change over time as the clients change their minds about things, but at any given time, there can only be one answer as to what the clients wishes are in terms of "Roofs". After we price out the clients home, there are lots of changes to selection decisions as the actual cost of the project becomes apparent. At some point, they commit to their various selections or choices, sign a contract with us, and they don't change after that point without a lot of cost to the client. Various people have access to the clients selections during the process. Once the client commits to the various selection decisions, we print a report and send it to our subcontractors/suppliers which implements the clients decision. I did not mean to imply that I would store this information in the contact table like a flat list, sorry if I led you astray on that. I was planing on a separate table, again called "client selections" that is a child table to the contact table.


            You have given me an idea but I am not certain at this point how to implement it. Rather than copying the entire very long list of possible selections to the child table for the client, the first time that our employee sits with the client and begins the selection process, they could scroll down through the master list of possible selections. If the clients project will have a roof selection, our employee could mark that record in the master list somehow and then it would be moved to the clients personal selection list. So I would be doing an import. I'll have to have some way to avoid the employee making multiple imports of the same line item - there can't be two roof selections in the clients database. That would be a disaster as I am certain we'd end up building the house with the wrong roof on it.


            Thanks for your help



            • 3. Re: Need some advice on just how to set this up correctly.....

              … or with two roofs – which in some regions might prove helpful


              You could set up like this: Customer ( -> Order ) -> CustomerSelections <- Items <- Categories. The Categories table would, among other things, help you constrain the item list during selection, e.g. while you're discussing the roof with the customer, show only items from the Roof category. In CustomerSelections, you could use a status flag, e.g. suggested, pending, approved etc., to show the status of the respective item within the project.


              Btw, originally I was thinking you would process just one list of master items which would then be turned into a selection list, in which case an import would make sense. The way you describe it now, it's better to create/delete child records on the fly via script to simulate checking/unchecking an item or fill a (short) list (no looping involved here …)


              I can give you some pointers re selection layout/processing, like avoiding multiple entries for a category etc. Let me know how it goes.

              1 of 1 people found this helpful
              • 4. Re: Need some advice on just how to set this up correctly.....

                Why import or copy the selections at all?  You can just change the relationship from the contact or prospect to the client..  That is, in the selection table, have a foreign key for contact and another for client.


                You can also have a lookup in the client table based on the old prospect key.  Create a new record, populate it with the prospect key, and the client table can look up any relevant info from the prospect table.  Then go to the prospect's selections and populate it with the new client key.  You can delete the prospect record then.


                Way faster, I bet.

                • 5. Re: Need some advice on just how to set this up correctly.....

                  I need to consider this, both sound like they would work well. Karen's suggestion has me a little confused. There is only one record for prospect/contact/client. I didn't want to create multiple records when a contact changes its status to client. I've been using a drop down list on the main layhout that indicates what the clients status is.


                  Maybe I am misunderstanding her suggestion.


                  Thanks for everyones help here.

                  • 6. Re: Need some advice on just how to set this up correctly.....

                    I misunderstood what you were saying, actually - I thought you had a separate table for customers.


                    Yes - that is the way that I would do it.  keep all the contacts in one table and just change status.


                    In that case, all you need to do is create a security rule in the selections table - once the selections are committed - contract signed, then do not allow changes to the selections.  You can use the status in customer - but you will also want a way to lock that down.


                    This you can set up in the FileMaker security settings - Record Level Access - to not allow edits to the selections table if the contract has been signed.  You won't be able to limit the Full Access accounts this way - but you can do a field-level validation that does the same thing.  And hopefully you do not have many Full Access accounts.


                    I usually use a "lock" field - just a number field with an auto-enter of zero.  If the value in the lock field is one, then the record or field or whatever cannot be modified.  You will need to learn about Record-Level Access - there are some good articles in the Knowledge base and Help files and the FileMaker Training series, and if you do a google search, you will likely find some sample files to get you started.  Here is a link to a White Paper on Security topics that might be a good place to start.




                    You may also want to allow an override for certain privilege sets if there is a change order or some other "legal" modification to the selections. 


                    I think a change order process would be best - you will likely want to have a table that tracks those, as they are contract modifications, that is, if you allow change orders once the contract is signed.


                    Hope that helps.



                    • 7. Re: Need some advice on just how to set this up correctly.....

                      Karen, I'm not sure I understand your suggestions, either. What in this context is a prospect, and do you realize that a contact is simply a client with selections, and that creating/deleting records in the child table is equivalent to adding/removing the client's choices to a list?


                      EDIT: wrote this while your reply was already posted.


                      Message was edited by: erolst

                      • 8. Re: Need some advice on just how to set this up correctly.....

                        Sorry I do realize that. A prospect is merely a contact that isnt a client yet.




                        Sent from my iPhone


                        Karen Weaver

                        Desert Dog Technology, Inc.