3 Replies Latest reply on Nov 29, 2015 1:21 PM by wimdecorte

    Moving from Access To Filemaker


      I am trying to eventually not have to maintain software on OSX and Windows. I have used a variety of database solutions in the past (dBase, Foxpro, Paradox, and Access) and tried Filemaker 11 several years back, but could not do the data mining I could do easily in Microsoft Access. I purchased a copy but probably never gave it a proper trial. Most of the data mining I did in Access involved linking select queries with group queries (with occasional update, append, or delete queries). In Filemaker, at the time, it appeared that calculated table fields and the find box were close to select queries, but the group by queries were done by reports in Filemaker, and could not be linked back to the find selections. I gave up.


      I want to try Filemaker again, and have purchased FM14. Most of the queries are health care related, but I was wondering if I could give a simple example and get your advice if this is going to be possible in Filemaker.

      Assume I run a company that sells items on the internet, and I have three tables to manage my customers and items sold. The customer table has a customer number, name, and address. The second table has items available and has item number, description, price, and category (food, book, software, housewares, etc.). The third table has purchases and has customer number, item number, date purchased.

      I want to create a flier to mail to the top 1000 customers who purchased the most number of items in calendar year 2015 and have the flier advertise items that roughly match the percentage of each category of these customers purchases over the past year. Since I want to create only 1000 fliers, I will use a tie breaker of total of purchase prices if there are more than 1000 customers who bought the same number of items.


      In Microsoft Access, one would first create a group query: grouped by customer number, where purchase year = 2015, and a field that counted purchases, and a field that counted cost of these purchases. It would be sorted in descending order on the last two calculated fields.

      The top 1000 records would therefore match the top 1000 customers for 2015. I would create a new query that grouped by categories of purchases (food, book, etc.) for these customers for calendar year 2015 and calculate percentages. I would then create the flier and use the customer information on the 1000 customers to create a mailing.


      In MS Access, all select and summary queries are just queries, so they can be linked together. Can Filemaker do this, or are the summeries only available in reports and can they be somehow linked to a "find" selection? I know that calculated fields in Filemaker are put in the original table as calculated fields, unlike in Access where they are in a query, but if there is a way to do this in Filemaker, I might be able to abandon maintaining Bootcamp sooner. Thank you very much!

        • 1. Re: Moving from Access To Filemaker

          FileMaker has a framework it works in whereby you always have to be in a layout and the context of the layout is determined by the layout's table occurrence and not by a SQL Query.  Also, note that your found set is live data and any changes you make to it is making changes to the actual data and not just a report.  You can do a SQL query in FileMaker, but it just returns a text array and does not change the context or found set.  But rest assured, what you are doing is a common task in FileMaker, it just is done differently than in Access. 


          You create create a relationship between Customers and Invoices and can have a calculation field in Customers that totals the Invoice amounts for each Customer for 2015.  Then you just find all records and sort by the new Invoice calculation field.  Go to the 1000th Customer and omit the rest of them leaving a found set of the Customers in 2015 with the highest Invoice totals.  You just design the layout to have whatever info you want on it, basically creating the flier.  FileMaker is more powerful than Access in putting graphics and layout manipulation than Access and supports many more types of graphic formats.  That can be useful in making fliers. 


          I'm sure I've overlooked things, but I hope this gets the discussion going. 

          • 2. Re: Moving from Access To Filemaker

            Thank you for your reply. So a calculated field can be a summary (like total number of purchases for CY2015 or total dollar purchases for CY2015 per individual customer)? That gets me a lot closer. However, how would one then take that top 1000 customers and go back and calculate the overall percentage of purchases in different categories for just those customers (% of food, books, software, etc.) to determine how to target these customers with the flier?


            One thing that seems more intuitive to me about Access/SQL is that if one asks ten questions, one creates ten different queries. In Filemaker it would appear that one has to create ten different calculated fields that exist in the table regardless of which question one was asking at a given time. Subqueries in Access are also sometimes easier to set up than creating a more complex query. Again, my experience is more with Access, so forgive my bias based on lack of experience with FM.

            • 3. Re: Moving from Access To Filemaker

              One technique to consider is the Virtual List / Virtual Table.  Do some googling on that.  That would leave your main data table free from calc and summary fields required only for reporting.