5 Replies Latest reply on Feb 20, 2015 10:31 AM by philmodjunk

    How useful might fmp be to identify discrepancies between spreadsheets?

    jimnagle

      Title

      How useful might fmp be to identify discrepancies between spreadsheets?

      Post

      My supervisor has explained a problem we are having in tracking information. The information is currently available in the form of 3 excel spreadsheets. The issue is discrepancies between these spreadsheets which come from different departments.

      I have pretty rudimentary knowledge of fmp having worked a year ago on a problem another dept was having getting video files (stored by reference) to work across computer platforms. I am aware that an excel spreadsheet can be converted into a fmp solution pretty directly (and vice versa). What I am unclear about is what tools fmp has that could be used to identify the discrepancies between and among these different spreadsheets (once they're converted into a solution). There is no 'real' db professional here available, just me, a trained biologist with a little experience dabbling around with a pre-existing solution. (I ended up adding a layout to the video db I was working on before in order to address the cross platform capability issue). Is fmp a tool that might useful to us without having to draw on the knowledge of a real DB professional? Our organization has license for the full suite of fmp products.

        • 1. Re: How useful might fmp be to identify discrepancies between spreadsheets?
          philmodjunk

          Perhaps, You could import each spreadsheet using Import Records into different tables of the same file and then use a variety of methods to check for discrepancies.

          But there are many different types of discrepancies from the total number of rows of data in the spreadsheet to specific text in a specific cell of that spreadsheet so much depends and the specific discrepancies that you need to check for.

          And you might consider replacing your spreadsheets with a hosted database designed to prevent the discrepancies from occurring in the first place.

          • 2. Re: How useful might fmp be to identify discrepancies between spreadsheets?
            jimnagle

            Thank you for the fast response. Unfortunately, a hosted DB as you suggested is unlikely to happen. But you've given me the first step in the mechanics of how a solution might be developed ie different tables. Then all that would be needed is probably a standardized set of queries to identify discrepancies and hopefully generate a report. I'm guessing that this might not be terribly difficult (even for me).  There would be a limited set of fields that would be tested against each other. A typical issue among these spreadsheets might be an empty field (container) in one table as opposed to some information in a different spreadsheet. Or dates not agreeing with each other.

            • 3. Re: How useful might fmp be to identify discrepancies between spreadsheets?
              philmodjunk

              FileMaker container fields are used to store images or other files and are the one type of data that you can't import from a spreadsheet due to design differences in the two systems.

              By using relationships, you can literally list the rows of spreadsheet data in paired rows where the data from spreadsheet 1 is in the upper row of the pair and the corresponding row of data from spreadsheet two can be placed in the lower row of the pair. Conditional formatting can then highlight fields where the data in a field of row 1 does not match the data in a corresponding field of row 2. You could also set up calculation fields that return a value when a pair of fields are not equal and you could then perform a find for that value in that calculation field to find a discrepancy.

              But the time spent setting up the DB, the time spent periodically looking for and correcting the discrepancies thus found would seem to me to quickly equal or exceed the cost of getting several copies of FileMaker and setting up a hosted peer to peer database for the same tasks, but with safeguards to prevent the discrepancies from taking place. (I'm not really trying to convince you of this, but to provide you with a rationale to use with those that would need to agree to this just on the off chance it provides you with the needed ammunition to effect a useful change in your organization.)

              • 4. Re: How useful might fmp be to identify discrepancies between spreadsheets?
                jimnagle

                Excellent Phil. That is very helpful. BTW - I'll be happy NOT to have to deal with container fields should I go forward with this. They're complicated from the perspective of this newbie/untrained developer.

                Re a peer to peer - I think I would probably need to show proof of concept in my more basic approach.

                Our agency has a site license encompassing (I believe) the entire suite of Fmp products (except maybe Go) so there would not be any acquisition cost to needing multiple copies of fm or having several people with Fmp access.

                Just having something like you describe where one could visually examine the status of related fields would be useful to generate a summary report to the limited group of people that need to get it.

                • 5. Re: How useful might fmp be to identify discrepancies between spreadsheets?
                  philmodjunk

                  I think I would probably need to show proof of concept in my more basic approach.

                  But the database you create here to find and resolve discrepancies would not be a database that you would host and use to prevent them from occurring in the first place. It might open a few eyes at to what is possible with FileMaker but it wouldn't be a working example of what I am suggesting to use as a long term solution.

                  And FM GO, BTW, is a free download from the App store so anyone with an iOS device can get it free of charge.