1 2 3 Previous Next 35 Replies Latest reply on May 24, 2012 9:01 AM by GuyStevens

    Database of Monthly Bills

    peanuts

      Title

      Database of Monthly Bills

      Post

      I'm having trouble creating one database for my monthly finances. Do I use "LAYOUTS" for this or what? I would like ONE DATABASE to show how I pay my bills each month. In Bento it was pretty easy by creating a "LIBRARY." Or should I try a specific template from within FileMaker Pro?

       

      Looking for suggestions please. And yes, I'm fairly new with FileMaker Pro. I'm used to Bento. Any help would be greatly appreciated!

        • 1. Re: Database of Monthly Bills
          GuyStevens

          First you would make tables. You do this by going to File - Manage - Database.

          Then when your tables are dond you can set up relationships.

          Then you make layouts that are based on your tables.

          If you you could open a starter solution to see if one of them might work for you. And else you can look at them to see how they are made and then you can make your own database.

          • 2. Re: Database of Monthly Bills
            rgnant_1

            It is easy to confuse a TABLE and a LAYOUT.  Let's say, for example, (not being sexist) that you make an inventory of everything in our kitchen.  These items would be listed in your TABLE.  You might have a name for the item, the date purchased, the cost, where you bought it, where it is stored, etc.

            You can visually show some of all of this information on a LAYOUT.  You could have one layout that would show only the item and where it is stored.  You could have another LAYOUT that shows the item and where you bought it along with the cost.  The point is that LAYOUTs draw on fields that are in a TABLE.

            In your case, you probably want a TABLE that would show information about your expenses.  Something like:

            Type of Expense (Heat, utilities, food, car, etc,)

            Description of what is included in the expense

            Date due

            How paid (check, debit, auto deduct, etc.)

            and so on.

             

            After you have created the TABLE, you can create a LAYOUT.  You can arrange the fields on the layout to look the way you want it to look.

            Does this make sense?   If so, we can continue on, but FM really depends on knowing a little about the TABLE and the LAYOUT.

            • 3. Re: Database of Monthly Bills
              peanuts

              Thank you so much for helping me find a solution. The thing is that I understand the concept of creating the table. But before I posted, I was trying to create these layouts based on the tables, but what confused me is that I coulnd't change the data in each of the layouts. You see I need to be able to compare ONE MONTH to the NEXT MONTH. And that makes sense because what I pay for in May, may NOT be the same in JUNE. You see what I mean?

               

              So then the question would be this. Do I create a basic table say: What Bill; Amount Due; Date Dute; Payment Date; Method of Payment; Notes.

              And from there do I create layouts named: April; May; June; July, etc? But again the amounts in April will be different than the amounts in say July.

              Let me know if I'm on the right track, based on your feedback. Again I do appreciate the help.

              • 4. Re: Database of Monthly Bills
                GuyStevens

                That sounds pretty straightforward.

                You create one table. Call it TblBills.

                In this table you create fields like:
                Id  -  This is a number field that has an Auto enter set to Serial Number.
                Date (Date Field)
                Description (Text field)
                AmountDue (Number field)
                DateDue (Date Field)
                PayementDate (Date Field)
                MethodOfPayement (Text Field)
                Notes (Text Field)
                s_Total (This is a summary field that shows the total amount you need to pay) It make the Total of "AmountDue"

                Then you create a layout. with all these fields. One layout is a simple, normal layout.Where you can enter bills.

                And a second one is a List view. Where you can have an overview of the bills and the total.

                If you want you can make this list with a subsummary field when sorted by Month. So you can see the totals per month.

                Then you could also perform finds to restrict the data to show only one month, or three months, or...

                The idea is to have all data available and to make specific "finds" when you need them, you can do this manually or you can creata buttons or dropdowns.

                The idea is that at any time you are able to see amounts payed, or not payed for any kind of date or date range.

                If you need any help or a little example, let me know.

                • 5. Re: Database of Monthly Bills
                  GuyStevens

                  You could also create a portal on your Bills layout that shows the past months and the amount payed each month.

                  • 6. Re: Database of Monthly Bills
                    peanuts
                     
                    • 7. Re: Database of Monthly Bills
                      peanuts

                      Woudln't want to turn that down. A little example would be absolutely great! I like the idea of a subsummary field-but that's a little over my head. :-)

                      • 8. Re: Database of Monthly Bills
                        GuyStevens

                        Don't worry, we'll get you there :)

                        What version of Filemaker are you using? 11 or 12?

                        • 9. Re: Database of Monthly Bills
                          GuyStevens

                          http://dl.dropbox.com/u/18099008/Demo_Files/BillsMonthly.fp7

                          This is a little demo I just made.

                          It's really basic. You could add more functionality and automated stuff.

                          In the layout you can add bills.
                          In the list view you can do finds and search within a date range etc. The total at the bottom is always updated as you perform a find and see only a selection of the bills.

                          • 10. Re: Database of Monthly Bills
                            peanuts

                            Oh thank you, thank you! I was out most of the day running errands, so I just saw this post. I will look at the sample and see how it works. Thank you again. You've been most helpful.

                            • 11. Re: Database of Monthly Bills
                              peanuts

                              The demo you made for me is pretty good. I now have a better (if not expert) understanding between layouts and tables, and the different views. What I can't figure out in your demo is how to add the months of the year. For example my category DESCRIPTIONS will always be the same from month to month. The amounts and dates will change from month to month. So I can see how to add the records, but I can't figure out how to add a new month for the same set of categories. If you can help with that, that I think I'm on my way using the demo you supplied. Thanks again. PS: I'm a graphic designer, so my focus has been on the look-so that seem to come to me pretty easily.

                              • 12. Re: Database of Monthly Bills
                                peanuts

                                Don't see my latest message, so I'm sending again. I now believe that I have a better understanding of layouts to tables. I can have one table and several layouts and design them to how I see fit. I have reviewed the demo you sent and I think it could work for me. However, I haven't been able to figure out how to add the rest of the months for the year. You see, it will be the same categories each month-just the amount due and dates change as needed. That's why I think what you started is great. But I would need to see the SAME categories for MAY-JUNE-JULY, etc.

                                Unfortunately, I am a graphic designer-so I have been focusing a lot on the visual look-content over design...form follows funciton--yeah I know. But if you could help me over this last "hump" I think I might be on my way. Thanks again.

                                • 13. Re: Database of Monthly Bills
                                  GuyStevens

                                  Hey Betty, I'm glad you are a graphical designer because my example was't really graphically stunning :)

                                  Actually the Month name is a calculation. It gets calculated from the Payment date.

                                  I don't know if you noticed but you have three date fields.

                                  - The date of the Bill
                                  - The due date
                                  - The Payement date

                                  I assumed that it was the payement date that was most relevant because you want to see what you actually payed during a partucular month.

                                  The Month name you see in the list is actually a calculation. It get's calculated automatically from the payement date.

                                  If you go into "File" - "Manage" - "Database" and you check out the fields of the table "Bills" you can see that one of the fields is called something like c_MonthName. If you open it's properties you can see the calculation is:

                                  MonthName (PayementDate)

                                  This means that when you enter a date in the Payement Date field this calculation automatucally calculated the Name of The Month.

                                  So that way you don't manyally add months in the list. They are created automatically when you enter a new payement date.

                                  That's kind of the idea with a database. That as much as possible happens automatically.

                                   

                                  If your description is always the same you could do the following.

                                  In your layout "LayBills" go to "Edit Layout" or go to Layout Mode. Then select your field descroption. Then in the Inspector go to the tab "Data" and under "Control Style" select "Auto Complete using existing values.

                                  Bills

                                  Alternatively you could also create a dropdown lists that shows you the values you have already entered before.

                                  Under Control Style select "Dropdown List" from the dropdown list.

                                  Bills 2

                                  Then click on the little pencil next to "Values From":

                                  Bills 03

                                  And select the desctiption field from the bills table as your source:

                                  Value list

                                  Then you probably want to "Include arrow to show and hide list" on the inspector.

                                  Try it out and see if it works. If you have any more questions, you know where we are :)

                                  • 14. Re: Database of Monthly Bills
                                    peanuts

                                    OK, great information and I will look into that. You know, I'm probably making more of this than I should, but the purpose was to learn Filemaker Pro and have more flexibility over the database. Bento is very nice, but limited. Look at the screenshots below and you will see what I was aiming at for explaining how I wanted the "MONTH" displayed in each view. Bento is very simple: you create a NEW LIBRARY-name it; duplicate your DESCRIPTION list; change the month and you're good to go. But I like all the options you get in Filemaker Pro.

                                    1 2 3 Previous Next