Formula Samples
- Elizabeth Storrs
https://knowledge.technolutions.net/docs/en/using-formulas-in-queries
Weighted values
(from GMCB Reader Review Form)
Average when values may be missing
To account for ‘no basis for evaluation’ type of responses (from GSBS - CTS Interview Evaluation)
Note that the data type on the fields still needs to be Integer or Real, even if some on the values are not numeric:
(@CTS_IntEval1+@CTS_IntEval2+@CTS_IntEval3+@CTS_IntEval4+@CTS_IntEval5+@CTS_IntEval6)/(((@CTS_IntEval1)?1:0)+((@CTS_IntEval2)?1:0)+((@CTS_IntEval3)?1:0)+((@CTS_IntEval4)?1:0)+((@CTS_IntEval5)?1:0)+((@CTS_IntEval6)?1:0))
If there are values in some but not all the variables in this formula, those will be averaged. There has to be a value in at least one of the variables. Otherwise, you'll get a divide-by-zero error; that's why variable 1 is assumed to be there (nothing conditional, just a literal 1 value), and only variables 2 and 3 will change the denominator based on if there's a value present or not
generic formula: (@var1 + @var2 + @var3) / (1 + ((@var2)?1:0) + ((@var3)?1:0))
https://knowledge.technolutions.com/hc/en-us/articles/360033239372
Turning non-numeric fields into something useful for submission condition formulas:
((@session1)?1:0)+((@session2)?1:0)+((@session3)?1:0)+((@session4)?1:0)+((@session5)?1:0)+((@session6)?1:0)+((@session7)?1:0)+((@session8)?1:0)+((@session9)?1:0)+((@session10)?1:0)+((@session11)?1:0)+((@session12)?1:0)+((@session13)?1:0)+((@session14)?1:0)+((@session15)?1:0)+((@session16)?1:0)+((@session17)?1:0)+((@session18)?1:0)+((@session19)?1:0)+((@session20)?1:0)
was used as a calculation value in a hidden field on an Interview Template in Vet, in order to create a lovely clean submission condition:
Note that submission conditions hold for administrative users as well as applicants; in order to allow admin users to register someone via a form submission when the submission conditions are not met, just add an or statement to the filter that allows for the particular user to submit the form. (Corner case: Patty registered applicants for interviews directly in the scheduler; in order to register people for the event, we have to submit the form, which is looking for a new event registration, and cannot see the old one.)
Like formula
John Michael Cuccia (RHB)
This isn’t Slate doing or not doing anything - it’s your SQL formula. It’s not specific enough. One resource.
http://tutorialsteacher.com
The LIKE operator is used in the WHERE condition to filter data based on some specific pattern. It can be used with numbers, string, or date values. However, it is recommended to use the string values.