loop records and satisfy a given condition.

Posted by shilpi.agarwal3373 on 21-Dec-2015 01:34

Hi all,

I have defined a table Customer which contains 2 fields Custid (PK) and custname in sql server2014. I want to validate records with unique custname in my rollbase app through trigger type Validate Record Data 'before create' a new record. When I create a new customer record then it should validate the records with custname whether a new custname does not match with existing customer name.If new Customer Name matches with existing one then an alert should appear 'Customer name  ...  already exists'.

So how to loop through all records that satisfy a condintion in rollbase?

Rollbase - Forum

All Replies

Posted by Srinivas Panyala on 21-Dec-2015 01:53

You can achieve the same without using triggers, by enabling this property "Do not allow duplicate values in this field." at in field definition.

Customer Object Definition -> Click on Edit link of the field custname -> Check the property "Do not allow duplicate values in this field" and save. Now try to create a new record. It will validate all existing customer names

Thanks

Srinivas

Posted by shilpi.agarwal3373 on 21-Dec-2015 02:08

Hi,

This works fine. But I want to use it with server side scripting in formula header of trigger. So how to loop records that satisfy the given condition?

One more thing : we know sql triggers(which are defined in sql server) does not work in rollbase, as "System Errror  appears".

so my question is "Is there any way to run sql triggers in my rollbase application? What should be the minimal changes in rollbase to work with sql triggers? Because triggers are 50 or 100 lines of code so do we need to recreate a same trigger in rollbase?

Please suggest.

Posted by Srinivas Panyala on 21-Dec-2015 02:45

You can use rbv_api.selectQuery("SELECT ... FROM ..", maxRows, arg1, arg2...) Server API to get the records. Execute rbv_api.selectQuery("SELECT count(*) FROM Customer", 1) to get Count of records. Then execute the select query to get all the records.It returns a double dimensional array. Now you can iterate through this array and implement the logic to perform the validation.

Example:

var count=rbv_api.selectQuery("SELECT count(*) FROM customer", 1);

rbv_api.println(count[0][0]);

var names=rbv_api.selectQuery("SELECT customername FROM customer", count[0][0]);

rbv_api.println(names[0][0]);

Thanks

Srinivas

Posted by shilpi.agarwal3373 on 21-Dec-2015 03:29

Hi all,

we know sql triggers(which are defined in sql server) does not work in rollbase, as "System Errror  appears".

so my question is "Is there any way to run sql triggers in my rollbase application? What should be the minimal changes in rollbase to work with sql triggers? Because triggers are 50 or 100 lines of code so do we need to recreate a same trigger in rollbase?

- See more at: community.progress.com/.../77529

Posted by Srinivas Panyala on 21-Dec-2015 03:53

You can use custom triggers. I am not sure this works for you. In customer trigger, you have to write java code to invoke DB trigger. This custom trigger can be invoked on 'before create' of a new record.

Custom trigger documentation is available @ www.rollbase.com/.../customt.zip

Thanks

Srinivas

Posted by Srinivas Panyala on 22-Dec-2015 04:33

Hi Shilpi,

I was wrong. we cannot invoke database triggers from Rollbase. Database triggers are only executed before/after rows are inserted/updated/deleted in the database.

Thanks

Srinivas

This thread is closed