Let Function and Case Statement to Sum Prices?

[jthawke]

I am attempting to refine how my project calculates a report price. In the past I was just using a Case statement with a series of possible "matches" and then letting the end user deal with entering a custom price if there wasn't a match. However, there are too MANY unmatched so when we need to change pricing we have to change too many strings . . . so we want to use a calc to just add all the parts together. I was hoping to combine a Let Function and a Case Calc to do this and the early parts worked but I've hit a wall in trying to get the parts to add as well as how to handle more than one instance of each variable. What I have so far is this:

Let ( [ T = 10 ; C = 10 ; L = 15 ; E = 10 ; Z = 15 ; N = 80 ; S = 15 ; P = 5 ; V = 10 ; M = 15 ; R = 25 ] ;

Case (

standard_service_modified = "TCLEZ" ; 50 - client_database::service_discount ;

standard_service_modified = "TCZ" ; 35 - client_database::service_discount ;

standard_service_modified = "N" ; 80 ;

standard_service_modified = "R" ; 25 ;

Length ( standard_service_modified ) = 1 and standard_service_modified ≠ "O" ; 20 ;

Sum (PatternCount ( standard_service_modified ; "T" ) > 0 ; T ;

PatternCount ( standard_service_modified ; "C" ) > 0 ; C ;

PatternCount ( standard_service_modified ; "E" ) > 0 ; E ;

PatternCount ( standard_service_modified ; "L" ) > 0 ; L );

0 ))

The letters in the Let Function are the possible codes in the service code for the report. The respective number is of course the price of each code. I have included a couple of exact codes as those get a "batch price", etc.

When you look at the part of the calc where I have the Sum is where it breaks down. Everything about that is working as expected. What I need to be able to do is take a code (ie, TCLE) and have the price calculated by adding the individual parts together. This is important because there could be a report with a code of TCLLEE for example so I need each L and E evaluated individually and then added together for a final price value.

Can this be accomplished using the Let function as stated above? Or should I be going about this logic in a completely different way?

Thank you!

Jason

[comment]

I suggest you change - temporarily - the Sum() to List() so you'll see what you are really summing and understand where your mistake is.

Overall, prices should not be hard-coded in calculation formulas. It is also best to stick to the 'one fact per field' rule.

[jthawke]

I did a quick List and saw some obvious issues. A bit more work and I came up with this, which SEEMS to solve the dilemma. This may be more complicated than necessary, but it will always allow us the ability to change prices by only changing the Let values. We can also add new report functions by adding a new Let function and a new PatternCount calc:

Let ( [ T = 10 ; C = 10 ; L =15 ; E =10 ; Z =15 ; N = 80 ; S = 15 ; P = 5 ; V =10 ; M =15 ; R = 25 ] ;

Case (

not IsEmpty ( custom_report_price ) ; ( custom_report_price - client_database::service_discount ) ;

PatternCount ( standard_service_modified ; "O" ) ≥ 1 ; 0 ;

Length ( standard_service_modified ) = 1 and standard_service_modified = "N" ; 80 ;

Length ( standard_service_modified ) = 1 and standard_service_modified = "R" ; 25 ;

Length ( standard_service_modified ) = 1 ; 20 ;

PatternCount ( standard_service_modified ; "T" ) ≥ 1 and PatternCount ( standard_service_modified ; "C" ) ≥ 1 and PatternCount ( standard_service_modified ; "L" ) ≥ 1 and PatternCount ( standard_service_modified ; "E" ) ≥ 1 and PatternCount ( standard_service_modified ; "Z" ) ≥ 1 ;

( PatternCount ( standard_service_modified ; "T" ) * T ) +

( PatternCount ( standard_service_modified ; "C" ) * C ) +

( PatternCount ( standard_service_modified ; "E" ) * E ) +

( PatternCount ( standard_service_modified ; "L" ) * L ) +

( PatternCount ( standard_service_modified ; "Z" ) * Z ) +

( PatternCount ( standard_service_modified ; "S" ) * S ) +

( PatternCount ( standard_service_modified ; "P" ) * P ) +

( PatternCount ( standard_service_modified ; "V" ) * V ) +

( PatternCount ( standard_service_modified ; "M" ) * M ) +

( PatternCount ( standard_service_modified ; "R" ) * R ) - 10 - client_database::service_discount ;

( PatternCount ( standard_service_modified ; "T" ) * T ) +

( PatternCount ( standard_service_modified ; "C" ) * C ) +

( PatternCount ( standard_service_modified ; "E" ) * E ) +

( PatternCount ( standard_service_modified ; "L" ) * L ) +

( PatternCount ( standard_service_modified ; "Z" ) * Z ) +

( PatternCount ( standard_service_modified ; "S" ) * S ) +

( PatternCount ( standard_service_modified ; "P" ) * P ) +

( PatternCount ( standard_service_modified ; "V" ) * V ) +

( PatternCount ( standard_service_modified ; "M" ) * M ) +

( PatternCount ( standard_service_modified ; "R" ) * R ) - client_database::service_discount ))

If anyone sees something I could do that is a bit less "pedestrian" please chime in! I wanted to post this in any case should someone else be looking for a similar answer.

[comment]

allow us the ability to change prices by only changing the Let values.

And what will happen to existing records when you do that?

[jthawke]

And what will happen to existing records when you do that?

Excellent question . . . but in our instance, when a report is completed, the current "report price" is pasted into a field called "final price". That's what the client is billed and what we see looking back at old reports. This calculation is designed solely for determining the price as the report is created and modified.