8 Replies Latest reply on Jan 25, 2015 1:44 PM by J_File

    Filemaker Pro to replace a heavily macro driven Excel Spreadsheet

    J_File

      Title

      Filemaker Pro to replace a heavily macro driven Excel Spreadsheet

      Post

      Hi there,

      I am at the start of a journey looking to see what software I can use to transfer information from a rather complex macro driven Excel Spreadsheet into an alternative software solution. I was originally thinking of an SQL database, but I wonder if FilemakerPro could work.

      I am effectively looking for something with a back end that can cope with rows and columns of data, use the data in the rows and columns within mathematical formulas and then present the results of the raw data / results of the mathematical formulas in a report of some kind - the report could run to 60 or 70 pages and should be able to display text in paragraphs, text in tables and graphs. It must be able to work offline as well as online, and be able to sync back up to a main server. Finally, it would be great if we could create custom reports from the data in the database to interrogate it in different ways.

      Do you think Filemaker could do this for me? I have no experience of databases / MySQL / filemaker, just many many years of Excel design!

      Thanks, J

        • 1. Re: Filemaker Pro to replace a heavily macro driven Excel Spreadsheet
          SteveMartino

          I think it could.  Although my SS's are probably not as complicated as yours, I import them and parse the data every day.  What I originally tried was to replicate the SS's in FM, but that did not turn out the way I liked it.  Excel is just better for that, so it's easier to import.

          The benefits of reports/graphs, working offline syncs and backups, and custom reporting fall into FM's wheel house. 

          ..."Ask not what FileMaker can do for you....ask what you can do to FileMaker to make it do the things you need..." 

          If you excel at Excel, you'll pick this up pretty easily.  However you'll type a comma when you should've typed a semi colon....a lot...in the beginning.

          • 2. Re: Filemaker Pro to replace a heavily macro driven Excel Spreadsheet
            J_File
            Thanks for taking the time to reply. I was hoping to transfer the data in the spreadsheet tabs into filemaker and make it do all the work so will have to think about that. I suppose I should look at getting the trial going and seeing if it will do what I want. The application I am trying to build would be a reporting mechanism that I would sell on to other businesses to use. It would not be for in-house use in a single company. I figured I could install the app onto a webserver on a data centre and then allocate partitions to each unique customer. They would have there own variant in the app and would update and sync it with their own unique data. Again is filemaker a good bet for this sort of application do you think?
            • 3. Re: Filemaker Pro to replace a heavily macro driven Excel Spreadsheet
              TKnTexas

              I have not found any worksheet that I could not replace with FileMaker Pro and do it better.  From version 3 to version 13 I have been doing it.  It is much easier in FileMaker Pro.  

              1 of 1 people found this helpful
              • 4. Re: Filemaker Pro to replace a heavily macro driven Excel Spreadsheet
                J_File
                Thanks. Even if the worksheet is more a "form"  as opposed to a traditional table of rows and columns? By "form" I mean that you work down the worksheet inputting information into certain cells. At the moment i have a "report" tab which then pulls these cells and formulas from all the other worksheets into it. The report tab can then be pdfed and sent to a customer.... As i said in the original post this is the beginning of a journey for me and googling this subject seems to bring all sort of references to sql, Oracle and other things!
                • 5. Re: Filemaker Pro to replace a heavily macro driven Excel Spreadsheet
                  philmodjunk

                  FileMaker is a relational database, not a spreadsheet. It should be able to do all that you describe here and without any use of a SQL based "back end" such as Oracle.

                  But it won't do these tasks in the same way that you are familiar with in a spread sheet program. The internal data structure is very different. You'll need to learn how to do this in FileMaker (You'd have to do the same thing with any other relational database system also.)

                  Note that you can import data from Excel into FileMaker, but the calculation expressions will not import. You'd need to redesign them to work in FileMaker anyway in most cases both due to internal structure differences as well as syntax differences.

                  • 6. Re: Filemaker Pro to replace a heavily macro driven Excel Spreadsheet
                    J_File

                    Thanks Phil.

                    OK, I think I will have a play and see where I get. There are a few of training centers in the UK, so if I am relatively confident after a play that it will do what I want it to do, I may pay for a bit of a crash course. Can anyone recommend a book to read up on it as well? The only other thing I can think of at the moment is about licensing, if I design a complex reporting app, and then want to sell it on a license basis to other companies, would that be allowable so long as I register all the licenses with Apple?

                    • 7. Re: Filemaker Pro to replace a heavily macro driven Excel Spreadsheet
                      philmodjunk

                      I recommend downloading the free 30 day trial of FileMaker 13 if you have not already done so. If after a "test drive" you want to invest in FileMaker as a development platform, I recommend spending the extra $$ to get the Advanced version. The developer tools in FileMaker Advanced can really save you time and effort.

                      When it comes to selling your design to others, you have a number of possible business models you might employ:

                      You might host your solution from a copy of FileMaker server and "lease" access to the server for multiple customers. There are ways to put the data from multiple users into the same set of tables yet keep each customer's access limited to just "their" data. The clients of such a database might purchase and install their own copies of FileMaker or you might use WebDirect to publish to the web and then clients can use a Web Browser to access the hosted database.

                      You might use FileMaker Advanced to generate "run time" solutions. These solutions will not require installing FileMaker to run, but there are some significant limits put on what they can or can't do, so you would need to research those limitations carefully to see if that works with the type of solution you want to market.

                      You might use FileMaker Pro to design solutions to be used on iOS devices via FileMaker Go. FileMaker Go is free to download. FileMaker GO can also be used to set up a phone or pad as a client of a hosted database.

                      You might sell copies of your file to customers who then need to purchase licensed copies of FileMaker Pro, and (possibly) FileMaker Server.

                      • 8. Re: Filemaker Pro to replace a heavily macro driven Excel Spreadsheet
                        J_File

                        Great bit of advice there Phil. Will look into it now and see where I get. Your help has been much appreciated.

                        J