Jump to content
  • 0
Sign in to follow this  
EmmePGN

Trigger when values exceed threshold

Question

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

 

 

 

 

 

trigger for when exceeding threshold value.png

Edited by EmmePGN
to attach the picture

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 1

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".

EHC5oo6.png

Message can be customized in Localization.

Hope this helps.

 

Regards,

vitalikssssss

 

Share this post


Link to post
Share on other sites
  • 0

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!  

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...