6 Replies Latest reply on Aug 5, 2009 5:13 AM by ninja

    Moving Records



      Moving Records


      Hoping someone can help me please. 


      I have two tables, one containing customers and one containing prospects. I want to be able to turn a 'prospect' into a 'customer' by clicking a button.


      I have created a relationship between the two tables allowing the creation of records on the customers side, but I don't know how to copy the records from the 'prospects' table into the 'customers' table. Ideally I would 'move' the record from being in the prospect table to the customer table.


      I want to have the tables stored as separate tables as the customers table may contain 50-100 customers whereas the prospects table contains over 15,000 prospects.


      Seems like this should be straightforward - fingers crossed.


      Thanks in advance, 

        • 1. Re: Moving Records

          There are any number of methods you can use to move data from one table to another. Much depends on how you've set up your database. Scripts can use set field to copy data one field at a time. You can also use Import Records in a script to do this.


          However, it would be much simpler to simply keep all these records in one table with a field that identifies them as either a "customer" or a "prospect". Then your button would simply need to change the value of this field to move a prospect record into the customer category.


          15,100 records is a number of records that filemaker should be able to handle quite easily. You can use relationships, portals, finds and sorts to work with just customers, just prospects or both.

          • 2. Re: Moving Records

            The reason I would prefer to keep the tables separate is that the Company Name in the Customers table is a drop down lost which is set to Autofill based on previously entered values.


            With 15,000 records (and it is intended this will grow substantially over time) I think this would end up becoming unwieldy.



            • 3. Re: Moving Records

              Howdy kennyferg,

              Thanks for the post.


              While often frowned upon, sometimes you just need to move a record.

              It seems like you've already thought through having a single table with a "status" field that reads either "customer" or "Prospect" and rejected it.  Think it through again to make sure...


              three methods to do it if you are sold on it:


              1. import the record FROM the prospect table TO the customer table (help menu covers importing fairly well)


              2. Brute force:

              Set Variable [$variable1; pfield1]

              Set Variable [$variable2; pfield2]...


              GoToLayout {customerlayout}

              New Record/Request

              Set Field [cField1;$variable1]

              Set Field [cField2;$variable2]...


              Brute force would need maintenance if you ever add fields to the table later...import is keener.


              3. Have a "Data" table with all of the info, a customer table with "UniqueIDcust" only and a prospect table with "UniqueIDprosp" only.  Now you only need to move the UniqueID from table to table, the data remains where it is.  For customers and prospects, I imagine that they change from one to the other often (I hope so...it helps business!)


              All in all, I'd look at the import function first.


              Enjoy the day!


              Edit: Phil and yourself beat me to it...I type too slow.

              • 4. Re: Moving Records

                Here's a simple way to get a drop down list of just a subset of your records:


                Create a calculation field that returns text:


                If you want a drop down of just prospects:

                If (contacttype = "Prospect" ; CompanyName; "" )


                Use this calculation field as the source for your value list and only "prospect" company names will appear. The same approach can be used for a "customer" based value list.


                Even very long value lists can be fairly manageable in filemaker if you use auto-complete. With that option turned on, typing in even just a few letters quickly "prunes" the list down to a much shorter list of values.

                • 5. Re: Moving Records

                  Thanks guys - I opted for the Brute Force option - the database has been working for a while now and at the moment, this was the quickest option for me - I need to have it reinstated at work in the morning!!!



                  Thanks for your help - its working exactly as I need it Ninja


                  • 6. Re: Moving Records

                    Howdy kennyferg,


                    Glad it works and is up running quickly...I would encourage you to keep in mind that the quickest/easiest solution is often the least effective.  I believe that this is also true in your case by using the "brute force" method.


                    It works, and you're off and running today...that's great, go for it.

                    Now is your chance to prove in a more effective, more sound and easier maintained method such as those that Phil or I proposed (filtering relationship, data table, all in one table with a "type" field, etc.).  Since it is working by brute force, let it ride for a month while you work out and get comfortable with a better way of handling your needs.  A month from now, switch the architecture over to your now-proven, non-brute-force method.


                    You've put in a patch...it'll hold while you reweave the garment...but don't forget that the garment needs reweaving.


                    My two cents...Enjoy the day!