Display only records from specific month

[Morenomdz]

Hello there!

I have built a DB to my tech store, we have many service orders of the repairs we do, these have the Date field to auto fill on creation of the record, what I need to do is display filter the records per month when in List Mode, I only it to show only the records of this month, and have a tab or a button to go to previous/next months, what is the best way to achieve that?

Thanks!

[Nuos]

In find mode, put into create field the following

">"&Date(month(get(current date));1;year(get(current date)))

Cheers

[Morenomdz]

In find mode, put into create field the following

">"&Date(month(get(current date));1;year(get(current date)))

Cheers

Hello there, thanks for the info, trying that it says the current date is an invalid parameter for the get function!

[Morenomdz]

Ok I got it was just a mispelled code:

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

It worked good for the current month! Is there a way I can setup a button that will show the previous or next month? Maybe a "month -1" function?

[Nuos]

Previous month:

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

Next month:

"<"&Date(Month(Get ( CurrentDate ))+2;1;Year(Get ( CurrentDate ))) ... ">="&Date(Month(Get ( CurrentDate ))+1;1;Year(Get ( CurrentDate )))

May need to change ">=" to the greater than and equal to symbol, can't do it only iPhone

[comment]

Is there a way I can setup a button that will show the previous or next month? Maybe a "month -1" function?

 

You need to think this out more carefully. First you asked how to find records in the current month. The script suggested above will do that for you - but the criteria for the find are not stored anywhere (except for the Modify Last Find command, where it can be easily destroyed by user performing another find).

 

So when you ask for a "month -1" function, you must also ask yourself: relative to what will the -1 be calculated? You need to have some kind of a register - a global field or a variable - remembering the last choice made.

 

For example, consider a script:

Enter Find Mode [  ]
Set Variable [ $$monthOffset; Value:$$monthOffset + Get ( ScriptParameter ) ]
Set Field [ YourTable::Datefield; Let (
today = Get ( CurrentDate )
;
Date ( Month ( today ) + $$monthOffset ; 1 ; Year ( today ) ) & "…" & Date ( Month ( today ) + $$monthOffset + 1 ; 0 ; Year
( today ) )
) ]
Perform Find [  ]

Here the global variable $$monthOffset is used to store the offset from the current date's month. You can call this script with a parameter of "-1" or "+1' (or any other value) to shift the offset forward or backward. You could even have a "This Month" button where the script parameter is "-$$monthOffset" to revert to the current month.

 

 

 

 

 

 

 

 

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

Next month:

"<"&Date(Month(Get ( CurrentDate ))+2;1;Year(Get ( CurrentDate ))) ... ">="&Date(Month(Get ( CurrentDate ))+1;1;Year(Get ( CurrentDate )))

May need to change ">=" to the greater than and equal to symbol, can't do it only iPhone

 

That's not a valid syntax.

 

And the idea itself is wrong: you cannot use both ">" and "..." operators in the same expression.

[Nuos]

You need to think this out more carefully. ....

Duh ... ya I see that now. Thanks

Edited March 25, 2015 by Lee Smith
Please do not quote the entire posts

[Morenomdz]

You need to think this out more carefully. First you asked how to find records in the current month. The script suggested above will do that for you - but the criteria for the find are not stored anywhere (except for the Modify Last Find command, where it can be easily destroyed by user performing another find).

 

So when you ask for a "month -1" function, you must also ask yourself: relative to what will the -1 be calculated? You need to have some kind of a register - a global field or a variable - remembering the last choice made.

 

For example, consider a script:

Enter Find Mode [  ]
Set Variable [ $$monthOffset; Value:$$monthOffset + Get ( ScriptParameter ) ]
Set Field [ YourTable::Datefield; Let (
today = Get ( CurrentDate )
;
Date ( Month ( today ) + $$monthOffset ; 1 ; Year ( today ) ) & "…" & Date ( Month ( today ) + $$monthOffset + 1 ; 0 ; Year
( today ) )
) ]
Perform Find [  ]

Here the global variable $$monthOffset is used to store the offset from the current date's month. You can call this script with a parameter of "-1" or "+1' (or any other value) to shift the offset forward or backward. You could even have a "This Month" button where the script parameter is "-$$monthOffset" to revert to the current month.

Thank you for the input, the said "$$monthOffset" can be a simple Date field or should I use something por specific to month? If so, how? Thanks again!

