I am trying to automate our monthly reports. I have a script that is triggered on the first startup of any given month. I am using a field -

utility:LastReportMonth

(Example - January = 1, March = 3, ...)

So, I was thinking that an easy way to perform a find within my script was to do the following:

...

Enter Find Mode

Set Field (AR_LineItemsPaymentDate;Utility::LastContractReportMonth & "/*/2008"

Perform Find

I have tried numerous ways of handling this, but I am always getting the (No valid criteria..." error. I have searched all over and am not seeing an easy solution to this. What am I doing wrong?

Set Variable [ $m; utility:LastReportMonth ]

Enter Find mode []

Set Field [ AR_LineItemsPaymentDate; $m ]

Perform Find []

P.S. note that this will only work for the current year

Edited October 13, 200817 yr by Guest
added PS

Once you go into Find mode, your relationship won't work. Try setting a variable before you go into Find mode and then work that into your Set Field calculation.

BTW, you don't need to construct the date to find the month. See this discussion.

Edited October 13, 200817 yr by Guest
John beat me to it.

no dice - still invalid criteria. The date field that I am performing the find on is (MM/DD/YYYY), but the utility field is ONLY the month.

So, you just recommended that I search a full date field with only the month. Is that possible? If so, maybe my issue is something else...

I responded before yor response, Fitch. I just read the discussion and it answered part of what I was asking. I am experimenting with the variables now. I am still doing something wrong - let me try to figure this out - I will post another question or a conclusion to this.

Thank you to both of you - Fitch and Mr_Vodka!

sf

Edited October 13, 200817 yr by Guest
didn't show proper respect to MR_Vodka's name...

Read Tom's post. You have to set the variable from a layout who's table occurence has a relationship to the utility table occurence, PRIOR to going into find mode

that helps, too - let me see if I can fix this.

Excellent - I ended up doing this:

Set Field (AR_LineItemsPayment::PaymentDate;Month(Get(CurrentDate))-1

and it is working. I still have a fair amount of work to do to get everything done like I need, but this was certainly an obstacle.

Thank you guys, again, for your insight. I am sure I will be posting again soon.

Scott

It may work now, but it won't work come January. The best way to find the records of previous month is to do so expressly by computing a range:

Date ( Month ( Get(CurrentDate) ) - 1 ; 1 ; Year ( Get(CurrentDate) ) ) & ".." & Date ( Month ( Get(CurrentDate) ) ; 0 ; Year ( Get(CurrentDate) ) ) 

That way you also don't depend on the date format currently in use.

See also:

http://fmforums.com/forum/showtopic.php?tid/186398/

http://fmforums.com/forum/showtopic.php?tid/191858/

Man - even better! I knew that I was going to have to figure something out in January, but was prepared to deal with it then, I guess. You beat me to it and I really appreciate it! I will put that in tomorrow when I get in.

THANKS!

sf

Excellent - I got it in and it works beautifully! I tested it with a January system date and it still works.

The two threads that you linked me too helped in explaining it - I will definitely be using those date functions in the future. I did search before posted, I guess it is tough sometimes because the search terms in my head don't always match what the answer is :

One last follow up - I noticed that you used 2 periods ("..") for the range, instead of 3 ("..."). Are these two interchangeable? Does it make a difference at all?

Thanks!

Scott

You can use either two or three periods, or even a single ellipsis character (the last one is undocumented, but it works).

However, you need to be careful when using two periods with a number range:

http://fmforums.com/forum/showpost.php?post/240786/

Whooo! - that would have driven me nuts had I run into that. Thank you for the tips, again. I am in the middle of some heavy automation scripts and I am sure I will be back soon.

I basically need to automate all of the monthly reporting for the accounting department. First step now is simply producing the reports. Later on, I will surely want to produce some data to send straight into the accounting system, but I don't want to get ahead of myself.

So, again, thanks so much too all who have contributed! I have learned so much from this forum and continue to do so.