3 Replies Latest reply on Jun 25, 2015 8:44 AM by philmodjunk

    Help: Drop down menu values connected to three other variables



      Help: Drop down menu values connected to three other variables


      I am trying to track a restaurant's performance over a period of 7 days for three services, i.e breakfast, lunch and dinner.

      - There is a drop down menu to select the service from three possible values - breakfast, lunch, dinner

      - Numeric variable field # 1 for number of customers - to be manually populated daily

      - Numeric variable field # 2 for number of servers -  to be manually populated daily

      - Numeric variable field # 3 for number of total revenues per service -  to be manually populated daily

      As this will be tracked over seven days, there will be 21 (3 sets for 3 services per day for seven day) sets of records. 

      Question: what do I need to do to connect each individual data set for a specific day and specific service. E.g. on Monday, for breakfast, we had 25 customers, 3 servers and $500 of revenues.

      Thanks for your suggestions!


        • 1. Re: Help: Drop down menu values connected to three other variables

          Is this all you want to do with the information? If so, in it's most basic form you can do this on one table.  Each service is a record.  Each record will have a date field, # of customer field, # of servers, total revenue, type of service (breakfast, brunch, lunch, dinner)

               1.  Date field-drop down calendar/auto enter current date, check the "Do not replace existing data..." box at the bottom

               2.  Number of customers-number field

               3.  Number of Servers-number field

               4.  Total revenue-number field formatted as currency.

               5.  Drop down field of service type. 

          Then basic searches will yield the information, or you can design reports to show anything you want.

          If this is all you plan to do with the database, I would just modify QuickBooks, or your accounting software, and you can get the same info out of it

          The reason why I say this is if you want to expand this database (and you should), there re much better ways to design the DB so you don't have to go back and re-design the database.

          Do you want to track the customer's (see who's a regular, reach out to them via, text, email, letter-for promtions, etc.), Then I would add a customer table.

          Do you want to track your servers (info regarding name address, etc., shifts worked, etc.), then I would add an employee table.

          Do you want to tie in revenue to items ordered, frequency, even reording?  Then you will need at least a menu table, and some tables for accounting (income, expenses, etc.)

          Then if you add these tables, a Service now becomes a join table, and each record is one service, where you populate the customers, servers, date, type, revenue.

          And as it grows, the accounting portion could also tie into a POS

          • 2. Re: Help: Drop down menu values connected to three other variables

            Thanks. How are all these fields connected? In other words, if I want to search for number of customers and total revenues for breakfast service for Monday, how would FileMaker know to pull data only for breakfast and not lunch or dinner (there will be three records for any one day, one for each service)? I am thinking I would need to have a way to connect numeric variable entries related to a particular service for a particular day to get the correct results? Please let me know if I am missing something.  

            • 3. Re: Help: Drop down menu values connected to three other variables

              Because each record has a service type field that identifies the service as "Breakfast", "Lunch" or "Dinner", you can use that data to find only records for a particular service and also to compute totals only for a specific type of service. You can add summary fields to compute the totals and you can either perform a find or use a relationship to see totals, averages and other aggregate values for just one service type or you can set up a summary report that shows these values for all your different service types in the same report.

              Here is a tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

              Other report formats are also possible but more complex to set up so you are better off with the summary report method first and you can explore more sophisticated options once you are able to get summary reports to work for you.