Finding last month's & Current Months' records cal

[mboyd]

Would someone be able to post a calc for finding last month's records and all records for the current month?

Thank you so much!!

[-Queue-]

Enter Find Mode [ ]

Set Field [datefield; Let( T = Get(CurrentDate); ">=" & Date( Month(T) - 1; 1; Year(T) ) )]

Perform Find [ ]

[mboyd]

Enter Find Mode [ ]

Set Field [datefield; Let( T = Get(CurrentDate); ">=" & Date( Month(T) - 1; 1; Year(T) ) )]

Perform Find [ ]

Thanks Queue, but i am getting "an operator is expected here error when trying to plug it in:

datefield; Let( T = Get(CurrentDate); ">=" & Date( Month(T) - 1; 1; Year(T) ) ) ----- would be the pasted result (changing datefield of course). aND DROPPING THE []

Also my humble request is for two separate scripts/calcs for last month's records and a separate script for current months records (not to be combined)

thank you so much for sharing the knowledge, it really helps =)

[transpower]

1. Previous month: Let([T=Get(CurrentDate)];"<=" & Date(Month(T)-1;31;Year(T)))

2. Current month: Let([T=Get(CurrentDate)];">=" & Date(Month(T);1;Year(T)))

[Søren Dyhr]

Wrong!!!

1. Previous month: Let([T=Get(CurrentDate)];"<=" & Date(Month(T)-1;31;Year(T)))

It's previous the month(S) (plural) and if we're talking February does you calc include march the 2nd!!!

Wouldn't it be better with:

Let([T=Get(CurrentDate)]; Date(Month(T)-1;1;Year(T)) & "..." & Date(Month(T);-1;Year(T)))

--sd

[mboyd]

"It's previous the month(S) (plural) and if we're talking February does you calc include march the 2nd!!"

If it's february, march 2 would be next month.... i want to be able to see the previous month's records no matter what the current month is... must allow for 31 and 30 days too. =)

thank you!

[-Queue-]

The calc should work if you are using FM7. It did include both last month and the current month's records, though, since that was what your original post appeared to be asking.

Transpower's calc for the current month should work, but you don't need the brackets around a single Let parameter.

Let( T = Get(CurrentDate) ; ">=" & Date( Month(T); 1; Year(T) ) )

The same goes for Soren's calc for the previous month, and you can use zero instead of -1, if you prefer.

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

[mboyd]

Transpower's calc for the current month should work, but you don't need the brackets around a single Let parameter.

Let( T = Get(CurrentDate) ; ">=" & Date( Month(T); 1; Year(T) ) )

The same goes for Soren's calc for the previous month, and you can use zero instead of -1, if you prefer.

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

Thanks Queue for sharing, I've enjoyed many of your posts,

last question about this, Let's say I have 30 records that were created in November but 5 of them are for event dates in December. I would love for the calc to display on November dates (current month), previous month, and if you are in the mood, all records for next month (all separate calcs.)

how would i incorporate a datefield into the mix, the above calcs work great for creation date records, how would i incorporate a "ReservationsDate" into the calc? I just can't get them right...

Thanks for sharing, and sorry i wasn't clear in my original post, it was late when i posted and I was frustrated.

cheers!

Michael

[mboyd]

Clients::DateRequested; Let( T = Get(CurrentDate) ; ">=" & Date( Month(T); 1; Year(T) ) )

I keep getting "an operator is expected here....what am I doing wrong??

[-Queue-]

You shouldn't be putting Clients::DateRequested into the calculation. It is the field to be selected when you click 'Specify target field'. In ScriptMaker it will look like

Set Field [Clients::DateRequested; Let( T = Get(CurrentDate); ">=" & Date( Month(T); 1; Year(T) ) )]

where the first portion is the field to be specified and the second is the calculation to be used.

Do you want to include records where either the creation date or reservation date is within the desired month? If so, just add a new find request using the New Record/Request script step, duplicate your Set Field step, and change the target field to your reservation date field, so it looks like

Enter Find Mode [ ]

Set Field [creationdate; calc]

New Record/Request

Set Field [requestdate; calc]

Perform Find [ ]

This will perform an OR find, retrieving all records where either result meets the criteria.

For next month's records, use Let( T = Get(CurrentDate) ; Date( Month(T) + 1; 1; Year(T) ) & "..." & Date( Month(T) + 2; 0; Year(T) ) )

When you use zero for the day portion of the Date function, it treats it as the last day of the previous month. So if it's November, the second portion of the calculation returns Date( 11 + 2; 0; 2004 ) => Date( 13; 0; 2004 ) => 1/0/2005 => 12/31/2004

Thanks for the support. I like your icon!

[mboyd]

Queue,

Awesome, you have great talent! Again thank you for sharing, you are indeed making a difference in people's lives..mine for sure, this date stuff baffles me... but you gave me a good chunk to sink my teeth into, i learn best from example, so I will plug this in tonight and fitz with it...i'm looking forward to no operator errors too... =)

thank you for sharing!

Michael

[mboyd]

You shouldn't be putting Clients::DateRequested into the calculation. It is the field to be selected when you click 'Specify target field'. In ScriptMaker it will look like

