find records within the current month

[carol333]

hi

I think this is a simple question that I should know how to do but it just eludes me each time I try it

I need to generate a report which finds records where a specific field is completed within the current month

I tried the following but it found hundreds of records

If ${database name}:specific field name here = MonthName ( Get ( CurrentDate)) & Year ( 2009 )

grateful thanks for any hints

Carol

[LaRetta]

Hi Carol,

If the 'specific field' is a date field, you can script finding records for the current month/year as:

Enter Find Mode [ ] uncheck pause

Set Field [ yourDateField ; Month ( Get ( CurrentDate )) & "/" & Year ( Get ( CurrentDate ) ) ]

Set Error Capture [ On ]

Perform Find

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ OK ; "No records found." ]

... do whatever if no records

Else

... do whatever with your found set

End If

[carol333]

hi

many thanks for your help

Yes my specific field is a date field and I used your code - but it still gave me records with dates not within current month - eg I got records way back to 2004 - seems very strange as your code makes good sense to me - I'm wondering if something else in my database is causing a problem

regards

Carol

[LaRetta]

Ooops. Are you really using vs. 6? If so then you couldn't have used my code because it has Get ( CurrentDate ) instead of Status ( Currrent Date ). The shortcut options for finding dates weren't available back then either. Can you provide your script or an empty zipped clone of your file pointing out the script?

[carol333]

hi

Sorry I'm using fm 9 - I think I have changed my profile now to show this corectly.

I don't know how to send you a clone. I'm searchng for a way to add a screen shot.

later - pdf is now attached

thanks

Carol

fm_script.pdf

Edited July 9, 2009 by Guest

[Raybaudi]

Hi

what appens if you write ( in Find Mode )

7/2009

into that field and hit return?

[carol333]

hi

I get just those records with dates in July

Carol

[LaRetta]

Carol, you have a RESTORE on your Perform Find[]. What does that say? You shouldn't have it there because it will interfer with your first find request on the date using the Set Field[].

Click on the Perform Find[] script step and then Specify button to see what is there. A stored find request will not MODIFY your prior request - it will replace it entirely.

[carol333]

hi

I did not know that - the Find is to get records that also have any date in another field - so it searches that date field as * - and I think this may be why I get so many records. I don't know how to set the find without Restore automatically activated. thanks

Carol

[Raybaudi]

Delete the script step:

Perform Find [ Restore ]

Enter a new one so that it appears as:

Perform Find [ ]

[LaRetta]

Carol,

No need to delete your Perform Find [RESTORE] script step and recreate it - just uncheck the Specify Find Request checkbox.

The other criteria which needs to be included in the find must be moved up by creating a second Set Field[] step right below the first. It would be:

Set Field [ yourSecondDateField ; "*" ]

This will provide an [AND] search, meaning find all records with date for this month year AND that also has a date in the other field.

[LaRetta]

I don't know how to set the find without Restore automatically activated.

It is true that, when you perform finds, FileMaker internally stores that find and attempts to provide it in the Find Request dialog box ... if you manually perform a find then open the dialog, you will see your last find listed. But that is only to make it easier, ie, you can manually create a find (if your search criteria is all static information), then open the Find Request dialog and verify it has been listed properly (or modify it as needed). Then activate the check box.

But RESTORE should never activate (be checked) automatically when you create the script steps.

[carol333]

Hi LaRetta

thanks for your excellent advice - I admit to still being a little stuck

1 - see attached I can't seem to be able to set up the Set field as you describe

2 - your comment

"But RESTORE should never activate (be checked) automatically when you create the script steps." - I cannot see any place that I have an option NOT to activate Restore - it just does it - sorry to be dense about this

regards

Carol

error_script.pdf

[ralph.nusser]

See the example file.

Ralph Nusser

Sogetes Computer-Services

www.sogetes.com

Date_Current_Month_SOGETES_1_0_0.fp7.zip

[LaRetta]