I made a Data field, $$monthOffset, now, when I change the (+1):

 

Set Field [ YourTable::Datefield; Let ( today = Get ( CurrentDate ); Date ( Month ( today ) + $monthOffset ; 1 ; Year ( today ) ) & "…" & Date ( Month ( today ) + $monthOffset + 1 ; 0 ; Year ( today ) ) ) ] 

To (-1):

Set Field [ YourTable::Datefield; Let ( today = Get ( CurrentDate ); Date ( Month ( today ) + $monthOffset ; 1 ; Year ( today ) ) & "…" & Date ( Month ( today ) + $monthOffset - 1 ; 0 ; Year ( today ) ) ) ] 

It simple says there was no matches found. Am I doing something wrong?

[Morenomdz]

My "Date" field is set to auto fill when creation and no edit, would that interfere with the script workflow?

[comment]

In my example, $$monthOffset is a variable, not a field. It is created by the Set Variable[] script step in the script itself, and it contains a number.

[Morenomdz]

In my example, $$monthOffset is a variable, not a field. It is created by the Set Variable[] script step in the script itself, and it contains a number.

Got it!

[Morenomdz]

It worked well going back a month (-1) or two (-2) and so on, but how do I make it cycle back one month every time I press that button? Is it possible?

[comment]

how do I make it cycle back one month every time I press that button?

 

Doesn't it do exactly that? If your button calls the script with a parameter of "-1", then each time you press it the $$monthOffset variable get decreased by 1 and the script finds the records one (more) month earlier than before. If that's not what you're seeing, check your implementation (for example, make sure you're using a global $$monthOffset variable, not a local $monthOffset one).

[Morenomdz]

Got it working perfectly now! The issue was... 2 am and no coffee, so I was using a duplicated script I made to trial and error it. Thank you very much man! Really!

[Morenomdz]

Got one small issue, it works fine until it doesnt find anything anymore, lets say I only have the past 6 months on my db, then after those 6 searchs it will ask to cancel, continue or modify as nothing was found, it stucks there, even if I "show all" again it won't search fine after it happened. How can we make it loop back or simple go back to the main table (or a custom window saying "done go back") after all search results?

Thanks again.

[comment]

I am not sure what you're asking. If you've gone past the earliest date of all records, you must reduce the offset held by the variable (for example by pressing the  [+1] button). To eliminate the 'no records found' dialog, you can use the Set Error Capture script step and add your own error-handling to the script.

[Morenomdz]

I've got it working with set error capture starting at top of script then used an if set variable = -9 (max months I want it to go back) to make it = 1 again, it is working perfectly now. Thanks!

[Agentshevy]

Going through this topic, I was greatly intrigued .. Though very much confused as how you implemented all these and it came to work perfectly. Please, can you upload a sample file of this script (Not your DB file)? It will be really helpful for understanding.. Thanks

[Morenomdz]

It is a bit crude as I got it working but it is not yet implemented on my db, not sure if how and when I will use it still. I've got two buttons, one set to -1 on script parameters and the other to +1. The +1 will do nothing when you are showing the current month records, but will go up one month when you are already back few months. The -1 will go back until it will start showing nothing as your records cease to exist in older months, in my case I wanted it to show only up to 10 months back so I set to when the search returns nothing (no records on that month), it will increase the script parameter back 10 in value. It probably would not work if you have a month without records, wich is not my case. Might want to change the "If" when using with "+1" parameter etc.
 

*LoopMeses

Set Error Capture [ On ] 
Enter Find Mode [ ] 
Set Variable [ $$monthOffset; Value:$$monthOffset + Get ( ScriptParameter ) ] 
Set Field [ Mdz Db A1_Aps::Data; Let (today = Get ( CurrentDate );Date ( Month ( today ) + $$monthOffset ; 1 ; Year ( today ) ) & "..." & Date ( Month ( today ) + $$monthOffset + 1 ; 0 ; Year ( today ) ))]
Perform Find [ ]
If [ Get ( FoundCount ) = 0 ] 
Set Variable [ $$monthOffset; Value:$$monthOffset = +10 ] 
Show All Records End If 
Set Error Capture [ Off ] 

[Agentshevy]

thanks for your reply.. Please do you have a sample file for this.. I am a little bit confused on implementing this script, parameters +1 and -1 (from a beginner perspective)