8 Replies Latest reply on Oct 4, 2010 9:23 AM by philmodjunk

    New to FMP and looking for some guidelines

    MikeLeathem

      Title

      New to FMP and looking for some guidelines

      Post

      Hi,

      Just purchased Pro and im trying to get to grips with my first database. Struggling with import and was hoping some kind soul might give me a few pointers to save me messing up.

      We have a single Excel spreadsheet with about 4 tabs that stores all our info at present. This gets updated every day. Mainly to add new records but occasionally small changes to existing data. This sheet gets posted to a common storage every morning.

      I want to import this info. Not every day but probably every other day into Pro so that we can use it more effectively. I have used import and know how to load the data into individual tables fine, but am struggling how to import the second time so that it doesnt add duplicate records or overwrite the existing data.

      Is there a way to import this info so that changes are merged. That is any changes made to our source data will update the Pro data individual field but not the complete record so that Pro changes are retained and any new records are added to the end of the PRo database?

      Thanks,

      Mike

        • 1. Re: New to FMP and looking for some guidelines
          ninja

          Hi Mike,

          Rather than answer the question you asked, esentially 'how do we keep using a spreadsheet as a database and use it to update the REAL database', let me encourage you to choose a different approach.

          If you are indeed using FMP so that you can use your data more effectively, then enter the data and update the data directly in FMP instead of XL.

          Post the FMP Dbase to your common storage every morning instead of the spreadsheet and have the updates typed right in the database.

          Will there be resistance because it "looks different"?...probably.  Will there be resistance because "we've always done it this way"?...probably.

          But you want to use the data more effectively...perhaps you might want to enter and modify the data more effectively as well, no?

          Just a thought...

          • 2. Re: New to FMP and looking for some guidelines
            rjlevesque

            I tend to agree, if you have FMP now I would use it completely. You can always spit out a report in Excel format for those that want one to look at. You can, however, display, manage, and manipulate your data much easier and more efficiently with FMP alone.

            • 3. Re: New to FMP and looking for some guidelines
              MikeLeathem

              I would do that but what I am trying to cover is the interim period. There are a total of 4 users but they are spread all over the country and using Dropbox to sync the data. A lot to bite off in one go to have the database go live first. Once the sync was working and all the layouts finalised the plan was to switch to Pro then.

              • 4. Re: New to FMP and looking for some guidelines
                rjlevesque

                You will have to have a maintenance time when all users are unable to use the spread sheets while you go live also, although it should only take a few minutes.

                There would actually be more work involved trying to update the excel sheets with something else if it could be done. What I am wondering is how you intend to share the database without FMP Server? Do you plan on using the IWP and hosting it somewhere at one of the locations?

                • 5. Re: New to FMP and looking for some guidelines
                  philmodjunk

                  I don't think you'll want to use Drop Box to update your FileMaker database once you get it up and running. Instead, you'll either have your users use FileMaker Pro and Open Remote... to link to your hosted file or you'll need to web publish the data base (IWP) so that they can access it with a Web Browser.

                  In the meantime, you can do what you describe with FileMaker, but there are key details not found in your posts that I'd need to know in order to spell out all the details. How can you identify which records have been duplicated when you visually inspect your data?

                  In general, you can import the files in to a separate table that is related your main table by the field or fields that uniquely identify your records. A script can then check each imported record for a matching record in your main table and update it if one exists. The records that don't have a matching record can then be listed for your inspection so that you can correct any that failed to match due to any data-entry errors. Then you can run the process again, but this time records that don't match a record in the main table can be imported into the main table.

                  • 6. Re: New to FMP and looking for some guidelines
                    ninja

                    If it's only the transition period you are concerned with, and you have your hands firmly on the initial import, why not import into a record-free clone each time?

                    I wouldn't want to do this for an extended period of time, but if it's just for a short term until you get everyone sold on the database concept this should work well for you.

                    • 7. Re: New to FMP and looking for some guidelines
                      MikeLeathem

                      Guys,

                      Thanks for the help. 

                      In reply to Phil first. Phil I cant tell easily. Once the records have been added in Excel they dont usually change but they can which is why I was wondering what import rules are available. I can live with only updating records which dont exist on import and that might be the best at the start. The transition period may be a few weeks while we try to complete the move im not thinking about downtime to switch over as such.

                      Having considered everyones comments if on import I cant easily identify records that have changed we will wive with just new records on input until we switch away from the excel sheets. That can be done cant it?

                      Mike

                      • 8. Re: New to FMP and looking for some guidelines
                        philmodjunk

                        It might be simpler to follow Ninja's idea.

                        If you are modifying data and need to put it back out to the other users, dump your entire database out to spreadsheet via export records and then don't touch your records in the database. At appropriate intervals, import all the data back in from the spread sheet into a clone of your database (One with no records) or just that particular table may be empty while you maintain related data not found in the spreadsheet in others. If you can disallow edits in one format (Spread sheet or FMP database) while making edits in the other--this will greatly simplify the process of synchronizing your data.