Make an unstored calculation field as
Get ( CurrentDate ) - 7
Then, make relation using it
theFiled <= eventDate
So you can make value list using the relation "only related values".
Okay, I understand the concept of what you mean, but I don't really understand how to go about creating it. Which table do I create the calculation field in?
After that, I create a relationship... so that's connecting 'one' event date to 'many' of the newly created calculation field?? Is that correct?
Once I've created the relationship, I'm not sure how to create a value list with only related values.
Sorry to ask so many questions, I'm so new to Filemaker. I'm finding it incredibly useful, but definitely challenging at times!
Are you viewing your "Event List" on a layout in List view, or are you using a portal to see the events?
If you are in a List View layout, I like to create a script which would omit the older events doing a "Find", and assign the script to a button. This way you can view all the events or just the ones in your time frame, whichever you desire by the click of a mouse.
Enter Find Mode
Specify Request: (uncheck the Pause box)
Select the TO and the event date field < Get (CurrentDate) - 7
Hope this helps
Thanks Jesse! That's a really interesting idea! Right now, I access the Event List through my income list. In the income list, I have a pop up menu that has values from my EventID, and a second value showing the event name. (I actually have it set to only show the value of the second field - event name only, not the ID.) Anyways, that's what I've been using, but I like the idea of going to a layout where I'm viewing a found set instead.
I'm having a bit of trouble with the script though. I know I'm doing something wrong, I just can't figure it out. Here's what I'm doing in my script:
1. Enter Find Mode (no problems there)
2. I uncheck the pause box, and then when I click "Specify Request" I press "New" and I get the "Edit Find Request Box".
3. This is where I'm a little mixed up. The one box says "Find records when" so I choose my event date field from there, but then the criteria box won't accept < Get (CurrentDate) - 7. It says "The value of this field must be a valid date in the range of years 1 to 4000 and should look like "2013/12/25"."
Try this script:
1) Set Variable [$Date; Value: Get (CurrentDate) - 7]
2) Enter Find Mode
(In the edit find request box change the action box from "Find Records" to "Omit Records")
Select your Date Field and in the Criteria box put "<$Date" (no quotations)
3) Perform find
I tested this and it works.
Value Lists can be very confusing.
Here is another option that I use:
#1. In your Events table create a new calculated field and call it Event_ValueList (or some other name that makes sense to you).
- the calculation should be text and the Indexing should be set to All
- the calculation is:
Case ( YourEventDate > ( Get ( CurrentDate ) - 8 ) ; YourEventName ; "" )
for YourEventDate use the date field you have in your file for the event
for YourEventName use the field that you are using to identify the event
the calculations says:
in the case that YourEventDate is more recent than 8 days ago YourEventDate > ( Get ( CurrentDate ) - 8 )
then the calculation returns YourEventName
otherwise the calculation returns and empty string this is signified by the 2 quotation marks ""
#2. Now for your Value List:
in the dialogue box where you set up the value list:
• "Use values from first field" should be your ID field
• check the box for "Also display values from second field ... and select the new calculated field Event_ValueList
• below the field lists you should:
- click the radio button for "Include all values"
- click the radio button for "Second field"
** note: if you do NOT want to display the ID then check the box for "Show values only from second field"
Let me know if that works. Michael
If you're really looking for a drop-down list or pop-up menu then I think user19752 put you on the right track.
If you're interested in alternative solutions you could consider the following. Instead of a pop-up menu or drop-down list on the field (I'll call it event_ID_target) in which you want to enter the eventID you could make a pop-up button besides the field. In the pop-up window you make a portal that shows records from your table with events. In the definition of the portal you then specify a calculation that filters the records that you want to see (i.e. the events that are not too old). In the portal you need to define a button with a script that inserts the EventID into the event_ID_target, closes the popover and (if you want) goes to the next field.
For the user who enters the data this may not be the handiest solution because he/she needs to click more. On the up-side you don't need to define a new relation. I find defining the filter calculation easier than defining a new relation. Also on the up-side is that you can present a lot more information about the events and control the layout far better in a portal than in a pop-up list or drop-down menu.
Again, if you don't need to show more information about the events in the list, you'd better stick to a drop-down list or pop-up menu.
Any table but usually I use the table which use the value list.
You don't need to care about one/many.
For "Use values from" , use the right side table on the relation I wrote as
theCaluculationField <= eventDate
then for "Include only reralted..." use left side table.
Thanks for spelling this out for me step by step - it worked and it's great!!
And thanks to everyone else for their responses. It's neat to see all the different ways to achieve a very similar result. I so appreciate all of your advice!!
Glad I could help. If you were trying to select an event by way of limiting values in a value list they are some good ideas listed here in this thread by both user19752 and Michael.
Yes! I've actually printed them out and made a note in my "Try Sometime" list!