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.
I was able to get the 6month before and after. Now, how would I implement the value list (drop down list) as:
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..
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.)
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..
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
So this is what I did.
I created two calculation fields in OrderItems table called
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...
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.
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?
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".