Using a variable within a date mask, and perform a find in a script

[Scott Lafayette]

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?

[mr_vodka]

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, 2008 by Guest
added PS

[Fitch]

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, 2008 by Guest
John beat me to it.

[Scott Lafayette]

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...

[Scott Lafayette]

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, 2008 by Guest
didn't show proper respect to MR_Vodka's name...

[mr_vodka]

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

[Scott Lafayette]

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

[Scott Lafayette]

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

[comment]

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/

[Scott Lafayette]

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

[Scott Lafayette]

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

[comment]

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/

[Scott Lafayette]

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.