Scripting Search Date Range, range calculated result from text

[buckbuck]

Hello

I am trying to script a search - Search 'Date Ordered' (date field). My steps are

Insert Calculated Result -

Target field, Financial Year Result,

Case (

INVOICES 2008::Financial Year = "Year2011";Date ( 1 ; 1 ; 2011 )&"..."&Date ( 12 ; 31 ; 2011 );

INVOICES 2008::Financial Year = "Jan2011";Date ( 1 ; 1 ; 2011 )&"..."&Date ( 1 ; 31 ; 2011 );

INVOICES 2008::Financial Year = "Feb2011";Date ( 2 ; 1 ; 2011 )&"..."&Date ( 2 ; 28 ; 2011 );

INVOICES 2008::Financial Year = "Mar2011";Date ( 3 ; 1 ; 2011 )&"..."&Date ( 3 ; 31 ; 2011 );

Financial Year is a Value List, Year2011, Jan 2011, Feb2011 etc.

I am trying to design the interface to show Jan2011 rather than a date range 01:01:2011...01:31:2011. It is more elegant.

Enter Find Mode

Set Field[Date Ordered;Financial Year Result

Perform Find

Sort Records

It does not work. Can anyone explain why? Thanks

[LaRetta]

Hello buckbuck,

I do not understand why you are setting Financial Year Result and then turning around and setting Date Ordered with Financial Year Result, instead of just setting the field directly. Calculations, including Case() calculations can be handled using Set Field[]. Insert Calculated Result[] should not be used except in rare situations because the field must be on the layout for it to work ... no such limits with Set Field[].

Financial Year I would assume it is (and should be) a global text field. User makes selection and script runs.

Having said that, I really cringe when I see values hard-coded. And by using Jan2011, Year2011 etc ... your value list will not sort in proper order for your Users. Here is an approach which will sort in order for User selection and also provide the find you request ... all without hard-coding either the value list or the date range values. I hope it helps. :^)

By the way, I suppose you should error trap, but the value list is based upon field values so there will always be records returned in your found set. By using values from field, it is always clear whether records exist for a specific year/month.

ADDED: I modified the script and simplifed the find. ;-)

YrMo.zip

[buckbuck]

Why do you write /100 as in the calculation? What if you want to do a date range? Is this possible?

Year ( Date_Ordered ) + ( Month ( Date_Ordered ) / 100)

& ¶ &

Year ( Date_Ordered )

What if you want to do a date range? Is this possible?

Thanks

[LaRetta]

By creating the calculation, you turn your records into the range so all records fit into either the year or the month/year category of your value list. I am not sure I understand what you are asking ... I believe this functionality gives you exactly what you asked for. If you want to search a free-date range, just search the date field directly. Can you expand your question, please?

[buckbuck]

At the moment the search is defined by the definition of a year or month. What if you want to generate searches defined as a calendar period, like a financial quarter? I found this calculation for a 'financial quarter' on a similar question elsewhere in the forum. I am experimenting with its results now. I do not understand the calculation exactly as my understanding of a divisor is shaky.

I think you used to expression '/100' to get a decimal result as in .2 for January. So the options read better. 2011.1 etc..

Thank you

Matthew

[LaRetta]

Hi Matthew,

Yes, that is correct, it read better and sorts in the value list as a true number; month being decimal. If you want to include quarter, just add the blue to the calculation:

Year ( Date Ordered ) + ( Month ( Date Ordered ) / 100 )

& ¶ &

Year ( Date Ordered )

& ¶ &

Year ( Date Ordered ) & ".Q" & Div ( Month ( Date Ordered ) - 1 ; 3 ) + 1

You can leave out the period before the Q ... whichever you think looks best. But this method removes all hard-coding. If your fiscal starts in different month let us know and we can adjust the calc for you.

[Ocean West]

I prefer this custom function so that you can state ThisYear and get 1/1/2012...12/31/2012 for use in a find.

http://www.briandunning.com/cf/748

Today

Yesterday

Tomorrow

ThisWeek

LastWeek

NextWeek

ThisMonth

LastMonth

NextMonth

ThisYTD

LastYTD

NextYTD

ThisYear

LastYear

NextYear

ThisQuarter

LastQuarter

NextQuarter

[hmethven]

Hello this calculation splits the dates up into 4 quarters but it assumes the fiscal year begins in January.

I would like the fiscal year to begin in October but can't figure out what to change in this calculation to get this result.

Many thanks

Year ( Date Ordered ) & ".Q" & Div ( Month ( Date Ordered ) - 1 ; 3 ) + 1

[LaRetta]

Try changing the last part to: Div ( Mod ( Month ( yourDate ) - 10 ; 12 ) ; 3 ) + 1

[hmethven]

