5 Replies Latest reply on Jan 17, 2013 4:39 PM by philmodjunk

    values and dates with graphical structure



      values and dates with graphical structure


           this is my expenses table

                          id                     expenses                     desciption
                          1                     gas                     route gas
                          2                     toll gate                     route toll

           and this is my other table

                          id                     expenseID                     date                     amount
                          1                     1                     12/08/2012                     250
                          2                     1                     12/09/2012                     400
                          3                     2                     12/08/2012                     10
                          4                     1                     12/18/2012                     380

           my goal is to have a graphical layout like this.

           for the gas expenses layout will look like...

                          sun                     mon                     tue                     wed                     thu                     fri                     sat                     date
                                                                                                                                                                                    dec 1
                                                                                                                                                              250                     dec 2-8
                          400                                                                                                                                                         dec 9-15
                                                                      380                                                                                                             dec 16-22
                                                                                                                                                                                    dec 23-29

           my question is how? I am starting to love Filemaker Pro and it does everything from small to heavy data input without having problems. Also I love how easily you can make it available through the web. 

        • 1. Re: values and dates with graphical structure

               You can use one row portals to put your data into columns like this. A portal filter can restrict what related data can appear in the portal to data for a specific date.

          • 2. Re: values and dates with graphical structure

                 hello, thanks for your prompt answer. I am totally new to this application and have been searching all over the web and found a very few tutorials about portal. Can you at least show me how to achieve it? I appreciate your time.

            • 3. Re: values and dates with graphical structure

                   This isn't a good option for a "newbie" to take on until they have a decent working knowledge of portals. Have you worked with portals at all yet? (can't do much in fileMaker without at least some basic portals...)

                   Have you looked them up in FileMaker Help?

                   Gotta figure out where to start here...

              • 4. Re: values and dates with graphical structure

                     I have done a few projects with portals, but just to let you know I am using FileMaker Pro 10. Does this version already have portal filter?

                • 5. Re: values and dates with graphical structure

                       Portal Filters were introduced with FileMaker 11. But you can still get what you need  by using a different relationships for each portal.

                       Here's how you can set up some tables and relationships for this:

                       Weeks::Day1 = ExpensesD1::Date
                       Weeks::cDay2 = ExpensesD2::Date
                       Weeks::cDay3 = ExpensesD3::Date

                       Continue this for each day of the week.

                       ExpensesD1, ExpensesD2.. etc are all different occurrences of the same Expenses table. Each is a different "box" on your relationships graph. (See this link, if "Table Occurrence" is a new term: Tutorial: What are Table Occurrences?

                       Day1 is a date field and cDay2 is a calculation field defined as:

                       Day1 + 1

                       with "Date" specified as its return type.

                       cDay3 would be defined as Day1 + 2 and so forth...

                       Then you can set up a list view layout based on Weeks with a portal to ExpenseD1 in the Sunday column, a portal to ExpensesD2 in the Monday column and so forth.

                       A summary field defined in Expenses that computes the total of your expense amount can be placed in the row of the portal to show the expense totals for that day.

                       This is just the bare bones outline, there are a number of details that can be dealt with such as whether days from the last week of the previous month should display expense totals or not and there are ways to use a script to control what records from weeks are in your found set so that you can see a months expenses at a time if you want that.


                       Shouldn't that value of 250 on Saturday, December 8 actually be 260? (250 + 10)