Set Field [Clients::DateRequested; Let( T = Get(CurrentDate); ">=" & Date( Month(T); 1; Year(T) ) )]

where the first portion is the field to be specified and the second is the calculation to be used.

That worked nicely, however I had to put a "show omitted records" into the script. =)

[mboyd]

next months records calc is perfect, don't even need a show omitted records

current months calc doesn't work for me, still showing records for december

Let( T = Get(CurrentDate) ; ">=" & Date( Month(T); 1; Year(T) ) )

and last months script needed a show omitted records, which i don't think was the plan??

--- recap:

Last Months Records

Enter Find Mode [ ]

Set Field [Clients::DateRequested; Let( T = Get(CurrentDate); ">=" & Date( Month(T); 1; Year(T) ) )]

Perform Find [ ]

Show Omitted Only

This Months Records *** not working.when viewing script/perform find/specify find requests it says 12/1/2004...12/31/2004 - it should be November only..

Enter Find Mode [ ]

Set Field [Clients::DateRequested; Let( T = Get(CurrentDate) ; ">=" & Date( Month(T); 1; Year(T) ) )]

Perform Find [ ]

Next Month's records

Enter Find Mode [ ]

Set Field [Clients::DateRequested; Let( T = Get(CurrentDate) ; Date( Month(T) + 1; 1; Year(T) ) & "..." & Date( Month(T) + 2; 0; Year(T) ) )]

Perform Find [ ]

[Søren Dyhr]

Just a simple question - hope you can bear with me for asking so daft??? But for each search which isn't a dedicated stacking of requests - do you remember to issue a "Show All Records" :? It must in my humble opinion be something like that since you write:

don't even need a show omitted records

--sd

[-Queue-]

Show Omitted Only is not needed, nor does it make sense in this context. I think your problem is that you're using the same calc for both this month and last month's records. Last month's calc should be

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

not Let( T = Get(CurrentDate); ">=" & Date( Month(T); 1; Year(T) ) )

which is a calc for anything greater than or equal to this month. If you show omitted after finding all records greater than last month, you will be left with all records less than the current month, which is close to, but not exactly, what you want.

This month's records will require a similar calc now that we know you have records for next month, and possibly beyond, as well. Normally, Let( T = Get(CurrentDate); ">=" & Date( Month(T); 1; Year(T) ) ) would work fine because you wouldn't have any records for next month or beyond that. Since you are using not only creation date but reservation date, you need to limit the calc to the end of this month instead of allowing it to find anything greater than or equal to the first of this month.

Let( T = Get(CurrentDate); Date( Month(T); 1; Year(T) ) & "..." & Date( Month(T) + 1; 0; Year(T) ) )

I'm not sure to what Soren is referring, but neither a Show All Records or Show Omitted Only is necessary for any of these find scripts.

[mboyd]

Thanks Queue!

Got the scripts working nicely now thanks to you. You're right, Show Omitted Only is not needed, nor does it make sense in this context. =)

Last month's records:

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

This month's records:

Let( T = Get(CurrentDate); Date( Month(T); 1; Year(T) ) & "..." & Date( Month(T) + 1; 0; Year(T) ) )

Next Months Records:

Let( T = Get(CurrentDate) ; Date( Month(T) + 1; 1; Year(T) ) & "..." & Date( Month(T) + 2; 0; Year(T) ) )

Thanks for sharing! I'm really thankful to have these calcs in the scripts. It's nice to be able to see these records like that. Thanks again

Michael =)

[mboyd]

Queue,

Would you be so kind to spare 4 more of your award winning calcs?

Something along the lines of...

Last month's records:

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

This month's records:

Let( T = Get(CurrentDate); Date( Month(T); 1; Year(T) ) & "..." & Date( Month(T) + 1; 0; Year(T) ) )

What i want to do is this:

Calc: 1: I need to call up the records from the 1st to the 15th, of the previous month

Calc 2; I need to call up the records from the 1st to the 15th of the current month

Calc: 3: I need to call the records up from the 16th to the 31st (if there is a 31st) of the previous month

Calc 4; I need to call the records up from the 16th to the 31st (if there is a 31st) of the current month

I really like your clean coded calcs, they have been doing wonders for me, if you could be so kind to spare a few moments for these I'd be most grateful. =)

[-Queue-]

Anything for a fan.

Let( T = Get(CurrentDate); Date( Month(T) - 1; 1; Year(T) ) & "..." & Date( Month(T) - 1; 15; Year(T) ) )

Let( T = Get(CurrentDate); Date( Month(T); 1; Year(T) ) & "..." & Date( Month(T); 15; Year(T) ) )

Let( T = Get(CurrentDate); Date( Month(T) - 1; 16; Year(T) ) & "..." & Date( Month(T); 0; Year(T) ) )

Let( T = Get(CurrentDate); Date( Month(T); 16; Year(T) ) & "..." & Date( Month(T) + 1; 0; Year(T) ) )

[mboyd]

Queue,

These are just absoultely gorgeous! I love them!! They made my work so much easier...Thank you!!

You are a true artist my friend....