Thanks so much! This is perfect

Can you maybe explain what you changed and how you work it out?

[hmethven]

Hi again, so I've followed the advice here. I am wondering if it would be possible to have an extra option in the dropdown to select all.

So at the top of the Dropdown list there is: "ALL" and on selecting that option it doesn't filter by date.

Many thanks

[LaRetta]

I am not sure how you are using this but I will assume you have a table with a date which this calculation evaluates? If so, you want each line of the calc to turn into:

your calc & CR & "All"

... CR is pilcrow - I cannot make pilcrow on iPad. So if you are using this in a relationship it will appear in pop-up and if selected, it will relate to all child records.

[Lee Smith]

your calc & CR & "All"

& CR & "All" i.e. & ¶ & "All"

[hmethven]

I am using a calculation field with this calc to create the list:

Substitute ( Year ( event_date )

& ¶ &

Year ( event_date ) & " Q" & Div ( Mod ( Month ( event_date ) - 10 ; 12 ) ; 3 ) + 1 ; 20 ; "FY" )

And another global field to select them.

What I'm trying to do is to have the dropdown go like this:

ALL

FY08

FY08 Q1

etc.

& CR & "All" i.e. & ¶ & "All"

Brilliant this is exactly what I needed

Thanks

EDIT: Is there a way for the ALL to always appear at the top of the list?

[LaRetta]

I just realized something ... there can be a mismatch between the real year and the fiscal year and which do you want to list? I believe the calculation is wrong for Matthew (BuckBuck) as well on the last calc we adjusted because of it. Oh I am so sorry that I did not notice the potential mismatch before!

It is because we are leaving the regular year alone when we should adjust it also because we are dealing with fiscal. Also, it would be more efficient to only apply the Substitute() to the portion needed instead of the full calculation. Here is an adjusted calculation which should work for you:

Let ( [

yr = Year ( Event_Date ) + 1 - ( Month ( Event_Date ) < 10 ) ;

fy = Substitute ( yr ; "20" ; "FY" )

] ;

fy

& &para; &

fy & " Q" & Div ( Mod ( Month ( Event_Date ) - 10 ; 12 ) ; 3 ) + 1

& &para; &

"All"

)

Edit ... include the +1 if you call your fiscal year the year when your fiscal ENDS.

[hmethven]

Oh yes! You are correct, thank you so much for your help, I don't think I could have done this alone! Or if I did it would have taken me a huge amount of time.

[LaRetta]

I ADJUSTED THIS CALC ... I had accidently added the +1 - typing from ipad. IIRC, November 3, 2012 should be the first quarter of 2012, right? I've never seen a fiscal that late in the year - it threw me! But be sure to remove the +1 that I've bolded in red!

[hmethven]

From October 2012, it is now Fiscal Year 2013. Q4 2012 ended in September, should I keep the calculation the same or change it now?

[LaRetta]

Not sure where my brain was ... some call start of fiscal year their fiscal; the end is technically correct, I think. But government starts Oct and that WOULD be + 1. I had forgotten about our government ... wishful thinking with the current debates, LOL

[buckbuck]

I noticed something odd on the result for this calculation (as originally presented by LaRetta);

Quote

• "Posted 16 January 2012 - 11:01 AM
  
• Hi Matthew,
  
• Yes, that is correct, it read better and sorts in the value list as a true number; month being decimal. If you want to include quarter, just add the blue to the calculation:
  
• Year ( Date Ordered ) + ( Month ( Date Ordered ) / 100 )
  
• & ¶ &
  
• Year ( Date Ordered )
  
• & ¶ &
  
• Year ( Date Ordered ) & ".Q" & Div ( Month ( Date Ordered ) - 1 ; 3 ) + 1
  

End quote

The month of October is not shown. Am I missing something?

Thanks

Matthew

[LaRetta]

Oh good grief.

I try to think of everything. My apology. It is because the first line is treating the year.month as number so it is dropping the trailing 0. Also, I had responded but deleted it because I noticed that you did not read the following thread where I pointed out that the year must be adjusted for fiscal.

Here is (hopefully) a final calculation which covers both issues:

Let ( [

yr = Year ( Date Ordered ) + 1 - ( Month ( Date Ordered ) < 7 ) ;  //  change number of your fiscal start

m =  Right ( "00" &  Month ( Date Ordered ) ; 2 )

] ;

Year ( Date Ordered ) & "." &  m

& ¶ &

yr  //  or Year ( Date Ordered ) ... fiscal or regular year, whichever you choose

& ¶ &

yr & ".Q" & Div ( m - 1 ; 3 ) + 1

)

[LaRetta]

