How to prevent formula field from getting to long, need a wo

Posted by Wim on 09-Sep-2015 13:36

While firing this loop (Loop_Begin) etc we need to loop thrue a lot of records, that throws an error:

FORMULE FIELD TO LONG;

Here is a piece of my code:

                   
{!#LOOP_BEGIN.all#354700}
 
 
    v_resultdiscountid =  {!discount.id};
    
    // COUNTRY
    
    v_countryid         = "{!discount.R_discount_isocountry}";
    v_country             = "{!discount.R_discount_isocountryl#value}";
                                        
    if(v_countryid.indexOf(",") > 0)    
    {
      //rbv_api.println(v_countryid.indexOf(","));        

      var v_tempcountry = [];      
      v_tempcountry = v_countryid.split(",");
      
      for (i=0; i<v_tempcountry.length; i++)
        {
          v_resultaatcountry = v_tempcountryIdea;           
          //rbv_api.println("WE PRINTEN EVEN EEN REGEL" + v_resultaatcountry);          
          if(v_resultaatcountry == v_isocountryid)
            {
              v_countryres = v_resultaatcountry;
                //rbv_api.println("GEVONDEN COUNTRY " + v_countryres);            
            } // end  if(v_resultaatcountry == v_isocountryid)
        } // end  for (i=0; i<v_tempcountry.length; i++)
    }    // end if(v_countryid.indexOf(",") > 0)    

    else
    {
      v_countryres = v_countryid;
      //rbv_api.println("LAND = " + v_countryres);
    } // end else


    // CONTRACT
    
    v_contractid         = "{!discount.R_discount_contracttype}";
    v_contract             = "{!discount.R_discount_contracttype#value}";
                                        
    if(v_contractid.indexOf(",") > 0)    
    {
      //rbv_api.println(v_contractid.indexOf(","));        

      var v_tempcontract = [];      
      v_tempcontract = v_contractid.split(",");
      
      for (i=0; i<v_tempcontract.length; i++)
        {
          v_resultaatcontract = v_tempcontractIdea;           
          //rbv_api.println("WE PRINTEN EVEN EEN REGEL" + v_resultaatcontract);          
          if(v_resultaatcontract == v_contracttypeid)
            {
              v_contractres = v_resultaatcontract;
                //rbv_api.println("GEVONDEN CONTRACT " + v_contractres);            
            } // end  if(v_resultaatcontract == v_contracttypeid)
        } // end  for (i=0; i<v_tempcontract.length; i++)
    }    // end if(v_contractid.indexOf(",") > 0)    

    else
    {
      v_contractres = v_contractid;
      //rbv_api.println("CONTRACT = " + v_contractres);
    } // end else


    // APPLICATIONTASK

    v_taskid         = "{!discount.R_discount_applicationtask}";
    v_task             = "{!discount.R_discount_applicationtask#value}";
                                        
    if(v_taskid.indexOf(",") > 0)    
    {
      //rbv_api.println(v_taskid.indexOf(","));        

      var v_temptask = [];      
      v_temptask = v_taskid.split(",");
      
      for (i=0; i<v_temptask.length; i++)
        {
          v_resultaattask = v_temptaskIdea;           
          //rbv_api.println("WE PRINTEN EVEN EEN REGEL" + v_resultaattask);          
          if(v_resultaattask == v_applicationtaskid)
            {
              v_taskres = v_resultaattask;
                //rbv_api.println("GEVONDEN APPTASK" + v_taskres);            
            } // end  if(v_resultaattask == v_applicationtaskid)
        } // end  for (i=0; i<v_temptask.length; i++)
    }    // end if(v_taskid.indexOf(",") > 0)    

    else
    {
      v_taskres = v_taskid;
      //rbv_api.println(v_taskres);
    } // end else
    


    
    // LABEL
    v_labelid         = "{!discount.R_discount_tyrelabel}";
    v_label         = "{!discount.R_discount_tyrelabel#value}";
                                        
    if(v_labelid.indexOf(",") > 0)    
    {
      //rbv_api.println(v_labelid.indexOf(","));        

      var v_temptyrelabel = [];      
      v_temptyrelabel = v_labelid.split(",");
      
      for (i=0; i<v_temptyrelabel.length; i++)
        {
          v_resultaattyrelabel = v_temptyrelabelIdea;           
          //rbv_api.println("WE PRINTEN EVEN EEN REGEL" + v_resultaattyrelabel);          
          if(v_resultaattyrelabel == v_tyrelabelid)
            {
              v_tyrelabelres = v_resultaattyrelabel;
                //rbv_api.println("GEVONDEN LABEL" + v_tyrelabelres);            
            } // end  if(v_resultaattyrelabel == v_tyrelabelid)
        } // end  for (i=0; i<v_temptyrelabel.length; i++)
    }    // end if(v_labelid.indexOf(",") > 0)    

    else
    {
      v_tyrelabelres = v_labelid;
      //rbv_api.println(v_tyrelabelres);
    } // end else


    // BRAND

    v_brandid            = "{!discount.R_discount_tyrebrand}";   
    //v_brandidres        = "{!discount.R_discount_tyrebrand#value}";                     

    if(v_brandid.indexOf(",") > 0)    
    {
      //rbv_api.println(v_brandid.indexOf(","));        
      
      var v_tempbrand = [];      
      v_tempbrand = v_brandid.split(",");
      
      for (i=0; i<v_tempbrand.length; i++)
        {
          v_resultaattyrebrand = v_tempbrandIdea;           
          if(v_resultaattyrebrand == v_tyrebrandid)
            {
              v_tyrebrandres = v_resultaattyrebrand;
                //rbv_api.println("GEVONDEN BRAND " + v_tyrebrandres);            
            }    //  end if(v_resultaattyrebrand == v_tyrebrandid)
        }    //  end for (i=0; i<v_tempbrand.length; i++)
    }    // end if(v_brandid.indexOf(",") > 0)    

    else
    {
      v_tyrebrandres = v_brandid;
      //rbv_api.println("GEVONDEN BRAND enkel" + v_tyrebrandres);
    } // end else   



    // SEASON

    v_seasonid            = "{!discount.R_discount_seasonappliance}";   
    v_seasonidres        = "{!discount.R_discount_seasonappliance#value}";    
    
    rbv_api.println("SEIZOENEN " + v_seasonid);   

    if(v_seasonid.indexOf(",") > 0)    
    {
      //rbv_api.println(v_seasonid.indexOf(","));        
      
      var v_tempseason = [];      
      v_tempseason = v_seasonid.split(",");
      
      for (i=0; i<v_tempseason.length; i++)
        {
          v_resultaattyreseason = v_tempseasonIdea;           
          if(v_resultaattyreseason == v_tyreseasonid)
            {
              v_tyreseasonres = v_resultaattyreseason;
                //rbv_api.println("GEVONDEN SEASON " + v_tyreseasonres);            
            }    //  end if(v_resultaattyreseason == v_tyreseasonid)
        }    //  end for (i=0; i<v_tempseason.length; i++)
    }    // end if(v_seasonid.indexOf(",") > 0)    

    else
    {
      v_tyreseasonres = v_seasonid;
      //rbv_api.println("GEVONDEN SEASON enkel" + v_tyreseasonres);
    } // end else   



    // TRANSPORTTYPE

    v_transportid            = "{!discount.R_discount_tyretotransporttype}";   
    v_transportidres        = "{!discount.R_discount_tyretotransporttype#value}";    
    
    rbv_api.println("TRANSPORT " + v_transportid);   

    if(v_transportid.indexOf(",") > 0)    
    {
      //rbv_api.println(v_transportid.indexOf(","));        
      
      var v_temptransport = [];      
      v_temptransport = v_transportid.split(",");
      
      for (i=0; i<v_temptransport.length; i++)
        {
          v_resultaattyretransport = v_temptransportIdea;           
          if(v_resultaattyretransport == v_transporttypeid)
            {
              v_tyretransportres = v_resultaattyretransport;
                //rbv_api.println("GEVONDEN TRANSPORT " + v_tyretransportres);            
            }    //  end if(v_resultaattyretransport == v_transporttypeid)
        }    //  end for (i=0; i<v_temptransport.length; i++)
    }    // end if(v_transportid.indexOf(",") > 0)    

    else
    {
      v_tyretransportres = v_transportid;
      //rbv_api.println("GEVONDEN TRANSPORT enkel" + v_tyretransportres);
    } // end else   



    // RIM

    v_rimid            = "{!discount.R_discount_rimdiameter}";   
    v_rimidres        = "{!discount.R_discount_rimdiameter#value}";    
    
    rbv_api.println("RIM " + v_rimid);   

    if(v_rimid.indexOf(",") > 0)    
    {
      //rbv_api.println(v_rimid.indexOf(","));        
      
      var v_temprim = [];      
      v_temprim = v_rimid.split(",");
      
      for (i=0; i<v_temprim.length; i++)
        {
          v_resultaatrim = v_temprimIdea;           
          if(v_resultaatrim == v_rimdiameterid)
            {
              v_rimres = v_resultaatrim;
                //rbv_api.println("GEVONDEN RIM " + v_rimres);            
            }    //  end if(v_resultaatrim == v_rimdiameterid)
        }    //  end for (i=0; i<v_temprim.length; i++)
    }    // end if(v_rimid.indexOf(",") > 0)    

    else
    {
      v_rimres = v_rimid;
      //rbv_api.println("GEVONDEN RIM enkel" + v_rimres);
    } // end else   




    /* Kijk of er een resultaat is voor LABEL    */

    rbv_api.println("VOORWAARDE " + v_tyrelabelres + " EN " + v_tyrebrandres + " EN " + v_resultdiscountid);

    if (v_tyrelabelres == v_tyrelabelid)
    
    {
    
      //rbv_api.println("Dit zijn de records " + v_tyrelabelid + " " + v_resultdiscountid);
      
      //Vraag het merk nu af
      if (v_tyrebrandres == v_tyrebrandid)
        {
          
          //rbv_api.println("Dit zijn de records LABEL: " + v_tyrelabelres + " en BRAND: " + v_tyrebrandres + " van KortingID: " + v_resultdiscountid);
      
          if (v_tyreseasonres == v_tyreseasonid)
            {
              //rbv_api.println("Dit zijn nog steeds de records LABEL: " + v_tyrelabelres + " en BRAND: " + v_tyrebrandres + " en SEASON" + v_tyreseasonres + " van KortingID: " + v_resultdiscountid);

              if (v_tyretransportres == v_transporttypeid)
                {
                  /*rbv_api.println("Dit zijn nog OVERBLIJVENDE de records LABEL: " + v_tyrelabelres +
                                  " en BRAND: " + v_tyrebrandres + " en SEASON" + v_tyreseasonres +
                                  " en TRANSPORT " + v_tyretransportres +  
                                  " van KortingID: " + v_resultdiscountid);
                  */
                  
                  if(v_rimres == v_rimdiameterid)
                    {
                      rbv_api.println("Uiteindelijk " + v_resultdiscountid);
                    
                      var v_resultrecord = [];
                      
                      v_resultrecord = rbv_api.selectQuery2("SELECT id, R_discount_customers, R_discount_customergroup, discounta_sellpercentage, " +
                                                            "discounta_netsellprice, discounta_selldealerpercentage, discounta_netselldealerprice " +                                                            
                                                            "FROM discount " +
                                                            "WHERE id = ?", 0, 100, v_resultdiscountid);    
                      for (j=0; j<v_resultrecord.length; j++)
                        {
                          // From the selected discount records we will use:
                          var v_discountid                = v_resultrecord[j][0];
                          var v_customer                 = v_resultrecord[j][1];
                          var v_customergroup             = v_resultrecord[j][2];
                          var v_sellpercentage             = v_resultrecord[j][3];
                          var v_netsellprice             = v_resultrecord[j][4];
                          var v_dealersellpercentage     = v_resultrecord[j][5];
                          var v_dealersellprice         = v_resultrecord[j]Devil;
                          
                          rbv_api.println("KLANT = " + v_customer + " Discount = " + v_discountid);
                          rbv_api.println("KLANTgroep = " + v_customergroup + " Discount = " + v_discountid);
                        
                      
                            rbv_api.println("percentage = " + v_sellpercentage);

                            if (v_sellpercentage > 0)
                              {
                                    rbv_api.println("hij is groter 0; " + v_sellpercentage);
                              }  // end if (v_sellpercentage > 0)

                         /*   if (v_sellpercentage > v_temp)
                              {
                                    v_temp = v_sellpercentage;
                              } // end  if (v_sellpercentage > v_temp)
                            */
                            
                            if (v_sellpercentage > 0)                              
                              {
                                // Beoordeel of de gevonden waarde tot deze klant behoort
                                if (v_customer == v_customerid)
                                {
                                  rbv_api.println("GEVONDEN WAARDE KLANT = " + v_sellpercentage);    
                                  
                                  if (v_prevailalways == 1)    // klant gaat altijd voor
                                    {                                                                            
                                        v_temp = v_sellpercentage;                                                                                                                 
                                    } // end  if(v_prevailalways == 1)
                                  
                                  else    // gaat alleen voor indien de waarde hoger is
                                    {if (v_sellpercentage > v_temp)
                                         {
                                            v_temp = v_sellpercentage;
                                         } // end  if (v_sellpercentage > v_temp)                                                
                                    } // end else
                                  
                                  
                                            //return v_temp;      
                                  
                                } //  if (v_sellpercentage > 0)
                                
                                // Als dat niet zo is beoordeel dan of dit record tot de klantgroep van de klant behoort
                                else
                                {
                                  if (v_customergroup == v_customergroupid)
                                     {
                                    rbv_api.println("GEVONDEN WAARDE GROEP = " + v_temp);    
                                    
                                    if (v_prevailalways == 1)    // klant gaat altijd voor
                                    {                                                                            
                                        if ( v_temp === '')
                                        {
                                          v_temp = v_sellpercentage;  
                                        }
                                    } // end  if(v_prevailalways == 1)
                                  
                                      else    // gaat alleen voor indien de waarde hoger is
                                    {
                                      if ( v_temp < v_sellpercentage)
                                        {
                                        v_temp = v_sellpercentage;
                                        } // end  if (v_sellpercentage > v_temp)                                                
                                    } // end else                                                                                                                                                                                    
                                    
                                  }    
                                  
                                  rbv_api.println("GEVONDEN WAARDE Na controle = " + v_temp);    
                                  
                                } // end else
                              }    //  if (v_sellpercentage > 0)
                          
                        } //end for (j=0; j<v_resultrecord.length; j++)
                      
                    } // end if(v_rimres == v_rimdiameterid)
                  
                }    // end  if (v_tyretransportres == v_transporttypeid)
              
            }    // end  if (v_tyreseasonres == v_tyreseasonid)        
          
        }    // end  if (v_tyrebrandres == v_tyrebrandid)      
      
    }    // if (v_tyrelabelres == v_tyrelabelid)

    
                    

{!#LOOP_END.all}                    
                    
                                          

Posted by Mohammed Siraj on 16-Sep-2015 11:53

Rollbase imposes restrictions on the max length of a parsed formula. The max length is configurable via this shared property. 'MaxFormulaSize'.

However, you should consider revising your formula definition as scanning through all records will impact performance. Plus, you are doing significant computation for each record.

On briefly analyzing your code, have deduced that you are selecting records by specific field values. Can you consider using selectQuery api for the same. If the field value is a concatenated string, you can still use LIKE operator in WHERE clause.

All Replies

Posted by Mohammed Siraj on 16-Sep-2015 11:53

Rollbase imposes restrictions on the max length of a parsed formula. The max length is configurable via this shared property. 'MaxFormulaSize'.

However, you should consider revising your formula definition as scanning through all records will impact performance. Plus, you are doing significant computation for each record.

On briefly analyzing your code, have deduced that you are selecting records by specific field values. Can you consider using selectQuery api for the same. If the field value is a concatenated string, you can still use LIKE operator in WHERE clause.

Posted by Wim on 16-Sep-2015 12:39

Thank you for your answer, we found  a solution where we skipped the n to n relation and made it 1 to n, so we can use the select query. Thanks for your suggestion on using LIKE in the WHERE clause.
Regards, Wim Hassink
 
Van: Mohammed Siraj [mailto:bounce-msiraj@community.progress.com]
Verzonden: woensdag 16 september 2015 18:54
Aan: TU.Rollbase@community.progress.com
Onderwerp: RE: [Technical Users - Rollbase] How to prevent formula field from getting to long, need a work arround for this situation
 
Reply by Mohammed Siraj

Rollbase imposes restrictions on the max length of a parsed formula. The max length is configurable via this shared property. 'MaxFormulaSize'.

However, you should consider revising your formula definition as scanning through all records will impact performance. Plus, you are doing significant computation for each record.

On briefly analyzing your code, have deduced that you are selecting records by specific field values. Can you consider using selectQuery api for the same. If the field value is a concatenated string, you can still use LIKE operator in WHERE clause.

Stop receiving emails on this subject.

Flag this post as spam/abuse.

This thread is closed