TeamDesk Knowledge Base & Support

   Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Use javascript and variables in formulas
It would be extremely helpful to be able to use javascript or some simliar language to manipulate formula results. The formula options are very limiting (e.g. if you want to extract certain text patterns from a text, regex not available)

Also being able to define variables would make formulas much better to use
ID
2021
Category
Setup
Author

bernhard.schulz@compensation2go.com
Date Created
11/6/2023 7:50:01 AM
Date Updated
11/16/2023 11:35:51 AM
Status
New Idea
Score
30
Promoted By
Guillermo Vazquez Anatoliy Zachynskiybernhard.schulz@compensation2go.com
Comments
calvin peters 11/14/2023 4:20:55 PM
>>>>e.g. if you want to extract certain text patterns from a text, regex not available
Perhaps you have figured this out already but...

Why not just perform this task from dbscripts.js ?

Unless I misunderstand something it sounds like it could be performed that way without too much coding. That is the idea of the dbscripts.js file.

For example: inside a text field "the red fox jumped over the lazy dog" to confirm "lazy dog" is contained in a column called f_111111 with a bit of js looking like:

var field = $('input[name="f_111111"]');
if (field.length) {
var fieldValue = field.val();
if (fieldValue.includes('lazy dog')) {
console.log('"lazy dog" is contained in the field f_111111');
} else {
console.log('"lazy dog" is not contained in the field f_111111');
}
} else {
console.log('Field f_111111 does not exist');
}

If you use the 'inspect' in your browser you will find the field id's and names and other attributes belonging to all of the elements on your form.

Use the name attribute rather than the id as per Kirill, the id is a sort of variable value and can not be assumed to be the same from one form render to the next.

'name' attribute can be accessed using jQuery library which is already loaded as part of TD environment so it would be addressed in this example as $('input[name="f_11111"]') where:

"$()" invokes the jQuery javascript library;

the CSS "selector" is '[name="f_11111"]' and the length property is used to check if the field exists. If length is returned as false it means the field is empty.

field.val() returns the content of the field to compare

if this result is compared to the target text using above example it returns true otherwise false.
The actual example as written displays the result in the inspection console of the browser.

This can be limited to look for the target text and field on specific forms using jQuery in a similar way and referencing the EDIT or VIEW of the table your form belongs to. That is found in the URL or also in the 'inspect' console at the top under the tag <body id= body_t_some#...>
Surround the code in a wrapper like this
if(jQuery("body#edit_t_yourTableNumberHere").length) {
...code from above goes here once it's adjusted for your case use...
}
This will only run it on the EDIT form of your target


if(jQuery('body#overview_t_yourTableNumberHere').length) {
... somthing like this would make it run on an overview page...like a dsahboard...
}

if(($('body#preview_t_yourTableNumberHere').length) {
...something like this would make it run on a preview form like a record details page...
}

This is the documentation for this type of solution.
https://www.teamdesk.net/help/database/resources/script-to-run-on-every-page-of-your-database/

It may take a bit of trial and error to figure it out but it is very useful once you do.
Provides solutions to a lot of challenges.

Hope that helps Bernhard et al
I can try and offer more if you get confused or lost... feel free to reach out and I'll try if you need.
bernhard.schulz@compensation2go.com 11/16/2023 9:50:47 AM
Hi Calvin, thanks for commenting! The dbscript.js only changes the columns value on the client side, but not on the server side. So the data would be visually correctly transformed for the user, but not correctly stored in the database. Use case is for example to extract some text via regex or do some more complex calculations with variables involved.
calvin peters 11/16/2023 11:35:51 AM
Hey Bernhard

So, If I understand the issue properly, when you make changes to the column values using dbscripts.js the value appears to change but on SAVE action the value has not been saved to the db...

Assuming that IS the issue, Kirill helped me out with this some time ago .

The UI forms require a POSTBACK action to be invoked on the form in order for the data to be changed and saved in the db.
With jscript, this requires the POSTBACK to be triggered with a "programmatic" update to a column value on your form.

To do this he shared a smal snippet of code which accomplishes that function and can be invoked by your changes. Needless to say, I use it a lot...so thanks Kirill...

This is the function:
if this is all new to you, any line beginning with " // " is a comment; just like in the formula setup with TD.
any lines surrounded by " /* with code in here... */ " is also a comment so neither will be executed
_______________________
// function to force change trigger on browser to enable trigger of default formulas in TD
// run as setValue(TD column, triggering var)
function setValue(name, newValue) {
var inputOrSelect = $('[name= "' + name + '"]');

var oldValue = inputOrSelect.val();
if(oldValue != newValue) {
inputOrSelect.val(newValue);
inputOrSelect.trigger('change'); // swap for .change(); .triggerHandler()...neither seem to update select column properly
}
};
_______________________

As noted above here : // run as setValue(TD column, triggering var)

So if you have a column with the name = f_111111 that you wish to update with a new value

Your function would be called like this

setValue(f_111111, newValue);

When your jscript reaches this line it will execute your setValue() function and insert the new value into your form and trigger the POSTBACK required to update your db so that when the user SAVEs the record, the value is saved with it.

In something a bit more complex than the "lazy Dog" example, the regex comparison can be done to any text value you wish to target

Something like:

var textBody = $('[some targeted text that might contain a credit card]').val(); // the value contained in your column containing the value to be checked against your regex.

// Regular expression for credit card number (16 digits)
var cardNumberRegex = /(\d{4}-\d{4}-\d{4}-\d{4})/; // so this regex would recognize a 16 digit credit card pattern

// Match and Extract the credit card number from the textBody variable above;

var cardNumberMatch = textBody.match(cardNumberRegex); //confirm if the pattern exists

var newValue = cardNumberMatch ? cardNumberMatch[0] : null; // this is a ternery statement which assigns the matching value if it exists to the variable "newValue" and if it does not exist, avoids an error by assigning a "null" value to the variable. Similar to an if() {...} (if cardNumberMatch exists then newValue = cardNumberMatch else null) statement but for binary (true or false) conditions ...simple and very handy statements.

Then use the setValue() function by calling it like this:

setValue(f_111111, newValue);

// this is the function from the top that sets the newValue to your target column " f_111111 " then triggers the POSTBACK on the form so it saves in your db.

I think that's essentially what you're trying to accomplish...but I whipped this together and didn't really test the regex or run the code...

The setValue() function is bullet proof though. Tried, tested and true. Thanks for that Kirill! (in case you read these...:)

So the whole match, extract, update, and postback would look like this:

___________________________
//declare your setValue function to be called later

function setValue(name, newValue) {
var inputOrSelect = $('[name= "' + name + '"]');

var oldValue = inputOrSelect.val();
if(oldValue != newValue) {
inputOrSelect.val(newValue);
inputOrSelect.trigger('change'); // swap for .change(); .triggerHandler()...neither seem to update select column properly
}
};

// declare your variables to be used and set the values

var textBody = $('[some targeted text that might contain a credit card]').val();
var cardNumberRegex = /(\d{4}-\d{4}-\d{4}-\d{4})/;
var newValue = cardNumberMatch ? cardNumberMatch[0] : null;

//call your setValue function to trigger the postback and save your newValue
setValue(f_111111, newValue);

_________________________________

That's it...save your record and the updated value ought to be saved into the target column.

Happy coding Bernhard...
Feedback
 
Back to Search Results