13 Replies Latest reply on Feb 25, 2014 5:25 AM by gdurniak

    Imports avoiding 729 errors ?

    Vincent_L

      Hi,

       

      Is it possible to avoid the 729 errors doing imports on specified fields, so than even if a user is editing a record (but not those fields), the import wouldn't fail ?

      Or at least a FAST way to know which records were skipped (so to re-update them later).

      Or is it possible via privelges sets to let FMP ignore locking of those fields even if the record is locked.

       

       

      Solution background :

       

      My solution manages product inventories in 10 wharehouses. Theres 3500 Products.

      My user need a list view with all the wharehouses inventories in colum.

      That's potentially : 3500 * (10 colums + 2 colums for sku and name).

      People often handle 100 to 3500 foundste with an average of 600. They do sorts constantly.

      Moreover the invotories data needs to be updated every 10 minutes.

       

      Navigating,sorting, searching AND updating should be extremely fast (or as fast as possible)

       

       

      Of course the obvious thing would be to have the invotories stored in another table than the main one. That's obviously what's I've done before. I had to re-put the inventories in 10 fields for performances reasons because Filemaker can't work fast with related data and unstored fields (yes that's so true unfortunately).

      I did everything that was possible i think :

       

      ESS : no way, way to slow

      Importing in invotories table, then replace the fields in main table, slower than import and record locking issue

      Importing in invotories table, then set fields loops in main table, way to slower for the data refresh

      Of course I did eveythuing server side (after having doing it via a bot).

       

      The fastest way for imports as well as for solution usage is to import in the main dabase with 10 fields. But obviously this fails on locked records.

       

      So isn't there a reliable and fast way to import data in the main table ?

        • 1. Re: Imports avoiding 729 errors ?
          Mike_Mitchell

          Vince -

           

          This is one of the main issues with using the Import operation. Since it's a single operation, it does not provide the granularity to give you specific records that error out, much less specific fields. So you eventually will run into the situation you're describing: Locked records break the import and there's no way to isolate which records broke.

           

          The solution is to abandon Import altogether. Instead, you loop across the records, using a temporary join from the current context via a global join field that points to the key field in each table (source and target). You can assemble the list of records to update by various means (ExecuteSQL is one) in a variable, then loop over the variable, setting the global field equal to each value. This establishes the relationship. Then use Set Field for each field to be updated.

           

          Ray Cologon (of NightWing Enterprises) described this method at DevCon a few years back, referring it to "processing in place". It carries several advantages over Import:

           

          1) There's no switching of layout context required. This is both faster and more user-friendly (no screen flashing, typically).

          2) You can trap not only which records are locked, but which fields via an error capture (since the Set Field script step can be trapped and you can log an error if one occurs).

          3) Because of an undocumented FileMaker behavior, you can create new records in the target table and the key field will automatically populate in the global field if the relationship between the global field and the target table is set to allow record creation.

           

          My suggestion in your case would be to use one or more timestamp fields in your source table. One would be the last sync to your destination table. Another would be a modification timestamp. Others might be needed based on your particular workflow. But you could use an ExecuteSQL query to isolate the key fields for any records in the source table that have been updated (or created) since the last sync. That would be your key field list. Then, you can update whatever fields you need, trapping the errors as you go.

           

          HTH

           

          Mike

          • 2. Re: Imports avoiding 729 errors ?
            Vincent_L

            Thanks mike, but set fields loops are muc much slower than and import, especially when you have 10 fields per records to set.

            The import script should be the fastest possible because otherwise it slow dowm FMS too much and Filemaker becomes unresponsive for all clients.

             

            Of course all of this shouldn't be problem if FMP wasn't so slow with 1 to 1 relationships

            • 3. Re: Imports avoiding 729 errors ?
              user28097

              I'm not completely clear on how your tables are structured, but if the transaction count is not too high, perhaps do a transactional update on the inventory instead of a full import each time. Run the full import during off-hours if possible to ensure the quantities don't get out of sync.

              • 4. Re: Imports avoiding 729 errors ?
                Mike_Mitchell

                1-to-1 relationships shouldn't be slow at all, if fields are indexed on both sides. What exactly are you doing that's slowing it down (you mentioned unstored fields)? Your problem may with with your structure, rather than with the import operation.

                • 5. Re: Imports avoiding 729 errors ?
                  Vincent_L

                  Imports are from a MySQL Table of another system.  I already only pick the records that are changed thru the MySQL query on the other system. So each update import concerns only 100 records, but sometimes it's more than that.

                  What's a "transactional update"

                  • 6. Re: Imports avoiding 729 errors ?
                    Vincent_L

                    I agree, they shouldn't but they are in FMP. They're much slower than in the same database. When your displaying a list view, when you want to sort, when you want to find. Just displaying is much slower than in flat DB.

                    • 7. Re: Imports avoiding 729 errors ?
                      Mike_Mitchell

                      What you're describing should not be slow, even for ESS. Importing 100 records through a loop shouldn't even be an issue if done in the background. 100 records at 10 fields each is trivial. Something else is going on. What is the structure of your final table? How many fields? What are these calculations that would be unstored if kept in the separate table?

                      • 8. Re: Imports avoiding 729 errors ?
                        user28097

                        By "transactional update" I just meant updating only the records that had an inventory transaction. Sounds like that's what you're doing, but that wasn't clear to me from your original post.

                        • 9. Re: Imports avoiding 729 errors ?
                          Mike_Mitchell

                          How many fields in the related table?

                          How many records in the related table?

                          Is this the MySQL table via ESS, or another FileMaker table?

                          • 10. Re: Imports avoiding 729 errors ?
                            Vincent_L

                            What matters most is the usage of the solution. Search, sorts, list view displays.

                            And routenitely list view display is 100 recors to 3000 displayed in list view, scrolled, sorted etc.

                            Un those situation nothing beats flat design, all indexed in the main table.

                             

                            Theres 30 000 records in that table, howewer 3000 are the one people works on.

                             

                            The MySQL foreigh system table contains 300 000 records, but anyway that doesnt' mater since I get the records I ned very fast (but ESS would die)

                             

                            Unfortunately import speed also matters because it slows down FMS, that in turnes slows downs clients. Import is the fastest way

                            • 11. Re: Imports avoiding 729 errors ?
                              Mike_Mitchell

                              No, all of that matters. A lot.

                               

                              The width (fields per record) of the MySQL table directly affects the list view. Every record being displayed has to download - every field, whether being displayed or not - in order to display a sorted list. The width of your FileMaker table has to do the same. That's why your 1-to-1 relationship seems slow. The more fields, the more bandwidth is being consumed. So you should strive to eliminate any excess fields in whatever table is being viewed.

                               

                              List View will automatically download as many records as will fit in the view on first load. After that, it downloads additional records as the view is scrolled (and this is why scrolling will appear slow). However, whenever an aggregate function is performed - such as Sum ( ), Count ( ), or any summary field, then entire found set has to be downloaded. The same is true for sorts (just to reiterate). This is why it's slow - it's a bandwidth issue. So get rid of any aggregate functions that don't need to be there, and encourage users to try to avoid sorting whenever they can (or at least do it on smaller record sets).

                               

                              Further, if there's no indexing on the MySQL key field, then it becomes very slow indeed. I don't know if that's the case, but whoever manages that database should be able to tell you. But you may also be able to speed that up by having the MySQL administrator create you a view of just the records / fields you're interested in, which may allow you to eliminate the import operation entirely.

                               

                              You can also look at using lookups or auto-enter calculations to pull the data into your primary table during data entry operations, as users are working. Script Triggers can be used to facilitate this process. By using an ESS connection to a narrower MySQL view, you may be able to eliminate the drag on the system as well as the record lock issue altogether (because the users update records automatically as they're working). Any records not caught during data entry can be caught during report generation or on a nightly batch job when nobody's in the system.

                               

                              Finally, if your users are only needing certain fields in a list view, consider a one-to-one relationship to a table that shows only the fields they need for that list view. This will dramatically reduce the bandwidth requirements for displaying the list, eliminating all the excess overhead.

                               

                              HTH

                               

                              Mike

                              • 12. Re: Imports avoiding 729 errors ?
                                stefan_s

                                Hi VIncent,

                                 

                                 

                                Vincent_L <noreply@filemaker.com> kirjoitti 24.2.2014 kello 18.28:

                                 

                                Imports are from a MySQL Table of another system.  I already only pick the records that are changed thru the MySQL query on the other system. So each update import concerns only 100 records, but sometimes it's more than that.

                                 

                                 

                                 

                                Did I understand correctly? You want to do the import on FileMaker Server?

                                 

                                FileMaker Server cannot import data from other FileMaker sources. If you are accessing the MySQL data from within FileMaker using the ESS functionality, then this is the case with MySQL data also.

                                 

                                I have built stuff like this and the only option you have in such a case is to do looping set fields.

                                 

                                If you need to import data, then the MySQL server should save the files in Excel format directly to a folder that FileMaker Server can read from like the Documents folder. Please check FileMaker Server documentation for details.

                                 

                                HTH

                                 

                                 

                                __

                                Stefan Schutt, Mouse Up, Finland

                                • 13. Re: Imports avoiding 729 errors ?
                                  gdurniak

                                  Then try related data with multiple stored fields in your products table.  You could run a scheduled script on server to update the stored totals periodically

                                   

                                  greg

                                   

                                  > Of course the obvious thing would be to have the invotories stored in another table than the main one. That's obviously what's I've done before. I had to re-put the inventories in 10 fields for performances reasons because Filemaker can't work fast with related data and unstored fields