Calc for performing find based on value list

[Steve Martino]

Hi Forum :).  

Always wondering/trying to learn if there is an easier way.  This does work but it seems I may have over complicated the calculation.

Basically I am selecting from a drop down (global), a value in a value list that will give me a range of aging criteria.  Based on that criteria, convert to dates to perform a find (and sort).  This is for a basic aging report for finding balances due.

The calculation is for the Set Field script step.

Here's the Value list:

All
0-15
16-30
31-45
46-60
61-90
90+

And the screenshot shows the calc.  I figured 90+ and 'All' had to be calculated differently (for the way I'm trying it), and the other ranges can be converted the same basic way.

It does work, but like I said, always trying to learn if there is a better way.

Thanks for looking.  All comments, criticism welcomed.

Edited September 12, 2015 by Steve Martino
typo

[comment]

Well, there's really no point in using the Let() function that way; the Case() function does all the work, and placing its result in a variable just to spit it right out serves no purpose other than to complicate the calculation unnecessary.

OTOH, you should use a variable in order to perform the Substitute(0 part only once.

 

P.S. Post your code as code, so we don't have to retype it.

[Steve Martino]

Thanks Comment, for...well...your comments

P.S. Post your code as code, so we don't have to retype it.

Sorry about that but I can't remember where I saw how to do that.

[comment]

Sorry about that but I can't remember where I saw how to do that.

Copy/paste? For best results, click the 'Add code' button [<>] and paste your code into the resulting window.

[Steve Martino]

Let (
AgeNum=
 Case(
   ImportFromQB::gAging = "All"; "<" & Get(CurrentDate);
   ImportFromQB::gAging = "90+" ;"<" & Get(CurrentDate)-90;
   Get(CurrentDate) - RightWords (Substitute ( ImportFromQB::gAging ;"-" ; "|" );1) & "..." & Get(CurrentDate) - LeftWords (Substitute ( ImportFromQB::gAging ;"-" ; "|" );1)
 );
AgeNum

)

Ahhh..thanks.  Didn't know about the <>

Edited September 14, 2015 by Steve Martino
typo