4 Replies Latest reply on Oct 18, 2011 10:17 AM by InvectusIlumni

    Replace Field Contents with MySql ODBC Data Source

    InvectusIlumni

      Title

      Replace Field Contents with MySql ODBC Data Source

      Post

      Hi guys,

      I have a table occurence [QUOTE WEB FORM] that retrieves data from an ODBC source (website MYSQL database) and I've created a relationship between that occurence and native table occurence [JOBS] where QUOTE WEB FORM is the parent.

      To increase efficiency, I'd like to be able to import/copy data from the QUOTE WEB FORM table into the JOBS table with a simply click. To achieve that, I've created a script which worked perfectly the first time I tried it, but has since driven me nuts.

      When I invoke the copy/import script, I get "Records Remaining To Update: 2650". Then it takes forever and I'm forced to stop the script since I don't want all but one record to be updated.

       

      Here's the ERD:

      And here are my script steps:

      I should also mention that due to secutiy restrictions on the MYSQL remote server, I can't create or modify fields on the remote Table with the exception of calculaation fields.

      I hope I was clear enough and thanks for any help.

       

      --Invectus

        • 1. Re: Replace Field Contents with MySql ODBC Data Source
          philmodjunk

          Replace Field contents modifies all the records in your current found set. If you "I don't want all but one record to be updated", I don't think this is the option you want. (You do mean that you only want to update one record?) If you only want to change the values in one record, use set field, or define the fields to use looked up value settings from the web quote table and trigger a relookup just on this one record.

          Hmmm, not sure why you need to copy all this data from the parent table--especially if this is the one to many relationship that you show here. That would seem to produce a great deal of denormalized redundant data that can be a real nightmare to update if the values in the parent record change. Why do you want to copy all this data in the first place? Can't you just refer to the fields in the related table?

          • 2. Re: Replace Field Contents with MySql ODBC Data Source
            InvectusIlumni

            Thx PhilModJunk as always.

            I know it seems redundant but just to give you an idea; here's a brief overview.

            This solution is a booking system for a moving company. Customers have two ways of requesting quotes (estimate): By phone or by submitting a quote request form available on the website. Once they've decided to hire the company, a "file" is created in the solution for them. I shall refer to this as booking.

            As you can see, all form submissions are kept on an external MYSQL database hosted by a thrdparty company. At this stage, there seems to be no appetite to overcrowd the solution with quote request data, especially because the form is externally hosted and because some customers submit multiple requests for the same estimate.

            The current booking process involves manually entering the data in the system once the customer decides to book which could happen at a much later time.

            What I wanna do is cut the extra typing time for quotes requested online since the data is available and can be copied electronically.

            Phone quote requests will still need to be booked manually, but that's unavoidable.

             

            I hope that helps

            • 3. Re: Replace Field Contents with MySql ODBC Data Source
              philmodjunk

              Then either of the two suggestions in the first paragraph of my last post should work.

              Import records might also be used to pull data from the External table into the local table if you first setup a found set of the desired record or records.

              • 4. Re: Replace Field Contents with MySql ODBC Data Source
                InvectusIlumni

                Thx Phil. your tip makes sense