Help with package calculation form

(@codepeople)

Hello @turewib

You can use conditional statements in the equations, data sources, plain JSON objects, etc.

I’ll describe one of these alternatives, the use of conditional statements.

Assuming the form includes a dropdown field (fieldname1) with three choices, Basis, Pro, Enterprise, and a number field to enter the number of employees (fieldname2).

Insert a calculated field in the form, and enter an equation similar to the following one:

(function(){
    if(fieldname2 < 25) return 'N/A';
    switch(fieldname1)
    {
        case 'Basis': 
            if(fieldname2 <= 74) return 150;
            if(fieldname2 <= 199) return 120;
            if(fieldname2 <= 349) return 80;
            if(fieldname2 <= 499) return 50;
            return 30;
        case 'Pro':
            if(fieldname2 <= 74) return 'N/A';
            if(fieldname2 <= 199) return 145;
            if(fieldname2 <= 349) return 95;
            if(fieldname2 <= 499) return 65;
            return 45;
        case 'Enterprise':
            if(fieldname2 <= 199) return 'N/A';
            if(fieldname2 <= 349) return 110;
            if(fieldname2 <= 499) return 80;
            return 55;
    }
})()

Best regards.

Thread Starter

(@turewib)

Hi, thanks! This makes perfect sense, and works like a charm.

Thread Starter

(@turewib)

Hi, the original question was answered above, but I now realize we have additional complexity to figure out, and that I need help with.

Example:
Number of employees: 100
Package: Basis

The calculation above uses a ‘stepped’ price range, where all 100 employees will cost 120, i.e. a total price of 12000.

What we need is for the first 74 to cost 150, and the remaining 26 to cost 120, i.e. a total price of 11100+3120=14220

In Excel the formula will be something like this:
=IF(B6>=C2;((B6-C2)*D3+C2*D2);”N/A”)

Plugin Author

(@codepeople)

Hello @turewib

The process is simple, you need some basic operations:

(function () {
    var result;
    if (fieldname2 < 25) return 'N/A';
    switch (fieldname1) {
    case 'Basis':
        result = MIN(fieldname2, 74) * 150;
        result += MAX(0, MIN(fieldname2, 199) - 74) * 120;
        result += MAX(0, MIN(fieldname2, 349) - 199) * 80;
        result += MAX(0, MIN(fieldname2, 499) - 349) * 50;
        result += MAX(0, fieldname2- 499) * 30;
        return result;
    case 'Pro':
        if (fieldname2 <= 74) return 'N/A';
        result = MIN(fieldname2, 199) * 145;
        result += MAX(0, MIN(fieldname2, 349) - 199) * 95;
        result += MAX(0, MIN(fieldname2, 499) - 349) * 65;
        result += MAX(0, fieldname2- 499) * 45;
        return result;
        
    case 'Enterprise':
        if (fieldname2 <= 199) return 'N/A';
        result += MAX(0, MIN(fieldname2, 349) - 199) * 110;
        result += MAX(0, MIN(fieldname2, 499) - 349) * 80;
        result += MAX(0, fieldname2- 499) * 55;
        return result;
    }
})()

Best regards.

Thread Starter

(@turewib)

Thanks! This works realy well, only I can’t seem to get an output for ‘Enterprise’ for some reason? It will display the N/A just fine, but no calculation result (which works great for Basis and Pro). Ideas?

Plugin Author

(@codepeople)

Hello @turewib

I’m sorry, there is a typo in the equation. Please, use the following one:

(function () {
    var result;
    if (fieldname2 < 25) return 'N/A';
    switch (fieldname1) {
    case 'Basis':
        result = MIN(fieldname2, 74) * 150;
        result += MAX(0, MIN(fieldname2, 199) - 74) * 120;
        result += MAX(0, MIN(fieldname2, 349) - 199) * 80;
        result += MAX(0, MIN(fieldname2, 499) - 349) * 50;
        result += MAX(0, fieldname2- 499) * 30;
        return result;
    case 'Pro':
        if (fieldname2 <= 74) return 'N/A';
        result = MIN(fieldname2, 199) * 145;
        result += MAX(0, MIN(fieldname2, 349) - 199) * 95;
        result += MAX(0, MIN(fieldname2, 499) - 349) * 65;
        result += MAX(0, fieldname2- 499) * 45;
        return result;
        
    case 'Enterprise':
        if (fieldname2 <= 199) return 'N/A';
        result = MAX(0, MIN(fieldname2, 349) - 199) * 110;
        result += MAX(0, MIN(fieldname2, 499) - 349) * 80;
        result += MAX(0, fieldname2- 499) * 55;
        return result;
    }
})()

Best regards.

Thread Starter

(@turewib)

Hi, thanks but I think something else is wrong. Now I can only get ‘Enterprise’ to output a much too low number (i.e. multiplying is wrong), but I’m unable to figure out why.

Plugin Author

(@codepeople)