Formula SQL with related field picklist

Posted by Carsten Zoll on 26-Feb-2017 08:54

I'd like to expand an existing and working expression field. The working phrase is:

rbv_api.selectValue("SELECT SUM(Umsatz) FROM Umsatz WHERE Jahr = #YEAR(CURDATE()) - 1 AND R331557946 = {!id}")

The new Phrase is:

rbv_api.selectValue("SELECT SUM(Umsatz) FROM Umsatz WHERE Jahr = #YEAR(CURDATE()) - 1 AND {!R331557946.Kundentyp} = 'W' AND R331557946 = {!id}")

I get an error with the validate formular button.

The field to calculate with this formula is in the object "Lieferant". Lieferant is related to the object Umsatz.
Umsatz has as related field "Kundentyp". Kundentyp is a picklist.

Rollbase - Forum

All Replies

Posted by Shiva Duriseati on 27-Feb-2017 07:06

Hi Carsten,

Please use multiple queries because you cannot compare related object field against current object using single select query ,only id's can be used to compare in WHERE clause.

Could you please try below code?

var relatedId=rbv_api.selectValue("SELECT id  FROM Lieferant  WHERE Kundentyp = 'W'");

var sum=rbv_api.selectValue("SELECT SUM(Umsatz) FROM Umsatz WHERE Jahr = #YEAR(CURDATE()) - 1 AND R331557946 = "+relatedId+"");

rbv_api.println(sum);

Regards,

Shiva

Posted by Carsten Zoll on 28-Feb-2017 01:55

Hi Shiva,

this seems to be the right way...

Though i get no result (0.00) with this code. Even with a very simple phrase:

var sum=rbv_api.selectValue("SELECT SUM(Umsatz) FROM Umsatz");

rbv_api.println(sum);

Any ideas? "Return" missing?

Regards,

Carsten

 

Posted by Shiva Duriseati on 28-Feb-2017 02:04

Hi Carsten,

Could you please tell me what is the type of field "Umsatz"?

SUM is applicable for fields which are of type  Integer and Decimal . If you want to simply count number of records use COUNT instead of SUM.

Regards,

Shiva  

Posted by Carsten Zoll on 28-Feb-2017 02:14

Umsatz is field type "currency".

and i want sum, not count...

Posted by Shiva Duriseati on 28-Feb-2017 02:26

SUM should work with currency as well  I have tested this its working fine. Can you share your app.xml which version of Rollbase you were using ?

Posted by Carsten Zoll on 28-Feb-2017 02:38

sorry beginner's problem: don't know where to share app.xml.

i can Show you my personal account in a private message

Posted by Shiva Duriseati on 28-Feb-2017 02:48

Can you please create a support ticket for this? I can assist you with further steps.

Posted by Shiva Duriseati on 01-Mar-2017 01:19

Hi Carsten,

Thanks for taking time and joining the call.

As suggested please use "return" statement before the variable "sum".

Please use below links which will help you in building Rollbase apps.

documentation.progress.com/.../RB_User_Guide.pdf

documentation.progress.com/.../

Regards,

Shiva

This thread is closed