Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum
filey

Months as Value list to track records by date

Recommended Posts

filey

Hi guys! I would like to call for help in my problem. I would like to search records via MONTH which I'm having a difficulty with. In my case, I like to create a value list that contains the name of the months either pop-up or dropdown and just search for the records within that month. I have done this http://help.filemaker.com/app/answers/detail/a_id/5912/kw/find%20records%20in%20current%20month but I would like to make it more easy for me to search records instead of filling up the START_DATE & END_DATE. I have all the fields to begin with but I can't do the scripting for the Month(value list) function. Thanks for the help!

Share this post


Link to post
Share on other sites
eos

You could create a table with the month names and numbers and use that to create value list; choose the month name, but insert the month number.

Problem is that when you use Show only second field, it will be sorted by that field – and an alphabetically sorted list of month names is not really useful.

 

Either don't check that setting, get a correct sort order and live with the additional display of the month number, or try another tack:

 

Create a value list with your month names, then in your script use

 

Set Variable [ $monthNumber ;

Let ( [
 ~l = List ( "January" ; "February"[i] ; [/i]"March" [i]{; etc. }[/i] ) ;
 ~f = YourTable::yourMonthNameField ;
 pos = Position ( ¶ & ~l ; ¶ & ~f ; 1 ; 1 )
 ] ;
 ValueCount ( Left ( ~l ; pos ) )
)

] // belongs to Set Variable[]; don't type this line … 

 

This gives you the month number, which you can now use to create your date range, e.g.

 

Enter Find Mode

Set Field [ YourTable::theDate ; Let ( y = Year ( Get ( CurrentDate ) ) ; Date ( $monthNumber ; 1 ; y ) & ".." & Date ( $monthNumber + 1 ; 0 ; y ) ) ]

Set Error Capture **

Perform Find

 

There's an easier syntax for searching all dates within a given month (or year), but I'll let you find that one for yourself … :D

 

** Since you've linked to (and read) the Knowledge Base article, I guess don't have to remind you about error trapping.

 

Note that the calculation above is a general approach to the problem “find index position of item y in list z”.

Share this post


Link to post
Share on other sites
filey

Thanks eos! I'll try it and I'll get back to you as soon I have done it. To be honest I'm quite a noob at scripting :D. Either way thanks1!

Share this post


Link to post
Share on other sites
filey

Oh yes! I forgot to mention that I have a field for date which is manually filled labeled as Date_Received. I can't seem to make the script work because I have left this information.

Share this post


Link to post
Share on other sites
eos

Not sure what you mean … is that the field you want to search in?

 

In my code example, you would replace YourTable::theDate with that field.

Share this post


Link to post
Share on other sites
filey

Yes I did that but sadly all it does is replace all the fields of Date_Received into range of numbers. :( I'm sorry if I can't explain it properly because I don't really know how to. :confusion:

Share this post


Link to post
Share on other sites
eos

Your script probably does not include an Enter Find Mode step; Set Field[] would then overwrite the field contents in Browse mode, instead of putting in a search criterion in Find mode.

 

Assuming that you're doing this in a single table, make sure you

 

• create a text field, storage: global which you format with the month names value list

 

• write a script with the steps listed above, like:

 

If [ IsEmpty ( your [b]global text field[/b] ]
# open Specify Calculation dialog, write '[i]IsEmpty ( [/i]', select your field name from the list to add to the formula, close with ' [i])[/i]'; this line: a script Comment # line that you don't need to add
 Exit Script
End If
# this line: a script Comment # line that would normally be empty, used as visual separator between script sections; see below
Set Variable [ [b]$monthNumber[/b] ; [i]calculation from above, where [/i]YourTable::yourMonthNameField is your [b]global text field[/b] ]
# bold: specify as variable name; italic: put into Specify Calculation dialog; this line: a script Comment # line
Enter Find Mode
Set Field [ [b]Date_Received[/b] ; [i]Let ( y = Year ( Get ( CurrentDate ) ) ; Date ( $monthNumber ; 1 ; y ) & ".." & Date ( $monthNumber + 1 ; 0 ; y ) )[/i] //not the bracket =>]
# bold: select from field list; italic: put into Specify Calculation dialog; this line: a script Comment # line
Set Error Capture [ On ]
Perform Find
#
If [ [i]not Get ( FoundCount )[/i] ]
 Show Custom Dialog [ "Nothing Found" …etc.
End If

 

Please refer to the documentation (and/or e.g. the linked Knowledge Base article) if you need further assistance in writing a script, or referencing a field in a calculation or a script step.

 

Which is not to say you cannot ask here, but you need to do some homework, too … :D

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use