Set Field [ Jnls-journal articles::art one recd < - goes in the 'specify target' section

and

"*" goes in the calculated dialog box itself.

Again:

Set Field [ Jnls-journal articles::art one recd ; "*" ]

[LaRetta]

2 - your comment

"But RESTORE should never activate (be checked) automatically when you create the script steps." - I cannot see any place that I have an option NOT to activate Restore - it just does it - sorry to be dense about this

Don't apologize, I just want to understand as well. Then I would appreciate input from others here then because if Find Requests DO activate automatically, I'd like to find out which OS and versions it does it on so I can report it as a bug.

Again, let's say you create a new script and you double-click Perform Find[] to insert it into your script. Are you saying that the check box 'Specify Find Requests' automatically checks? It doesn't matter if (within the box when you click the Specify button) FM inserts your prior find information ... that doesn't put that find information into effect unless the check box 'Specify Find Requests' is checked.

Please verify and can others confirm or deny as well?

[comment]

if Find Requests DO activate automatically, I'd like to find out which OS and versions it does

I don't think they do. Just to make sure, I tested v.9 and v.10 on OS X, with both Enter Find Mode[] and Perform Find[].

I suspect we are dealing with a flashback to v.6. :

[LaRetta]

Thank you for testing for me, Michael. I couldn't test on Windows 2000 - only Windows 2002. Ah yes, I worked with vs. 6 for short time - it was PIB when dealing with that non-intuitive restore/replace dialog when leaving.

Carol, you won't have to deal with that in your vs. 9. :laugh2:

[bruceR]

You are not using the set field script step correctly.

To be specific, you appear to be trying to do both actions all at once.

There are two parts to set field and two buttons you must use.

You must specify the target field (top button)

You must specify the value the field is being set to (bottom button)

[LaRetta]

What I said ...

And right before that, I explained that Carol can't have both a Set Field[] and RESTORE. I don't get it, Bruce, what didn't I say that you did? :idunno:

[bruceR]

Oops, didn't see your comment. And mine stepped through the explanation a little differently.

[LaRetta]

And mine stepped through the explanation a little differently.

Sometimes when it appears that a point is missed, I suppose repeating isn't a bad thing. It just made me feel that you either had me on your 'invisible' or you completed skipped reading my posts. It's happened quite a few times before as well. I appreciate you explaining it. :smile2:

[carol333]

Hello everyone

Grateful thanks for all the help given.

In response to LaRetta

"Again, let's say you create a new script and you double-click Perform Find[] to insert it into your script. Are you saying that the check box 'Specify Find Requests' automatically checks? It doesn't matter if (within the box when you click the Specify button) FM inserts your prior find information ... that doesn't put that find information into effect unless the check box 'Specify Find Requests' is checked. "

Yes you are absolutely correct - when I insert a Perform Find step in the script – it’s only when I ‘specify’ a find request that it comes back as Perform Find [Restore].

~~~~~~~~~~~~~

In response to Bruce R

"You must specify the target field (top button)

You must specify the value the field is being set to (bottom button)"

yes many thanks –I was doing this but in the value field I was being very dense and putting the field name back there as well as in the target so that’s why Filemaker didn’t like what I was doing.

~~~~~~~~~~~

Now I think I have the script exactly as it should be but if you look at the attached it shows my script and just behind it you can see some of the results I’m getting – some from back in 2007

the pink field is the field that should only show current month – the first date field is my criteria for “*”

– I’m just stumped why this should happen

best wishes

Carol

error_2_script.pdf

[carol333]

sorry - I had the field wrong (you'd think I'd start the week bright & fresh!!??!!)

attached pdf shows the same thing happens even when I get the right field in the script

Carol

error3_script.pdf

[LaRetta]

I suggest you zip and attach your file. It can be empty but I would prefer have a few records with the dates intact in those two fields.

[carol333]

hi

I can do this but not happy to have it available to all - could I send to you privately? - sorry to everyone else but company confidentiality may be involved

best wishes

Carol

[LaRetta]

I have sent you a private message. When we figure out what is wrong, I will post the results here, so that others who might be wondering about the issue, will also find out what worked for you. :wink2:

[ejpvi]

I am having basically the same issue, and have been following this post eagerly hoping to find a solution.

Performing a find on an unstored calc.

The calc is pulling data from another related table.

I did it this way, because when I try to perform a find on a referenced related variable, it would return results like Carroll was saying.

Whats strange, is the unstored calc worked perfectly when I used the database locally, but when I moved it to the server, all of the sudden it starts returning values that don't have anything to do with the find like I mentioned above, like what Carrol is saying.

[LaRetta]

If you are searching a related table then you will only find current Parent records which match - you will NEVER find related records. Envision this:

Main table: ContactID 47

Related table has several date records with:

3/14/2009

4/11/2009

5/3/2009

6/4/2009

Main table: ContactID 48

Related table has these date records:

5/3/2009

If you perform a find in a field belonging to the related table (which is placed on a parent layout) and you search for 5/3/2009, it will find both of these PARENT records. But that FIRST related record (on ContactID47) will display 3/14/2009 in the field because, when you place a related field on a parent layout, it will always only display the FIRST related record according to the relationship sort (or natural sort order of creation date).

The second parent ContactID48 will show 5/3/2009 as you would expect but ONLY because that is the only related record and there are no prior related records. So the find is working exactly as expected. If you want to find children, switch to the child table.

I did not feel that was Carol's issue but I'm waiting to review her file. So searching a related field on a parent layout may surprise you with the results. If you are unsure, place a portal of those related records and see if the date you searching for (in the related table) exists in the PORTAL.

I cannot tell you anything else without detailed information which you haven't provided. :wink2:

[ejpvi]

Well I believe you. No matter what I tried, couldn't perform a find on one layout, based on a field in another referenced table.

Though earlier, before I was using a server, I was able to store referenced information in an Unstored Calc, and sort and find by that.

Once I moved to the FM server, it started behaving the same way, as if I was searching the referenced data, with strange results.

I finally got a workaround scripted. By going to the layout for the table I wanted to search in, performing the find there, then using the "go to related records" "show found set" I was able to go back to my original layout with the proper records filtered out. So now it Works.... I am just baffled how my other way worked at all in the first place.

Thanks, I was inspired for the work around by reading your posts.

Hope you can help Carroll.. sorry for jumping into the thread.

[LaRetta]

No problem for jumping in but I still encourage you to figure out why you get the results you do. I have NOT heard reports of a bug such as this. Can you make up a sample demo file for me to serve and try which shows the behavior you describe?

I still believe there is something here that you (we) are missing. And I would hate to think that you go through the rest of your FileMaker career performing unnecessary, unstored finds because of a misunderstanding in the underlying theory.

[LaRetta]

Hey Carol ... thanks for providing your file back channel. Open your Script and go to the first Set Field [ ]

Month ( Get ( CurrentDate ) ) & "/" & Year ( Get ( CurrentDate ) )

In your script calculation, you have a space after the first " and before the slash and another space after the slash and before the second "... kinda hard to see but it will stop that date field from being set with anything because FM will not recognize it as a valid date type.

[carol333]

Amazing - simply amazing - that's why I love and hate Filemaker at the same time - I'm so grateful to you LaRetta for taking the time and trouble to help me - enormous thanks - it works a dream now

(good luck with solving ejpvi's problem)

all best wishes

Carol