12 Replies Latest reply on Nov 19, 2014 7:14 PM by wfgclapp

    Hold my hand on importing tab-delim from server

    wfgclapp

      Here's what I'm looking to do:

       

      (Filemaker 13)

       

      1. Want to eventually have multiple FM solutions for sales dept.

      2. Want these solutions to all have access to a common database containing customer data. Let's call that db "Customers".

      3. First question...can multiple FM solutions access a common db like that?

      4. I need to update the Customers db on a daily basis with info from our back-end.

      5. I am currently dumping a tab-delim file of customer data from our back-end to the FM Server on a daily basis. Filename is static. That's the file I want to update my Customers db with.

      6. How to create this import script and make it run automatically on the server?

       

      I have read a bunch of articles and such on scripting and PSOS but can't seem to piece it all together.

       

      I'm assuming this is a common task and I'm hoping there's a dummy step-by-step out there somewhere. Anyone know of something?

       

      Thanks!

        • 1. Re: Hold my hand on importing tab-delim from server
          deninger

          Upon what platform is the Customer databse runing?

           

          If the Customers DB is Filemaker or a supported ODBC DB platform, you would only need to add an external data source to your solution that points to the Customer DB. In this way, everyone always has access to the most up to date data without needing to import the data regularly.

          • 2. Re: Hold my hand on importing tab-delim from server
            wfgclapp

            The Customers DB is a filemaker DB but we don't have any data sources available except tab-delim files. I know, I know...

            • 3. Re: Hold my hand on importing tab-delim from server
              deninger

              5. I am currently dumping a tab-delim file of customer data from our back-end to the FM Server on a daily basis. Filename is static. That's the file I want to update my Customers db with.

              6. How to create this import script and make it run automatically on the server?

               

              So am I to understand that the customer DB is kept on FM but not served by FM Server? Is there a reason that it is not available on FM Server?

               

              Adding an external data source to your solution that points to the hosted customer db would solve the issue without needing any export / import steps.

              • 4. Re: Hold my hand on importing tab-delim from server
                wfgclapp

                The Customers DB is hosted on the server. And I will be putting my import file on the server as well.

                 

                Yes, I wish I could do an external data source, that is the way to go, but alas, I don't have that option.

                • 5. Re: Hold my hand on importing tab-delim from server
                  Datagrace

                  It feels like you are asking one question-- how to import text into Filemaker Server-- and entertaining another-- how to move data to non-hosted Filemaker clients.

                   

                  To import text into FMS, you'll need some mechanism-- a system-level script-- to get it as far as the Documents (or Temp) folder on the server. Then you simply run the import. You can schedule both tasks to run sequentially, using FMS' schdular, running a 'Script Sequence'. The first task would run a system-level script that would bring in the text file from your back end; the second would run a native Filemaker import. As these would be scheduled events, you don't need to use PSoS, as they are already running server-side. You can find a description on the FMI site:

                   

                  http://help.filemaker.com/app/answers/detail/a_id/7035/~/import%2Fexport-script-on-filemaker-server

                   

                  Regarding the non-hosted clients, that sounds like a mobile deployment, where the sales staff are loading up their contacts at the start of their day, then dis-connectng and going out into the field. If that is the case, I suggest reading up on Filemaker synchronization. It's a complex subject, with so many variables that one answer will not apply to every scenario, and you haven't said what your scenario is.

                   

                   

                   

                  John Weinshel

                  • 6. Re: Hold my hand on importing tab-delim from server
                    wfgclapp

                    Thanks for the reply.

                     

                    Getting the tab-delim file from my back-end to the server has already been done. That part is handled.

                     

                    I just don't know how to get the server to import the records from the tab-delim file into my Customers DB. This is the part that I'm looking for : a simple-dimple step-by-step of how to create this import process.

                     

                    Now...regarding the non-hosted clients, I'm not sure i'm following you. And being brand new to Filemaker, it's likely due to an immature understanding on my part of how some pieces of FM work.

                     

                    1. I will be delveloping FM solutions in Filemaker Pro Advanced 13 to be hosted on FM Server

                    2. These solutions will be used primarily by users running Filemaker Go. Some users will consume using the FM Web Direct. No other users will be using Filemaker Pro.

                     

                    My assumption is this:

                    1. My job is to update the data in the Customers DB on FM Server. This is what I need help with most. I want to create a mechanism that will update my Customers DB with data from a tab-delim file that will exist on the server in the Documents folder.

                    2. The data in the FM Go solutions will aumatically update when the users connect to the sever on their iPad/iPhone.

                    3. I'm not sure what you mean when you mention FM Syncronization as it seems to me step 2 is all the syncronization i'm looking for. My users are really just consuming data. Nothing will be traveling back to my back-end.

                     

                    Thanks much!

                    • 7. Re: Hold my hand on importing tab-delim from server
                      deninger

                      What you are really describing is not so much as update, but a sync (mentioned earlier) and these have a few gotchas.

                       

                      Are your moble users (FM Go) updating customer info, or are they just viewing it? If they are updating, then you really need to look at a sync solution (which is more complex). If they are read-only users of customer data, then you can simplify some.

                       

                      Because this is a FM Go implementation (sorry, without knowing this, I was fixated on desktop solutions), getting the updated data to the user is a bit more complex as well. Accessing a shared file directory from FM Go is not really possible as I have used it. Are you planning on updating these iOS devices while they are "home" and have access to the FM Server, or do they need to be able to update on the road?

                       

                      In the simplest case where the user only reads the data and will only need to update the customer db before leaving the home office daily (or when getting back at night), you could do the following

                       

                      Script a "Delete all records" from the iOS database (so you don't end up with duplicate data accidently)

                      Script an Import from the hosted FM solution while they are on the "Home" wifi

                      • 8. Re: Hold my hand on importing tab-delim from server
                        Datagrace

                        Once your data is in the FMS documents folder, you will run a script with the script step:

                         

                        Import Records [perform without dialog]

                         

                        Aside from whether or not to display a dialog, there are two other params: the source file, and the import map. Set these both up manually to start with, and then schedule the script when you've got them right.

                         

                        To point to the source file, set a local variable ($documenPath, not $$documentPath) to the Get() function Get(DocumentsPath), and append the file name. That will give you the path to your source.

                         

                        The field mapping will maintain their order if, after setting it up, you check 'Specify Sort Order', and the makeup of fields does not change in either the source or Customers.

                         

                        It may take a few tries to figure out how these parts all work; you will find plenty of documentation in the online help (in the Filemaker Pro Help), as well as on the internet. It's not particularly difficult.

                         

                        The question about synchronization arises if your Go clients will, as is likely, pull in the appropriate Customers records at the start of their shift, which they would do by logging in to the server. At that point, we say they are 'hosted'. After they load up, I assume they will disconnect from the server, and the salespeople will go out into the field, working through their leads. If the don't touch the customer data, but only write up orders, and the next day replace the customer data with new customer data, no sync is needed. But if they make changes to those customer records while they are not connected to the server, the customer records on their mobile devices are now dirty, and steps must be taken to reconcile their new state with the state on the server and the state on other salespeople's devices.

                         

                        These same issues are likely to come up again if salespeople are writing orders.

                        • 9. Re: Hold my hand on importing tab-delim from server
                          wfgclapp

                          Few things to clarify…

                           

                           

                          1.       At the time I’m not counting on any ‘offline’ capability. I’m ok with the users only having access to the solutions by being online with the FM server.

                           

                          2.       I’m going to block a lot of fields from being editable. The ones I’m going to allow as editable are such that won’t require strict sync. In other words, if one user steps on another, that should be very rare and even so, basically ok.

                           

                          3.       This is a CRM solution and the only editable fields by users will be status report of prospective customers. I want something where the salesman in the field can create notes on a prospective contact and those notes appear to their managers in FM Go.

                           

                          4.       The customer data that I’m wanting to import daily is data that won’t be editable by users anyway. So there aren’t really any sync issues there.

                           

                          I hope this helps. I feel like I’ve done a poor job of explaining my scenario and ya’ll have been terribly helpful in spite of that!

                           

                          So, to recap and clarify…

                           

                           

                          1.       I’m building a Filemaker Go / Web Direct solution in Filemaker 13 and will be hosted on FM Server.

                           

                          2.       Users will only have access to the soln if online and connected to the FM Server

                           

                          3.       The solution is a CRM primarily used for prospecting customers. Main utility will be for field sales to maintain notes on customers that can then be shared via FM Go with their managers.

                           

                          4.       I’m assuming/guessing to have two databases, Customers and CustomerNotes. These are both hosted on FM Server.

                           

                          a.       The Customers DB will not be editable by the users and will contain data that needs to be updated from our back-end on a daily basis

                           

                                                                                         i.      This is the part I am working on fixing at the moment.

                           

                                                                                       ii.      I already have a process that places a tab-delim file (my only option for file type) in Documents on the FM Server on a daily basis.

                           

                                                                                      iii.      I need step by step help in now creating a process for the Customers DB to be updated with the data in the delim file

                           

                          b.      The CustomerNotes DB will be data entered by the end users. Records in this file will have a key that matches a key in the Customers DB.

                           

                                                                                         i.      Both field sales and managers can edit fields in this DB but I’m not worried about them stepping on each other’s toes. If it happens, fine.

                           

                          I think that’s all the pertinent stuff.

                           

                          I need help with 4.a.iii

                          • 10. Re: Hold my hand on importing tab-delim from server
                            deninger

                            Just considering 4.a.iii

                             

                            1) Be sure your customer DB (and your tab-delimited update file) have the same Primary Key. This is the only way to update the records and add any new records

                             

                            2) Before you script this, be sure you understand the steps (that will later be scripted).

                             

                            3) Work on a copy of the database, not the production server if possible and...

                             

                            • Navigate to the layout for the Customers  (Go To Layout ...) and choose the menu item Records -> show all records
                            • Use the File -> Import Records -> File menu item
                            • Navigate to the location of the tab-delimited file and select it
                            • Select "Update matching records in found set" and check the box "add remaining data as new records" (both are located in the lower left corner in the Import Action section)
                            • Be sure to match all of your colums to the appropriate fields on the  resulting "Import Field Mapping" dialog.
                            • ***critical step*** click the primary key arrow until it shows a two-headed arrow (Match records based on this field) -- see the Field Mapping "key" in the lower right area of the dialog
                            • if the first row of your tab delimited file includes field names,  check "Don't import first record (contains field names)"
                            • Select Import (and select Perform auto-enter options while importing if necessary)
                            • Check if things work as you want

                             

                            Once you can do this, you can easily script the same thing by:

                             

                            • Start by making a script that you run manually (not scheduled yet)
                            • Add the Go To Layout step
                            • Add a Show All Records Step
                            • Add the Import Records step, and configure it as shown. Don't choose "perform without dialog" just yet
                            • Test it to be sure all of your settings are as you need and then move to "perform without dialog"

                             

                             

                            Now that you have a hands-off working script to import, now you can consider how to schedule it. Before you will be successful, however, you will have to adjust your path for the file with respect to the SERVER. This may take a bit of trial and error. Remember that the file (your tab delimited update file), when being accessed by the script engine on the server, needs to be able to get to the file and read it.

                             

                            One way to trouble shoot this might be to create a path variable instead of a hard-wired path in the File step.

                            1 of 1 people found this helpful
                            • 11. Re: Hold my hand on importing tab-delim from server
                              wfgclapp

                              Well, maybe I'm close.

                               

                              I have a script that, when run locally via Pro, works just fine.

                               

                              But when I schedule this script on the server, I get an Error 100, which I understand to be 'File not Found'.

                               

                              So...appears I have a path issue, which you've warned about.

                               

                              I am using a path variable. See if this looks right...

                               

                              $filename=Get(DocumentsPath)&"inboundfm\fmcustomers.txt"   (this works fine locally)

                               

                              The actual location of fmcustomers.txt on the server is:

                              C:\Users\Administrator\Documents\inboundfm\fmcustomers.txt

                               

                              Is there possibly something to be done to assure FM Server has access to that directory?

                              • 12. Re: Hold my hand on importing tab-delim from server
                                wfgclapp

                                Ok, I've got it running now.

                                 

                                Turns out, I was putting my delim file in the wrong Document folder on the server.

                                 

                                I was assuming Get(DocumentsPath) resolves to  C:\Users\Administrator\Documents\

                                 

                                but it actually resolves to C:\Program Files\FileMaker\FileMaker Server\Data\Documents\

                                 

                                Thanks to all who responded and put me on the right track!