I have lost track of what this is even for. The FIRST year should be TRUE year not fiscal year, since it combines with the month. I have no idea what you think the second year should represent ... whether just true year or whether just fiscal year. Adjust the calc however you need it to give you what you need. I corrected the above calc

And now can I screw this thread up any more than it already is? I doubt it. :laugh2:

[buckbuck]

Dear LaRetta - I have tried to parse your solution and have been unable to figure out how some aspects of your solution works. First if I make the calculation a Text result and the original missing 10th month shows, and all the quarters work as well. I have dropped the Let ( yr....... ) part because I cannot understand how to change my fiscal start, which would in fact be useful since my financial year starts in February. Can I ask you to look at this thread again and explain a little your arithmatic?

Always grateful

Matthew

[LaRetta]

Hi Matthew,

 

This thread includes many things.  What is confusing about this thread is the mix of fiscal year and regular year in single multi-line calculation.  There is also confusion, even amongst some business owners, on what fiscal year represents.  Some refer to the START as their fiscal year but it is more commonly the END of fiscal which designates the 'fiscal year'.

 

So for example, your fiscal year is February 1, 2012 through January 31, 2013 so would be known as fiscal year 13 because that is when it ends. Using date of December 29, 2012 with February fiscal start, I show the result should be

 

2012.12  <--- calendar year
2013  <--- fiscal year
2013.Q4 <--- fiscal year, quarter

 

So for you the calc might be (see the  " < 2 ) " in the year line?

REMOVED ... please see below

 

The calculation says, take the current year and add 1 (meaning get the year end).  Then subtract 1 if February is not here yet (that is a boolean test between the blue parentheses).  So if you give me specific example of dates and what you want returned and what you are not getting, we can help you with it. :^)

Edited December 30, 2012 by LaRetta

[buckbuck]

Hi LaRetta

Interesting how you think about this.

"Then subtract 1if February is not here yet" What does this tell you?

In my case my Fiscal Year ends January 31, 2012. At the moment my calculation reads;

 

Let (
[
yr = Year ( Date_Ordered ) + 1 - ( Month ( Date_Ordered  ) < 6 ) ;  //  change number of your fiscal start
m =  Right ("00" & Month ( Date_Ordered  ) ; 2 )     
] ;

yr & "." &  m
& ¶ &
yr
& ¶ &
yr & ".Q" & Div ( m - 1 ; 3 ) +1

- I changed your 7 to a 6 to see if I could understand the effect on the equation.

My value list result shows 2013 Q2, 2013 Q3, 2013 Q4 but no 2013 Q1. It also shows only months (20.13.06 through to 2013.12)

I await your reply. Hopefully some insight into the < 6 effect.

Thank you

LaRetta

Correction, January 31, 2013, is my next year end.

Matthew

[LaRetta]

Ah, yes, the calc is off; Matthew.  I am certainly rusty on date calcs.  Try:

Let ( [
yr = Year ( DateOrdered )  - ( Month ( DateOrdered ) < 2 ) ;  //  change number "< 2" to your fiscal start
m =  Right ( "00" &  Month ( DateOrdered ) ; 2 )
] ;
Year ( DateOrdered ) & "." &  m
& ¶ &
yr  //  or Year ( DateOrdered ) ... fiscal or regular year, whichever you choose
& ¶ &
yr & ".Q" & Div ( Mod ( Month ( DateOrdered ) - 2 ; 12 ) ; 3 ) + 1
)

Please note that it is a 2 (which represents your fiscal start month) not a 6 or a 7 so the calculation deducts 1 if month is less than Feb and the quarters have been adjusted as well (DOH, I could not simply divide them).  Sorry for producing forum crud everyone.  Where is Comment when we need him?  LOL.

[buckbuck]

Hello
Is it possible to constrain the found set or increase functionality to the script button
I thought I could add a Set Field (CompanyCountry; ≠ "United States" ) to omit US companies, within the EnterFind Mode/Perform Find. It did not work.

Thanks

[doughemi]

Try

 

Set Field[CompanyCountry; "United States"]

Omit Record

 

Set Field...Omit Record steps should normally be the last steps in a Perform Find criteria list.

[buckbuck]

Doughemi solution did not work. I wonder whether you have read complete thread.

[LaRetta]

You have said twice now ... to two different suggestions ... that 'it did not work' but you are providing no specifics on what steps you took and what didn't work about it. Can you provide more information or hopefully your zipped test file? :^)

[buckbuck]

Not sure I understand LaRetta's comment however I have recreated the set up in the attached file. You will note in the script that the suggestion as I understood it was applied, but it did not work here, so it is disabled. Enable to test yourself.

RDE Test.fp7.zip

[comment]

Try it this way.

RDE 2.fp7.zip

[buckbuck]

Thank you. RDE2.fp7.zip worked perfectly.