Hello,
I’m trying to set up a stored calculation that allows the user to set “manual” codes as overrides or sets the appropriate value automatically. In the example below, if the codes AD or EM are set, they should be used. Else another table will be checked to know if a client has active memberships or has had memberships before. If not, a default value is set.
Is this possible? I cannot seem to get the the current value of the field bp_type_cd into my local variable l_bp_type_cd. No matter if I use bp_type_cd directly or if I use the variable l_bp_type_cd, the type of the variable is “function” (even if I initialize as character) and of course cannot be used the way I’d like…
If it did work, would it be a problem performance wise? I have a couple of thousand rows in the concerned table and a couple of thousand in obj_mbrship. Maybe I should do the calculation in a stored procedure and flush the cache?
Thanks for your help!
function bp_type_cd()
{
var l_bp_type_cd = bp_type_cd;
if ( l_bp_type_cd == "AD" || l_bp_type_cd == "EM" ) {
l_bp_type_cd = l_bp_type_cd;
}
else {
var query = "select count(obj_id), count(activ) from obj_mbrship where bp_id = " + obj_id;
var maxReturedRows = 1;
var dataset = databaseManager.getDataSetByQuery(forms.frm_base.controller.getServerName(), query, null, maxReturedRows);
var l_cnt_activ = dataset.getValue(1,2);
var l_cnt_inactiv = dataset.getValue(1,1);
if ( l_cnt_activ > 0 ){
l_bp_type_cd = 'KD'
}
else if ( l_cnt_inactiv > 0 ){
l_bp_type_cd = 'EK'
}
else {
l_bp_type_cd = 'IN'
}
}
return l_bp_type_cd;
}
xtsr:
Hello,
I’m trying to set up a stored calculation that allows the user to set “manual” codes as overrides or sets the appropriate value automatically. In the example below, if the codes AD or EM are set, they should be used. Else another table will be checked to know if a client has active memberships or has had memberships before. If not, a default value is set.
Is this possible? I cannot seem to get the the current value of the field bp_type_cd into my local variable l_bp_type_cd. No matter if I use bp_type_cd directly or if I use the variable l_bp_type_cd, the type of the variable is “function” (even if I initialize as character) and of course cannot be used the way I’d like…
Are you sure you are using the exact same name for the calculation as the table column, making it a stored calc ?
I tested it with 3.5.7 and 4.0.1 and you can use a stored calc that can accept manual input or set a predefined one.
As for accepting the manual input you can simple do a return with no value and the input is accepted.
xtsr:
If it did work, would it be a problem performance wise? I have a couple of thousand rows in the concerned table and a couple of thousand in obj_mbrship. Maybe I should do the calculation in a stored procedure and flush the cache?
Using a calculation you have no real control over when or how much it’s being triggered. Perhaps you should use a onDatachange method on the field or even a Table Event. Table Events are only triggered when saving data (for the onRecordInsert/Update event).
Also I don’t get your SQL. You are doing a count on obj_id and activ. Since it’s a count you will end up with the same results. A count does count the returned records. I think you might want to use a sum instead. Also shouldn’t the inactiv’s being counted by some inactiv column instead of the PK ?
So your code could look like this (I changed the logic a bit) :
function bp_type_cd()
{
if ( bp_type_cd == "AD" || bp_type_cd == "EM" ) {
return; // accept the manual input
} else {
// set the default return value
var l_bp_type_cd = 'IN',
query = "SELECT count(obj_id), count(activ) FROM obj_mbrship WHERE bp_id = ?",
dataset = databaseManager.getDataSetByQuery(forms.frm_base.controller.getServerName(), query, [obj_id], 1);
if ( dataset.getMaxRowIndex() > 0 ) {
// it could be that there was no result, so always check or else the following code will error.
var l_cnt_activ = dataset.getValue(1,2),
l_cnt_inactiv = dataset.getValue(1,1);
if ( l_cnt_activ > 0 ) {
l_bp_type_cd = 'KD';
} else if ( l_cnt_inactiv > 0 ) {
l_bp_type_cd = 'EK';
}
}
return l_bp_type_cd;
}
}
Thanks for your help. The code looks better now, but something still does not work properly. Let’s say I have the bp_type_cd “AD” already in the database. Now it is overwritten by “IN”. It seems like I’m just not able read the current value of bp_type_cd. It works fine for the active and inactive memberships, though.
I guess, I should go with a script that handles the recalc. The recalc is a bit tricky, but could be done with events. Upon loading the solution I need to recalc the membership table first (active is dependent on the current date), then the bp_type_cd has to be recalced for all entries. Furthermore, I have to do a recalc on the client when a membership is entered (table event on obj_mbrship) or when a client is modified (table event on client table).
As you noticed correctly, my SQL does not work with MySQL. I normally use Oracle, where the count only counts non-null values, which is very handy! However, I’m working with MySQL here, so I use a sum and case construct, which also works fine.