9 Replies Latest reply on Jul 5, 2011 8:00 AM by philmodjunk

    Dynamic value list that updates automatically - month and year

    yuichim

      Title

      Dynamic value list that updates automatically - month and year

      Post

      I am looking for some kind of method to update a value list automatically depending on the month and year you are in.

      For example, we are in June-2011.  I would like to have a value list starting from 12 month before and 12 month after today.

      Starting from 06-2010 ~ 06-2012

      Would that be possible?  I am thinking some kind of custom function can do this?

        • 1. Re: Dynamic value list that updates automatically - month and year
          philmodjunk

          No custom function needed as we already have a function for today's date and we can calculate this range from that date.

          c6Before: Let ( today = get ( currentdate ) ; Date ( Month ( today ) - 6 ; 1 ; Year ( today ) )

          c6After: Let ( today = get ( currentdate ) ; date ( Month ( today ) + 7 ; 0 ; Year ( today ) )

          Then your conditional value list can be based on this relationship:

          YourTable::c6Before < ValueTable::Date AND
          YourTable::c6After > ValueTable::Date

          Just make sure that these two calculation fields are unstored or they won't update correctly.

          • 2. Re: Dynamic value list that updates automatically - month and year
            yuichim

            Thanks Phil!

            I was able to get the 6month before and after.  Now, how would I implement the value list (drop down list) as:

            12-2010
            01-2011
            02-2011
            03-2011
            04-2011
            and go on....    I guess it's like the credit card expiration dropdown list that updates depending on your current date..
            I think I am getting a bit confused at teh "YourTable" part so I am not understndling clearly..

            • 3. Re: Dynamic value list that updates automatically - month and year
              philmodjunk

              Is this the only data in the table? or are there other fields that supply the data to the value list? (Are you selecting a month and year each time or are you selecting other datat and this column just serves as the filter?)

              Note that the calculation fields I suggested compute actual dates where this data would appear to be text. The data you show here won't work with that relationship. You may need to create a calculation field in this table that converts it into an actual date:

              Date ( Left ( textfield ; 2 ) ; 1 ; Right ( textfield ; 4 ) )

              and if your data never specifies the day, we can modify the 6After calc to be: 

              Let ( today = get ( currentdate ) ; date ( Month ( today ) + 6 ; 1 ; Year ( today ) )

              (The combination of month + 7 and day 0 produce a date for the last day of Month + 6.)

              • 4. Re: Dynamic value list that updates automatically - month and year
                yuichim

                No, the table is actually an OrderItem table and has many more fields.  However, the field that I am trying to use is a date field which is used to hold a start month and year date.  It is like for example, if you have a table for all orders for apartment lease, it will have a field to show the lease start month/year.  That is basically what I am trying to achieve.
                What I thought was by creating a dynamic dropdown (value list) for the user to be able to just choose from the list, it was going to be good usability wise..

                • 5. Re: Dynamic value list that updates automatically - month and year
                  philmodjunk

                  The problem is that your date field is not actually a date field, it is a text field and will not order correctly in order for the relationship I posted to work. ("12-2011" is less than "02-2009") If you add the calculation field I described in my last post, you can set up the relationship I described to support a conditional value list that restricts the entries to the range of values you specified. You can still select your "date" text field as column 1 of your conditional value list if that is the value you want to enter into your field--it just won't be used directly as part of the relationship that filters the values.

                  I've been assuming that you know how to set up a conditional value list. If you don't, take a look at these links:

                  Forum Tutorial: Custom Value List?

                  Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                  Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

                  • 6. Re: Dynamic value list that updates automatically - month and year
                    yuichim

                    So this is what I did.

                    I created two calculation fields in OrderItems table called

                    c6Before
                    c6After

                    They are unsorted and updates regularly.

                    Now where do I put the  Date ( Left ( textfield ; 2 ) ; 1 ; Right ( textfield ; 4 ) ) in?  I am getting a bit confused...

                    • 7. Re: Dynamic value list that updates automatically - month and year
                      philmodjunk

                      As a new stored, indexed calculation field in your table of values so that it can be used on the other side of the relationship than the c6Before and c6After fields. "textfield" in this expression would be the field in this table that holds the Month year data.

                      • 8. Re: Dynamic value list that updates automatically - month and year
                        yuichim

                        Sorry, Phil.

                        I am still trying to figure this out.  You have refered to "Value Table".

                        What do you exactly mean by that?  Do we need a specific table to store the value dates to create the dynamic list?

                        • 9. Re: Dynamic value list that updates automatically - month and year
                          philmodjunk

                          Conditional value lists are based on a relationship between two table occurrences. One is most often the occurrence on which you've based your layout by selecting one in the "Show Records From" drop down found in Layout Setup... (In some cases, it's an occurrence "close to" it in Manage | Database | Relationships.) The other is the occurrence of the table from which your value list will draw it's values. The relationship between the two works as the "filter" to control which values from that occurrence's table will actually appear in the drop down list or pop up menu.

                          Since I do not know what your tables or occurrences are called, I have referred to the table from which these values are drawn as the "value table".