11 Replies Latest reply on Nov 19, 2009 8:08 AM by mrvodka

    Record Modified By Another User Since You Started Editing It (Single User)

    jschaefe

      Title

      Record Modified By Another User Since You Started Editing It (Single User)

      Post

      <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;o:OfficeDocumentSettings&gt;   &lt;o:AllowPNG/&gt; &lt;/o:OfficeDocumentSettings&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:TrackMoves&gt;false&lt;/w:TrackMoves&gt;   &lt;w:TrackFormatting/&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:DrawingGridHorizontalSpacing&gt;18 pt&lt;/w:DrawingGridHorizontalSpacing&gt;   &lt;w:DrawingGridVerticalSpacing&gt;18 pt&lt;/w:DrawingGridVerticalSpacing&gt;   &lt;w:DisplayHorizontalDrawingGridEvery&gt;0&lt;/w:DisplayHorizontalDrawingGridEvery&gt;   &lt;w:DisplayVerticalDrawingGridEvery&gt;0&lt;/w:DisplayVerticalDrawingGridEvery&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:DontGrowAutofit/&gt;    &lt;w:DontAutofitConstrainedTables/&gt;    &lt;w:DontVertAlignInTxbx/&gt;   &lt;/w:Compatibility&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;276&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:Times;      panose-1:2 0 5 0 0 0 0 0 0 0;      mso-font-charset:0;      mso-generic-font-family:auto;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} @font-face      {font-family:Cambria;      panose-1:2 4 5 3 5 4 6 3 2 4;      mso-font-charset:0;      mso-generic-font-family:auto;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin-top:0in;      margin-right:0in;      margin-bottom:10.0pt;      margin-left:0in;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ascii-font-family:Cambria;      mso-ascii-theme-font:minor-latin;      mso-fareast-font-family:Cambria;      mso-fareast-theme-font:minor-latin;      mso-hansi-font-family:Cambria;      mso-hansi-theme-font:minor-latin;      mso-bidi-font-family:&quot;Times New Roman&quot;;      mso-bidi-theme-font:minor-bidi;} p      {margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ascii-font-family:Times;      mso-fareast-font-family:Cambria;      mso-fareast-theme-font:minor-latin;      mso-hansi-font-family:Times;      mso-bidi-font-family:&quot;Times New Roman&quot;;} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Table Normal&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin-top:0in;      mso-para-margin-right:0in;      mso-para-margin-bottom:10.0pt;      mso-para-margin-left:0in;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ascii-font-family:Cambria;      mso-ascii-theme-font:minor-latin;      mso-fareast-font-family:&quot;Times New Roman&quot;;      mso-fareast-theme-font:minor-fareast;      mso-hansi-font-family:Cambria;      mso-hansi-theme-font:minor-latin;} &lt;/style&gt; &lt;![endif]  --><!--  StartFragment  -->

      Good Morning,

       

        This may be a simple issue, but any help or direction would be greatly appreciated (new to FMPro 10 Advanced).

       

        I am attempting to edit data within records on an external ODBC source (Oracle 10g server, accessing via actualTechnologies ODBC for Oracle 2.9f ODBC connector), and am able to access and read all records within a table hosted on a remote server.  Currently, I am creating a new database, and through the 'file -> manage database' menu, then 'relationships' tab, I am adding my external table from that remote source.  I can see all records and so on. However, on any edit to the data in the remote table, I receive the following:

       

      "This record was modified by another user since you started editing it.  Do you want to save your changes anyway and possibly overwrite their changes? <yes, no, revert record>"

       

        I can choose to update the record, and the data is updated on the server appropriately.  Should I be mounting this data source in some other way (to allow me to do updates to the remote data set?)

       

        Thanks again for any help or direction anyone can provide!

       

      -James

      <!--  EndFragment  -->

        • 2. Re: Record Modified By Another User Since You Started Editing It (Single User)
          jschaefe
            

          mr_vodka,

           

            Thank you very much for the response.  Here's the fun part :

           

            Chris G. (you replied before in that thread) is a colleague  of mine, and we're troubleshooting the same problem, but on different servers (it hasn't gone away since July, I was only recently brought in to have a look at it).    :)

           

            In your previous post, you noted that FM Pro uses Pessimistic Locking - is there a way of disabling that, or at least turning off the warning?  There are no triggers associated with the Oracle DB (so says the DBADMIN), but I note that while I'm connected to the DB server, I have a persistent connection open via ODBC (as expected).  When I try to update any record, a second connection is opened, but only for the duration of the change.  As soon as I commit (or cancel) the change, OS X immediately closes the conneciton (sends SYN anyway and the connection moves to time_wait, and eventually times out (also as expected)).

           

            I'm wondering if the 'another user' is me (my previous connection) being left with that record (or table) open, via pessimistic locking...  ie - meybe there's no way to turn it off, but just disable the warning?

           

            Again, thanks for all your help with this (or any other ideas you might have) - this is my first venture into fmpro land!

           

          -James

          • 3. Re: Record Modified By Another User Since You Started Editing It (Single User)
            mrvodka
               There is no way that I know of to turn that message off. If you are on the record and lets say you use an Execute SQL statement to update the record, you will get that message as some action has updated that record since you first displayed that record. Does that make sense?
            • 4. Re: Record Modified By Another User Since You Started Editing It (Single User)
              jschaefe
                

              Okie doke,

               

                Thanks anyway - It's strange that there's no way to update data on a remote ODBC data source without the warning.  Again, I'm not doing anything fancy (no scripts, lookups or mods), just editing a zip code on a single record, for instance, and I get the warning (but am able to update the remote database).

               

                If I click <yes>, the data updates on the ODBC database (remote server), and everything's fine.  It's the warning that makes working in this application annoying (and impossible to do mass updates like importing a spreadsheet to update records in the remote ODBC database).

               

                I ended up calling support (the pay support number) - unfortunately, they told me the same thing, that you can't update an external ODBC/ESS record without the warning :( Ouch.

               

                Thanks for your help mr_vodka!  Very insightful indeed! I appreciate it!

               

               

              • 5. Re: Record Modified By Another User Since You Started Editing It (Single User)
                mrvodka
                  

                There seems to be a little confusion here. One can updates through the ESS records itself without seeing the message IF the record hasnt been modified by someone or something else.

                 

                You also can modify the record directly by using EXECUTE SQL to update the record.

                 

                However, when using a combination of both, if you have the ESS record open and then run an Execute SQL, then you will get the message. Make sense?

                 

                One should not be getting the message if the record is being updated directly through the ESS layout without some other action going on.

                 

                 

                 

                 

                • 6. Re: Record Modified By Another User Since You Started Editing It (Single User)
                  jschaefe
                    

                  Mr_vodka,

                   

                    I'd love to be able to get this working, and perhaps I am confused here...

                   

                    How do I connect to the table I'd like to modify (the remote ODBC table)?

                   

                    Currently, I'm creating a new, empty database.  From there, I do the following:

                   

                  1.)  Go to File -> Manage -> External Data Sources

                  2.)  Create a new external data source, giving it a name, specifying an existing DSN (fromODBC Manager), and specifying a userid and passwd.

                  3.)  I can then go back to File -> Manage ->  Database, and select the Relationships tab.  I can then add a table from the data source I indicated in step 2.

                   

                    At this point, I can create fields in my local table (FMpro) and link them to the external table, or just keep it simple, and edit ANY field on ANY record on the external table.  When I move off of the record I'm updating, I get the warning.  Again, I'm the only user on the db server.  My DB admin says there are no update triggers applied, and can see me updating that record on the DB itself. I have a persistent TCP connection open to the database for as long as I have the table open in fmpro, and a new connection is spawned on any update to a record (I believe that is why I am getting the modified warning - FM pro already has a connection open?)

                   

                    If I'm approaching this incorrectly, PLEASE let me know what I'm doing wrong here.  All I need to do is be able to update those records (which I CAN do, but I get the warning when I try to update the record), and be able to do them in mass (via import, which I can't do as I get the warning message).

                   

                   Perhaps a better way to ask this question is:

                   

                    If you had to update data in an external database (non fmpro), and you just needed to be able to mount and edit records, how would you attach fmpro to that data source to do so?  

                   

                  Again, thanks for all the help...

                   

                  -James

                   

                  • 7. Re: Record Modified By Another User Since You Started Editing It (Single User)
                    mrvodka
                      

                    Using ESS to update data should not yield that message unless that record is open and as I said earlier, something else is updating it.

                     

                    BTW I have to ask, how are you doing your imports? Is there anything particular about your setup? Can you spell out the details of your import process?

                     

                     

                     

                    Also, you may want to contact Jonathan Monroe from Actual Technologies. I dont deal with the Mac ODBC drivers so I cant really comment but perhaps there is a setting.

                    • 8. Re: Record Modified By Another User Since You Started Editing It (Single User)
                      jschaefe
                        

                      Agreed - It seems as though my client itself keeps the records (the entire table perhaps) open and locked for the duration of my session (*again, note that I have a persistent TCP connection that remains open so long as I have the table mounted).

                       

                      Imports fail outright (again I believe the same issue is causing the problem).  I can import in sql express or ms Access (on the win platform of course) and update records to my heart's delight on those platforms; that's how I got data into the tables to begin with, but I have duplicated this issue across several Macs.  If I can't get a single record to update without the warning though, I can imaging that imports would have the same problem.

                       

                      Is there any way to tell what records, fields, tables, etc FM pro has locked (in use) at any given time?  Can I see if I'm locking those tables on access, causing the problem when I try to update?

                       

                      I WILL send a note to Jonathan, and I REALLY appreciate the help again!!

                       

                       

                      • 10. Re: Record Modified By Another User Since You Started Editing It (Single User)
                        jschaefe
                          

                        Okay mr_vokda, okay.

                         

                         

                        You were right, from the beginning...  Turns out that one of the fields in the record I'm trying to update is being updated by Oracle - a 'last updated by' field.  Once I remove it completely from my view of the external table, bingo, everything updates perfectly.  Individual record updates, imports... they're all peachy...

                         

                        Let this thread be a lesson to anyone getting this error - the network connections: individual TCP connections per update and persistentTCP connection open for the duration you have a table loaded, is normal behavior (confirmed with Actual Technologies)...  Check the db source for tables Oracle (or SQL) might get cranky about if you try to update them manually.

                         

                        Now I just have to map out which tables contain these types of fields and always ignore them...

                         

                        Thanks again for all your help...   Sorry for the disbelief :o