Simple Calculation Field Help

[ebrandt]

I have a table called accounts
3 of my fields can return the following values:

Type Dealer/Retail
FreeShipping Yes (Optional)
PermanentLoaner Yes (Optional)

I wanted to capture the configuration of these three fields into a field.
I created a text field called AcctCommConfigCode
I then created a calculation field with the same name to store the result.

The result would return 1 of 8 possible configurations using following key:
Retail = R, Dealer =D, Free Shipping = FS, Permanent Loaner = PL

Possbile Configurations:
R, RFS, RPL, RFSPL, D, DFS, DPL, DFSPL

I Have tried various "if", "elseif" and"Switch" statements and am having trouble with multiple conditions.

In Filemaker I would have used a case statement with the "and" operater.

example:
Case(Type = "Retail, "R",
Type = "Dealer", "D",
Type = "Dealer" and FreeShipping ="FS","DFS",
Type = "Retail" and FreeShipping ="FS","RFS",
Type = "Retail and PermanentLoaner = "Yes", "RPL",
Type = "Dealer" and PermanentLoaner = "Yes","DPL",
Type = "Dealer" and PermanentLoaner = "Yes"and FreeShipping ="Yes","DFSPL",
Type = "Retail" and PermanentLoaner = "Yes"and FreeShipping ="Yes","RFSPL",
"",

How can I recreate this calculation field in servoy. If someone could suppy a small sample of logic, I would greatly appreciate it.

Thanks,
Erich

[pbakker]

I think this would do, haven't tried it though...

var result = ''

if (type == 'dealer')
{
result += 'D'
}
else
{
result += 'R'
}
if ( FreeShipping == 'Yes')
{
result += 'FS'
}
if ( PermanentLoaner == 'Yes')
{
result += 'PL'
}

return result;

[ebrandt]

Thanks, makes sense to me. I will give it a try.

[ebrandt]

I tried your example but it did not work. I did get the following code to work, but there are two issues. First, my records will not update until I edit one of the fields (2,500 records). Second, this is most likley not a very clean coding example.

Thanks again for you help.

if (type == "Retail" && freeshipping == "" && permanentloaner == "")
{
return "R"
}
if (type == "Retail" && freeshipping == "Yes"&& permanentloaner == "")
{
return "RFS"
}
if (type == "Retail" && freeshipping == "" && permanentloaner == "Yes")
{
return "RPL"
}
if (type == "Retail" && freeshipping == "Yes" && permanentloaner == "Yes")
{
return "RFSPL"
}
if (type == "Dealer" && freeshipping == "" && permanentloaner == "")
{
return "D"
}
if (type == "Dealer" && freeshipping == "Yes"&& permanentloaner == "")
{
return "DFS"
}
if (type == "Dealer" && freeshipping == "" && permanentloaner == "Yes")
{
return "DPL"
}
if (type == "Dealer" && freeshipping == "Yes" && permanentloaner == "Yes")
{
return "DFSPL"
}

[pbakker]

I've tried it now and here it works fine.... Are you getting errormessages? What is displayed in the field attached to the Calc?

As for the Calculation not updating: Calculations are only updated when when the record is accessed by Servoy.

So, if you want the entire table to be updated, you'll have to loop through all records on time. Afterwards (when you don't do external updates) everytime when the info is updated, the calculation will follow.

Paul

[ebrandt]

Paul,

OK, I found the problem, I used a double quote instead of a 2 single quotes. (var result = '')

I also got errors when I used the += with a space in between the operators. I thought that whitespace was ignored in the code?

When you say to loop through the records, is there an associated action being preformed to each record as with filemaker like a relookup or something to that effect?

Thanks for the help.
Erich

[pbakker]

OK, good that it works now.... I was wondering why you would have different behavior...

As for the space between operators: I this += is seen as 1 operator, just like ==, && etc. Putting a space inbetween would then create two separate operators, thus causeing the code not to work (properly)

As I'm not a Filemaker person (one of the few is seems ), I do not know if it's similar to the Filemaker actions. I do know that Servoy has to access a record to have the Calculation updated.

Paul

[ebrandt]

Well with filemaker the information is updated immediatly. The only time I would perform loops would be to cycle through a record group to look for a particular condition, or to perform some sort of action to each record. How would a loop (for statement) look to just access each record?

I appreciate your patience as I am trying to get through the learning curve.

[pbakker]

No problem, we've all been there.

As for the loop, please search the forum, it must be there somewhere. I do not know the code from the top of my hat and currently no time to check it, sorry.

I think it's aslo in the documentation somewhere.

Paul

[ebrandt]

Paul,
I found the code:
for(var i=1; i<= controller.getMaxRecordIndex(); i++) { controller.recordIndex = i;}

I updated 2400 records and it did 40 at a time and took about 2 minutes.
When I migrate to Servoy I am going to have many updates to do involving more complex calculations (if I ever get there).

Some of my tables have in excess of 200,000 records. I had figured my migration was going to be quite an effort, this speed seems like it could be an issue. Why would running this code be so slow, is this behavior normal. I am using a MS SQL2000 backend.

[pbakker]

yup, that's your code allright

As for the speed: I guess it depends on your calculation a bit, but if all info is comming from the same record, it looks slow to me. The reason for it being "relatively slow to update many records through Servoy is that each records is updated individually, meaning: for every recordupdate, an SQL update statement is fired at the DB

I do know there are several posts on this issue scattered around the forum, maybe they offer some info. One sollution offered by Servoy is the FoundSetUpdater, but i do not know if it's relavant in the case of updating calculations the first time.

For migration purposes, you could write an SQL update statement, to fill your calculationfields with the proper value. Updating the 200K records with a value like the calculationstatement with which this post started would not take more than a couple of minutes. Once it's done once, you will never have to repeat it. For info on SQL updates: Google the internet, many, many sites there... or post a question and I'll be happy to help where I can...

Hope you'll find your way through the info...

Paul

[Harry Catharell]

Hi Erich,

How does this data get into your tables ?

I ask because it seems like it could be an activity where the data is initially set and then would change very rarely.

With 2400 records updated once at the outset, how often would individual records change following that update ?

How does the 2400 record set get into the table manual keying or import ?

Manual keying would fire the calculation so update is instant.

Import may require a loop be run to then update the calculations but once done the result is stored !

If individual records changed infrequently then the action of modifying the occasional record would fire the SQL calculation and update that specific record instantly.

Also the action of adding a new record would again fire the stored calculation and the data would be updated instantly again.

I suppose what I am saying is that this appears to be a small amount of pain initially looping to set the result in each record but then incremental changes following that would very rarely, if ever, mean that you would have to loop through all records to update the calculation again !

Hope this makes sense

Harry

[ebrandt]

You are right, this would just be done initially when records are imported during roll out. I was just curious as to what was effecting the speed.

Thanks for your help