8 Replies Latest reply on Feb 6, 2017 2:17 PM by kaotishe

    Data Entry Solution

    kaotishe

      I am looking for an easy way to set up a solution for tracking expenses. In the past I have done this with a spread sheet, which meant going to the correct month, scrolling down to the date and then across to the expense column. That worked ok but it was kind of a long process and did not allow for any flexibility when an expense changed half way through the year like if my advertising strategy changed.

       

      What I would like to do is have an "entry page" (for lack of knowing the correct description) where I would enter the date, amount, category, subcategory, and any notes that needed to go with the expense which file make would then store in a CSV or similar file type. Then at the end of the year or as needed a general report could be generated using a script to give a total of expenses by category or if more detail was needed the CSV could be exported in its entirety.

       

      SO where do I start on putting something like this together?

        • 1. Re: Data Entry Solution
          philmodjunk

          In FileMaker, you'd start by figuring out, as you have already done, what data you need to enter, Then you start a new solution and start defining those fields in a table.

           

          I'd think in terms of a check book register or bookkeeping ledger as a "model" of how the data might be set up in your table and arranged in a layout.

           

          date, amount, category, subcategory, and any notes

          lists the fields that you'd need to get started. From there, you might use one field for spending and another for deposits instead of a single amount field (think of a bookkeeper's debit and credit columns) with a combination of a calculation field and a summary field to get a running balance column if you want that.

          would then store in a CSV or similar file type.

          There would be no need for that, just leave the records as they are in your table.

           

          Then at the end of the year or as needed a general report could be generated using a script to give a total of expenses by category or if more detail was needed the CSV could be exported in its entirety.

           

          The simplest way to do that in FileMaker would be to set up a summary report (you can look up that two word term to learn more. Other more sophisticated reporting options are also possible.

           

          But that's just a brief outline of how you might get started. Be prepared to invest time learning how to set up a relational database in general and FileMaker in particular in order to get the results that you want.

          • 2. Re: Data Entry Solution
            brsamuel

            You might want to look at the starter solutions for ideas.  I believe there is one explicitly for expense tracking.

            • 3. Re: Data Entry Solution
              kaotishe

              Yeah, I did look at it, but it has a bunch of stuff in it that I don't need. Thanks.

              • 4. Re: Data Entry Solution
                kaotishe

                I have the basic layout set up. I did not do a good job of explaining what I want it to do. Right now the way it operates I need to add a new record each time I have a new expense. And by the end of the year I will have hundreds of records and then as the years go by thousands. I want to simplify the front end entry so that I don't need to keep adding new records. I would like an "enter button" on a landing page that when I, (or perhaps more importantly) someone else enters an expense all they need to do is open that page fill in the fields and click "enter".

                 

                The CSV is for the tax accountant at the end of the year. They do not use Mac and do not have access to FileMaker.

                 

                The other question that has occurred to me, is it possible to set the sub categories up so that the drop down will only offer related choices? I.e. when advertising is selected as the main category I don't want doctors copay showing up as a sub category option. I would only want that to show up if the main category was medical?

                 

                I do have some relational tables already set up - which is where I want to add the income section of the fields so that I can track it by employee. And I want to relate income by employee to jobs. I also want to be able to do that with the COGS expenses from this table.

                 

                Right now I am tracking all of this with spread sheets which has meant entering some of the same data in multiple places.

                • 5. Re: Data Entry Solution
                  philmodjunk

                  And by the end of the year I will have hundreds of records and then as the years go by thousands. I want to simplify the front end entry so that I don't need to keep adding new records. I would like an "enter button" on a landing page that when I, (or perhaps more importantly) someone else enters an expense all they need to do is open that page fill in the fields and click "enter".

                   

                  Thousands or even millions of records really shouldn't be a problem for this. Just because you have that many records does not mean that you have to have them all on your layout at the same time. With your method, you are still adding a new record each time, you are just doing the data entry on a form view layout. You can create a new record and then fill in the data, or you can set up a form view layout with a set of global fields and use a "save" button to run a script that creates a new record and transfers the data from the global fields to corresponding fields in your current table.

                   

                  The CSV is for the tax accountant at the end of the year.

                   

                  Exporting from FileMaker to CSV or to Excel (which many accounts prefer), is a very straight forward process. You find your records and select Export Records from the File Menu. You can also set up a script to do this at the click of a button. The script can even attach the file to an email and email it to your accountant

                   

                  is it possible to set the sub categories up so that the drop down will only offer related choices? I.e. when advertising is selected as the main category I don't want doctors copay showing up as a sub category option. I would only want that to show up if the main category was medical?.

                  This is called a "conditional value list" there are a number of ways that  you can use the value selected in one value list to "filter" the list of values that appear in another. The most common method relies on a relationship, but it is not the only way.

                   

                  Here are two instructional files on selecting values from a list. Each file has multiple working examples that come with detailed instructions in how they were set up and how they work:

                  Adventures In FileMaking #1 - Conditional Value Lists

                  Adventures In FileMaking #2 - Enhanced Value Selection

                  • 6. Re: Data Entry Solution
                    kaotishe

                    Thank you for the information. It took me a couple of days but it appears I now have the conditional value field working properly.

                     

                    "You can create a new record and then fill in the data, or you can set up a form view layout with a set of global fields and use a "save" button to run a script that creates a new record and transfers the data from the global fields to corresponding fields in your current table." Where do I get started on this? I think it could save me (especially if Patti does some data entry) from accidentally writing over a previous entry...

                    • 7. Re: Data Entry Solution
                      philmodjunk

                      That really shouldn't be a problem even with a list view layout designed to look and act like a bookkeeping ledger. Overwriting a record would only happen if you were entering data into fields that already contain data and this would be very obvious to the user.

                       

                      But the part of my previous post that you quote actually describes two different methods:

                      A form view layout can be created with the fields from your table placed on it. A click on a button or New Record from the Records menu could create a new record on this layout for the user to use to log a new transaction.

                       

                      The other method would place a set of global fields on this layout. A Save button could perform this script:

                       

                      New Record/Request

                      Set Field [YourTable::Field1 ; YourTable::GlobalField1]

                      Set Field [YourTable::Field2 ; YourTable::GlobalField2]

                      ...and so forth for each field pair...

                       

                      Set Field [YourTable::GlobalField1 ; ""]

                      Set Field [YourTable::GlobalField2 ; ""]

                       

                      the first set field steps transfer data from the global fields and then the second set clears the global so that they are ready for the next new transaction.

                      • 8. Re: Data Entry Solution
                        kaotishe

                        Thank you.