Trigger when values exceed threshold

[EmmePGN]

Hello, I'm new to Caspio.

I have an Update Form with a calculated field called "Total_Percentage",  that takes the values from other fields.

Code below:

Isnull([@field:PointFactor_Duties_PercentDuty1],0)+Isnull([@field:PointFactor_Duties_PercentDuty2],0)+Isnull([@field:PointFactor_Duties_PercentDuty3],0)+Isnull([@field:PointFactor_Duties_PercentDuty4],0)+Isnull([@field:PointFactor_Duties_PercentDuty5],0)+Isnull([@field:PointFactor_Duties_PercentDuty6],0)

 

What I would like to do is to prevent users from going forward when their total exceeds 100%. 

I also, would like to show them, which field(s)  is/are causing this field to go over the threshold of 100%.

The rules section is not conducive to this goal, as rules section is limited.

Triggered action section forces me to either email or text the users if their total exceeds 100%. I don't want to do this, since the whole point is for users to correct errors immediately.  

Is there anyone out there who can impart me a script or two to help me?

SQL or javascript that would work with Caspio.

Thank you so much.

Emme

 

 

 

 

 

Edited January 6, 2020 by EmmePGN
to attach the picture

[Vitalikssssss]

Hi @EmmePGN,

There are different ways how you can tackle this challenge.

You may use a JavaScript or you create an expression which results in SQL error.

Here is a JavaScript which will hide the "Submit" button if user selection exceed "100":

<script>

function checkLimit(){
  
    let value = document.querySelector('input[id*="InsertRecordYOUR_FIELD"]').value; //enter your actual field name
    let button = document.querySelector('input[id^="Submit"]');

    if (value > 100) {

        button.style.visibility = "hidden";
    }

    else { 

        button.style.visibility = "visible";
    }

}

setInterval(checkLimit, 500)

</script>

This code should go into Footer of the Datapage and also make sure you disabled an HTML editor.

Second option is to have the expression in Calculated value field which results in SQL error, e.g.

CASE WHEN

(Isnull([@field:PointFactor_Duties_PercentDuty1],0)+Isnull([@field:PointFactor_Duties_PercentDuty2],0)+Isnull([@field:PointFactor_Duties_PercentDuty3],0)+Isnull([@field:PointFactor_Duties_PercentDuty4],0)+Isnull([@field:PointFactor_Duties_PercentDuty5],0)+Isnull([@field:PointFactor_Duties_PercentDuty6],0)) > 100

THEN CAST( 'a' + 1 as INT)

ELSE (Isnull([@field:Number_1],0) + Isnull([@field:Number_2],0) + Isnull([@field:Number_3],0))

END

User will see an error if he enters amount which exceeds "100".

Message can be customized in Localization.

Hope this helps.

 

Regards,

vitalikssssss

 

[EmmePGN]

Hi vitalikssssss, thank you for this. I opted to use the SQL syntax that creates a deliberate error message, I think it's more attention-grabber than the disappearing button (so it's more like "the flogging will not stop until everyone gets it" kind of stuff). 

Incidentally, I edited the character and integer combination like so, which works for me and my Update Datapage.  I know this is elementary,  but still....Yeayyy me!

The number of  numeric variables after the character variable 'a' (to be cast) corresponds to the number of fields that I wanted to sum up. Which is 5.

 

CASE WHEN ((

(Isnull([@field:PointFactor_Duties_PercentDuty1],0)+Isnull([@field:PointFactor_Duties_PercentDuty2],0)+Isnull([@field:PointFactor_Duties_PercentDuty3],0)+Isnull([@field:PointFactor_Duties_PercentDuty4],0)+Isnull([@field:PointFactor_Duties_PercentDuty5],0) )
 ) >100)

THEN CAST( 'a' + 1 + 1 + 1 + 1 + 1 as INT)

ELSE

(Isnull([@field:PointFactor_Duties_PercentDuty1],0)+Isnull([@field:PointFactor_Duties_PercentDuty2],0)+Isnull([@field:PointFactor_Duties_PercentDuty3],0)+Isnull([@field:PointFactor_Duties_PercentDuty4],0)+Isnull([@field:PointFactor_Duties_PercentDuty5],0) )

END

 

Ciao!