Jump to content

Display only records from specific month


Morenomdz
 Share

This topic is 2803 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

  • Like 2
Link to comment
Share on other sites

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

Duh ... ya I see that now. Thanks

Edited by Lee Smith
Please do not quote the entire posts
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 ] 
Link to comment
Share on other sites

This topic is 2803 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.