MySQL formula - Using MySQL functions

[pepe]

Hi Sergey,
there is some strange behavior of MySQL formulas when using CASE. In development, when adding logic to the formula, everything works fine up to a certain point where the system gives error message:

In order to find error, I commented out certain sections in formula field, including an INSTR(str, Substring) line. In the example below, I have commented out every line in formula field (using the "#" sign). I am not asking to read the logic. The code just provides proof of having commented out every singel line in formula:

#CASE
# Keine Fristen (TODAY wird automatisch eingetragen)
# WHEN get_value([902])=0 THEN '---'
# Meilenstein / Vormerkung (Tatsächlicher Beginn und Fertigstellung müssen identisch sein)
# WHEN get_value([902])>=1 AND get_value([902])<=2 THEN
# CASE # Level 2
# WHEN [692]=0 AND [674]=0 THEN '&cross; Bauaufsicht Terminkontrolle: Tatsächlicher Beginn u. Fertigstellung leer'
# WHEN [692]=0 THEN '&cross; Bauaufsicht Terminkontrolle: Tatsächlicher Beginn leer'
# WHEN [674]=0 THEN '&cross; Bauaufsicht Terminkontrolle: Tatsächliche Fertigstellung leer'
# WHEN [692]<>[674] THEN '&cross; Bauaufsicht Terminkontrolle: Tatsächlicher Beginn &ne; Tatsächliche Fertigstellung'
# ELSE '&check; Bauaufsicht Terminkontrolle: okay'
# END # Level 2
# # Bauzeitplan / Aufgabe / Mängelrüge / Sonstiger Vorgang (Tatsächlicher Beginn muss vor Fertigstellung sein)
# WHEN get_value([902])>2 THEN
# #Test ob Beginn/Fertigstellung Bauzeitplan oder Beginn/Fertigstellung Gewerk schlagend
# CASE # Level 2
# WHEN INSTR([972],'&cross;')=0 AND INSTR([998],'&cross;')=0 THEN
# #Beginn/Fertigstellung Gewerk schlagend
# #Termine Test ob vor Beginn, in Arbeit, nach Fertigstellung
# CASE # Level 3
# #TODAY vor Geplanter Beginn
# WHEN [667]>UNIX_TIMESTAMP(CURDATE) AND [692]=0 THEN '&bull; Arbeit noch vor Beginn'
# WHEN [667]>UNIX_TIMESTAMP(CURDATE) AND [692]>0 THEN '&check; Arbeit vor Datum Beginn Gestartet'
# #TODAY >= Beginn und <=Fertigstellung
# WHEN [667]<=UNIX_TIMESTAMP(CURDATE) AND [668]>=UNIX_TIMESTAMP(CURDATE) AND [692]>0 THEN '&check; in Arbeit'
# WHEN [667]<=UNIX_TIMESTAMP(CURDATE) AND [668]>=UNIX_TIMESTAMP(CURDATE) AND [692]=0 THEN '&cross; Beginn Arbeit Prüfen/Säumig'
# #TODAY nach Geplanter Fertigstellung
# WHEN [668]>UNIX_TIMESTAMP(CURDATE) AND [674]=0 THEN '&cross; Fertigstellung Arbeit Prüfen/Säumig'
# WHEN [668]>UNIX_TIMESTAMP(CURDATE) AND [674]>0 THEN '&cross; Arbeit erledigt'
# ELSE 'ERROR: CI-Terminprüfung Bauaufsicht (Beginn/Fertigstellung Gewerk schlagend)'
# END # Level 3
# WHEN INSTR([972],'&cross;')>0 OR INSTR([998],'&cross;')>0 THEN
# #Bginn/Fertigstellung Bauzeitplan schlagend
# #Termine Test ob vor Beginn, in Arbeit, nach Fertigstellung
# CASE # Level 3
# #TODAY vor Geplanter Beginn
# WHEN [917]>UNIX_TIMESTAMP(CURDATE) AND [692]=0 THEN '&bull; Arbeit noch vor Beginn'
# WHEN [917]>UNIX_TIMESTAMP(CURDATE) AND [692]>0 THEN '&check; Arbeit vor Datum Beginn Gestartet'
# #TODAY >= Beginn und <=Fertigstellung
# WHEN [917]<=UNIX_TIMESTAMP(CURDATE) AND [713]>=UNIX_TIMESTAMP(CURDATE) AND [692]>0 THEN '&check; in Arbeit'
# WHEN [917]<=UNIX_TIMESTAMP(CURDATE) AND [713]>=UNIX_TIMESTAMP(CURDATE) AND [692]=0 THEN '&cross; Beginn Arbeit Prüfen/Säumig'
# #TODAY nach Geplanter Fertigstellung
# WHEN [713]>UNIX_TIMESTAMP(CURDATE) AND [674]=0 THEN '&cross; Fertigstellung Arbeit Prüfen/Säumig'
# WHEN [713]>UNIX_TIMESTAMP(CURDATE) AND [674]>0 THEN '&cross; Arbeit erledigt'
# ELSE 'ERROR: CI-Terminprüfung Bauaufsicht (Beginn/Fertigstellung Bauzeitplan schlagend)'
# END # Level 3
# ELSE 'ERROR: CI-Terminprüfung Bauaufsicht (Beginn/Fertigstellung Bauzeitplan oder Gewerk schlagend)'
# END # Level 2
# ELSE 'ERROR: CI-Terminprüfung Bauaufsicht'
#END CASE

When running Ruko again, it still gives me the error message referring to problems with the INSTR function. I also did empty cache in the browser. I have no idea why the error is still being raised. Are there limits as to how many levels you can perform recurrsions with a formula and/or within Ruko? I ran into similar situations twice before this now. I had to solve to problems by going back to earlier backups and redo the formula input.
Best, pepe

[support]

Hard to say what is it.

[pepe]

Do you think, there is a problem in DB? Please see below screenshot: In some formula fields, there are empty value while others have NULL values or string (as far as I can see, the fields are short_name, tooltip, required_message):

Best, Pepe

[support]

No, I don't think. Looks like issue in query in formula. Remove it. And then add it but with one "case" and if it works then add second "case". Do it step by step and you will find line where issue is.

[pepe]

How do you define the sequence to calculate field values using MySQL formula?
Fields which depend on values of other calculated fields in the same entity may have a problems if those other fields are not being computed/updated at the time when they needed in calculations of other fields. Therefore, there must be some logical sequence established by Ruko to compute the various formula fields? Could you please explain because that sequence might yield to errors I am getting.
Thank you, Pepe

[support]

Well, probably issue can be with "null" value. If you have null in result then it will not calculated sum or can be issue with other conditions. So better use IFNULL to check value from function.

[pepe]

Okay, will implement this! How about sequence of computations?

[support]

When you use formula in formula then it doesn't mean we insert value of formula. We insert full formula query in formula so calculation will work correctly.

[pepe]

Understand, thank you for info!

[pepe]

Update after further testing:
1. I went back to a backup that worked.
2. Implemented formula fields and logic step-by-step, works.
3. Added new fields and moved some formula fields in form listing (Although they are not listed on form itself. However, that way you can change the order in which they appear in field configuration). Got the DB ERRORS again.
4. Went back to working version of database.
5. Implemented step 3. again and now I do not get the DB ERROR.

I am certain, there is some nasty problem in there. Is there a way to get to the line number which is listed by MySQL Error 1064, ...... at line number .....
I have tried to copy the whole thing into notepad++ but there are only a few lines, line breaks seem to be not there or are being erased.
What else can be done to get that bug?
Best, Pepe